I'm actually working on a Timeclock App myself!
Originally, I went with a table setup similar to yours, however, after putting in some data and trying to generate Reports off of it, I decided to make a few changes to the Times Table (in mine, I call it the Timeclock Table):
Timeclock Table:
TCEntryID - Autonumber, PK
TCUserID - Long Integer, FK (to Users.UserID), Required
TCDateIn - Date/Time, Formatted as Short Date, Required
TCTimeIn - Date/Time, Formatted as Long Time, Required
TCDateOut - Date/Dime, Formatted as Short Date, Not Required
TCTimeOut - Date/Time, Formatted as Long Time, Not Required
TCModUserID - Long Integer, Not Required
TCModTimestamp - Date/Time, Not Required
Basically, this gives me a "clock in" and "clock out" Field for each Record, making it much easier to calculate hours worked and to modify if, say, an employee forgot to clock in/out. And it makes it easier to generate the various "hours worked" Reports.
Also, instead of having a username/password setup, I just assign a (random) PIN to each user that they use to log entries. Its much easier (and quicker) to type and people (my users at least) are more used to keeping a PIN secret than a password so they're less likely to share it with others.
The VBA of my clock in/out button is as follows:
Code:
Private Sub Button_Enter_Click()
On Error GoTo Error_Button_Enter_Click
DoCmd.Hourglass True
Dim db1 As DAO.Database
Dim rstLoginAttempts As DAO.Recordset
Dim rstTimeclock As DAO.Recordset
Dim rstUsers As DAO.Recordset
Dim boolRst As Boolean
Dim dteNow As Date
Dim nbrUserID As Long
Set db1 = CurrentDb()
Set rstLoginAttempts = db1.OpenRecordset("SELECT * FROM LoginAttempts WHERE False", dbOpenDynaset)
Set rstUsers = db1.OpenRecordset("SELECT [UserID] FROM Users WHERE [UserPIN]='" & Me!PIN & "'", dbOpenForwardOnly)
dteNow = fGetServerTime() ' Get the current time from the local Domain Controller (to prevent a user from changing their local time if they're late)
With rstLoginAttempts
.AddNew
!AttemptDate = Format(dteNow, "Short Date")
!AttemptTime = Format(dteNow, "Long Time")
!AttemptSysUser = Get_Username() ' Get Domain user currently logged on to workstation
!AttemptSysWorkstation = Get_Hostname() ' Get workstation's WINS name
.Update
End With
If rstUsers.RecordCount = 0 Then
' If the PIN entered doesn't match that of any users
MsgBox "Incorrect PIN entered. Please try again."
Me!PIN.SetFocus
Else
' If a valid PIN has been entered
' Search for a timeclock entry for the user with no clock-out time
Set rstTimeclock = db1.OpenRecordset("SELECT * FROM Timeclock WHERE [TCUserID]=" & rstUsers("UserID") & " AND [TCDateOut] Is Null AND [TCTimeOut] Is Null", dbOpenDynaset, dbSeeChanges)
boolRst = True
If rstTimeclock.RecordCount = 0 Then
' If an entry with no clock-out time IS NOT found, create a new clock-in entry
With rstTimeclock
.AddNew
!TCUserID = rstUsers("UserID")
!TCDateIn = Format(dteNow, "Short Date")
!TCTimeIn = Format(dteNow, "Long Time")
.Update
End With
MsgBox "Clocked in at " & Format(dteNow, "h:mm AMPM") & "."
Else
' If a clock-in entry is found, add a corresponding clock-out entry by filling in the two blank fields in the Record
With rstTimeclock
.Edit
!TCDateOut = Format(dteNow, "Short Date")
!TCTimeOut = Format(dteNow, "Long Time")
.Update
End With
MsgBox "Clocked out at " & Format(dteNow, "h:mm AMPM") & "."
End If
' Reset the Form for the next user/entry
Me!PIN.SetFocus
Me.PIN = vbNullString
End If
Function_Closing:
On Error Resume Next
DoCmd.Hourglass False
If boolRst = True Then
boolRst = False
rstTimeclock.Close
Set rstTimeclock = Nothing
End If
rstLoginAttempts.Close
Set rstLoginAttempts = Nothing
rstUsers.Close
Set rstUsers = Nothing
Set db1 = Nothing
On Error GoTo 0
Exit Sub
Error_Button_Enter_Click:
' If there's an error, save it to the database for debugging purposes
Log_Error Err.Number, Err.Description, nbrUserID, "frmTimeclock", "Button_Enter_Click", nbrUserID & ";" & Now()
' Alert the user of an error
MsgBox "Unable to clock in/out. Please contact your Systems Administrator."
Resume Function_Closing
End Sub
I'm currently working on adding encryption support so I can secure the PINs. It's a pain though, because about half of the locations are running Access 2000 which doesn't natively support the Cryptographic API :/