Set up a form control that updates when a value in a combo box is selected

Question

I have a combo box on an Order Entry Form I designed that allows me to select from a dropdown of Company Names from a table entitled tblCustomers. The row source for this control is:

SELECT CustomerID, CompanyName FROM tblCustomers;

The bound column is 1 so that I can store the CustomerID in the underlying tblOrders table.

My question is:

I would like a text box on the form that displays the SalesPerson (which is also in the tblCustomers table) associated with the Customer chosen in the combo box.

I figured it out. I used: Private Sub Customer_AfterUpdate() Dim CustID As Long CustID = Me.Customer Me.SalesRep = (DLookup("SalesPerson", "tblCustomers", "CustomerID=" & CustID)) End Sub — prayingmantes

Fionnuala Fionnuala · Accepted Answer · 2013-02-18T20:01:28

You can take advantage of the column property in combos:

If the row source of your combo is:

 SELECT CustomerID, CompanyName, SalesPerson FROM tblCustomers;

You can set the control source of a textbox to refer to column property, counting from zero:

 =ComboX.Column(2)

You will need to set the column count to 3.

Set up a form control that updates when a value in a combo box is selected

1 Answers