M:N Relationships Use Join Table to Assign a Person one or more Codes

[FinisherProgrammer21]

Hello my friends!



As I recently posted my first thread here, I was quite grateful that other's are willing to help. I have a new question and in order for you to better understand what the problem is, I wanted to take care of a suggestion that was made in my first thread which is showing my database.
Introduction:
I have three tables: Persons, Codes and PersonCodes. In my case, one Person can have one or more Codes and one Code can be assigned to one or more Persons, so it is a classical M:N relatioship.
I was doing some research and understood that if you have a M:N relationship, you need a third table which is the so called "Join Table" if I'm not mistaken. I also implemented the join table "tbl_PersonsCodes" which has an 1:n relationship (Persons:PersonsCodes) and n:1 relationship (PersonsCodes:Codes). See the screenshots below.

Here is my problem:
I would like to implement a form which can assign each Person one or more codes and which immediately shows in the current form which codes (that could be selected in a list) are left and which are selected for person.
I am also showing my form with the combobox because the idea is that I am also able to select one Person via the combobox drop down menu and do it for this specific person.

I am not quite sure how I can get this done. I did some research and came across: Lists / Listviews / MultiSelect / Listboxes.

I am also not quite sure about my join table "tbl_PersonsCodes" because it has only the primary keys of "tbl_Persons" and "tbl_Codes" as foreign keys. I also provide a screenshot of this. The way I did it is definitely not the way I want it to get it done. It's quite "pragmatic".

I would be very grateful for help and also a little more detailled explanations and maybe a few hints for why it is a good choice to implement it this way. Thank you! And have a nice day

MY CURRENT DATABASE

With a form, I managed to implement a combobox to search Persons and to show them in the detail section below after update and I was also able to implement a button with which you can export data to a word template.

My current DB consists of 3 tables and a few forms. See the screenshots attached.

Greetings,

FinisherProgrammer21

[davegri]

Maybe you can adapt this DB:
You could move the combobox to the header, then include all the demographic fields above the listboxes. That would eliminate the need for dedicated form frmMembers.

FinProg-Example-davegri-v01.zip

[ssanfu]

Another option is to use a Main form/sub form design.
Both forms would be in Continuous forms view. Access will complain, but just go back and set both form to Continuous forms view.

In form frmPersonsCodes, the record source for the main form would be the tbl_Persons data and the sub form would be the junction table tbl_PersonsCodes. In the sub form, there would be a combo box to select the code.

In form frmCodesPersons, the record source for the main form would be the tbl_Codes and the sub form would be the junction table tbl_PersonsCodes. In the sub form, there would be a combo box to select the person.


In table tbl_PersonsCodes, use a compound index (PersionID/CodeID) if you don't want Dennis to have 2 or more entries for "Drug Dangers".
(Dennis is PK 7
Drug Dangers is PK 4
Chemical Dangers is PK 1)

tbl_PersonsCodes
--------------------
7 - 1
7 - 4
7 - 4 <<--- duplicate - generally bad (?)