We help IT Professionals succeed at work.

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

Matt Chornomaz
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----


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.

Watch Question

President / Owner CARDA Consultants Inc.
Distinguished Expert 2018
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


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

My pleasure.