update last entry in sign in/out log

[mrbboy]

Hello.

I have two forms, one for signing in and another for signing out. The sign in form has cbo_Employee and txt_SignInTime. The sign out form has cbo_Employee and txt_SignOutTime. The table structure is:

tbl_SignIn:

Employee (Text)
Sign In Date (date)
Sign Out Date (date)

I am using the code below behind cmd_SignOut button in the form frm_SignOut to enter the sign out information into the table. This works great if the employees signs in everytime. However, when they sign out without signing in, the sign out overwrites the last sign out entry. What I need is for this to somehow check the sign in table to see if the sign out information already has an entry and if it does, it just creates a new row in the table without overwriting anything. Please help.

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim strcriterium As String
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tbl_SignIn", dbOpenDynaset)
strcriterium = "EMPLOYEE = '" & Me.EMPLOYEE & "'"
Rs.FindLast (strcriterium)
If Not Rs.NoMatch Then
Rs.AddNew
Rs.Edit
Rs("SIGN OUT DATE") = Date
Rs.Update
End If
Rs.Close

 

[HarleyQuinn]

How about just holding a boolean value (blnSignedIN for example, could even be a property of the sign out form) and setting it to true when someone is signed in? That way you can validate against that and change your record adding process accordingly.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[mrbboy]

I am not familiar with that. Please explain.

 

[PHV]

Why not simply replace this:
strcriterium = "EMPLOYEE = '" & Me.EMPLOYEE & "'"
with this ?
strcriterium = "EMPLOYEE = '" & Me.EMPLOYEE & "' AND [SIGN OUT DATE] Is Null"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886