Compare Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marie - 28 Mar 2007 02:18 GMT

I have a table for vehicles and a table for trip details. Every day, the
vehicle gets used, and the user must enter the odometer reading before the
route, and the odometer reading when the vehicle is brought back. The next
day, the process is repeated. A lot of times, the user puts the wrong
odometer reading at the start of the trip. If the reading when the vehicle is
brought back the day before is 7000, and the user enters a number less than
7000 at the start of the next day's route, obviously it is a mistake, because
the number has to at least be what the ending number was the day before. If
this happens, is there a way I can have a message pop up to say that "The
Odometer Number Entered is Incorrect"? I have VIN as the vehicle ID, and
TripID as the Route ID in 2 separate tables. Thanks for your help.

Reply to this Message

Allen Browne - 28 Mar 2007 11:28 GMT

Use the BeforeUpdate (or AfterUpdate) event procedure of the text box on
your form to DLookup() the highest value so far for that vehicle, and give
the warning.

For help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a table for vehicles and a table for trip details. Every day, the
> vehicle gets used, and the user must enter the odometer reading before the
[quoted text clipped - 11 lines]
> Odometer Number Entered is Incorrect"? I have VIN as the vehicle ID, and
> TripID as the Route ID in 2 separate tables. Thanks for your help.

Reply to this Message

Marie - 29 Mar 2007 01:34 GMT

Allen and Jerry: I tried both of your ideas, but I keep getting a Compile
Error: Syntax Error. I cut and pasted Jerry's and just changed the field
names etc. to match mine, but it kept coming up with an error. With Allen's,
this is what I put:

=DLookup("ODOStart", "tblTrips", "VIN = " & [VIN])
I then put the message underneath. I wasn't sure how to go about using both
the ODOStart field and the VIN field together. Was I supposed to put a
greater than or less than sign as well?

I couldn't try Jamie's suggestion, because I think that CHECK is a SQL
feature. If it isn't, I couldn't find it.

> Use the BeforeUpdate (or AfterUpdate) event procedure of the text box on
> your form to DLookup() the highest value so far for that vehicle, and give
[quoted text clipped - 20 lines]
> > Odometer Number Entered is Incorrect"? I have VIN as the vehicle ID, and
> > TripID as the Route ID in 2 separate tables. Thanks for your help.

Reply to this Message

Allen Browne - 29 Mar 2007 02:11 GMT

If VIN is a Text field (not a Number field in table design), you need extra
quotes:
=DLookup("ODOStart", "tblTrips", "VIN = """ & [VIN] & """")

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen and Jerry: I tried both of your ideas, but I keep getting a Compile
> Error: Syntax Error. I cut and pasted Jerry's and just changed the field
[quoted text clipped - 42 lines]
>> > and
>> > TripID as the Route ID in 2 separate tables. Thanks for your help.

Reply to this Message

Marie - 29 Mar 2007 16:20 GMT

Yes, it is a text field. I cut and pasted your code, and when I put my curser
on the next line, an error message, "Compile Error: Expected: Line number or
label or statement or end of statement. I wonder what I'm doing wrong.....

> If VIN is a Text field (not a Number field in table design), you need extra
> quotes:
[quoted text clipped - 46 lines]
> >> > and
> >> > TripID as the Route ID in 2 separate tables. Thanks for your help.

Reply to this Message

Allen Browne - 29 Mar 2007 17:42 GMT

Sounds like you pasted in in the code window?

It was intended for the Control Source property of a text box.

If you want to put it in code, add the name of the control you want to
assign it to before the =.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Yes, it is a text field. I cut and pasted your code, and when I put my
> curser
[quoted text clipped - 62 lines]
>> >> > and
>> >> > TripID as the Route ID in 2 separate tables. Thanks for your help.

Reply to this Message

Marie - 29 Mar 2007 20:42 GMT

I'm sorry....I'm not sure what you mean...in your first post you said to put
it in the beforeupdate or afterupdate property. Then where do I put the
message?

> Sounds like you pasted in in the code window?
>
[quoted text clipped - 69 lines]
> >> >> > and
> >> >> > TripID as the Route ID in 2 separate tables. Thanks for your help.

Reply to this Message

Allen Browne - 30 Mar 2007 02:30 GMT

Two possiblities:

a) Just set the Control Source propety to the expression.

b) Set the property to:
[Event Procedure]
Click the Build button (...) beside the property.
Access opens the code window.
Enter something like this:
[Text00] = DLookup(...

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I'm sorry....I'm not sure what you mean...in your first post you said to
> put
[quoted text clipped - 86 lines]
>> >> >> > TripID as the Route ID in 2 separate tables. Thanks for your
>> >> >> > help.

Reply to this Message

Marie - 31 Mar 2007 14:24 GMT

I finally got it to work! This is how I did it: When I tried Jerry's idea:

If Me.OdoStart < DMax("[OdoStop]", "Trips", "[VIN] = " &
Forms!Orders!VIN ) Then

it kept coming up with an error message that the code was incorrect. When I
tried Allen's last idea, there were no error messages, but no matter what
number I entered, the message box kept popping up. I then added Allen's code
to Jerry's DMax code, and it finally worked correctly. Here is what I used:

If Me.ODOStart < DMax("[OdoFinish]", "tblTrips", "VIN = """ & [VIN] &
"""") Then
MsgBox "The Odometer Number Entered is Incorrect"
End If

Thank you guys so much for your combined effort. Because of this forum, for
years I have been able to do the impossible and have learned so much.

> Two possiblities:
>
[quoted text clipped - 97 lines]
> >> >> >> > TripID as the Route ID in 2 separate tables. Thanks for your
> >> >> >> > help.

Reply to this Message