List boxes; managing data from multiple tables

[garybug]

My database has 3 tables, tblQuestionnaire, tblWorkAreasLink, tblWorkAreas.

e.g. within a paper Questionaire, a respondee can select multiple areas of work. All the available areas I have put into tblWorkAreas. As there is a many to many relationship between tblQuestionnaire & tblWorkAreas, I have put in the 'link' table tblWorkAreasLink containg primary key details for both tables

e.g. in tblWorkAreasLink there may be

questionnaireID workAreaID
1 2
1 5
2 2
2 6
3 4
4 2

In the frmQuestionnaire Form, I would like to have a LIST BOX, showing all the available choices, so the user can highlight multiple relevant Work Areas. The Work Areas highlighted would then be stored in the 'link' table. I'm at a loose end as I can get the LIST BOX to show the values from the tblWorkAreas easily enough - but can't seem to link into & save the selected data to the 'link' table.

Would really appreaciate any help. Its my first job & using Access for the first time & would like to make a good impression!!

Many thanks

Garybug

 

[RickSpr]

Using an associative table like tblWorkAreasLink is definitely the best approach to handling a many-to-many relationship. However, when using an associative table, you usually have to do insertions and deletions in code--especially if, as in this case, you want to insert multiple rows at a time.

A good approach here is to put your code in the Form's AfterUpdate event. You could use a DAO recordset to insert the rows (use the AddNew method, set the values into the Fields collection, and then use the Update method), or you could build an SQL INSERT statement for each row and execute it with DoCmd.RunSQL. (The recordset technique is more efficient, but you have to know some DAO to do it.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein