Multi field fill from single field lookup (1 Viewer)

I have a form that we use for data entry and it has a combo box lookup for Builder's name. In the builder lookup table I have Builder Name, salesman, contact, phone, and a few other fields. When the builder field is updated on the form, I want the form to get the three other corresponding fields from the builder lookup table and populate the form so the data can be stored in my Project_TBL.

I need to store the data in the Project_TBL and not just associate builder name in Project_TBL with builder name in the lookup table because the salesman and the other fields can change over time and I need to be able to look back and see who was the salesman a year ago and not just the current salesman assigned to the builder.

I know I need to use the After Update Event Procedure but I am unsure how to write the code.

Any help would be appreciated.

Thanks JRM

Just bring in the data in the recordsource of your combo and then assign the extra columns to your controls.

Me.Salesman = Me.cboBuilder.Column(x) where x is the column number. Note, numbers start at zero.

HTH

To expand on that just a little bit...

Set up your Combobox using the Wizard and include the Fields you need, from Left-to-Right.


Here's a general example of assigning values from a Combobox to Textboxes. If, in the Row Source for the Combobox they appear, reading left-to-right, as

Field1 | Field2 | Field3

Then this code will assign them to corresponding Textboxes on the Form:

Code:Copy to clipboard

Private Sub YourComboBox_AfterUpdate()
   Me.txtField1 = Me.YourComboBox.Column(0)
   Me.txtField2 = Me.YourComboBox.Column(1)
   Me.txtField3 = Me.YourComboBox.Column(2)
End Sub

Note that the column index is Zero-based.

Also be sure that the number of fields you see, when the values are dropped down, are the same as the Column Count (Properties - Format - Column Count.) If the Combobox was made with a Wizard the PK field may not be visible.

Linq ;0)>

Thank you so much. I was struggling with all the fields not coming in and thought I was doing something wrong, my column count was set at 1.