Link to home
Create AccountLog in
Avatar of --TripWire--

asked on

Form and Subform values being stored in separate records


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?
Avatar of als315
Flag of Russian Federation image

Can you upload your db with sample data?
Avatar of --TripWire--


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.

I don't clearly understand your aim. Both forms (main and sub).
May be this example will help you
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.
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.
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
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
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?
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
Avatar of als315
Flag of Russian Federation image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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?
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