Using form to add record to Junction table

[SaskiFX]

So I have a Junction table that is combining a tblParts and and tblVendors that are combined into tblPrice. It uses "Part Number" and "Vendor ID" to join the tables. Each part can have multiple vendors with different prices.



I have a form that is used to add new parts to the tblParts table, and I want to have a button that launches another from to add a Vendor as a supplier for a part. (All the vendors are already in the tblVendors table.) The second form comes up just fine, and I can get it to jump to a new record (using the OnLoad), but when it does that it clears all the fields. However, I want to keep one of the fields filled. When a user is hitting this button, the part number from tblParts field needs to stay the same, so when the user selects a vendor from my list, it generates a new unique record with the Part number and the Vendor ID.

I have tried searching, and I can't seem to locate the correct settings or code to accomplish this.

Help? Thanks!

[June7]

Consider a form/subform arrangement.

Main form bound to tblParts and subform bound to tblPrice.

Combobox on subform to select VendorID.

No code required.

[SaskiFX]

Actually, that's the way I have it setup now. The main from is bound to tblParts, and the popup form is bound to tblPrice. I'm using a listbox instead of a combobox, but I wouldn't think that matters. But when I tell the pop-up form bound to tblPrice to generate a new record, it wipes out the "Part Number" field which I need to stay the same, since I'm just using the popup form to add a new vendor and part cost to the tblPrice table. I could have the user type it in again, or use another listbox, but it would make the interface unwieldy.

Is there anyway to retrieve the "Part Number" value and hang onto it and automatically insert it in the new record? (In the end, I won't even have the Part Number field showing on the subform.)

[June7]

A 'popup' form is not a subform. A subform sits on the main form. Related records are synchronized by the subform container control Master/Child links properties. The part number would automatically populate in the subform because of this link. This is the simplest approach for entering parent/child records. No code required.

One way to 'carry forward' the part number to a new record is to set the DefaultValue property of the textbox in the textbox AfterUpdate event.

[SaskiFX]

Solved this by grabbing the Part Number key and storing it when launching the popup form, and restoring it when the popup loads.

[June7]

One way to pass a value to an opening form is with the OpenArgs argument of DoCmd.OpenForm. Then the opening form can have code in Current event to extract value from OpenArgs property.