Many to Many (junction) data entry via forms (1 Viewer)

[NBRJ]

My database has a few many to many relationships that are resolved with junction tables.

I'm now building the forms. I have no problem on forms making the associations between records via the junction table if both sides of the M:M have records, but I can't figure out how to do the following:

On a form, how do I create a new record in the other table the junction table is attached to?

For example:
tblLocation 1:M tblLocationRequirement M:1 tblRequirement.

• tblLocation - rooms that software can be installed on.
• tblRequirement - a request for an item of software in a room.
• tblLocationRequirement resolves the many to many. Each entry specifies 1 SW requirement to 1 location.

I need to be able to enter data into these tables via forms in two ways, the first I've done:

1. [done] Specify the item of software required and then select all the rooms it's needed in. The tblLocations data is full populated so it's a matter of just making the associations.

• frmRequirement (specify the software item)
• subformLocationRequirement (with a cbo that lists the records from tblLocation) (specify the multiple rooms it's to go in)

1. [This is the one I really need help with] Select the location (frmLocation) and using a subform add a record for every item of software requested for that location. Because the software requirement records aren't yet a entry in tblRequirements I need to create then as well as the associated junction table entries. How do I do that?

• frmLocation
• subformLocationRequirement??? subformRequirement??? I need data in both, and I assume, Requirement first, before the association can be made in LocationRequirement. Do I want a pop-up to enter in a Requirement which then updates the junction table on close? It sounds a bit round-the-houses.

Any pointers would be appreciated.

 

[MarkK]

I would expect you to have two main forms, Location, and Requirement, both of which contain the same subform, LocationRequirementSFM which is based on your join table. Then, in the Requirement main form, add a new requirement, and select the locations from the subform where that requirement will appear. This M:M arrangement is like a candle you can burn at both ends.

And typically in a M:M relationship, the parent tables don't change much compared to the join table, which is where all the action happens.

Hope this helps,

 

[NBRJ]

Sorry, not sure I was clear in the original post.

I already have a Requirements form from where I can select the Locations that ONE SW item needs to go in. Not a problem because the Locations table is static, so I just pick which rooms I want to associate to that Requirement (via a cbo) and the data populates the junction table (subfrmLocationRequirement).

What I need to do, is the other side, a Locations form where I select the room (no modification to the record is needed) and then I add the Requirements one at a time for that room. That's the one I'm struggling with because I have to be able to create the Requirements entries , not pick them. Do you see?

 

[MarkK]

Yes, and you create a new requirement in the Requirements form, correct? And if you have your join table as a subform on both forms, and both forms are open, then you can very easily add a requirement and link it to a location from the Requirements form, where you just added a new requirement. No? Then refresh the Locations form/subform, and *poof*, the new LocationRequirement appears there too, because the join table (the LocationRequirement) is a child of both parents.

 

[NBRJ]

Ignore me, spent all day failing with it, and now I've got it working.

I just had a fit and deleted my previous attempts, recreated the frmLocation with subformRequirement and it auto populated the junction table. It was erroring over and over again, but now, after posting my stupidity, I've got it working.

So I haven't got the join table as the subform on this one, but it's working.... and how I want it to.

EDIT: Thank you MarkK (again, you've helped me before), you lot all deserve big thanks for helping me out with what are probably stupid questions. Thanks for taking the time to respond.