List box + combo box to enter records into a Many-Many?!

[typhoonikan]

I am getting frustrated.
I have my first many-to-many situation and can't figure it out or find anything on the googles to help.

I'm using access 2013.

The situation:
I have agents(REALTORS) who need to take some trainings(Courses). Agents can take more than one training and a training can have obviously more than one agent.

What I want:
A snazzy form where the user can select any training from a combo box then in the same form, proceed to select multiple agents from a list box.

I know that my problem is coming from the incorrect set up of the many-to-many relationship. In the relationship window in access, I am not sure of how to set up the join types for each table.

I have the agent, training, and agenttraining tables.
Agenttraining haveing the keys of both tables, of course.

What should I do?
Image for referencce: go to this link snag.gy/3Jjcl.jpg

 

[jdraw]

You should go to youtube and search
"trainsignal msaccess many to many" and watch the video.

https://www.youtube.com/watch?v=JBG4SzNhA9A

 

[typhoonikan]

You should go to youtube and search
"trainsignal msaccess many to many" and watch the video.


Yes, well I understand what a many-to-many relationship is.
That video is rather basic. I am needing assistance with manipulating Access to do what I want, not learn basics.

 

[jdraw]

Well, if you still have a many to many, you haven't resolved the structure problem.

I checked your jpg. The Agent and Training links to AgentTraining look fine.

Seems you need a loop construct and some vba to insert records into your junction table.
'general approach

Get the value from the combo
For each selected item in the listbox
insert into the junction table (agentID, TrainingId)
Values ( combobox.value, listboxitem.value)
next listboxitem

 

[typhoonikan]

Thank you. I will try the VB code.

 

[Pat Hartman]

Take a look at this example. It shows a many-to-many relationship from either side.

 

[typhoonikan]

I still haven't resolved this issue. I met with a programmer today who has helped me with VB.Net in the past, and he was stumped because the VBA that is used in Access is apparently ancient...

The code logic follows just as jdraw mentioned above.
Get the value from the combo
For each selected item in the listbox
insert into the junction table (agentID, TrainingId)
Values ( combobox.value, listboxitem.value)
next listboxitem

But I am not sure how to get the PK id from a table from a selected value (something like cmbTraining.Selected.WhatsThePrimaryKey!?)

So my barrier at this point is the programming and I don't know where to go for help.

 

[Pat Hartman]

You don't need to write code at all. Did you look at the example I posted? If you examine it you will see that the subform in both cases is bound to a query of the junction table.

 

[jdraw]

You do not have to worry about the PK. Access will take care of that if you set up your relationships (referential integrity).

Look at Pat's sample database, it is the easiest and doesn't require vba coding.