Many to many relationship on form/subform

[jam]

Hello everyone,

I have a many to many relationship as such...

tblRequirements
RequirementID (Autonumber)

tbljunction1
junctionid (autonumber)
RequirementID
AgentID

tblAgents
AgentID (Autonumber)

The main form will be based around the requirement table and infact is already setup. Now what I want to do is create the agent table as a subform.

However if I use tblAgents as my subform when it asks how your going to link master to child I'm going to hit a problem because tblAgents doesn't include a RequirementID field.
So should I use the tlbJunction1 as the subform, but then how do I display tblAgents info?

Cheers in advanced
jam

 

[Mr. B]

jam,

Because you have a many-to-many situation, you will not be able to simply use a sub form in this situation.

The way that I manage a situation like this is that I create a list box on my form that will be used to display the list of Requirements that at some point will have been selected by the user for this Agent.

For this to work, you will need a form where your users will manage the entire list of Requirements. Once you have the list of Requirements defined and managable, the selection process is accomplished by presenting your user with another list box (making it multi-select is an option) where they can select one or more requirements form the list. A command button would then be available and when clicked, your VBA code will create the records in the junction table, thus creating the many-to-many relationship between agents and requirement.

HTH

 

[jam]

OK I follow what your saying but I need to do it slightly different.

I don't need the many-many relationship represented in this form I only need to see a 1-many side at this point.

IE I have 1 requirement (the requirement is for land in a particular area) and there MAY be more than 1 agent dealing with or for the business who has the requirement. So what i'm looking to do is have a drop down box to select from a predefined list of agents of which there could be many or just 1.

What will be happening in the background is a list of agents will be linked to a list of requirements Ie many agents to many requirements.

I hope this explains what I do slightly better as I'm struggling with the context you put the DB in your reply.

Thanks for your help though !
jam

 

[Mr. B]

I think you need to rethink what you are saying. At the beginning of your last post, you say, "I don't need the many-many relationship represented in this form I only need to see a 1-many side at this point." Then near the end you say, "What will be happening in the background is a list of agents will be linked to a list of requirements Ie many agents to many requirements. " This is a many-to-many relationship and you have your table structure defined basically correct.

I got the relation backward in my senerio, but you should get the idea of how to create this type of relationship in your data. Just reverse the description that I gave. Thus, when you have a requirement, you will have a pre-defined and manageable list of Agents in a table and managed through a form. You will then at some point have to provide a list from which the Agent(s) can be selecte and the link established between the current requirement and the selected Agents(s).

HTH

 

[jam]

The problem is my list box would be massive. Theres so many agents out there is ridiculous, so having a major list box where I can select multiple values although great isn't going to be feasible.

The second thing I need to know is how do I get it to create the record in the junction table? If I add in Agent to the requirement how do I get it to link into the agent table and also the junction table?
Control source would = ????
How would this link to the junction table?

 

[Mr. B]

It is possible to provide your users with a way to find a specific record in the list box or filter the list box by having a text box displayed just above your list box where the use can simple starting typing the name of the desired Agent and have the list box to either select the first record matching what the user types or haveing the list filtered to match the value entered.

You will need to have VBA code behind some command button that will execute an Append query to add the record to the junction table.

HTH

 

[CEH]

Maybe Im reading this wrong...... Your junction table represents....One record correct? So the JunctionID should be in both Agent table and Requirement table. One to many Junction to Agents and one to many Junction to Requirements? Well, thats not how your tables are set up.

 

[SOS]

The Requirements would be the main form, the junction table would be the subform and you select the applicable agents using a combo box on the junction table subform.

 

[Mr. B]

CEH,

You are reading this wrong. The JunctionID is just a unique identifier for each of the records that will be in the Junction table that link one Agent with one Requirement. There can be multiple combinations of each of these. The JunctionID would only be ing the Junction table.

 

[CEH]

Looks like I did.... I think the word "Requirements" threw me.

 

[jam]

Thanks to those who posted up answers to my problem. I did a slight work around but had to start a new form to do so.

I created a query based on all 3 tables. No criteria (at present) so it'll show me all records.

I created the Agents table as a subform in datasheet view (which is fine for what I want).

Then...
I created a new query which shows Agent name and AgentID from the tblAgents.
Then I changed the text box to a drop down box. Bound column 2 (AgentID) put the column width to 20cm so that the ID can't be seen and limited to list.

This works perfectly albeit you can select the same agent twice which is once again fine for my purposes as there maybe more than 1 agent from the same firm with the same requirement IE Retail, Fish and chip shop and hotels in the same area (shopping centre for instance).

Thanks again all those who helped me out, your help is much appreicated.