Will Not update last record in Table

[mrbboy]

I am building a table, table1, with the following fields:
Name (text)
Sign in date (date/time)
Sign in time (date/time)
Sign out date (date/time)
Sign out time (date/time)

I have 2 forms that enter data into table1, sign in form and the sign out form. The sign in form contains a combo box for the employees names, a text box for the sign in date and another text box for the sign in time. The sign out form contains a combo box for the employee's names, a text box for the sign out date and another text box for the sign out time.

I need the sign out information to always fill in the sign out date and sign out time fields from the employee's last sign in date and time. I am using the following code on the sign out form's On Close event but it always saves over the employee's first sign in records. I don't want it to overwrite the first record.

Private Sub Form_Close()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strcriterium As String
Set db = CurrentDb
Set rs = db.OpenRecordset("table1", dbOpenDynaset)
strcriterium = "name = '" & Me.name & "'"
rs.FindFirst (strcriterium)
If Not rs.NoMatch Then
rs.Edit
rs("sign in date") = Date
rs("sign in time") = Time
rs.Update
Else
MsgBox "employee does not exist"
End If
rs.Close
End Sub

Please help.

 

[mrbboy]

This is proving to be a difficult one. Now, I get " Invalid use of null".

Perhaps I should just stay with it querying the employee name.

 

[Zion7]

Is SignInDate bound? I think yes...

therefore either

Me.refresh
strcriterium = "[SIGN IN DATE] = #" & CDate(Me.[SIGN IN DATE]) & "#" ....

Or

Me.[SIGN IN DATE].setfocus
strcriterium = "[SIGN IN DATE] = #" & CDate(Me.[SIGN IN DATE].Text) & "#"