Previous Record Value in Different New Record Field

[Guest]

Hi all,

I'm writing a Mileage program and need to populate the new record's Miles
Out as the previous record's Miles In field. For example

Record 1 - Miles Out = 10, Miles In = 15

The New Record should default to - Miles Out = 15

Any help is appreciated.

 

[Jon Lewis]

Try adapting this function:
Make "=PrevMiles" without the quotes the control source of your Miles Out
control

Private Function PrevMiles As Integer 'Assumes Miles in & out are whole
numbers
On Error GoTo Err_PrevMiles
Dim rs As DAO.Recordset
Set rs = YourForm.RecordsetClone
rs.FindFirst "[PrimaryID] = '" & Me.PrimaryID & "'" '(Assumes
Primary ID is numeric)
rs.MovePrevious
If Not rs.BOF Then
PrevMiles = rs!MilesIn
End If
Exit_PrevMiles:
Set rs = Nothing
Exit Function
Err_PrevMiles:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_PrevMiles
End Function

 

[Jon Lewis]

In case you thought otherwise, the Miles Out control needs to be unbound as
you don't need to store this value - in fact it would be bad practice to do
so.

HTH