Rabbit is exactly correct on this.
Just for those that follow, this has a lot to do with
Database Normalization and Table Structures.
The Kids are either outside or down for a nap so I have a little time to give you an example of how I would handle this... hopefully this will inspire you.
So, let's do something
very simple here and note that I am omitting a lot of things here in that I have no real need of time clock at home :) :
PK = Primary Key
FK = Foreign Key
tbl_employees
[employees_pk] autonumber
[employees_badge] text(20), indexed, required, no duplicate
[employees_fname] text(20) required
[employees_lname] text(20) required
[employees_pin] text(8) required
[employees.... ] the remaining fields
tbl_punchtype
[punchtype_pk] autonumber
[punchtype_Text] text(20) required
tbl_timepunch
[timepunch_pk] autonumber
[timepunch_fk_employees] numeric(long), 1:M with tbl_employee
[timepunch_fk_punchtype] numeric(long), 1:M with tbl_punchtype
[timepunch_dateandtime] date, required
Now the tbl_punchtype table isn't strictly needed; however, instead of command buttons, I'd use a combobox and this table as the recordsource for it. I'd also have just two values, IN and OUT; however, you might need to have a more detailed reason for the punches. Say you have a company policy where you need to be able to prove that the employees have taken their breaks or lunch, in which case, this design allows you to add such detail without having to re-write a ton of code.
I might make a binary index on the first and last names to prevent duplicated employee names... or add a third field form the middle intial/name. It is concevable to have 2 or more people named "John Smith" working for you; however, I doubt that the middle name would also be the same... what do I know... I'm a chemist and all of my stuff have unique names so that we don't unknowningly make stuff that goes booom or whooooooooffffff in the lab. (note the unknowningly, sometimes the snap-n-pop is the fun part!)
The pin code I would store as either the MD5 or SHA2 digests you can find some information on these in the
Bytes > Sitemap > Microsoft Access / VBA Insights documents.
The [employees_badge] is the company's current tracking (the company I work for is on it's 2nd such incarnation and if we get a new time clock system... more than likely a third).
The form would have: label with the current date and time, combobox for the employee badge, combobox for the punch type, a textbox for the pin, and finally a command button to commit the punch.
Note: The comboboxes would be my defacto lookup functions. These would have two columns with the first being the primary key for to the related table, and the second being the human readable. The first column would be bound to the control.
I would use the ontimer event to update the label with the current time every minute.
Events would happen as follows:
Employee makes the correct selections in the CBO, enters the Pin, and clicks the Punch CMDBTN.
THe CMDBTN would the have the following
Capture the currently shown time to local variable.
Call to the Sub module where the employee validation happens, I would use a function that returns a true/false
Then the logic to create a new record.
As this code in various forms is readily available all over the net, here is a simple code based on the above and DAO object:
NOTE:
This is partially aircode. I have made no attempt to debug the following for syntax errors. Even then, I'm fairly certain that what code is here is correct and will function correctly when used in context.
- '(Note: A lot of code is being omitted here i.e. the event header)
-
'
-
Dim zdb As DAO.Database
-
Dim zpunchrs As DAO.Recordset 'punch recordset
-
Dim zstrpunchtime As String
-
'more type classing as needed.
-
'
-
'the on timer event has been updating the displayed time in
-
'the correct formating for data entry.
-
'so just to be sure that the time is "frozen" for this punch
-
'let's just store it for the moment and format it for
-
'the time date field.
-
zstrpunchtime = "#" & me.z_lbl_currentime.caption & "#"
-
'
-
'code to validate that the controls are not empty
-
'set the focus back to that control if it is
-
'I'd use a for each loop checked against the control name
-
'
-
'Call to the employee validation function
-
'returning true if the employee badge and pin are found
-
'in the correct pairing.
-
'
-
If fnc_employeegood Then
-
Set zdb = CurrentDb
-
'
-
'now, note, we are only going to add a record;
-
'thus, there's no need to read the table into memory
-
'just look at the table, create the new record, and
-
'get out.
-
'
-
Set zpunchrs = zjdb.OpenRecordset("tbl_timepunch",dbOpenDynaset,dbAppendOnly)
-
'
-
'Add the new record
-
zpunchrs.AddNew
-
zpunchrs![timepunch_fk_employees] = _
-
me.z_cmbbx_employee.Value
-
zpunchrs![timepunch_fk_punchtype] = _
-
me.z_cmbbx_employee.Value
-
zpunchrs![timepunch_dateandtime] = _
-
zstrpunchtime
-
zpunchrs.Update
-
Else
-
Msgbox "Either the Employee Badge Number" & _
-
vbcrlf & " or the PIN entered is invalid" & _
-
vbcrlf & "If you think they are correct" & _
-
vbcrlf & "please contact any supervisor and" & _
-
vbcrlf & "the HR departement.", vbOKOnly+vbCritical, _
-
"Unable to Log Punch"
-
End If
-
'
-
'Clean up
-
'if you set it, clear it.
-
'if you open it, close it.
-
If Not zjpunchrs Is Nothing Then Set zjpunchrs = Nothing
-
If Not zjbrs Is Nothing Then Set zjbrs = Nothing
-
'
-
'error traping code and the end module
Notice that I didn't record the actual employee name, badge number, nor the text for punch type in table tbl_timepunch? Instead, I stored the value for the PK from the tbl_employee and tbl_punchtype as a FK in the tbl_timepunch table. When we go to pull the information for a report or other reasons, we can refer to these tables and pull the required information.
As for the employee PIN... my company uses the employee badge as the PIN. We're supposed to keep this information to ourselves; however, in my position as one of the senior staff I know all of the employee badges as does any member of Management. Having a PIN I think is a really good idea, so long as it is not stored
except as a digest as I noted earlier. MOST management types and I think people in general are trust worthy; however, people get angery with each other and other issues, and the next thing you know, people are making some poor choices and messing with the timecards to get others in trouble... seen it happen!
The remaining queries and code I leave for the user.
13
