Access: insert multiselect box values into database

0
votes

I have a form setup in Access that contains many input fields and dropdown boxes. This form doesnt contain an insert button. I just click on 'add record' at the bottom of the window.

Now I`ve add a multiselect box which contains multiple values.

I want the form to insert the selected values from the multiselect box along with all values from the other input fields in the same row if possible.

For example; the column that holds the multiselectbox values needs to contain 1,2,3

Another option is to store these values in a different table as long as I can join both tables when generating reports later on.

Any pointers are appreciated

1
ms-access
What version of Access are you using? The answer changes based on that in this situation. - Brad
We use both 2003 and 2013 (mixed environment) - jortiexx
ok, cool, 2016 would have a special datatype for you but since not...How do you populate the list box? Can you add another table to store the multiple values or do you need to store them concatenated together in your original table? - Brad
The box is populated using a table that holds the values. Its not a problem to add another table, as long as I can link them together when selecting data - jortiexx

1 Answers

1
votes

You have two options. Either way requires a table structure like this (called a many-to-many relationship):

enter image description here

Option 1 is to make a subform which has a combobox on it. The subform is bound to this cross reference table ParentTableLookups and the combobox's control source is ParentTableLookups.LookupStoreValue and Rowsource is the Lookups table.

This is much less code and doesn't require a save button however it does mean that in order to see the things a user did not select they need to open the combobox. Maybe not quite as visually complete as the listbox.

Option 2 is to use the same table structure but instead of a subform you fill in a listbox on your parent form from Lookups. Then you iterate over ParentTableLookups where ParentTableLookups.ParentTableId = your current record and for each ParentTableLookups.LookupStoreValue you select that in the listbox.

In the parent form's AfterUpdate and AfterInsert records you need to repopulate ParentTableLookups based on the selected listbox values. You can create the records using DAO easily.