Updating many to many reference tables from form

[Fantast]

Hello. I expect the answer to my question is quite easy, however I did not manage to find the answer through searching the forums.

I have three tables for which I would like to create a data entry form. The tables will contain the answers to a questionnaire.

Table 1: contains text field answers and the id for the questionnaire.
Table 2: contains a list of values, which are used as answering possibilities for one of the questions. the user can choose more than one value.
Table 3: contains references between the two tables.

So by example the tables could be filled like this:

Table 1
-------------
ID: 1
Name: User1
-------------
ID: 2
Name: User2

Table 2
-------------
ID: 1
Value: Value1
-------------
ID: 2
Value: Value2
-------------
ID: 3
Value: Value3

Table 3
-------------
ID: 1
table_1_id: 1
table_2_id: 1
-------------
 ID: 1
 table_1_id: 1
table_2_id: 2
-------------
 ID: 1
 table_1_id: 2
table_2_id: 3

Now I would like to have one single form to do the data entry for those tables. I created one, setting it's data source to be a query, which joins the three tables. After that I added the fields by dragging them into the form from the field list pane. I used a multiselect listbox for the data from table 2 and set the relationships correctly through the database tools ribbon.
Unfortunately the text field shows the correct values, and I can successfully add new data to table 1 through this form. However, the listbox, even though showing all the correct values, is inactive: I can not select any of the values in the listbox. Also if I add values to table 3 manually, the correct values are not shown as selected on the listbox.
How can I get the listbox activated and showing the selected values? I think I would be able to do it if I would code it all in VBA, but I was hoping that this was possible without VBA coding.

Thanks in advance.

 

[Mr. B]

Managing a many-to-many relationship will require the use of VBA code because you are going to need to have the user make multiple selections from your list boxes and then process each of the selected items, writing to or removing from the table that links the records together.

This is one of the most difficult data management processes to understand and create. It is more a matter of being able to display the correct information at the appropriate time.

You will not be able to use a single bound form to accomplish everything you are attempting to do. You will need to start by creating the user interface so that the needed information can be displayed to the user. Then you can start the process of allowing the management of the data.

As an example, if you have a bound form that displays the records from table 1 you would then need a list box that would show any of the values that have already been selected from table 2 in one list box but you would also need another list box that would show the remaining available options that can be selected, excluding the ones that have already been selected. You would need to provide the user with the ability to remove a previously selected value from the list box that displays selected items. You would also have to provide a way that the user could select any value that is not already selected and add those selected values to the list of selected values. This will required you to be able to add or remove multiple records in Table 3 as determined by the users actions. You will also have to be requering you list boxes each time a changes is made to Table 3. The only way to really do this is with VBA code.

 

[Fantast]

Thank you for the reply Mr B.

I have created class modules in the past which handle double list boxes very well, doing what you described and more (select all/deselect all / switch columns). I definetly could use them again for this case. I was just hoping that I could get the job done without VBA. This seemed to me as a pretty standard way of linking data in databases, so I kind of assumed that access had a built in form solution for it.

Anyway thanks again for the feedback.

With kind regards
Fantast~

 

[Fantast]

Hi again. I'm posting this answer to my own question as it may help others who struggled with the same issue. I will first give a short summary of the usual way in which it is done, and after that the way you can do this without VBA programming and minimum effort. This is done in Access 2010 and thus it is probably a bit different than for older versions.

Basically you want to be able to have many to many relations between two tables, and be able to maintain the data through only one form.

As stated above a good solution would be to use the usual method: have a third table which will hold the relations between two tables. Then create a form from scratch, add each field individually and set it's data source from the appropriate table / query. Handling the already available connections between them and updating them according to the wishes of the user must then be done through VBA programming.

However, in my case I wanted to do things with a minimum effort as time is an issue for my project. Also, the project was only temporary and intended for a very limited number of users. So it was not necessary to have it extremely polished and user friendly. In such cases, there is a better way of handling the situation (imho):

1. Create all the tables needed for the data, but do not create the tables which should hold the connections between the tables (in the example above table 3).
2. One of the tables (in the example above, table 1) holds the main data. In that table create a field for each many to many relation, and set it's data type to "Lookup wizard".
3. In the Lookup wizard go to the table which holds the data of the concerning field, go through the steps of the wizard and make sure to check the box "Allow multiple values".
4. After finishing the Lookup wizard save the table and create a form through the "form wizard" under the "Create" tab in the ribbon.

That was it. The form will show you all the fields and you are able to select and deselect values for the many to many relations fields.

PS. Obviously, the lookup wizard steps could be replaced by clicking on the "Lookup" tab at the bottom of the table design, next to the "General" tab, and setting teh row source manually. Also you can edit the display control for the form from there.