entry validation

[Walter]

I am trying to validate odometer entries on a subform(frmTripDetails) based
on the truck # on the main form(frmTrips). I created a query which returns
the correct result. I used this query to define a variable with which to
compare the present entry to in the before update event of the Odometer
control. When I check the variable, the value is the same as the entry so it
can never validate. I thought a query only returned results from the table
and the table was not updated until after the before update event. I tried
to set the value of another control = to the query result in the enter event
of the odometer control but that didn't work. I have run out of things to
try. Any help will be greatly appreciated. Here is the present code I have:
Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim varPrevOdometer As Variant

varPrevOdometer = DLookup("Odometer", "qryLastOdometer")

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

End Sub

 

[KARL DEWEY]

Enter odometer reading into an unbound textbox and validate upon exit, update
table if valid from unbound textbox.

 

[Walter]

Thanks for your response. I've created the unbound text box and copied the
validation code into the before update event. The validation was not working
so I checked the variable and found varPrevOdometer=null. Apparently the
query is returning no results here but it did return results when used in the
other control. Here is the SQL for the query:
SELECT tblTrips.TruckID, Max(tblTripDetails.Odometer) AS MaxOfOdometer
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]));