Results 1 to 11 of 11
  1. #1
    birchleg is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    6

    Access as a timeclock

    I have mysql setup on the backend and it's hosting my timeclock database which has two tables I need to work with, but I have no idea how to make access do what I want. In one table I have the employee information including RFID number. In the other table, I have the punch in information that get populated by a web frontend. I want to add the ability to punch in via RFID using an MS Access front end. I don't know how to get the database to take the rfid number, associate it with the employee name (both in one table) and put that information into the punch table with the current time and date.



    In other words I want a blank form with the focus on a singular input box that when I scan the RFID card, it translates the number to a name and place the name in the appropriate table with the current time and date. In a nut shell, that's it.

    If anyone could please help me, I would very much appreciate it. I never ask for help, but I've spent all day trying to think about this and my brain just doesn't understand databases. Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,620
    Why would you need to save the name? Just record the ID. The name can be retrieved by query joining tables on the primary/foreign key fields. That's what a relational database is for.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    birchleg is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    6
    I was thinking about this all wrong. I was trying to conform to the limitations of the PHP page that generates the report. Instead I should just use the power of access the generate the reports. Okay, that's is give me allot more functionality with the data, now my only dilema is creating a form to do the punch in. I'll figure the query out later.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    I haven't used RFID cards, but I have used barcode scanners and magnetic card swipers. Each basically sends its data plus a tab/enter (or can be configured to). Presuming the RFID card does something similar, you program the before and after update events of a textbox to handle the input and do what you want with it. I use the before update event to validate the data and the after update event to process it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    birchleg is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    6
    I apologize, I know I'm way in over my head. I understand what you are saying, I just don't know how to do it. I work for an elementary school and we have a budget of zero dollars, so I'm constantly piece mealing solutions together and making things work. My biggest problem is I don't know how to give up. I'm extrememly technical, but for some reason, databases just stump me. I just don't have the brain for it. As for the RFID reader, yes, it is just like a bar code reader. It inputs it's characters then actuates a return.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,620
    Build a form that has a RecordSource of the table you want to store the ID. Create a textbox on the form that is bound to the ID field of table. Scan the ID and a value will be entered into the field. Close the form or move to another record or save record will commit the record to table. So as pbaldy stated will need some code that will:
    1. verify the ID entry is valid
    2. verify the entry not an accidental repeat
    3. set the date/time value
    3. save the record

    Alternative is an unbound (no RecordSource) form and an unbound textbox. Code will use SQL INSERT action to save record with ID, date/time to table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    birchleg is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    6
    Okay, I'm going to attempt this, thank you for taking the time, I appreciate it very much.

  8. #8
    birchleg is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    6
    Here are the two tables I am working with.

    employees
    empfullname
    rfid
    tstamp
    password
    displayname
    email
    groups
    office
    admin

    info (punch table)
    fullname (same as empfullname in employee table)
    inout
    timestamp
    notes
    ipaddress
    Should I create an RFID column in the "info" table too since that is were the punch info is going to go, or can I somehow have the fullname populated by association to the unique rfid number? Do any of these need to have a relationship established.
    I tried putting some SQL code in there like you said but apparently my syntax was not correct.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    As I think June7 mentioned, the only field I would have in the info table from the employee table is rfid, presuming that's the key field in the employee table.

    Hard to say what might be wrong with your code without seeing it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    birchleg is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    6

    Talking

    Okay, I know I'm driving you nuts, but can you show me the code for a form text box that does this:
    1. accepts input (the RFID which is ten numbers)
    2. uses that rfid input to query table "employee" which has an rfid column
    3. resolves the rfid to the "empfullname" column in that table
    4. inserts that "emfullname" value into table called "info" in a column called "fullname"
    5. adds a timestamp in the same table in column "timestamp"
    6. updates the timestamp in the "employee" table (column tstamp)
    7. ready for next record.
    This is exactly what I want to do. If I can get someone to show me how to do that, I would be in heaven. I've been scouring the net and trying different bits of code and I just can't seem to make this happen. Thanks!

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,620
    Use the AfterUpdate event of the rfid data entry control for code. I use only VBA, not macros. Search Access Help on topic 'Introduction to Programming in Access'.

    1. type or scan or select from droplist of combobox
    2. DLookup("rfid","employee","rfid=" & Me.rfidCOMBOBOX)
    3. why?
    4. why? as I mentioned earlier, should only save the rfid to time record
    5. Me!timestamp = Now()
    6. why?
    7. Have the form set to DataEntry Yes, then: Me.Refresh
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums