We help IT Professionals succeed at work.

Access VBA - Open a form, jump to the record found in a recordset

Matt Chornomaz
on
I'm sure this is something simple, but i can't seem to find it.  I think i'm probably using the wrong search terms.  So if this was answered elsewhere, i apologize.

Here are the steps i'm trying to take...
- I open a recordset in vba based on some conditions.  Note: there will always be multiple records returned in this recordset.
- I then find one specific record in that recordset.
- I want to then open up a form with the multiple-record recordset behind it (so user can navigate back and forth through the found records).
- Then i want the form to jump to the specific record i previously found in the code - This is the part i need help with

Note: FindFirst will always be successful (no need to check for matches).

With myRecordset
    .FindFirst "hearingdate = " & HearingDate
End With

DoCmd.OpenForm "frmHearing", acNormal

Set Forms!frmHearing.Recordset = myRecordset

-----This is the question...now that the form is open with the desired recordset-------
-----I want to jump to the specific record that was found using FindFirst----

Forms!frmHearing.Requery

Set myRecordset = Nothing
Set myDB = Nothing

Open in new window


I know this must be super simple.  But i just can't seem to locate the solution for whatever reason.

Thanks!
Comment
Watch Question

President / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Commented:
You can do it different ways

    Dim frm                   As Access.Form
    Dim rs                    As DAO.Recordset
    Dim sCriteria             As String

    DoCmd.OpenForm "frmHearing"
    Set frm = Forms![frmHearing].Form
    Set rs = frm.RecordsetClone
    sCriteria = "[hearingdate] = " & HearingDate
    rs.FindFirst sCriteria
    frm.Bookmark = rs.Bookmark
    Set rs = Nothing
    Set frm = Nothing

Open in new window


You could also just filter within the OpenForm directly
DoCmd.OpenForm "frmHearing", , , "[hearingdate] = " & HearingDate

Open in new window


I hope HearingDate is properly formatted #mm/dd/yyyy#


You could also pass the filter as an OpenArgs variable of the OpenForm and use the Open Event to apply it.
Matt ChornomazProbation Officer

Author

Commented:
Thanks Daniel!  The first method got me on the right track.  It's working great now.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
My pleasure.