This is one of those extremely fundamental aspects of any form, adding some easy record search system so users can quickly locate and retrieve a record.

In this article, I thought I’d several techniques that can be used in combination with a Combo Box to retrieve a specific record.

Below, you will find the following approaches:

• Using the Filter property
• Using FindFirst/Bookmark
• Binding the Record Source to the Combo box
• Using a dynamic Record Source

Using the Filter property

For this approach, we start by binding the form to the complete RecordSet (table – all of the records) and then use the After Update event of the combo box to apply a filter based on the selection. To do so, we would have:

Form Record Source

SELECT * FROM Contacts;

Combo Box After Update Event

Me.FilterOn = False
If IsNull(Me.cbo_Search_Name) = False Then
    Me.Filter = "[ContactId] = " & Me.cbo_Search_Name
    Me.FilterOn = True
End If

When a selection is made, assuming we are filtering on the PK field, it will restrict the form to a single record.
 

Using FindFirst/Bookmark

In this approach, again we bind the form to the complete recordset (table – all of the records) and then perform a FindFirst to locate and then navigate to that Bookmark matching record.

Form Record Source

SELECT * FROM Contacts;

Combo Box After Update Event

Dim rs                    As DAO.Recordset

If IsNull(Me.cbo_Search_Name) = False Then
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ContactId] = " & Me.cbo_Search_Name
    Me.Bookmark = rs.Bookmark
End If

In this case however, it will bring you to the requested record while still leaving the other records available for you to navigate through, if so desired.
 

Binding the Record Source to the Combo box

In this approach, we add a criteria directly to our form Record Source back to the search Combo box.

Form Record Source
To only hide all records until a selection is made

SELECT Contacts.*
FROM Contacts
WHERE (Contacts.ContactId=[Forms]![frm_Contact_RecordSource]![cbo_Search_Name]);

To show all records when the control is empty (no selection yet)

SELECT Contacts.*
FROM Contacts
WHERE ((Contacts.ContactId=[Forms]![frm_Contact_RecordSource]![cbo_Search_Name]) OR ([Forms]![frm_Contact_RecordSource]![cbo_Search_Name] Is Null));

For more information on this, review:

Combo Box After Update Event

Me.Requery

Using a dynamic Record Source

In this approach, we use VBA to dynamically change the form’s Record Source whenever the user make a selection.

Form Record Source
To load showing no records

SELECT Contacts.*
FROM Contacts
WHERE 1=0;

To load showing all records

SELECT Contacts.*
FROM Contacts;

Combo Box After Update Event

Me.RecordSource = "SELECT * FROM Contacts WHERE [ContactId]=" & Me.cbo_Search_Name
Me.Requery

Best Practices

Ass always, be sure to implement proper error handling and also, although I use SELECT * FROM Contacts in my examples, you truly should avoid selecting all the field in any scenario (Form, Report, …) and only return the required fields for whatever you are doing.
 

Which Approach Is Best?

That all depends on you Back-end, quantity of records and the user experience you want to give.

I personally like the Dynamic Record Source approach. I like loading the form with no records which makes it load faster and avoid potential issue of user accidentally overwriting an entry and then letting the user make a selection to go an retrieve that single record. This is a very efficient technique and reduce data being pushed and pulled by the form.

At the same time that I say that, for simpler Access systems, and when I still want the user to have access to all the record so they can still navigate through the records, I typically employ the FindFirst technique.

But the choice is yours. Try them out yourself and see which one best suits your needs.
 

Demo Database

Feel free to download a 100% unlocked copy of the sample database (tested on Win10/Acc365 & Win10/Acc2013).

Notice About Content/Downloads/Demos

Disclaimer/Notes:

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

Page History