Look up most recent value from the most recent record (1 Viewer)

[ddewald]

Hello all,

I have a database that tracks mileage and fuel for our fleet of trucks. We have a paper trip report that gets entered into an access database.

On the form "frm_TripReports" there is an "OdometerStart" and an "OdometerEnd". Each trip report is specific to each Vehicle. Vehicle is defined by "LicensePlate".

Is there a way to start each entry with the "OdometerStart" auto filled with the most recent record's "OdometerEnd"? "LicensePlate" specific.

 

[theDBguy]

Hi. When discussing computer processes, "most recent" must have a logical sequence. Does your table store the date and time when records are entered? If so, would you consider these values in proper sequence. Otherwise, would you simply consider "most recent" as the one with the "highest" value?

 

[ddewald]

Hi. When discussing computer processes, "most recent" must have a logical sequence. Does your table store the date and time when records are entered? If so, would you consider these values in proper sequence. Otherwise, would you simply consider "most recent" as the one with the "highest" value?

I would say the most recent would be the one with the highest value. Odometer never goes down so that's probably a safe bet.

 

[theDBguy]

I would say the most recent would be the one with the highest value. Odometer never goes down so that's probably a safe bet.

In that case, you could try using DMax().

 

[ddewald]

In that case, you could try using DMax().

So would I do a DLookup to find records by license plate and then a DMax to narrown down to highest Odometer value?

 

[MajP]

Something like.

Public Function GetMaxOdometer(LicenseID as string) as long
   GetMaxOdometer = dmax("OdometerEnd","SomeTable","License = '" & LicenseID & "'")
end function

this you can test in the immediate window to make sure it works
?GetMaxOdometer("xyz-123")
See if you get the answer.

Then you can use this on the forms on current event to set the default value of the OdometerStart or set actual value.
me.OdometerStart.defaultvalue = getMaxOdometer(Me.license)

 

[theDBguy]

So would I do a DLookup to find records by license plate and then a DMax to narrown down to highest Odometer value?

Hi. Sorry for the delay. See the function MajP provided above. The DMax() function allows for a criteria to narrow down the scope of what you're searching in the data. Cheers!

 

[ddewald]

Something like.

Public Function GetMaxOdometer(LicenseID as string) as long
   GetMaxOdometer = dmax("OdometerEnd","SomeTable","License = '" & LicenseID & "'")
end function

this you can test in the immediate window to make sure it works
?GetMaxOdometer("xyz-123")
See if you get the answer.

Then you can use this on the forms on current event to set the default value of the OdometerStart or set actual value.
me.OdometerStart.defaultvalue = getMaxOdometer(Me.license)

Please excuse my ignorance. Where would I put that function in at?

 

[MajP]

I would put the function in a standard module so you can call it from anywhere.
How is your form set up. I am guessing it might be a main form with "License" on the main form and a subform with in and out trips?

If that is the case then you could do something like this in the subform on current event
me.OdometerIn.defaultvalue = getMaxOdometer(me.parent.license)

this assumes you have a field on the main form called license

 

[isladogs]

Another approach involves the use of a subquery
http://allenbrowne.com/subquery-01.html#AnotherRecord