Save MainForm data into Subform table

[jbravo2]

I have a form (frmCustomer) which is based on a link to a table-view in our SQL Server database. With this form I display all the contact information for the customer (name, address, phone, etc) through the use of a search button. I also have a subform (frmItems), which is going to be use to input new orders and items information. I placed this subform in the MainForm. I can search any record in the main form and get customer information, then at the bottom I use the subform to enter new data. When I save the subform (the "Save" button is on the subform) it saves what it is entered in the subform textboxes. But due to business requirements I need to also attach the customer information to each new record, which is only found on the Main Form. Right now I only get the customer# from the Main form because that's the primary key link I have between both forms/tables. My question is, is it possible to add to the subform's save button a code that also saves the current data displayed in the main form to the table in the subform?

 

[spikepl]

1. Use line breaks and spacing to make your stuff readable for others but yourself

2. Your data structure sounds wrong. Orders usually have some common data called Orderdata in additions to OrderLInes (or items) , and that is where one might save common customer data (like shipping address). What is you data structure? Attach a screen shot (zipped, since you have <10 posts)

 

[gemma-the-husky]

But due to business requirements I need to also attach the customer information to each new record, which is only found on the Main Form. Right now I only get the customer# from the Main form because that's the primary key link I have between both forms/tables. My question is, is it possible to add to the subform's save button a code that also saves the current data displayed in the main form to the table in the subform?

The ONLY info that should be in the SUBTABLE is the ID of the customer#.

You then use a query to join the customer info to the order info when you need the combined result. You NEVER need to duplicate the data.

It's often easier to think data, rather than think forms. "forms" is just a presentational detail

 

[jbravo2]

Thanks Gemma-the-husky, I didn't think about that. I will create queries using both tables to satisfy the reports needed.

 

[jbravo2]

Just in case for some other reason someone needs to reference data from the main form and place it in the subform, I found this:

Me.subformTxtName = Forms![MainFormName]![MainFormTxtName].Value