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:

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

  1. 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. 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. 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. pedrumj says:

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

  2. 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. pedrumj says:

      Hi there

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

  3. C. Shah says:

    Can I populate textbox fields from a query?

Leave a Reply

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