Form and Subform values being stored in separate records

--TripWire--
--TripWire-- used Ask the Experts™
on
Hello,

I have a subform in a form and I want the data I put into both of them to reflect in a table. I'm finding that half of my control values are being stored in one record and the other half are being stored in a different record.  I'm finding that the data from the main form controls are going to one record, and the controls on the subform are sending them to another record.  How do I combine the two?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you upload your db with sample data?

Author

Commented:
Here it is.  Please note that a lot of data and items have been deleted or changed for the sake of privacy.
Also - Form1 is the subform and Form2 is the Main Form I'm talking about.

Thank you.

EE-example.mdb
I don't clearly understand your aim. Both forms (main and sub).
May be this example will help you
EE-example.mdb
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
I can't open the file.  It says it's located outside the local intranet or an untrusted site.

My logic:
I have two tables Items and SuppliesRequest.  Items has details about the individual items themselves and SuppliesRequest are all the requests from each dept from the company for those items.  It's a one to many relationship.  

So the Form/Subform is a request form where a dept can request multiple items at the same time.  So I want to be able to select I.T. from the drop-down and link a number of different items to I.T. since they're the ones requesting those items.
You should save this file to trusted zone or enable it, pressing on the yellow ribbon with warning.
I hope my example is near your wish.

Author

Commented:
That works -- Thanks a lot!  What did you do differently?
I've removed record source from yor main table, added query for sub form with Criteria for Section and default values for Section and Date in subform.

Author

Commented:
Hmm...Well I thought it was the answer I was looking for, however, when I try to implement it in my original work, I'm finding it's still not saving what I want into the table fields.

I noticed there's no RecordSource for the main form.  Is this supposed to be?  Because when I select "I.T." on that form, I want it saved in the field Section/Unit in the table SuppliesRequest.
Record source from main form should be removed. In afterupdate event for Section Combobox is requery for sub form. Section in table should be default value for this field in subform

Author

Commented:
I don't understand
You should add following to your form:
1. Remove record source from main form.
2. Change record source of subform to query from my example (check field name in Expr1)
3. Set default value for fields with Section and Date to corresponding fields in main form (you can use builder)
4. Add AfterUpdate event to field with Section in main form with code from my example (check name of subform)
Addition to point 3: in subform

Author

Commented:
Well I don't think the example you gave was working the way I'm looking for.  In the end, the data isn't being stored in my table.
It is classic Access form. Open Northwind.mdb and you will see absolutely same.
What do you like to get? May be you can upload real db with sample data?

Author

Commented:
I've uploaded the sample file here.  Open "Form2".
You'll notice that when you fill in the information and open up the table called tblSuppliesRequest, the productID number is showing up where the product description should be and nothing is showing up under Date and Product ID fields.

Also, when you fill in Form2, I want the ProductID to automatically show up when you select the ProductDescription from the dropdown box.  Please explain your steps should you find a solution if possible.  

Thank you
MySampleDB.mdb
You don't need field Complete Descripion in tblSuppliesRequest, all nesessary information for future reports can be collected in query from table tblItems.
ProductID can be stored with two column combo box (ProductId is hidden).
MySampleDB.mdb

Author

Commented:
The thing is, I think I do need the description field, because I want the person to be able to select the description of the item (e.g. Mead Binders) and the ProductID to auto-generate and be stored in tblSuppliesRequest.
I've added description due to similar positions in table tblItems with difference only in description field:
20 Binders Office Supplies Mead
33 Binders Office Supplies Mead White
How do you like to select these items?

Author

Commented:
I want to select "Binders Mead (White)" from the drop-down and for the number 33 to show up in the Product ID and save into one record.
Sample is working absolutely as you want. You can change Row Source for Product ID field according to neds. For selecting Binders Mead (White) you will need:
Name: [Item] & "  " & [Brand] & IIf(IsNull([Description]);"";" (" & [Description] & ") ")
But stored will be Product ID

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial