That indicates that you've got a StoreNo, but no InspDate.

If you look at the snippets I posted earlier, I think you'll see that I used
the Nz function to handle those cases:

If IsNull(DLookup("StoreNo", "[tblDMInspections]", _
"[InspDate] = " & _
Format(Nz(Forms![frmAddDMInspections]![InspDate], #01/01/100#), _
"\#yyyy\-mm\-dd\#") & _
" AND [StoreNo] = " & _
Nz(Forms![frmAddDMInspections]![StoreNo], 0))) = False Then

All I'm doing is putting in a value that should never occur naturally, so it
won't blow up the DLookup and won't return a duplicate.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ridgerunner" wrote in message
...
I am having another problem now. When I put data into the Store control,
before putting data in the InspDate control, I receive the following error
message:

Run-time error '3075':
Syntax error (missing operator) in query expression '[InspDate]=
AND [StoreNo] = 11'.

Below is the code:

Private Sub StoreNo_BeforeUpdate(Cancel As Integer)

Dim strMessage As String
If IsNull(DLookup("StoreNo", "[tblDMInspections]", _
"[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate],
"\#yyyy\-mm\-dd\#") & _
" AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False
Then
MsgBox strMessage & "Store and Inspection Date already exist. Press
Esc to correct."
End If

End Sub

The arrow in the debug screen points to the line starting with AND.


"ruralguy via AccessMonster.com" wrote:

It is just my opinion but I prefer to notify a user as soon as they make
a
mistake. My preference would be to have verification in the BeforeUpdate
event of both controls and just check for missing entries in the
BeforeUpdate
event of the form. Just my $0.02.

ridgerunner wrote:
Even if I want them to see the error before the form is complete?

NO! It should be in the form's BeforeUpdate event, not the
BeforeUpdate
event of the individual controls.
[quoted text clipped - 227 lines]

End Sub

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200805/1