We help IT Professionals succeed at work.

Use combobox to populate multiple fields in access form

peterslove53 asked
I have a combobox  which i use to populate a field on my form. But i can only populate one field. Is there a way i can populate multiple fields on my form using this one combobox?
Watch Question

President / Owner
Fellow 2019
Most Valuable Expert 2017
Yes and no.

What you can do is make the combo multiple columns.  Then in the combo's AfterUpdate event, do:

 Me.<somecontrol> = Me.cboMyCombo.Column(1)
 Me.<somecontrol> = Me.cboMyCombo.Column(2)
 Me.<somecontrol> = Me.cboMyCombo.Column(3)

However, combo performance starts to suffer after more than a few columns if it has a lot of list entries.  In that case, your better off to fetch the data using a recordset in the combo's after update and populate controls that way.

Distinguished Expert 2017

Are you actually trying to duplicate data or are you trying to show reference data?  For example, in an Order Entry application, you will frequently want to copy the unit price from the item selected by a combo to the OrderDetails table.  That locks in the price at the time it was ordered which will facilitate reporting.  The alternative method is more difficult and requires keeping a price history table and looking up the price at a certain point in time for EVERY action that shows price.

Most of the time, the most efficient way to show multiple items from a lookup table is to include a join to it in the form's RecordSource query.  Using our Order Entry application again, when a customer makes a new order, you don't actually want to copy his name and address and order history to the Order, you just want to display it for reference.  Whenever you join to a lookup table, be sure to set the Lock property to yes to prevent accidentally updating data from the lookup table unless you actually want to allow it.  So, you would have to make a decision, do you want the customer's name and address changed in the customer table if someone were to change it on an order?  The other concern is if the foreign key to the lookup table is not a required field, then you will need to use a Left Join to the lookup table.  Most Order Entry applications require a valid CustomerID when placing an order.  Your app may have to take orders from walk in customers who don't have ID's.  In that case, the CustomerID is not required and so you must use a Left Join to tblCustomer when you are looking up the customer name and address.