yoducati
asked on
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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
Me!sfrmOrderDetails.Form.[
and
Forms!frmOrders!sfrmOrderD
neither of which work.
Me!sfrmOrderDetails.Form.[
and
Forms!frmOrders!sfrmOrderD
neither of which work.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
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.