How do I use ONE control source with MULTIPLE combo boxes from a USER FORM, in Access?

Question

I am currently developing A quote sheet for work, I have 21 Combo boxes that are all populated by the same row source. The problems that I am having are When all my combo boxes are set to the same control source all the list values in the combo box are filtered and set to one value. For example combo box 1 is selected and prep is selected now the other 20 combo boxes are set to prep, and I can't select the other options. Now if I have no control source for my combo boxes than previous example works. If I select combo box 1 and set it to prep then I can select combo box 2,3,4... and put any value I want for each one. I want to be able to have each combo box with its own value.

The reason I am wanting the combo boxes to have the same control source is so when a combo box has a selected value it will add a new record to my table. So if 10 combo boxes have values selected then there will be 10 new rows added to my table when I click my add record button.

I am thinking a maybe having a loop do the work, but am not to familiar with with using vba to run sql??

MoondogsMaDawg MoondogsMaDawg · Accepted Answer · 2016-12-13T20:22:44

You can't use the same control sources to generate multiple records on the same form. They must be unbound. At least, I don't know how to make that work the way you have it set up, but can still help.

Delete the control sources but keep the row sources.

You will need a query to save the values to your table:

INSERT INTO MyTableName ([MyFieldName]) VALUES ('[ComboboxValue]');

Copy this code and paste in query builder. Overwrite "MyTableName" and "MyFieldName" with the appropriate object names for where you want a single combobox value to be saved. It only adds one row at a time. [ComboboxValue] will be the parameter we use to pass each combo value to the query to execute.

Name the query whatever you want and save it. I will use the placeholder "MyQuery," make sure you update it!

Create a command button on your form, and in its On Click event, paste in this VBA code (you will need to update the combobox references where I use Me!ComboboxName):

Dim qdf As DAO.QueryDef
Dim combo As String

Set qdf = CurrentDb.QueryDefs("MyQuery")

combo = Me!Combobox1Name
qdf!ComboboxValue = combo
qdf.Execute

combo = Me!Combobox2Name
qdf!ComboboxValue = combo
qdf.Execute

'Copy those three lines for each combobox, adjust your combobox references accordingly, 
'or use a loop

'End your procedure like this:
Set qdf = Nothing

If you are using a multi-column combobox, you may need to adjust the reference from Me!Combobox1Name to Me!Combobox1Name.Column(1).

How do I use ONE control source with MULTIPLE combo boxes from a USER FORM, in Access?

1 Answers