Avatar of --TripWire--
--TripWire--
 asked on

Form and Subform values being stored in separate records

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?
DatabasesMicrosoft AccessSQL

Avatar of undefined
Last Comment
als315

8/22/2022 - Mon
als315

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

ASKER
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
als315

I don't clearly understand your aim. Both forms (main and sub).
May be this example will help you
EE-example.mdb
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
--TripWire--

ASKER
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.
als315

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.
--TripWire--

ASKER
That works -- Thanks a lot!  What did you do differently?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
als315

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.
--TripWire--

ASKER
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.
als315

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
--TripWire--

ASKER
I don't understand
als315

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)
als315

Addition to point 3: in subform
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
--TripWire--

ASKER
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.
als315

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?
--TripWire--

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
als315

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
--TripWire--

ASKER
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.
als315

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?
--TripWire--

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
als315

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