Prefilling information in a form based on Vehicle number and date

[Tim from TNTCSLLC]

I am working with a database designed to track vehicle usage, milage, and
service. I want to be able to have it prefill the LAST FILL block with the
vehicles odometer reading based on the Vehicle Fleet number and Date of
purchase. This is so that we can get a current MPG for the vehicle to help
determine vehicle status.

The fields used are:

FleetNumber: This is the number of the vehicle. Example: 110, 101, 108, etc.

DateOfPurchase: This is the Date that the Gas or other service was purchased.

TimeOfPurchase: This is the Time that the purchase was made. This is used
so that if several purchases were made that day for the same vehicle, there
is a time line to follow.

OdometeReading: Yes I spelled it wrong, but I just left it that way. This
is to enter the current Odometer reading at the time of purchase.

LastFill: This is the field that I want to have automatically fill in with
the highest odometer reading based on the first 3 items listed above.

I am thinking that it is something to add to the after update in the Vehicle
Fleet Number field, but I can't seem to get it to work.

Currently I have a MakeTable query run after I enter the FleetNumber. This
inturn opens up a small pop-up form to list the purchases and Odometer
readings of that vehicle and then the user has to manually choose and enter
the LastFill information based on this pop-up form.

While this works, it is far from the final results that I am looking for.

Thanks in advance for your help.

 

[HK]

I don't know if you are planning to keep the make table query or not.
I use a DLookup for something like this. DCount, DLookup, and DSum
(there are probably others too) all have a similar structure and
syntax.

DLookup looks up a certain value based on your criteria. If more than
one match, it returns the first one.

DCount is used to check how many records of a certain criteria exist.
I use this to warn users that they may be making duplicate entries.

DSum will sum the totals of a number or currency field based on your
criteria.

Getting back to your problem, I would use DLookup on the AfterUpdate or
the Exit event of your date field, assuming that at this point the
Vehicle number and the date have both been entered.

The only problem I see that I can't think of a fix for right now is how
to look up the previous entry based on the three fields you are using.
For that reason I'm not going to leave any code examples here. Maybe
someone else has an idea how to do that.

You may have to run one DLookup first to find the "newest" entry for
the vehicle number and then do another DLookup to find the mileage
value in that entry.

 

[HK]

After a little research, I see that you could use the DMax function to
find the highest value in any specific field. This would help you find
the last entry for that vehicle, based on (A) the highest mileage (B)
the latest date. A single DMax function would be all you need if you
use it to find the highest mileage. -HK

 

[Tim from TNTCSLLC]

Thanks for answering so quickly.

The make table query was just a temp fix solution. It was the only thing I
could think of at the time. I was trying to write the code for the DLookup
and the DMax values but couldn't get the syntex correct.

If I can find my old notes, I will post them here for the things I tried.
If you have any ideas, please post them and I will try them as well.

THanks.