Use an Access Combo Box to Find a DAO Record
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.

- 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.
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.
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.
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;
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.
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
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.

Update the combo box for the active query
Use this option when each form button opens a different query and the list should contain only clients from that query.
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.

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.




