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.
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:
Contents
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:
Write the following expression in the expression builder that opens:
Expression:
=DLookUp("[First Name]","Students","[Students].[ID] = " & Str([Combo17].[Text]))
is the name selected for the combo box in this example. The expression [Combo17
returns the current value selected in the combo box. The expression [Combo17].[Text]
converts the value in the combo box to a string variable and concatenates it to the rest of the criterion string.& Str([Combo17].[Text]))
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”