Problem with a second combo box for a junction table

[AshB]

Hi all,

I have been using Access in a very light-touch way for years, but am trying to build a new database to replace one I made some years ago. The reason for the rebuild is to separate out my data more, rather than keeping everything in essentially one big unwieldy table.

I have created a simple test database to begin the rebuild. I need to keep records about events and organisations, and then have a many-to-many relationship between these two sets.

So I have 2 tables (Events and Orgs) and a junction table (Event2Org). I have a form for each table. Events is the master form, and this contains a subform which is the form for Event2Org junction table. This contains a combo-box which allows me to choose to associate an organisation to the event record, and this updates the junction table.

All of this works perfectly well.

My question is this: how do I add a second organisation to my event record? I have tried adding a second combo box, and a second sub-form, but in each case if I change the option in the second combo-box/ sub-form it changes the option in the first one.

I want to be able to associate more than one organisation with each event, and I cannot figure out how to do this! Any help would be greatly appreciated!

 

[jdraw]

You have Events and Orgs and a junction.
You wouldn't add an Org to Event table.

Please explain what exactly you are trying to do.

 

[AshB]

Thanks, Jdraw, and apologies for not being clear.

Using the form, which is based on the Event table, I want to be able to associate more than one organisation with that event (using the junction table).

So currently, I have a subform based on the junction table embedded within the form based on Event table. I can choose from a combo box on this subform which organisation to associate with the event, and it updates the junction table.

What I can't do is figure out how to associate more than one organisation with each event.

The Junction table has its own PK and 2 Foreign Keys (based on the PKs from Org and Event). Obviously using this method, I can have each org associated with many events, but I cannot have many orgs associated with 1 event.

I think the issue is the way that I am using the combo-box in the subform, but I could be wrong. Perhaps I need to have a fourth column in the Junction Table called 'Org 2' which is also in a Many-One relationship with the PK in the Org table?

 

[AshB]

I figured out the issue. I simply hadn't noticed that because I am using a sub-form, I can click 'new record' for the subform and it adds a second association to the junction table. :banghead::banghead:

 

[jdraw]

When using a junction table and having a separate autonumber PK, I recommend you put a unique composite index on the EventId OrgID combination to prevent duplicates.