entry validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Enter odometer reading into an unbound textbox and validate upon exit, update
table if valid from unbound textbox.
 
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]));
 
Back
Top