How to automatically populate start mileage from previous record end mileage.

[HMac]

Access 2010 Novice! I have 2 tables - Daily Trip Log and Daily Trip Log Stops. Daily Trip Log Fields are dtlID (Autonumber), departdate, departtime, departmiles, arrivetime, arrivemiles, totalmiles, and totaldrivinghours. Daily Trip Log Stop Fields are dtlID (Autonumber), date, reasonforstop, stoptime, mileage, placestopped, and departtime.



A driver can stop many times during the day for deliveries, lunch, fuel, etc.

My Form is Daily Trip Log with a subForm Daily Trip Log Stops.

When a driver enters the departdate on the primary form I would like the departmiles to automatically populate using the previous days arrivemiles. I have tried DLast to no avail.

Total time is arrivalmiles minus departmiles and totaldrivinghours is arrivetime minus departtime. In addition to this I would like to know how to add the numerous days stops enroute (departtime minus stoptime) to then be deducted from the daily total driving hours.

Everything works fine except having the departmiles on the new record reflect the arrivemiles from the previous day and how to total the amount of stopped time for all the stops during the day.

Thank you in advance.

[JeroenMioch]

Cant you do it with a Dlookup ?

Private Sub departdate_AfterUpdate()
Me.departmiles = Dlookup ("Arrivemiles", "[Daily Trip Log]")

If there are several drivers (or cars) youll have to include a WHERE clause in the Dlookup so Access knows from wich record to extract the Arrivemiles from. The WHERE clause in this case should be a personell or licenceplate number for example.

[HMac]

Thank you JeroenMioch. Dlookup works except it always takes me to the first record in the table instead of reading the arrivemiles in the previous record. Could you explain how to work around that please.

[JeroenMioch]

Youll have to specify wich record access needs to select for you

Me.departmiles = Dlookup ("Arrivemiles", "[Daily Trip Log]", "LicenceplateNumber = me.YourLicencePlateNumberControl")

In above code. Access will fill the departedmiles control of your form, with the value it finds in the Daily Trip Log table, arrivedmiles field WHERE the licenceplate field is YourLicencePlateNumberField on your form.

With no WHERE clause, access just returns the first record it finds

[pbaldy]

Youll have to specify wich record access needs to select for you

Me.departmiles = Dlookup ("Arrivemiles", "[Daily Trip Log]", "LicenceplateNumber = me.YourLicencePlateNumberControl")

That would get the first record for that vehicle, which would likely not be the previous day's record.

[JeroenMioch]

That would get the first record for that vehicle, which would likely not be the previous day's record.

You might have a valid point there mr. Baldy ^^

Still not that difficult though. Unfortunately im not at work anymore and i dont have Access at home.
So feel free to help out here.
Else
I work it out when back at work tomorrow

[HMac]

Thanks but that will not work as I need the previous day's record. The arrivemiles at the end of the day is what I want to use populate the new record departmiles field the next day. I have found some code but cannot get it to work. My field dtlID is an Autonumber field. The code example says it will look at sequential numeric fields and is shown below:
Private Sub dtlDate_AfterUpdate()
Me.dtlDepartMileage = DLookup("dtlArriveMileage", "[Daily Trip Log]", "[dtlID]=Forms![Daily Trip Log]![dtlID]-1")
End Sub
After I enter the date in the dtlDate field I get the following: Run Time Error '3075'. Invalid use of '.', '!', or '()' in query expression '[dtlID] = Forms! [Daily Trip Log] ! [dltID- '.
Can you please give me the correct statement to make this work. The error has something to do following the "Table" portion of the code but I don't know what or how to correct it.
Thank you.

[JeroenMioch]

Normally i check these kind of codes with examples within my databases. But im not at work momentarely so i cant do that now.

What you can do is try this :

Me.dtlDepartMileage = DLookup("dtlArriveMileage", "[Daily Trip Log]", "([dtlID] = me.dtlID)-1")

Its aircode, but worth a shot..

[pbaldy]

I might try a DMax() on the ending mileage field with a criteria on the vehicle. Presuming you enter in date order, I'd think that would work.

[HMac]

Jeroen the "Aircode" you provided does not work. Pbaldy could you please explain how to use Dlookup and DMax together. I only have 15 records of test data and as you know Autonumber is sequential/numeric and the trips are entered in date order with dtlArriveMileage field in the last record has the highest numeric value and that is the value I would like to be the dtlDepartMileage value when I start a new record. Thank you.

[HMac]

Jeroen I do not get an error using your aircode it just does not populate the dtlDepartMileage field in the new record.

[pbaldy]

I wasn't suggesting using them together, I was suggesting using DMax() alone. As long as you enter in date order, the maximum ending mileage in the table would be the last one, thus the beginning mileage for the vehicle being entered.

[HMac]

Thank you pbaldy. That works and solved the problem. Below is the code:
Private Sub dtlDate_Change()
Me.dtlDepartMileage = DMax("[dtlArriveMileage]", "Daily Trip Log")
End Sub
I used the date change on the new record so when I click in the dtlDepartMileage field it automatically populates from the last dtlArriveMileage.
Thanks again for your help.

[pbaldy]

No problem. I take it there's only 1 vehicle being tracked? If not, that will give you the max mileage overall, not for a given vehicle. I would expect a criteria on vehicle ID.