How to display data from other fields in the same record that’s selected in a combo box.
Q: Robert Colum wants to know how to display data from other fields in the same record that is selected in a combo box.
A: This can be done easily using the Column property of the combo box. First, make sure that all the fields you want to see in the other controls are in the combo box’s row source (you can set their column widths to 0, so they don’t display in the list, if desired). Then use the following syntax in the ControlSource property of a text box to display a value from the selected row in the combo box:
=[cboStorageLocation].Column(2)
where cboStorageLocation is the name of the combo box. Note that column numbers are zero-based, so Column(2) is actually the third column in the combo box’s list.
See p. 198 of my Access Inside-Out book for an example of column reference.
Later, Mr. Colum wrote to ask how to display data from other columns in a query for use as a report’s record source. For that purpose, base the query on the combo box’s row source table or query, and use a criterion on the combo box’s bound field that matches the value in the combo box on the form. Then you can get the values from the same record for all the other fields in the query.
