Access as a timeclock

[birchleg]

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!

[June7]

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.

[birchleg]

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.

[pbaldy]

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.

[birchleg]

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.

[June7]

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.

[birchleg]

Okay, I'm going to attempt this, thank you for taking the time, I appreciate it very much.

[birchleg]

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.

[pbaldy]

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.

[birchleg]

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!

[June7]

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