Access 2016 - Creating a Form for a many:many relationship

[Fionnbar]

Dear Forum Members

Thanks in advance for any assistance. Apologies for any long-windedness here, just want to make sure I specify the problem correctly.



I'm reasonably handy with computers but not in any way an IT or database professional. Many years ago I had to do some work with a program called DataEase for DOS so have a general appreciation of basic relational concepts. I am however struggling with aspects of form design for Access.

I help out with a charity and have been asked to manage our safeguarding or PVG process. In essence, this involves keeping a record of all our volunteers, what they do and whether they have appropriate PVG (basically criminal record checks). Not all volunteer roles require PVG, just some "regulated" roles. Roles typically have many volunteers, volunteers may have several roles, some regulated, some not. Main thing is to ensure that no volunteer has a regulated role who does not have a PVG. I also need to produce an annual report listing each volunteer with regulated roles and their role(s).

In terms of table design I thought of

1. table_volunteers: (volunteer_ID, surname, forename, contact number, address, PVG Y/N ...)
2. table_role: (role_ID, rolename, role_description, regulated Y/N)

Because there is a many:many relationship between volunteers and roles I created a third, junction, table to represent, in effect, each occasion of a role being assigned to a volunteer if that makes sense. volunteer_ID and role_ID are foreign keys to this table. They are automatically generated by Access.

3: table_volunteer_role: volunteer_role_ID, volunteer_ID, role_ID)

This table structure works absolutely fine for me and my queries and reports come back with exactly the information I would expect. I was able to load data into the tables manually.

I want however to hand this work on in time to someone else and I expect that my successor will need user-friendly forms. It was trivially easy to create forms to create and edit volunteers and roles as just followed a wizard and used the corresponding tables as a basis for the form.

I am struggling however to create a form that lets me add/delete rows to the table_volunteer_role table in a user-friendly way. In other words, assign and de-assign roles to volunteers. I have tried various combinations of forms and subforms but nothing quite hits the mark. I had in mind being able to select volunteer name and roles from drop downs and having the corresponding ID numbers entered automatically.

Any advice gratefully accepted

Fionnbar

[davegri]

How about a form with a dropdown at the top to choose a volunteer. Then below that, 2 listboxes, side by side. The left listbox contains Roles Available, the right Roles Assigned (to the volunteer). Between the listboxes are 2 buttons, one to move Roles left and one to move roles right.
The underlying code for the 2 buttons either adds a record to the junction table or removes a record from the junction table. The listboxes' rowsources are queries that update the listboxes with appropriate data as the roles are moved back and forth.

I have done this and it works well. PM me and we can work together. No charge, it's a hobby.

[Fionnbar]

How about a form with a dropdown at the top to choose a volunteer. Then below that, 2 listboxes, side by side. The left listbox contains Roles Available, the right Roles Assigned (to the volunteer). Between the listboxes are 2 buttons, one to move Roles left and one to move roles right.
The underlying code for the 2 buttons either adds a record to the junction table or removes a record from the junction table. The listboxes' rowsources are queries that update the listboxes with appropriate data as the roles are moved back and forth.

I have done this and it works well. PM me and we can work together. No charge, it's a hobby.

Dear Davegri

I don't know VB so your solution might be a little sophisticated for me! Thank you for your kind offer to take me through it offline on PM.

When I understand the solution I can post it here for those to follow

Kind Regards
Fionnbar