Share via

MS Access- How to create a list from a Combo Box

Patrick Snyder 0 Reputation points
2025-12-05T18:24:30.45+00:00

On my form, I want a combo box that displays a list from my table that based on a select from the table, returns a list of matching records.

Microsoft 365 and Office | Access | For business | Windows
0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2025-12-05T18:24:41.54+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    To create a combo box in MS Access that displays a list of values from a table and returns matching records based on a selection, you can follow these steps:

    1. Create the Combo Box: In your form, add a combo box control. You can do this by using the Controls toolbox in the form design view.
    2. Set the Row Source: Set the RowSource property of the combo box to the table or query that contains the values you want to display. This can be done in the property sheet of the combo box. For example, if your table is named Products, you can use a SQL statement like SELECT ProductName FROM Products; to populate the combo box with product names.
    3. Limit to List: If you want to restrict the user to only the items in the list, set the LimitToList property of the combo box to Yes. This will prevent users from entering values that are not in the list.
    4. Handle Selection: To return matching records based on the selection made in the combo box, you can use the AfterUpdate event of the combo box. In this event, you can write code to filter the records displayed in the form based on the selected value from the combo box.
    5. Requery the Form: After setting the filter based on the combo box selection, use the Requery method to refresh the form and display the matching records.

    By following these steps, you can effectively create a combo box that displays a list from your table and returns matching records based on user selection.

    0 comments
  2. Duane Hookom 26,555 Reputation points Volunteer Moderator
    2025-12-05T18:55:52.4766667+00:00

    You stated "On my form, I want a combo box that displays a list from my table that based on a select from the table, returns a list of matching records."

    The row source is basically the table or query name or an SQL statement. The important point is the bound column should have unique values. To display values from a table that match the selection, you have many options including placing a subform on your form that uses the Link Master/Child properties to filter the subform records based on the combo box value.

  3. DBG 11,531 Reputation points Volunteer Moderator
    2025-12-05T19:50:55.3933333+00:00

    As already mentioned, the Row Source property controls what is listed in the dropdown of the combobox. If you want it to list selected matching records from a table, make sure you assign it the query or SQL statement that produces that list of matching records and make sure the Row Source Type is set to Table/Query. Hope that helps...

  4. Ken Sheridan 3,546 Reputation points
    2025-12-06T13:55:48.41+00:00

    You might like to take a look at FindRecord.zip in my Dropbox public databases folder at: 

    https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

    This little demo file includes an option to 'Open form to filter by a non-key value. This uses a simple example where a LastName value can be selected in an unbound combo box to filter the form so that it returns only those Contact records with the selected last name. The combo box's RowSource property is the following SQL statement:

    SELECT
    LastName,
    
FROM
    Contacts
UNION
SELECT
    "All Names",
    
FROM
    Contacts
ORDER
    SortColum,
    LastName;

    This returns a distinct list of last names from the Contacts table, along with an 'All Names' item as the first row in the list, which enables the user to show all records from the table. When the user selects a name the combo box's AfterUpdate event procedure filters the form to those names only, using the following code:

    Private Sub cboLastname_AfterUpdate()
    Const MESSAGETEXT = "No matching records found."
    Dim ctrl As Control
    Dim strFilter As String
    
    Set ctrl = Me.ActiveControl
    
    strFilter = "LastName = """ & ctrl & """"
    
    If ctrl = "All names" Then
        ' turn off filter
        Me.FilterOn = False
    Else
        If Not IsNull(DLookup("ContactID", "Contacts", strFilter)) Then
            ' flter form to name selected in combo box
            Me.Filter = strFilter
            Me.FilterOn = True
        Else
            ' inform user if no matching records found and show all records
            MsgBox MESSAGETEXT, vbInformation, "Warning"
            Me.FilterOn = False
            Me.Requery
        End If
    End If
       
End Sub
    0 comments
  5. Ken Sheridan 3,546 Reputation points
    2025-12-06T14:18:44.85+00:00

    My first reply failed to reproduce the SQL statement correctly, and can't be edited. The following is the SQL statement as plain text:

    SELECT LastName, 1 AS SortColum FROM Contacts UNION SELECT "All Names", 0 FROM Contacts ORDER BY SortColum, LastName;

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.