Share via

Using a combo box to find a record in a form

Anonymous
2015-10-21T22:14:19+00:00

Using Access 2016... in the steps below, the THIRD option in Step 5 (underlined)... to have the wizard set up a link to check for a record within a form DOES NOT DISPLAY. Therefore, there is NO OPTION to search for a record within a form using the combo box wizard. IT IS THERE in Access 2003 through 2013, but it is NOT there in Access 2016. Only the first two options in Step 5 display in Access 2016.

Anybody have a solution?

Create a list box or a combo box by using a wizard

  1. Right-click the form in the Navigation Pane, and then click Design View. NOTE   This procedure assumes that the form is bound to a table or query. Some of the steps will not apply if the form is unbound. To determine if the form is bound to a table or query, press F4 to display the property sheet. On the Data tab of the property sheet, the Record Source property box displays the table or query that the form is bound to.
  2. On the Design tab, in the Controls group, ensure that Use Control Wizards  is selected.
  3. Click either the List Box  tool or the Combo Box  tool.
  4. On the form, click where you want to place the list box or combo box.
    • Depending on your choice, the List Box Wizard or the Combo Box Wizard starts.
  5. When the wizard asks how you want to get the values for the control, do one of the following:
    • If you want to display the current data from a record source, click I want the list box/combo box to look up the values in a table or query.
    • If you want to display a fixed list of values that will seldom change, click I will type in the values that I want.
    • If you want the control to perform a find operation, rather than serve as a data entry tool, click Find a record on my form based on the value I selected in my list box/combo box. This creates an unbound control with an embedded macro that performs a find operation based on the value the user enters.
  6. Follow the instructions for specifying how the values will appear.
  7. If you chose one of the first two options on the first page of the wizard, the wizard asks what you want Access to do when you select a value. Do one of the following:
    • To create an unbound control, click Remember the value for later use. This means that Access will hold the selected value until the user changes it or closes the form, but it will not write the value to a table.
    • To create a bound control, click Store that value in this field, and then select the field you want to bind the control to.
  8. Click Next and type a label for the control. This label will be displayed next to the control.
  9. Click Finish.
Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2015-10-21T22:45:20+00:00

    If you've been working with Access since 2003, then it's high time you get away from wizards and macros and learn to do this using VBA. 

    In your combo box's AfterUpdate event, add the following code:

    Private Sub CbxExample_AfterUpdate()

        On Error GoTo Err_Process

        Dim strCriteria As String

        'Make sure to format the compare item accordingly for text, date or numeric data

        'For text, enclose compare item in quotes (")

        'For dates, enclose comprare item in pound signs (#)

        'Numeric data does not require special format characters

        '

        'Examples:

        'Text - strCriteria = "<your field name>=" & Chr(34) & CompareItem & Chr(34)

        'Date - strCriteria = "<your field name>=#" & CompareItem & "#"

        'Numeric - strCriteria = "<your field name>=" & CompareItem

        If (Not IsNull(Me.CbxExample)) Then

            strCriteria = "<your field name>=" & Me.CbxExample

        End If

        Me.Filter = strCriteria

        Me.FilterOn = (strCrtieria <> "")

    Exit_Process:

        Exit Sub

    Err_Process:

        MsgBox Err.Number & " " & Err.Description

        Resume Exit_Process

    End Sub

    2 people found this answer helpful.
    0 comments

14 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-11-08T01:00:50+00:00

    I was having the same issue with the 2016 Access (and was overwhelmed by the possible need for coding after reading all the responses to your original question), but I was able to figure out how to make the table "bound" and then the wizard gave me the third option!

    To get the combo/list box wizard to show the third option of "Find a record on my form based on the value I selected in my list box/combo box," your form must be bound.

    To do so, in layout view or design view first go the the property sheet for the form you are adding the combo or list box to. The selection type should be simply "Form". On the Data tab, under Record Source select from the drop-down the table or query you wish to "bind" the form to. If the form is "unbound" the Record Source should read SELECT and then list in [brackets] all of your database's tables and queries alphabetically. Once you have selected the table or query you want you form bound to, you can reopen the combo/list box wizard and your third option to find a record on the form should appear.

    This is where I got the helpful info: https://support.office.com/en-us/article/Create-a-list-of-choices-by-using-a-list-box-or-combo-box-70abf4a9-0439-4885-9099-b9fa83517603

    The most important text that helped me fix it: To determine if the form is bound to a table or query, press F4 to display the property sheet. On the Data tab of the property sheet, the Record Source property box displays the table or query that the form is bound to.

    1 person found this answer helpful.
    0 comments
  2. ScottGem 68,810 Reputation points Volunteer Moderator
    2015-10-22T16:46:15+00:00

    but how do I make it "bound" since it is "unbound"?

    Like I said a bound form has a table or query or SQL statement as its Recordsource. Generally, I use the Form wizard to initially build my forms. This prompts you to select a table to be the Recordsource. 

    If you just open a form in Design mode and just start adding controls, then you have an unbound form. 

    To turn an unbound form into a bound one, just select a Recordsource for the form and then select Controlsources for the controls.

    1 person found this answer helpful.
    0 comments
  3. DBG 11,711 Reputation points Volunteer Moderator
    2015-10-22T16:17:19+00:00

    Hi. Did you try the steps I posted above? Did it work? Like I said, I don't have a copy of 2016, but I suspect that it behaves the same way as previous versions. To bind your form, you just need to make sure the Record Source property is set to a table, query, or SQL statement. Hope that helps...

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2015-10-22T15:09:41+00:00

    RunningMan.... thank you for taking the time to show a solution using VBA. For the most part I understand the coding, but I've never used VBA at all before... so, it looks a little like Chinese to me (no disrespect to people of Chinese heritage... just means it looks like a foreign language). I don't program professionally and only use Access now and then when I have a project for which a database seems to offer the best solution.

    In THIS application I'm writing a DB for our church to track student's progress through a correspondence course. I was wanting to have a text data field to search for the first name of students to quickly find and navigate to their record in the database to update their progress.

    I'm aware there are two other ways to do this:

    1. put the cursor in the first name field and press Control F and type what you want to find in the search box;
    2. put the cursor in the first name field and type what you want to find in the search filter at the bottom of the screen

    The former 2003, 2007, 2010 and 2013 Access programs HAD that feature as the 3rd option in the combo box wizard... to find a record in a form... but, alas, Access 2016 does NOT display that 3rd option... even though the help file in Access 2016 says it does. I've watched several YouTube videos that show how to do it WITH the 3rd wizard option in combo boxes... and, that looked quite easy. But, without that wizard option I was completely stumped.

    I'm sure your VBA solution will work IF I can figure out HOW to implement it.

    Thank you again for taking the time to write another solution.

    IseeEye Doc

    0 comments