DMax/DLookup help

[Walter]

I am trying to validate an odometer entry >= the last entry for this truck.
From what I gather, I can't use DMax because the truck number for the
criteria is in a different table. I've created a query to return the last
odometer and tried to use DLookup to get the result. The error message shows
"?followed by a square" for what the last odometer was. Can someone tell me
how to accomplish this? Here is the code I have in the BeforeUpdate event.

Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim varPrevOdometer As Variant

varPrevOdometer = DLookup("Odometer", "qryLastOdometer")
If Me.Odometer < varPrevOdometer Then
MsgBox _
"The last odometer entered for this truck was " & _
DLookup("Odometer", "qryLastOdometer") & vbCrLf & _
"Please enter an odometer greater than or equal " & _
"to this.", , _
"Invalid Odometer Entry"
End If

End Sub

 

[Andreas]

- Rename the controls on your form according the standard conventions.
- Then try the following aircode:

Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim lngPrevOdometer As Long 'Presumably this is a whole number?
lngPrevOdometer = Nz(DLookup("Odometer", "qryLastOdometer"),0)
If Me.txtOdometer < lngPrevOdometer Then
Cancel = True
MsgBox _
"The last odometer entered for this truck was " & _
lngPrevOdometer & vbCrLf & _
"Please enter an odometer greater than or equal " & _
"to this.", , _
"Invalid Odometer Entry"
End If
End Sub

Regards,
Andreas

 

[Walter]

Thanks Andreas for your reply. I am not sure what effect the control name
would have. Your name, txtOdometer, indicates it to be a text control. I
have it defined as number, long integer since all data will be numeric. I
changed my code to match your's with the exception of the Odometer control
name. I tried this, with a valid entry, and received a runtime error '13' -
Type mismatch with the line
lngPrevOdometer = Nz(DLookup("Odometer", "qryLastOdometer"),0) highlighted
in yellow. When I hold the cursor over lngPrevOdometer, it shows = 0.
Me.Odometer = 407971 which is correct. "Odometer" from the query shows
nothing.

To be certain I haven't overlooked something, here is my present code.
Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim lngPrevOdometer As Long

lngPrevOdometer = Nz(DLookup("Odometer", "qryLastOdometer"), 0)
If Me.Odometer < lngPrevOdometer Then
Cancel = True
MsgBox _
"The last odometer entered for this truck was " & _
lngPrevOdometer & vbCrLf & _
"Please enter an odometer greater than or equal " & _
"to this.", , _
"Invalid Odometer Entry"
End If

End Sub
Thanks Again,
Walter

 

[Walter]

Since my last reply, I changed the Odometer to a text control. That took
care of the runtime error I was getting. However, the validation is not
working. If I enter an incorrect number,(less than the last) it goes on to
the next control. I tried changing the control name to txtOdometer as you
suggested but had a number of other control errors because they reference the
odometer control so I changed it back rather than trying to hunt down all
these references. I can do so if that is the problem. I tried to put a break
point in the code to see what the variables were but was unable to get that
to work.
Many thanks again for your help,
Walter