Access Form with Subform and Junction Table

I am building a simple (or so I thought) data entry form based upon an orders table with a details subform.  In the main form I have the fields orderID, Date Ordered, and VendorID.  OrderID is the primary key and the vendor Id is a combobox looking up a list of vendors in another table.  The subform shows the detail records for items the user wants to add to the order.  The subform is tied to the order details table which is a junction table between orders and properties.  Some of our teams order multiple items for one property, others order the same item for multiple properties.  The junction table has a multiple field primary key made up of the orderID, property number, and item number.  My plan is to build independent data entry forms for each team.  The one I am building now will be for orders of multiple items against one property.  The problem I have is that when I enter a record in the main table the orderID field does not get populated for the subform records.  The error for missing primary key is thrown, and after I hit ok four or five times, it lets me continue with entering data in the subform but still does not populate the orderID.  If I click through the error messages, enter data in the subform, then hit escape, the orderID is passed to the subform and I can continue entering data and save the record.  I can't figure out why the orderID is not being passed to the subform at the beginning of the data entry.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Make sure that the subform's master/child links are set correctly.  That is what sync's the subform with the main form and that is what tells Access how to populate the subform's foreign key.

I would think long and hard before duplicating forms for special purposes, especially early in the game when there still might be changes that will need to be made and if you have duplicated the forms, you will have duplicated the maintenance tasks for yourself.  You can probably have the user choose an order type and let that show/hide certain fields if that makes more sense to the users.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Couple comments:

1. Access can't model a M to M relationship directly.  Instead, you need to look at it as two 1 - M relationships:

 so for example   table "A"  1 - M - M - 1  Table "B"

 So you need two can only do one side of the relationship at a time.  Typically, this is a:

Main form based on table A
Subform based on the M-M junction table
and a combo that *sets* the FK field in the junction table for table B, with it's rowsource set for table B.

then the opposite:
Main form based on table B
Subform based on the M-M junction table
and a combo that *sets* the FK field in the junction table for table A, with it's rowsource set for table A.

<< I can't figure out why the orderID is not being passed to the subform at the beginning of the data entry. >>

 Check the master/child link fields on the the first case, they should only be dealing with Table A.   B's are set/selected with the combo.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yoducatiAuthor Commented:
That's how I have my junction table set up but Im not sure I understand the forms part.  The master/child links are set and correct.  I don't understand setting the other rowsource with the combo.  Right now I have a text box for the user to enter the foreign key for the properties table in the junction table subform but I get the primary key error before they can even type in the field.  With other subforms Ive done as soon as the autonumber field is created in the main form it populates in the subform.  That is not happening in this one.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

yoducatiAuthor Commented:
I don't know what was going on but I finally deleted the subform and put a new one on and it works fine now.  Can you tell me how to reference a control on the subform?  I want to set the value of a control on the subform based upon a user entered field on the main form.  I've tried referencing it both with




neither of which work.
It doesn't make sense to reference controls on a subform from outside of the subform.  Exactly which row did you want to put the data into?  Unlike Excel which has a row and column reference model where you can place a value in any specific column of any specific record, the relational model does not work that way.  You can reference a specific column but without knowing the actual primary key of a record, there is no way to reference a specific row.

Also, if you feel that you need to place a common value in all records of a subform, the column probably doesn't belong in the subform table, it probably belongs in the main form table so you can set it once and have it apply to all "child" records.

Going at this from the other direction makes more sense (although it is probably still a bad design since you would be duplicating data).  In that case, you would use the BeforeInsert event of the subform and copy the data as the subform record is being created.

Me.PropID = Forms!frmOrders!commonPropID

Simply based on the column name, I'm going to go with my instinct and say that the column belongs in the Order table rather than the OrderDetails.  The ONLY time the column would go in the subform is if it could be changed so that ultimately, some OrderDetail rows for the Order will have one value for PropID and other rows will have a different value.  To use a more common example, shipping address presents a similar conundrum.  Most of the time, all items on an order will ship to the same address but your app may support separate addresses for each item.  Granted, you can handle this by changing the Schema but keeping with this example, you would enter the shipping address for the order and have it populate all the detail items and then you could go in and change specific items to ship to a different location.  There are better ways but I think this is a clear example of why you might want to duplicate data in subform records.
yoducatiAuthor Commented:
You are correct.  I had the control on the main form but its actually one of the foreign keys of the junction table.  When I had it on the main form I kept getting the key errors.  In this case I do want each of the order details to apply to the main order so ideally the propertyID would be on the main form with the order details underneath, but I do not want to change the schema.  As it is with the junction table I can accommodate the data very cleanly in all circumstances so I just need to figure out the most efficient way of having the propID key for each detail record in the subform be populated by the users entry in an unbound control on the main form.  I figured out the reference issue I was having.  When I deleted the original subform and put the new one on, I forgot to change the name back to what it was.
If the propertyID is the foreign key (field in subform table that points to the parent table), then setting the master/child links correctly will cause Access to automatically populate the foreign key for the subform.

Even though I think you may not be doing this correctly, my previous post told you how to solve this problem.  I should mention that occasionally people say "subform" when in reality they are talking about a popup form.  so if this is the case, you cannot set master/child links because the popup is a main form rather than a subform and therefore, you do need to populate the foreign key yourself.  Just make sure to do it in the popup form's BeforeInsert event so your code doesn't dirty the record before the user does.  The BeforeInsert event will run as soon as someone types the first character in the popup form.  Your code will run and copy the PK from the main form and place it as the FK in the popup form.
yoducatiAuthor Commented:
This isn't as straightforward as setting the master/child links like you would on a simple PK FK relationship, as this junction table has a multiple field PK where only one of the FK fields is in the main table.  I fixed it by passing the value of the unbound field on the main form to each record of the subform as they are added and its working perfectly now.  It is a form/subform not a popup.  Thanks to you both for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.