How can I auto populate related foreign keys with the primarykey autonumber?

[sflinda]

For instance, I have a table (Vendor) with primary key VendorID which is related to another table called Products with VendorID as a foreign key. I have a form that allows the user to search on a particular vendor and bring up another form with products, contacts etc relating to that one vendor. However, if I try to add a new record for a new product on this form I get an error message, as the VendorID field in the table Products is not automatically populated by the auto number assigned in the primary key field VendorID in main table (Vendor).

I've tried combo boxes, linking fields, select queries using SELECT DISTINCTROW but no luck - any ideas?

I'm hoping that all I need to do is to automatically populate the foreign keys with the auto number assigned to their related primary key field?

thanks.

 

[josephwalter]

Put a hidden textbox on the Product form.
Set the Control Source to VendorID (the foreign key field of the Product table).
Then set the Default Value property to the VendorID textbox on the Vendor Form
(i.e. =Forms!frmVendor!txtVendorID... your object names may be different, but I hope that clarifies the idea).

 

[Pat Hartman]

In a subform, you don't need any code to accomplish this. Access takes care of it as long as the master/child links are properly set. If the form in question is not a subform then you need only a single line of code. It is important though to place this line of code in the correct event. The proper event to use is the BeforeInsert event. This event is triggered as soon as someone types the first character in a new record. There is no need to add a control to the form to hold VendorID. VendorID only needs to be in the form's RecordSource.

Me.VendorID = Forms!YourOtherForm!VendorID

Since the forms are separate, it is important to make sure that the current record on the original form has been saved prior to opening the secondary form. To do that add one line of code immediately in front of the OpenForm method.

DoCmd.RunCommand acCmdSaveRecord

 

[bloody_football]

Pat

Sorry for the newbie questions but I gota ask - how does this line

Me.VendorID = Forms!YourOtherForm!VendorID

look if you need the ID from a table which is called Quotes? I tried

Me.ID = Tables!Quotes!ID

and a few other variations but without success.

 

[Pat Hartman]

You have another post on this topic. I tried to answer your question there.