dlookup for forms validation

[shanebgross]

My database keeps track of vehicle maintenance. I have a form to enter all
information. I want an message box and cancelevent to occur if a record for
a specific vehichle chosen from a combo box on the form already has a record
for a date entered in a text box on the same form. I have a query called
date validator that finds any record with the same date as the date text box
on the form and the same vehichle ID as chosen in the combo box. I have the
following code on the form's beforeupdate event:
Private Sub Form_BeforeUpdate(Cancel As Integer)

If DLookup("[Date]", "Date Validator", "[Date]=" &
Forms!Maintenance!Text7) Is Null Then
MsgBox "An entry for this vehicle already exists with this date." &
Chr(13) & "Click OK to change the vehicle and/or date or Cancel to abort this
entry.", 5 + 16 , "Entry already exists!"
End If

End Sub

I get Error 424--Object required when I try to go to the next record. What
is wrong? I can make it work with a macro, but I want an "ok" and "cancel"
button on the message box to give the person entering options.

 

[Klatuu]

First, your DLookup syntax is incorrect. It should be:
If IsNull(DLookup("[Date]", "Date Validator", "[Date]= #" _
& Forms!Maintenance!Text7 & "#") Then
(Dates are deliniated with #)

A few other issues:
1. Don't use Date as a field name. It is a reserved word in Access and may
cause problems.
2. I suggest you replace the 5 + 16 with the VBA constants. You may know
what they mean, but most (including me) would have to look them up.

Where do you get the error?

 

[Al Camp]

1. The IsNull should come before the DLookup.
2. Don't use [Date] as a field name... it is a reserved word in Access.
3. A minor point... use meaningful names for your fields like
[MaintenanceDate] instead of [Text7]

If IsNull(DLookup("[Date]", "Date Validator", "[Date] = " &
Forms!Maintenance!Text7)) Then
MsgBox "An entry for this vehicle already exists with this date." &
Chr(13) &
"Click OK to change the vehicle and/or date or Cancel to abort this
entry.", 5 + 16 , "Entry already exists!"
End If

 

[shanebgross]

Thanks for the pointers, guys. I used all of the suggestions from both of
you and it appears to work.

1. The IsNull should come before the DLookup.
2. Don't use [Date] as a field name... it is a reserved word in Access.
3. A minor point... use meaningful names for your fields like
[MaintenanceDate] instead of [Text7]

If IsNull(DLookup("[Date]", "Date Validator", "[Date] = " &
Forms!Maintenance!Text7)) Then
MsgBox "An entry for this vehicle already exists with this date." &
Chr(13) &
"Click OK to change the vehicle and/or date or Cancel to abort this
entry.", 5 + 16 , "Entry already exists!"
End If
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

My database keeps track of vehicle maintenance. I have a form to enter
all
information. I want an message box and cancelevent to occur if a record
for
a specific vehichle chosen from a combo box on the form already has a
record
for a date entered in a text box on the same form. I have a query called
date validator that finds any record with the same date as the date text
box
on the form and the same vehichle ID as chosen in the combo box. I have
the
following code on the form's beforeupdate event:
Private Sub Form_BeforeUpdate(Cancel As Integer)

If DLookup("[Date]", "Date Validator", "[Date]=" &
Forms!Maintenance!Text7) Is Null Then
MsgBox "An entry for this vehicle already exists with this date." &
Chr(13) & "Click OK to change the vehicle and/or date or Cancel to abort
this
entry.", 5 + 16 , "Entry already exists!"
End If

End Sub

I get Error 424--Object required when I try to go to the next record.
What
is wrong? I can make it work with a macro, but I want an "ok" and
"cancel"
button on the message box to give the person entering options.