DLookup on CheckBox Not Working

[EMoore12]

On the OnClick event of a checkbox I have the following code:

Dim strState As String
Dim strEvent As String
Dim varYear As Variant
Dim strFinal As String

strState = Forms!frmLogCriteria!cboState
strEvent = Forms!frmLogCriteria!cboEvent
varYear = Forms!frmLogCriteria!cboYear

If (Not IsNull(strFinal = DLookup("[FinalFile?]", "tblLogs", "[State]= '" & strState & "'" & _
" and [Event]= '" & strEvent & "' and [Year]= '" & varYear & "'" & _
" and [FinalFile?]= -1"))) = True Then
MsgBox "There is already a shipment marked as final file."
chkFinalFile = 0

End If

What I want the code to do is bring up a message box if the Final file field is checked for an already existing record. If the Final file field is not checked then the user should be allowed to place a checkmark in this field. The problem is that sometimes the message box appears regardless if the final file field is checked or not in the table. I've tested the code in a separate subroutine and it works fine. But it does not work on the OnClick event. I've tried moving the code to the BeforeUpdate and AfterUpdate events and it still does not work properly. Does anyone have any other suggestion that I might try. I'm using MS Access 2003.

 

[PHV]

What about this ?
If DCount("*", "tblLogs", "State='" & Me!cboState & "'" & _
" AND Event='" & Me!cboEvent & "' AND [Year]='" & Me!cboYear & "'" & _
" AND [FinalFile?]=True") Then
MsgBox "There is already a shipment marked as final file."
Me!chkFinalFile = False
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EMoore12]

Thanks for the response. I'm still having the same issue with DCount that I'm having with DLookup. The form that I'm using has buttons that allows the user to scroll through the records. When I click on the first record where the Final File checkbox is not checked and I try to check it I receive the message box, which is correct. But when I navigate to the record where the Final File is checked and try to uncheck it, I still receive the message box.

Thanks,

...ecm

 

[EMoore12]

I was able to figure out another solution to this problem without using the message box. I placed the following code on the OnCurrent property of the form.
Private Sub Form_Current()
Dim strState As String
Dim strEvent As String
Dim varYear As Variant
Dim strFinal As String

strState = Forms!frmLogCriteria!cboState
strEvent = Forms!frmLogCriteria!cboEvent
varYear = Forms!frmLogCriteria!cboYear

If chkFinalFile = 0 Then

If DLookup("[FinalFile?]", "tblLogs", "[State]= '" & strState & "'" & _
" and [Event]= '" & strEvent & "' and [Year]= '" & varYear & "' and" & _
" [FinalFile?]=-1") = True Then
chkFinalFile.Locked = True
End If
Else
chkFinalFile.Locked = False
End If
End Sub

Now if the Final File checkbox on the current record is not checked and there is an already existing record where the final file is checked, the checkbox field will lock.