Multi field fill from single field lookup

[jmills]

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

 

[Gasman]

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

 

[missinglinq]

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:

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)>

 

[jmills]

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.