Open form to specific record, but not filter to it (1 Viewer)

[gojets1721]

I have a tabular form which lists a bunch of customer complaints. For each record, there's an 'open' button to view that specific record in a more detailed form. The below code for the open button works to pull up that record in the separate form, but it filters down to that exact record. Ideally, I would want to still be able to bounce around from record to record in the detailed form after it is pulled up. Any suggestions?

Private Sub txtOpen_Click()
On Error GoTo txtOpen_Click_Err

    DoCmd.OpenForm "frmDetails", , , "[ComplaintNumber] =" & Nz(Me.[ComplaintNumber], 0) & ""
    DoCmd.Close acForm, "frmComplaintList"

txtOpen_Click_Exit:
    Exit Sub

txtOpen_Click_Err:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"
    
    Resume txtOpen_Click_Exit

End Sub

 

[theDBguy]

One approach is to pass the ComplaintNumber in the OpenArgs argument of the OpenForm method and then process it in the Load event of the other form to navigate to that record using a recordset and the bookmark property.

 

[June7]

A common approach is to use RecordsetClone and Bookmark to locate record. Review https://docs.microsoft.com/en-us/office/vba/api/Access.Form.RecordsetClone

And don't even need to declare recordset variable. Instead of the WHERE CONDITION argument, pass criteria with OpenArgs.

    With Me.RecordsetClone
        .FindFirst "LabNum='" & Me.OpenArgs & "'"
        Me.Bookmark = .Bookmark
    End With

 

[MajP]

DoCmd.OpenForm "frmDetails
Forms("frmDetails").recordset.findfirst "[ComplaintNumber] =" & Nz(Me.[ComplaintNumber], 0)

 

[gojets1721]

DoCmd.OpenForm "frmDetails
Forms("frmDetails").recordset.findfirst "[ComplaintNumber] =" & Nz(Me.[ComplaintNumber], 0)

This worked! Thanks so much

 

[CJ_London]

Just be aware the where parameter of openform is a filter, not a criteria. Simply remove the filter to see all the records