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:

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:

The user can select an ID from the combo box, and the fields will be updated accordingly:

Result:

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

• Access Combo box Populate With Table Values

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 2:

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

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:

• DLookUp, Access Expression Builder

Write the following expression in the expression builder that opens:

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:

• Fill Fields Based on Combo Box.Zip

See also:

• DLookUp, Access Expression Builder
• Access Combo box Populate With Table Values