Have a Form Field Fill From Another Record in the Table

[Santara]

I have two fields on my form for the miles driven in a days work. The first
field is StartMiles for the odometer reading at the beginning of the shift.
The second field is FinishMiles for the odometer reading at the end of the
shift. And of course there is a field called DailyMiles, that calcultes the
number of miles driven in the shift.

All this works fine.

Now...they want to have the system enter the StartMiles for a person from
the last time they worked based on the FinishMiles. Example: Bob worked
Friday July 8th with Start Miles of 6,500. He drove 75 miles, so at the end
of his shift the FinishMiles were 6,575. When he returns to work on Monday
July 11th, his next record entered should automatically reflect the
StartMiles as 6,575.

Keep in mind that their are 15 other cars being driven 24 hours a day by 18
other workers. So his next record will NOT follow in the table. There will
be multiple other records for other workers entered before he works again on
Monday.

Normally, they spend about 98% of their time in the same car, with no one
else driving that vehicle. However, they sometimes use a different car, and
would need an override for those times. When they are in a different car, it
is okay to bring forward that FinishMiles to his next record. It can then be
over ridden when he returns to his regular vehicle.

Here is an example of what happens for new records entered on the form:

When the form is opened, the user enters the UnitID number and then the
WorkDate. Now they choose the WorkStatus of On Duty. Several fields later
is the StartMiles field.

So what we need is for the system to identify WHO this record is for, then
search for the last date they were ON DUTY (From the WorkStatus field), find
the FinishMiles and bring that value forward and drop it into the StartMiles.

Something else, there can be records entered with WorkStatus of Vacation or
Holiday where there are NO miles entered for that record.

I don't even know where to begin fixing this problem. I thought about using
a DLookUp, but it doesn't seem to have the power for this problem.

Can some one help me with this problem?

Thanks!

Santara

 

[Arvin Meyer [MVP]]

DLookup will work, but DMax will work better. So your expression in the
default value property of the new record's Mileage text box would read
something like this (air code):

=DMax("MileageField", "TableName", "VehicleID = " &
Forms!frmFormName!txtVehicleID)

substituting your control names, of course.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Allen Browne]

The answer will depend on your data structure.

Presumably your mileage table has fields for:
VehicleID = identifies the vehicle being driven
DriverID = the employee who drove.
OdometerStart Number (start of trip)
OdometerEnd Number (end of trip)

It seems to me that the beginning mileage will typically be the end mileage
for the same vehicle (not driver.) If so, you could use the AfterUpdate of
the VehicleID combo box in your form to do something like this:
Dim strWhere As String
If Not IsNull(Me.VehicleID) Then
strWhere = "VehicleID = " & Me.VehicleID
Me.OdometerStart = DMax("OdometerEnd", "tblMileage", strWhere)
End If

Note that if *all* trips are always entered, and entered in the right order
(i.e there can never be gaps between the records), you might want to store
only the end mileage.

 

[Santara]

Arvin,

They are not tracking the Vehicle ID, so would the criteria portion look for
the Unit ID of the new record AND the "lastest" WorkDate from the table for
that Unit ID AND the WorkStatus of On Duty?

=DMax("FinishMiles", "tblWorkLog", ??????

Thanks!

Santara

 

[Santara]

Allen,

Except, we are not tracking the Vehicle ID.

We have the UnitID, which is the police officers ID number. (I can see how
UnitID looks like the vehicle id... sorry that I didn't explain that before.)

Each officer (UnitID) normally drives the same car, but could drive any one
of the available cars. They want to know how much of the day they spent on
the road and how far they went, not track data on the vehicle.

The mileage data is tracked with other activites that they perform on that
day in the table named tblWorkLog.

Does that explain what I'm doing?

Santara

 

[Allen Browne]

You could use similar logic on the DriverID, or OfficierID or UnitID or
whatever you call it.

Not sure I'd build the db that way, but it may depend on what else you need
to handle (e.g. servicing.)

 

[Arvin Meyer]

Unless you are tracking the officer's mileage, the UnitID won't help much.
Think about how your data describes the activity. It is a vehicle which logs
up mileage, not the unit. After all, if the officer drives Vehicle 1 on
Tuesday and Vehicle 2 on Wednesday, how many miles did Vehicle 1 go? Answer:
The mileage on Tuesday + the mileage from whomever drove it on Wednesday (if
at all). You can't relate the odometer from 1 vehicle to the next, so you
need to start tracking vehicles. Odometers belong to vehicles, not officers.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access