Solved: Calculate using previous record values

[JustJerry]

Hello everyone,

I am trying to create a database that keeps track of the amount of mileage a vehicle travels in different states.

I have a Main Form frmMainMile with a linked subform subfrmMileDetail. On the form frmMainMile I have two fields that the user enters in the starting mileage and state for a specific month. These fields are [BeginMI] and [BeginST].

Then, on the subform, they will enter in the date, odometer reading, and the name of the state they are crossing into. I also have two other fields, one that calculates the total mileage traveled for the previous state, and one that lists that previous state.

These are the fields of the subform:

[MileDetailID] Unique (Primary Key) ID, autonumbered - hidden from user
[Date] Date that a vehicle crosses stateline
[Mileage] Odometer reading of vehicle at stateline crossing
[State] State vehicle is crossing into
[TotalMileage] Total of miles driven in previous state (Calculated field)
[TotalMileState] Previous State for total miles driven

I am totally confused by how to access the values stored in previous records. But what I do have that works for the FIRST record, is this:
Private Sub Mileage_Exit(Cancel As Integer)
If Me.CurrentRecord = 1 Then
Me.TotalMile.Value = (Me.Mileage - Me.Parent.BeginMI)
Me.TotalMileState.Value = Me.Parent.BeginST
End If
End Sub

After the user exits the field [Mileage], the value for [TotalMile] is calculated from the beginning mileage on the Main Form. Also, the value for [TotalMileState] is entered from the value of the Beginning State on the Main Form.

Now, I want to add an ELSE statement for records 2 and higher of the subform. Basically I need the value of [TotalMile] to equeal the current records value of [Mileage] minus the PREVIOUS records value of [Mileage]and the value of [TotalMileState] to equal the value of the PREVIOUS records value of [State].

Thank you,

Jerry

[OBP]

Jerry, you can place VB in the after update event procedure of the [Mileage] field that opens a duplicate form to the current record, step back one reocrd and perform the calculation and then close the duplicate form.

[geekgirlau]

OBP's method sounds pretty simple (not to mention lateral - nice work!).

Another method is using a recordset


Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim strSQL As String


strSQL = "SELECT TOP 1 [Mileage], [State] " & _
"FROM MyTable " & _
"WHERE [MileDetailID] < " & Me.MileDetailID & " " & _
"ORDER BY [MileDetailID] DESC;"
Set db = CurrentDb
Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rec
Me.TotalMile = Me.Mileage - !Mileage
Me.TotalMileState = !State
End With

rec.Close
db.Close
Set rec = Nothing
Set db = Nothing

[OBP]

geekgirllau, I use that method as you can actually see the values to check them and also transfer other data.

[JustJerry]

Thank you both for the excellent examples. I have so much to learn, and appreciate you taking the the time to point me in the right direction. I tried both suggestions, but ended up using geekgirl's VBA coding as the final solution~