(Q) How do I have a textbox automatically fill in with a
value after I select an item from a combo box on a form (e.g.,
select a person's ID in a combo box, and have the person's last
name automatically display in a textbox)?
(A) 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:
SELECT ID, LastName
FROM tblPersons
ORDER BY ID;
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.
Private Sub cboPersonID_AfterUpdate()
Me.txtPersonName.Value = Me.cboPersonID.Column(1)
End Sub
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.
|
