Full Version: Adding many Field values together in a subform
UtterAccess Forums > Microsoft® Access > Access Forms
I've been working on this long enough before I posted it here. I try not to bother people but...I'm simply not seeing where to enter the code to make this work. I've been reading Access 2007 VBA that I purchased as well.
In any case...I have 4 fields on a Table...that is bound to a subform. I have 4 additional fields on the subform as well that are Yes/No.
When the value in field 1 is changed to Yes, the value in let's call it field 1A is PROPERLY being updated. All 8 fields are being accessed through the subform.
I's actually like to do 2 things....1) Create a "save record" button on the subform that will write all the records I've created in the subform to the bound table. 2) I have one more field on the subform, that I'd like to end up with a total of 4 of the other fields.
So...field 1 has 1A, field 2 has 2A, field 3 has field 3A and field 4 has Field 4A. When the value in the 1,2,3 or 4 is "Y", then it correctly updates it's corresponding "A" field with a "number". This works fine.
I'd like to create a "SAVE REcord" button on the subform that Totals all the "A" fields together and enters the value in a "Total" field and saves the records to the table that is bound to the subform.
All the logic is working fine to update the "A" fields. And, when I hit the "main" save icon in Access, it writes the record just fine, although I should have a "save" button somewhere.
Oneed to create this "Save Record" button for the multiple records that can be created in the subform and add up the values and post it to the Total field in the subform at the same time.
I guess I'm not finding the right "Event" to use to have this fired off.
Thank you,
If I correctly understand what you are trying to do, there are several ways I can think of to accomplish it.
ossibly most simple would be to create a text box on your form which sums the four values you want. Then just add a button which runs a query to update the record(s) in your table.
Please read my post. I said I already have a "total" Text Box on the subform to total (1A & 2A & 3A & 4A)...
I've created code to add those fields and post it in the TOTAL box. But no matter with what Event I put the code, I can't get it to work.
Also. I'd like to put a SAVE Record button on the subform, but I'm displaying the SUBFORM in DATASHEET View. So adding a button on the subform does not show up.
I've already created a TOTAL box...WHAT IS THE CODE and WHERE (What Event) should I put it with?
Thanks again,
Jack Cowley
Steve -
If I understand you correctly you have four FIELDS in a RECORD and another 4 FIELDS that are Yes/No. If that is correct then your structure is not normalized as those are 'repeating groups'. Those FIELDS should be RECORDS in a related table not FIELDS in a RECORD as it appears that they are.
Another thing is that you do not want to save the total of a calculation, if that is what you want to do. Values from calculations should not be saved, but calculated as needed...
You also mention saving data to the table when the data is entered into the subform. If the subform is bound to a table it is saved by default and you do not need to force a save...unless you are using unbound forms....
My 3 cents worth..
OK, I'll be a little more explicit:
Create a query which updates the TOTAL field in whichever table it is in. Use the builder to set the source data for that query to the TOTAL field you already have on your form. Add a button which runs the query.
By the way, as a fellow with teribly not normalized tables, I can sympathize with the other comments too.
The wizzard creates a macro which you can convert to code: DoCmd.OpenQuery("PostMyTotal")
Ok...Thanks guys...but I think I've found my reason for the "TOTAL" box not working... Sometimes, you get so caught up that you overlook the simple things...I'll test it and get back to you...but I believe the issue has to do with any one of my four "source" fields..(1A,2A,3A,or 4A) being null. When adding the four fields together, if any one of them is null, then you do not end up with a total. So I have to add some logic to account for null values.
LSO...my Table IS normalized. I"ve been working with Databases for 15 years....
Field 1 is a YES/NO fields... and I VB logic that says if field 1 is YES, then enter a "Number" 1 in field 1A...
Field 2 is a YES/NO Field ...if 2 = Y, then enter a number(Value) 2 in 2A..
Field 3 is a Yes/No Field... if 3 = Y, then enter a number (value) 4 in 3A..
Field 4 is a yes/no field...if 4= Y, then Enter a number(Value) 8 in 4A...
In each case if the data entered is NO...then enter a "0" in the associated A field. Apparantly 0 is not "really" entered and the field ends up being null.
I have all 8 if these fields on the same TABLE & SUBFORM for a reason.... it was getting my TOTAL box to add up all the values in the "A" fields to work..... Last night, I tried putting a "Y" in all four boxes and the TOTAL BOX worked. Hence my thought that I have to add logic for null values....
I appreciate your help...but sometimes you guys need to stop trying to over explain and out think the next guy with your answers and just try to simply answer the question without going to deep. I realize some people that post up here are not very experienced and there are others that ARE. I try to post a question and ask for a simple answer....
THANK you ALL...and I'll let you know if accounting for the NULL value does what I need.
Hi Steve
gree with Jack when he says your table is not normalised
Have you tried this to get your total?
Thanks for your reply Mike...
Since I'm not totally familiar with your use of syntax...could you tell me what Nz stands for?
Also...where would I put this formula?
If one or more of your textboxes are empty (Null) you would get a Null results for you addition operation, because
ull + anything = Null
the Nz() function replaces a Null value with something else. The Default is Zero. Since it is the Default you don't actually have to use it.
NZ([nameof1A], 0)
do the same thing.
HAs for
"I'd like to create a "SAVE REcord" button on the subform that...saves the records to the table that is bound to the subform."
This is really unnecessary, because once you move off of a record to enter another one, the first record is automatically saved by Access.
OK...Nz was just what the doctor ordered! Worked like a charm.
entered the code in the "AfterInsert" event of the Form(In this case Subform) and the Nz took care of the Null value problem, hence the addition worked.
Thanks to ALL......
Hi Steve
Glad your sorted
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.