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:
The table has 5 fields:
- “First Name”
- “Last Name”
- “Phone Number”
We want to create the following form:
The user can select an ID from the combo box, and the fields will be updated accordingly:
I have previously explained about populating combo boxes with values from a table in the article below:
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:
On the property sheet, on the data tab click on the 3 dot button of the Control Source property:
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:
=DLookUp("[First Name]","Students","[Students].[ID] = " & Str([Combo17].[Text]))
is the name selected for the combo box in this example. The expression
returns the current value selected in the combo box. The expression
converts the value in the combo box to a string variable and concatenates it to the rest of the criterion string.
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: