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”

Leave a Reply

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