Hey Cuttsy,
The way to do this depends upon whether the textbox is bound to a field in the form's Record Source or not.
Unbound Textbox
In this example, we'll assume that your desired information is in a table named tblPersons, and that this table contains four fields: ID (the person's ID); FirstName (the person's first name); MiddleName (the person's middle name); and LastName (the person's last name).
Set the Row Source Type property of the combo box (assume it's named cboPersonID) to "Table/Query". Set the Row Source to a query that is based on tblPersons and that selects the ID and LastName fields:
' ***** Code Start *****
SELECT ID, LastName
FROM tblPersons
ORDER BY ID;
' ***** Code End *****
Set the Bound Column property of cboPersonID to 1.
Set the Column Count property to 2.
Set the Column Widths property to this expression: 1";0".
Set the Control Source of a textbox (assume it's named txtPersonName) to this expression:
=[cboPersonID].[Column](1)
Note that the Column property of a combo box is "zero-based," meaning that the first column (field) in the combo box's Row Source is column 0, the second column is column 1, etc.
When you make a selection of a person's ID in the combo box, the textbox will automatically display the person's name. You can extend this example to include more textboxes by adding more fields to the combo box's Row Source query and setting the Control Source of each textbox to the appropriate column number of the combo box.
Bound Textbox
We'll use the same setup for the combo box as described in the "Unbound Textbox" example above. The difference in this situation is that an expression cannot be used as the Control Source for the textbox. Instead, we use VBA code on the AfterUpdate event of the cboPersonID combo box to place the desired value into the txtPersonName textbox.
' ***** Code Start *****
Private Sub cboPersonID_AfterUpdate()
Me.txtPersonName.Value = Me.cboPersonID.Column(1)
End Sub
' ***** Code End *****
When you make a selection of a person's ID in the combo box, the textbox will automatically display the person's name. You can extend this example to include more textboxes by adding more fields to the combo box's Row Source query and adding additional steps to the code so that each textbox receives a value from the appropriate column number of the combo box.
Hope this helps,
Rusty
