Others

Use an Access Combo Box to Find a DAO Record

Chanuka GeekiyanageChanuka Geekiyanage Nov 14, 2026 869 views

Question details

Select a client by name in an unbound Access form and use the unique ClientID to retrieve the client's fields with DAO.

Use an Access Combo Box to Find a DAO Record
Product
Microsoft Access
Device & OS
not provided
Scenario
An unbound form uses DAO recordsets and navigation buttons to display client information in text boxes.
Observed behavior
The combo box should show client names, retain each unique ClientID as its value, and populate the unbound text boxes when a client is selected.
Before you start

Check that the Clients table contains ClientID, FirstName, and LastName, and confirm the names of the combo box and text boxes in the form's Property Sheet. The example below assumes ClientID is a numeric field and that DAO is available in the Access VBA project.

Solution 1Recommended

Look up the selected ClientID in an After Update event

Use this approach to show names in the combo box while retrieving the corresponding client by its unique numeric ID.

1
Configure the combo box

Open the form in Design View, select the combo box, and open its Property Sheet. Set Name to cboGoToClient, Row Source Type to Table/Query, and Row Source to: SELECT ClientID, LastName & ", " & FirstName AS ClientName FROM Clients ORDER BY LastName, FirstName;

2
Hide the ID column

In the combo box's Property Sheet, set Bound Column to 1, Column Count to 2, and Column Widths to 0cm;5cm. The first column remains the selected value but is hidden from the list.

3
Add the lookup event

On the Property Sheet's Event tab, choose After Update, select [Event Procedure], and click the builder button. In the VBA editor, add this code, adjusting the control and field names if needed: Private Sub cboGoToClient_AfterUpdate() Dim rst As DAO.Recordset Dim strSQL As String If IsNull(Me.cboGoToClient.Value) Then Exit Sub strSQL = "SELECT ClientID, FirstName, LastName FROM Clients WHERE ClientID = " & CLng(Me.cboGoToClient.Value) Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) If Not rst.EOF Then Me.txtClientID = rst!ClientID Me.txtFirstName = rst!FirstName Me.txtLastName = rst!LastName Else MsgBox "The selected client was not found." End If rst.Close Set rst = Nothing End Sub

4
Test the selection

Save the form, switch to Form View, and select a client from cboGoToClient. Check that txtClientID, txtFirstName, and txtLastName show the selected client's values.

Look up the selected ClientID in an After Update event
Keep navigation in sync: This code fills the text boxes but does not move the separate DAO recordset used by your navigation buttons. If navigation must continue from the selected client, locate that ClientID in the active recordset and populate the controls from it instead. For a text ClientID, use a properly quoted and escaped text criterion rather than CLng.
Free Microsoft Office alternative

Work with client lists in WPS Spreadsheets

WPS Office is a free, lightweight alternative for working with spreadsheets, documents, and presentations. Its familiar interface and Microsoft Office format compatibility make it easy to move existing files into WPS. It does not run Access forms or replace the DAO lookup described here.

Organize client IDs and names in WPS Spreadsheets.Open and edit Microsoft Excel XLSX files, as well as Word DOCX and PowerPoint PPTX files.Use a familiar interface when moving everyday Office files to WPS.Work with a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why should the combo box use ClientID instead of the name?

Names can be duplicated, so a name-only lookup may return the wrong client. Set Bound Column to 1 to use the unique ClientID while displaying the name in the second column.

Why are the first and last names appearing in separate columns?

In a value list, a semicolon separates columns. Add the ID and combined name as two values, or set Row Source Type to Table/Query and use an expression such as LastName & ", " & FirstName for the name column.

How do I hide ClientID without losing it as the selected value?

In the combo box Property Sheet, set Bound Column to 1 and Column Count to 2, then set the first Column Widths value to zero, for example 0cm;5cm.

Will selecting a client move my navigation recordset?

Not with the separate lookup recordset shown above. To keep navigation synchronized, find the selected ClientID in the active DAO recordset and read the form values from that recordset.

Can I show more information for clients with the same name?

Yes. Add a distinguishing field, such as an address, to the combo box Row Source, increase Column Count, and set a visible width for the added column. Keep ClientID as the bound column.