Subform Query using values from previous record

  • Thread starter Thread starter Ted
  • Start date Start date
T

Ted

I have a subform that is based on a query using a
calculated control from the main form. As I move from
record to record the filter in the query uses the
calculated value from the previous record instead of the
current. How do I get the query to run only after the
current record completes its' calculations?
 
If you do not need to filter or sort by the calculated value, you can
perform leave the calculation out of the query and perform it in the form.
This way it will always refer to the previous row in the form regardless of
how the form is filtered or sorted.

Paste the function below into a standard module and save.
Add a text box to your form, and put this in its Control Source property:
=GetPreviousValue([Form], "MyField")
Leave the Form bit exactly as it is.
Replace MyField 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