when the form is open again it fail to check for existing records,

[princess12]

I tried it I now get error "too few parameters, expected one. I appreciate for all the help you giving me, I need the time in and time out so I can work out the total hours employee work to work out their wages. I cant seem to understand how the time only will help me do that.



thanks again

[June7]

There are ways to deal with a single time field but the queries can be tricky.

The code did not error for me. Did you modify table as described? Moot point now because that structure and process is not what you want to do. Putting TimeOut on the same record as corresponding TimeIn should make it easier to calculate elapsed time. How will that be accomplished? Nothing in the db you posted indicated a process for that. If there are two time fields populated in each record, the Status field is unnecessary.

Time & Attendance type of db is one of the more difficult to build. And a clock in/out system makes it more complicated.

So, back to square one.

Need code that looks at the most recent record for employee and if the TimeOut field is empty, don't allow another TimeIn record to be committed. Consider this version:

Private Sub cmdClockIn_Click()
Dim db As dao.Database
Dim rst As dao.Recordset
Set db = CurrentDb()
If Not IsNull(Me.cboEmployee) Then
    Set rst = db.OpenRecordset("SELECT TOP 1 TimeOut FROM tblClockInOut WHERE EmployeeID =" & Me.cboEmployee & " ORDER BY TheDate Desc, TimeIn Desc;", dbOpenDynaset)
    If Not rst.EOF Then
        If IsNull(rst!TimeOut) Then
            MsgBox "Must click ClockOut"
            Exit Sub
        End If
    End If
    CurrentDb.Execute "INSERT INTO tblClockInOut(EmployeeID, TheDate, TimeIn) " & _
                    "VALUES(" & Me.cboEmployee & ", Date(), Time())"
    MsgBox " signed in"
    Set rst = Nothing
Else
    MsgBox "Must select employee ID"
End If
End Sub

Private Sub cmdClockOut_Click()
Dim db As dao.Database
Dim rst As dao.Recordset
Set db = CurrentDb()
If Not IsNull(Me.cboEmployee) Then
    Set rst = db.OpenRecordset("SELECT TOP 1 TimeOut FROM tblClockInOut WHERE EmployeeID =" & Me.cboEmployee & " ORDER BY TheDate Desc, TimeIn Desc;", dbOpenDynaset)
    If Not rst.EOF Then
        If Not IsNull(rst!TimeOut) Then
            MsgBox "Must click ClockIn"
            Exit Sub
        End If
    End If
    rst.Edit
    rst!TimeOut = Time()
    rst.Update
    MsgBox " signed out"
    Set rst = Nothing
Else
    MsgBox "Must select employee ID"
End If
End Sub

This works as long as the In and Out don't cross midnight. Then would require full date/time stored for each In and Out.

[princess12]

this is exactly what I wanted, thank you so much, one last thing - am not familiar with this line, could you please explain

("SELECT TOP 1 TimeOut

what is top 1? thanks

[June7]

TOP x is an SQL criteria that will select x number (or x percent) of records from the start of the query. http://www.w3schools.com/sql/sql_top.asp

[princess12]

thank you very much for your help, really appreciate it, thank you!