List Box vs. Combo Box

[bsdixon]

I have a form where I select a vendor name out of a combo box.

The rest of the vendor information then defaults into combo boxes below. I want these to actually be list boxes so that the user does not have to select the rest of the vendor information. The only problem is that when I turn them into list boxes the data does not save into the underlying table. How do I get the list box information to save??

 

[Pat Hartman]

The only thing that needs to be stored is the VendorID. Other information should be obtained via the query that is used as the form's RecordSource. Simply join the main table to the vendor table on VendorID. When you choose a VendorID, the related vendor fields will auto fill. No code is required although it is best to set the locked property for these related fields to yes to prevent accidental update.

So, remove the vendor fields execpt for VendorID from your table and rather than using a table, use a query as the RecordSource for your form.

 

[bsdixon]

The only problem is that I somtimes need 4 of these vendors to show up as one record on a report.

I thought that I might need to add address2, vendor2, city2... fields to my table so that I can get the report to show the four vendors.

Is there any other way for 4 vendors to be assigned to one contract and then have all 4 vendors information pull on to one record of a report?

 

[Pat Hartman]

If more than one vendor needs to be stored, you have a many-many relationship and that requires an additional table.

tblOrderVendors
OrderID (primary key fld1, foreign key to order table)
VendorID (primary key fld2, foreign key to vendor table)

In the report, use a subreport based on a query that joins tblOrderVendors to tblVendor to show the vendor information.