Use combobox to populate multiple fields in access form

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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.