Inserting data from forms w/ Junction Tables

[repoman07]

I am looking to create a form that will insert data into my Junction Table that I created for my Many-to-Many relationship between Orders and Part #'s. I didn't have a problem when I created the form with a One-to-Many Relationship between Parts and Orders. Now that I want an Order to consist of many Part #'s, I'm having troubles figuring out how to Create a form that correctly Inserts and Updates my records.

Please help! I'm somewhat a beginner with Access.

Thanks for your help.

Bill

 

[dgm]

If your main form is based on the Orders table and your subform (i assume your using a subform?) is based on your junction table, u just need to set the child/master links in the subforms properties. Which will most likely be OrderNo or something like that?

 

[repoman07]

DGM,

Thanks for the reply....Yes my main form will be the Orders Table....I assumed my subform needed to be the Parts table, cuz that where the Parts information is stored...what I'm trying to do is create an Order that can constist of many Part #'s...The Order # and the Part# needs to create a unique record in my junction table so that I can pull a query to know all the Part #'s to an Order#....how do I create the master/child link in a Subform? And should that Subform be the Parts table or the Junction table....

thanks again for your help....Bill

 

[dgm]

No your subform should be based on your junction table, cause thats where the data will be stored.

Say your tables look like this:

tblOrders
ordNo
ordDate
etc

tblParts
partNo
partDesc
etc

tblJunction
ID
jOrdNo
jPartNo
jQtyOrd
etc

Then your main form is based on tblOrders,
and your subform is based on tblJunction, with the parts being pulled in via a combobox or somthing from tblParts. (Note, ordNo is not displayed in the subform, it's just there to link the two forms.

To set up the child/master links, select the subForm control and open the properties window. In the data tab, look for the "Link Child Fields" and "Link Master Fields" settings.
In the example i gave above, the Child link would be jOrdNo and the Master link would be OrdNo. Or u can click on the ...'s to open up the link builder.

Hope this helps...

 

[repoman07]

dgm,

your awesome I get it now...last problem (I hope)...I have all my required fields from the main form (Orders) all set, but now I want at least 1 part # entered as part of Order....Since the Part# is being pulled in as a Combo Box within the subform, how do I make this a required field on the Form? Keep in mind, I have a continuous form for the Sub-Form so I can add as many parts as there are parts in the Parts Table...

I've tried making the fields required at the table level, but that did not seem to work....

Thanks again

 

[dgm]

Good question...

Problem is, the required property in a table only works for each record in that table. As the partNo is in your subform, there's no real way of telling access that every time an order is placed, a partNo is required... By default anyway.

U could probably get around this by using some fancy code. Depends on your coding skills though.
For example:
If everytime data is entered into the main orders form, u insert a date or something into the subform... That's kinda messy though.

Likewise you could have a hidden field on your main form that is set as required, and when a new part is added to the subform, a value is placed in this hidden field. Again that could be confusing to any users...

Maybe u will just have to trust that someone who places an order will add at least one part? lol

Unless someone else can come up with a solution?

 

[repoman07]

Dgm,

thanks again for your advice...I'm not really a programmer, so fancy code is probably not good for me...I think you're right, I'll have to trust my users to add a part to the order...

Thanks for your help with this problem...Have a nice day...Bill