CODEWITHSUNDEEP
vbams-access
Benjamin E.
Benjamin E.

Reputation: 5162

Access VBA find record in code and then set to form

The following code produces this error runtime error '2455' you entered an expression that has an invalid reference to the property Form/Report.

The RecordSource behind the subforms is an actual table name.

When debugging, it stops on Set rs = ... or the next line.

Private Sub Form_Current()
    Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM " & Forms!frmTrafficAndLogistics!sfrTnLPO.Form.RecordSource)
    If Not (rs.EOF And rs.BOF) Then rs.MoveFirst

    rs.FindFirst "PONum = " & Me.PONum

    If Not rs.NoMatch Then
        Forms!frmTrafficAndLogistics!sfrTnLPO.Form.Bookmark = rs.Bookmark
    End If

    rs.Close
    Set rs = Nothing
End Sub

Upvotes: 0

Views: 7560

Answers (1)

PaulFrancis
PaulFrancis

Reputation: 5809

You will only need to assign the Form's recordsource to the Recordset object, like

Private Sub Form_Current()
    Dim rs As Recordset

    Set rs = Forms!frmTrafficAndLogistics!sfrTnLPO.Form.RecordSetClone

    If Not (rs.EOF And rs.BOF) Then rs.MoveFirst

    rs.FindFirst "PONum = " & Me.PONum

    If Not rs.NoMatch Then
        Forms!frmTrafficAndLogistics!sfrTnLPO.Form.Bookmark = rs.Bookmark
    End If

    rs.Close
    Set rs = Nothing
End Sub

Upvotes: 3

Related Questions