Combo box help

[Jim]

I'm trying to populate multiple fields on a purchase order form from a
selection in a combo box. Currently I have all the relevant data from the
vendor table as fields in the row source for the combo box as follows:



SELECT [Vendor & Bill Addresses].VendorID, [Vendor & Bill
Addresses].Company, [Vendor & Bill Addresses].Address, [Vendor & Bill
Addresses].City, [Vendor & Bill Addresses].State, [Vendor & Bill
Addresses].Zip, [Vendor & Bill Addresses].PhoneNumber, [Vendor & Bill
Addresses].FaxNumber FROM [Vendor & Bill Addresses] ORDER BY [Vendor & Bill
Addresses].Company;



If I use =cboCompany.column(1) for the address field, it fills in the way it
should. But if I use =cboCompany.column(2), etc for the rest of the boxes,
they don't fill in, they're just blank. What is the best way to fill in
multiple boxes on a form by selecting a value from a combo box?

I also need to tie the form to a purchase order table to fill in things like
order date, ship via, etc. What is the best way to tie the tables together
and still have the combo box fill in all the data?

Thanks for any help.



Jim

 

[Douglas J. Steele]

Based on the SQL you've posted, the Address should be cboCompany.column(2),
not cboCompany.column(1)

Are you saying you've set the ControlSource of a text box to
=cboCompany.column(1)? I'd suggest putting code in the AfterUpdate event of
the combo box to populate all of the text boxes:

Private Sub cboCompany_AfterUpdate()

Me.txtAddress = Me.cboCompany.column(2)
Me.txtCity = Me.cboCompany.Column(3)
' etc.

End Sub