Data from previous record

  • Thread starter Thread starter Alan Nicoll
  • Start date Start date
A

Alan Nicoll

I have some data that I need to interpret based on the
value of the same field in the previous record. If I were
working in a Visual Basic array it would be easy enough,
but I don't know how to do this in Access.

That is: I have a shift code in one field that indicates
(among other things) time of day when an employee worked.
The rate the employee earns depends on Day/Evening/Night
rates. Now, sometimes the shift is coded as "Overtime"
and so doesn't indicate the time of day.

When the shift is coded "Overtime" I want to look back at
the previous record and take the time of day from there.
In most cases this will be correct, which will be more
convenient for the user than just plugging in the Day rate
(user checks all this by hand anyway).

Thanks.

Alan
 
The main difficulty with trying to read a value from the previous row is
that "previous row" can be redefined at any instant, depending on how the
user filters or sorts the data. The only safe approach, therefore, is to use
the RecordsetClone of the form.

1. Paste the function below into a standard module (created from Modules tab
of Database window), and save.

2. Open your form in design view, and add a text box. Put this in its
Control Source:
=GetPreviousValue([Form], "PutYourFieldNameHere")
replacing the bit in quotes with the name of the field you wish to read
from the previous row.


Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function
 
Back
Top