Full Version: sum, tax and total on a form
UtterAccess Forums > MicrosoftŪ Access > Access Forms
KristianDude
I have a form with 6 cost fields that may or may not be Null, then the subtotal field, then tax, shipping and total. What would I write in VBA to make this all work?!!!... if it's possible?!.... . What do you guys think????
xample:
1.) fldCost1+fldCost2+fldCost3+fldCost4+fldCost5+fldCost6= fldSubtotal
2.) fldSubtotal * 0.0775= fldTax
3.) User inputs fldshipping
4.) fldSubtotal+ fldTax+fldShipping= fldTotal
KristianDude
also, where would it all be.... the before/after update, etc. Thank you so much in advance!!!
Jack Cowley
Do you have 6 cost fields in a single record? If so then you have a problem with normalization as those 'fields' should be 'records' in a related table.
would suggest you normalize your structure before you go much further or you will continue to run into problems when trying to add up columns.
Assuming your data is fixed then something like this in the Control Source of unbound controls should work (you will not be saving this data).
=Sum([fldCost])
=(Sum([fldCost]) * 0.0775 )+ Sum([fldCost])
..user input
=(Sum([fldCost]) * 0.0775 )+ Sum([fldCost]) + fldShipping
This is all air code so take it with a grain or two of salt...
Jack
KristianDude
I'm sorry.....I didn't explain all.... this form is being used for ease of use to input data into a MS Word template. These fields/records are not in a table at all. These are being input by the user and saved and printed in a specified Word document through automation.
Jack Cowley
Use a command button to put the values you want in to the various controls on your form:
e.fldSubotal = Nz(flCost1) + Nz(fldCost2) ...Nz(fldCost6)
Me.fldTax = (Nz(flCost1) + Nz(fldCost2) ...Nz(fldCost6)) * 0.0775
Me.fldTotal = (Nz(flCost1) + Nz(fldCost2) ...Nz(fldCost6)) * 0.0775 + fldShipping
I think you get the idea. The code above may not be dead on, but should be enough for you to sort it out.
Jack
Rainlover
Try
[fldCost1]+[fldCost2]+[fldCost3]+[fldCost4]+[fldCost5]+[fldCost6]
Place this in your Control Source. I would also suggest that for each control that you have a default value of 0
You also need to format each controll as a number.
If you can get this to work then you should be able to do the rest.
Rainlover
Jack's use of NZ is a better idea. This will get you past the Null possibility.
KristianDude
Hi Rainlover!... would it still work if each control was formatted as currency?....I'll check back in the morning! Thanks!!!
Rainlover
Yes
KristianDude
I did as you guys suggested........in the box is says #Name?... if I wanted to put it in code (VBA), is that where jack's would work???.... would it be before update?
KristianDude
I even tried the command button option........... what am I doing wrong???? crazy.gif
KristianDude
.....in the on click of the "calculate button" I created, I have:
Private Sub Calculate_Click()
'calculate all fields on form
Me.Subtotal = Nz(E1) + Nz(E2) + Nz(E3) + Nz(E4) + Nz(E5) + Nz(E6)
Me.Tax = (Nz(Subtotal)) * 0.0775
Me.Total = Nz(Subtotal) + Nz(Tax) + Nz(Shipping)
End Sub
...all the control sources are currency, default 0 the names are all correct..... any ideas???
KristianDude
one more thing, sorry............ when I click the calculate button, error reads:
The expression On Click you entered as the event property setting produced the following error:
Procedure declaration does not match description of event or procedure having the same name.
Thanks in advance! :-) UA ROCKS!!!
jmcwk
KristianDude,
Not questioning your thinking but I am curious Why you are needing or using a click event to calculate the values? Why not use the expression within each control and capture the result on the fly?
KristianDude
I would love to!....I tried what was suggested earlier, and my boxes gave me the good 'ol #Name?..... I'm obviously a newb, and probably should've learned the math stuff already, but I'm illiterate when it comes to the math stuff. I thought Jack (above) said to use a button, was I wrong????? HELP?!?!?!?!?!!... I liked Rain's idea, but that's where I got the #Name?.....
jmcwk
Hey! Don,t be so hard on yourself ALL of us have to start somewhere can you attach a zipped copy of your DB no larger than 500000kb ? I am still a Newb in a lot of respects myself frown.giffrown.gif
KristianDude
I'm trying to get it ready to attach, but in the zipped folder I made, it's saying it's read only... how do I get rid of that?
KristianDude
Here it is still read only
jmcwk
Did not attach take a look at the attachment in This Thread
KristianDude
Sorry.........here is the read only attachment (it timed me out)
KristianDude
can't attach?
jmcwk
Give me a little bit and I will see what I can put together is there a reason you can not attach it?
KristianDude
It was too big.....I thought I could attach up to 500000 KB! Ha!
KristianDude
There are more fields I need to add to a couple of the forms. It is definately on it's way to being a mess if I don't normalize it. As Grover Park George suggesed this morning..... I'm also trying to figure out an autobutton for the PO form.... his advice....
Grover's suggestions
jmcwk
Attached is a Demo and as suggested If this was a working Database it would NOT be structured properly in this Format it is merely to show you how the calculations are being calculated and the Expression/s used in doing so. Open the Form and go to a new record and start putting values in the controls. Look at the query and the expressions in the CostTotal,Tax etc. As Georges post suggests Normalization,Structure, etc. are paramount to a good product and should not be ignored regardless of how minor. Anyway I hope the attached gets you on the right track and provides some answers to your questions.
TH
KristianDude
John, I don't see an attachment!....
jmcwk
My Apologies! Thats what I get when I get in a hurry
KristianDude
thank you soooooooooooooooooo much!!!!! o! o! thumbup.gif o! laugh.gif cool.gif giveup.gif
jmcwk
Looks Like YOU are a happy Camper frown.giffrown.gif Makes Me Happy As Well! Your Very Welcome Need more help just post UP! Good Luck With Your Project!
KristianDude
a quick side question............. are the High posters of access just doing this for fun, or is there a business that runs this site???????
jmcwk
Could be wrong but I think it is merely (Although the greatest) a site that Users frequent such as myself and others weather it be a problem they may have dealing with Access or other Microsoft Office Programs.
embers will do their best to assist and help a fellow member with their problem. It is a wonderful resource.
To answer your question in a nutshell it is for fun and the enjoyment of helping others.
Nowhere else will you find a better Forum dealing with Access than Utter Access! And we are Happy To have You as a Member:)
Rainlover
I was just going through some of my posts and noticed that you did not get an answer to your last thread. Do you still need help? If so, I or someone else will come to your aid. I can't help at the present moment as it is Bed time for me in this part of the world.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.