Check for existing date

[twinnyfo]

When are you getting this error? It is more helpful to us to tell us exactly when and where so we can focus troubel shooting.

[didacticone]

As soon as i open the form... sorry

[didacticone]

I added some sample data to the table and that removed the messaged but the buttons are not disabling here is my code:

Option Compare Database
Option Explicit
Dim fCheckIn  As Boolean
Dim fCheckOut As Boolean
Private Sub CheckStatus()
On Error GoTo EH
 
    CheckEmployee 1
    CheckEmployee 2
    CheckEmployee 3
    CheckEmployee 4
   
    Exit Sub
EH:
    MsgBox "There was an error checking the status!  " & _
        "Please contact your Database Administrator.", _
        vbCritical, "WARNING!"
    Exit Sub
    
End Sub
Private Function CheckEmployee(Employee As Integer)
On Error GoTo EH
 
    fCheckIn = Nz(DLookup("[SignIn]", "tbl_Master", _
        "[Employee] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    fCheckOut = Nz(DLookup("[SignOut]", "tbl_Master", _
        "[Employee] = " & Employee & " " & _
        "AND Format([SignOut], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    Me("cmdSignIn" & Employee).Enabled = Not fCheckIn
    Me("cmdSignOut" & Employee).Enabled = Not fCheckOut
 
    Exit Function
EH:
    MsgBox "There was an error checking the status!  " & _
        "Please contact your Database Administrator.", _
        vbCritical, "WARNING!"
    Exit Function
End Function


Private Sub Form_Open(Cancel As Integer)
On Error GoTo EH
 
    Me.Filter = "Employee = ''"
    Me.FilterOn = True

    Exit Sub
EH:
    MsgBox "There was an error initializing the Form!  " & _
        "Please contact your Database Administrator.", _
        vbCritical, "WARNING!"
    Exit Sub
     CheckStatus
End Sub

[didacticone]

I stepped through the code and the error is coming up here:

Private Function CheckEmployee(Employee As Integer)
On Error GoTo EH
 
   [B]fCheckIn = Nz(DLookup("[SignIn]", "tbl_Master", _
        "[Employee] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)[/B]
 
    fCheckOut = Nz(DLookup("[SignOut]", "tbl_Master", _
        "[Employee] = " & Employee & " " & _
        "AND Format([SignOut], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    Me("cmdSignIn" & Employee).Enabled = Not fCheckIn
    Me("cmdSignOut" & Employee).Enabled = Not fCheckOut
 
    Exit Function
EH:
    MsgBox "There was an error checking the status!  " & _
        "Please contact your Database Administrator.", _
        vbCritical, "WARNING!"
    Exit Function
End Function

The bold or first part of that code skips it to the error

[didacticone]

i changed the error coding to:

EH:
    MsgBox "There was an error with the search!  " & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"

and received the following error upon opening the form
"data type mismatch in criteria expression."

[twinnyfo]

Post #48, Line 49 should be:

Me.Filter = "Employee = 0"

I forgot to make that change.

Also, lines 25 and 30 should reference the Field [Employee2] (see Post #39).

Put a break point at the MsgBox in the EH. When it gets there, type ?Err.Descriptio n in the Immediate Window. Cut and paste the error.

[twinnyfo]

And.... my thought is that we are trying to set a boolean variable as a date.... If we were setting it as a number, we would be OK. But that is a minor problem.

If your tbl_master also has a primary key (an ID field), we can change the DLookup() to this:

    fCheckIn = Nz(DLookup("[ID]", "tbl_Master", _
        "[Employee2] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)

    fCheckOut = Nz(DLookup("[ID]", "tbl_Master", _
        "[Employee2] = " & Employee & " " & _
        "AND Format([SignOut], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)

Also noticed in post #48: Line 58 should be line 51 - you should not have that statement in the EH, but in the main sub.

[didacticone]

Put a break point at the MsgBox in the EH. When it gets there, type ?Err.Descriptio n in the Immediate Window. Cut and paste the error.

Not sure what you want me to do here.

[twinnyfo]

You achieved the same by putting the Err.Description in the MsgBox.

These other changes need to be made to fix any additional problems.

[didacticone]

Sorry misunderstood.

"Data type mismatch in criteria expression"

[didacticone]

Here is the entire code, with the new changes:

Option Compare Database
Option Explicit
Dim fCheckIn  As Boolean
Dim fCheckOut As Boolean
Private Sub CheckStatus()
On Error GoTo EH
 
    CheckEmployee 1
    CheckEmployee 2
    CheckEmployee 3
    CheckEmployee 4
   
    Exit Sub
EH:
    MsgBox "There was an error checking the status!  " & _
        "Please contact your Database Administrator.", _
        vbCritical, "WARNING!"
    Exit Sub
    
End Sub
Private Function CheckEmployee(Employee As Integer)
On Error GoTo EH
 
   fCheckIn = Nz(DLookup("[ID]", "tbl_Master", _
        "[Employee2] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    fCheckOut = Nz(DLookup("[ID]", "tbl_Master", _
        "[Employee2] = " & Employee & " " & _
        "AND Format([SignOut], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    Me("cmdSignIn" & Employee).Enabled = Not fCheckIn
    Me("cmdSignOut" & Employee).Enabled = Not fCheckOut
 
    Exit Function
EH:
    MsgBox "There was an error with the search!  " & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
   
    Exit Function
End Function


Private Sub Form_Open(Cancel As Integer)
On Error GoTo EH
 
    Me.Filter = "Employee = 0"
    Me.FilterOn = True
CheckStatus
    Exit Sub
EH:
    MsgBox "There was an error with the search!  " & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
     
End Sub

[twinnyfo]

Make the changes I've identified and these errors should go away.

[twinnyfo]

And does this form work yet? Any additional errors?

[didacticone]

"Data type mismatch in criteria expression" on form open

[twinnyfo]

Which line is causing the error? Everything looks fine to me.