Creating a new record in a sub form in a many to many

[cboath]

Perhaps i'm over thinking this, i don't know.

What i'm trying to do is add a new record to a subform. The problem is, I couldn't use a subform based on a table in order to achieve this. I needed extended information for it to be useful, so I made the visible part of the subform based on a query instead.

I'd like to add a button that adds a new object to the main job. I'd like the adding of that button to call a new form allows you to fill in the information for that object (this called form actually contains 5 subforms to populate all the data of the object). That form works.

What I need is for the button to call the 2nd detailed form AND create the necessary entries in the project/object junction table.

The idea as it is now is a button on the main form, but if it was possible to do so via continuous form in a subform, that's doable. I'm just having problems seeing the path so to speak.

Thanks

 

[rzw0wr]

It the query an updateable query?
If you would tell us what you have and what you want and we can possible help you find an answer.

Dale

 

[cboath]

it's probably an explanation issue on my end, sorry.

I have a main table/form of projects.

This is connected to an equipment table by means of a junction table. Essentially Main.ID links to JunctionTable.MID and JunctionTable.EID links to tblEquip.ID

I can connect that on a form with parent/child keys etc.

The problem i'm having is on the Main form, i have an Add Equipment button which I want to open the form to create a record for a new piece of equipment and still have it link back to the main form.

The add equipment button creates a new record in the equipment table so you can spec out everything about that piece.

Using DoCmd I can open that form in add mode through the button and even pass an openArgs statement to it containing the main records ID.

What this winds up doing is skipping the creation of a record in the junction table linking the piece of equipment to the project.

Now, the reason I used a query in the main form and not a subfrom of the junction table, is that I'm trying to show some basic information about the piece of equipment on the main form. Directly linking the junction table just would just show a pair of numbers.

That's my quandry at the moment. I'm looking for a way to get the button to generate the connection between project and equipment and callling the equipment subform.

 

[rzw0wr]

It sound like you need to read the following.

http://symplebyte.com/microsoft_office/access/how_to_normalize_data_access.html

Dale

 

[cboath]

Can you be more specific as to where you think the issue there?

My Project table has but a couple fields. The Equipment table has an autonumberPK, material and size fields. Attached to that are 5 more junction tables consisting of other aspects of the piece of equipment. I don't really have anything else I can split off into it's own table.

On the Equipment table, I have standard links to equipment types (pick a name from the list where the list is a separate table) and the same with material. The rest of the parameters are linked to the Autonumber PK of the equipment table. That is linked to the project table through a junction table.

 

[rzw0wr]

Post a picture of your table relationships.
I am wondering why a many to many relationship with just 2 tables.
I am thinking a 1 to many.

Dale

 

[cboath]

Was just about to post the image actually

I have a form based on the project (tblProjects). This form has a subform showing a list of equipment on the project. That is currently a query as i'd like it show the equipment type/size/mtl which i've not been able to do otherwise.

I'm trying to add a 'Add Equipment' button on the main form that launches a form based on tblTipMM to create a new piece of equipment and allow you to configure it. And that I can do. My problem is it doesn't create a junction table entry to connect it to the job. And that's obvious as to why as there's nothing actually joining the two other than the location of the button.

I've thought of making the subform based off tblProjTipsMM but I don't see how to create a new table entry on tblTipsMM that way.

 

[rzw0wr]

Sorry, I don't know where I got the idea you had only 2 tables.
Your relationships look backwards to me.
Some should be reversed. The 1 is on the wrong table.
tblProjects
tblAccessories
tblStructure
tblPurge
tblVess
tblPilFlameDet
tblPilIgnittion
tblPilot
Tbltips
Look backwards to me
Dale

Note: You can not enter data into a table on the many side without an entry on the one side first.

 

[cboath]

The way i read them they seem right unless my logic is off.

Each Entry in tblTipMM can have multiple entries from tlbAccessories, etc (i.e. multiple types of accessores) added to it.

Looking at it again, the connection between tbleProjTipsMm and tblTipsMM may be wrong.

Each project can have multiple tblTipMM's, but each tblTipMM can only appear in one project.

Fwiw, the form for create the equipment info has all 8 subforms listed and it works beautifully.

 

[cboath]

I can pass the ID of the project into my subsubform for the equipment information wth openargs.

Could I not then take that ID and couple it with the new ID of new equipment object and when the record is complete have it use SQL to Insert a new record in the junction table of the two ID's required?

 

[rzw0wr]

Each Entry in tblTipMM can have multiple entries from tlbAccessories, etc (i.e. multiple types of accessores) added to it.

Your logic is off I think.
The way you are setup on tblAccessories and tblAccJ means the tblAccJ will have many entries and tblAccessories will have 1.
Are there many AccJIDs for 1 Accessory? If so then you are correct.

Dale

 

[cboath]

When reading on m2m relationships, i was under the impression that if I wanted one piece of equipment to have multiple accessories attached to it, that was the way to go.

The way it should be set is that I can pick mutliple different accessory types (ID's 2, 5, and 11 for example) and attach them to Tip 17 and that tblAccJ would be:

AK 2 17
AK 5 17
AK 11 17

(where AK equals an autonumber PK)
Thus, when coming up on tip 17, it would show it has accessories 2, 5 and 11.