Access Form, Populate TextBoxes Based On ComboBox

In this article I will explain how you can create textboxes in an Access form that will be filled based on the value selected from a combo box and a Table.

The Access form has multiple textboxes and a single combo box. The user will be able to select a value from the combo box. Based on the value selected from the combo box, the text boxes will be populated with data from a Table.

Consider the table below:
Table

The table name is:

  • “Students”

The table has 5 fields:

  • “ID”
  • “First Name”
  • “Last Name”
  • “Phone Number”
  • “Email”

We want to create the following form:
Form
The user can select an ID from the combo box, and the fields will be updated accordingly:
Combobox
Result:
Result

I have previously explained about populating combo boxes with values from a table in the article below:


Step 1:

Open the form in design mode. Select the First Name text box by either clicking on it or from the drop list in the property sheet:
Step 1


Step 2:

On the property sheet, on the data tab click on the 3 dot button of the Control Source property:
Control Source


Step 3:

In order to implement the functionality we are after we will need to use the DLookUp function. for more information about this function please see the link below:

Write the following expression in the expression builder that opens:
Expression Builder

Expression:

=DLookUp("[First Name]","Students","[Students].[ID] = " & Str([Combo17].[Text]))

[Combo17 is the name selected for the combo box in this example. The expression [Combo17].[Text] returns the current value selected in the combo box. The expression & Str([Combo17].[Text])) converts the value in the combo box to a string variable and concatenates it to the rest of the criterion string.


Step 4:

Repeat steps 1, 2 and 3 for the rest of the text boxes using the expressions below.

Last Name text box:

=DLookUp("[Last Name]","Students","[Students].[ID] = " & Str([Combo17].[Text]))

Phone Number text box:

=DLookUp("[Phone Number]","Students","[Students].[ID] = " & Str([Combo17].[Text]))

Email text box:

=DLookUp("[Email]","Students","[Students].[ID] = " & Str([Combo17].[Text]))

You can download the sample file used in this article from the link below:

See also:

9 thoughts on “Access Form, Populate TextBoxes Based On ComboBox”

  1. Avatar
    gmonak says:

    Thanks for this tutorial. But is it possible to use First Name or Last Name in the combobox and use either of them to set the values for the other textboxes?

    It seems the DLookup function only works for the Primary Key field being used in the combobox. I’d love to have it the other way round.

    Thank you

    1. Avatar
      pedrumj says:

      Hi there

      First Name and Last Names are text values. Therefore the DLOOKUP statement would look something like this:

      =DLookUp(“[First Name]”,”Students”,”[Students].[First Name] = “”” & [Combo17].[Text] & “”””)

      As you can see I’ve removed the “Str” function and added additional quotes to the start and end of the last part.

      Please let me know if you are still having trouble.

      1. Avatar
        gmonak says:

        Thanks so much. It really works. God bless you.

        The only slight problem I have with it is that when there’s no value in the combobox, all the textboxes display #Type!. I’m sure it’s some kind of error but I can’t figure out how to make the textboxes display blank when the combobox is blank, especially when the form starts.

        I have another issue. How do I populate, say, combobox2 with a list from table based on value selected in, say, combobox1.

        Thanks!!!

        1. Avatar
          pedrumj says:

          Please send me your file and I will have a look at it.

      2. Avatar
        Efrem says:

        It is saying error when icselecr something in the combo box

  2. Avatar
    gmonak says:

    Hi, and thanks for the help. I’ve been able to solve that problem. Now I want you to help me with another problem.

    I’m designing a query for all numbers in a field of table greater than a number given number in a textbox to be displayed.

    I’m using: >=[Forms]![Form_Name]![Textbox_Name] in the criteria but the query table returns no values.

    However, if I type: >=2 in the criteria field of the query, it returns all the records with their field number greater than or equal to 2.

    How do I please go about it???

    Thanks

    1. Avatar
      pedrumj says:

      Hi there

      Could you please send me the file so that I can have a look at it. Thanks.

  3. Avatar
    C. Shah says:

    Can I populate textbox fields from a query?

  4. Avatar
    Kyra Ledkins says:

    I need to do that exact thing. The table that I am working from is called “Product Master Data” instead of “Students.” My dropdown menu is called “Protean Resource” instead of “ID.” The name of the textbox that I am editing is “Product,” which would be the equivalent of ” First name” in this example. What I have written is:

    =DLookUp(“[Product],”Product Master Data”,[Product Master Data].[Protean Resource] =”& Str([Combo146],[Text]))

    It’s not working for me. I’m sure there is something wrong with my formula but I cannot figure out what. Any ideas?

Leave a Reply

Your email address will not be published. Required fields are marked *