Morning, I know the answer to this will be simple, but I am missing it blindly. I have a form that on each entry it lists the cost of "X" x QTY = field 36. That works great for multi line items. The problem I have is getting a field that will add them up. I have tried the text field in several ways. I have to use the formula for the control on the field to get the value. I have the total line cost field text 37 tied back to text36 as default value=[text36]*.10+[text36]. This provides me a grand total of the cost +10%, this works also. I can not get a text field going that will sum all of the line totals into one amount. I would use a query, but I am having trouble trying to get the fields to write to my table since my control is the actual computations. I have tried to make a field that will extract the values to it, but it does not seem to work, by using default value of =[orders]![text37], but it comes up blank. Any ideas on what I am doing wrong or how I can fix it would be greatly appreciated. Thanks!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
adding values of fields on a form
- Thread starter julius1
- Start date
Toby1kenobe
Instructor
Hi
I dont quite understand what you need to do but maybe you need some unbound text boxes, ie not linked to a table.
so you could set up two unbound text boxes, txtPART1 and txtPART2
in the 'afterupdate' property of txtPART2 you could enter the code
field36 = txtPART1 * txtPART2
assuming field36 is the field in the table you want to write to.
Hope this helps a bit
Toby
I dont quite understand what you need to do but maybe you need some unbound text boxes, ie not linked to a table.
so you could set up two unbound text boxes, txtPART1 and txtPART2
in the 'afterupdate' property of txtPART2 you could enter the code
field36 = txtPART1 * txtPART2
assuming field36 is the field in the table you want to write to.
Hope this helps a bit
Toby
- Thread starter
- #3
I did try that. I took one unbound text field and set it to compute the unit*qty (text32). I took another field and set it to compute [text32]*.01+[text32](text33). The total comes up fine. I went to the after update section and typed in LineTotal=text33. It still leaves a blank line in it.
Have you tried creating another text box 34 and in its control source put =Sum etc. Text box 34 would be your sum of text box 33. You will need to put this in a footer.
HTH
An investment in knowledge always pays the best dividends.
by Benjamin Franklin
HTH
An investment in knowledge always pays the best dividends.
by Benjamin Franklin
- Thread starter
- #7
(txt34) =Val([txt32]+[txt34])
Should that have been (txt34) =Val([txt32]+[txt33])?
If so then that would be correct. Text 34 would be the combination of the 2 values. Text 34 would be my Line Total field in my table. My question is do I put the info
(Line Total) =Val([txt32]+[txt33])into the txt33 after update section or in the Line Total field in the "Default Value" section?
Should that have been (txt34) =Val([txt32]+[txt33])?
If so then that would be correct. Text 34 would be the combination of the 2 values. Text 34 would be my Line Total field in my table. My question is do I put the info
(Line Total) =Val([txt32]+[txt33])into the txt33 after update section or in the Line Total field in the "Default Value" section?
Do you want to add text box 32 + text box 33 and have the value be in text box 34 or do you want to sum text box 33???
If you want to add the two fields, in the control source (look in properties) of text box 34 enter
=[text32]+[text33]
You shouldn't need to use "val or Line Total". The control source implies this. No code shoud be necessary.
An investment in knowledge always pays the best dividends.
by Benjamin Franklin
If you want to add the two fields, in the control source (look in properties) of text box 34 enter
=[text32]+[text33]
You shouldn't need to use "val or Line Total". The control source implies this. No code shoud be necessary.
An investment in knowledge always pays the best dividends.
by Benjamin Franklin
- Thread starter
- #9
ok I think I worded it wrong.
I have a field titled Inst Hours. I also have a field titled Inst Cost. Both write to my table. The Inst Cost should be Inst Hours x 50.00. I have an hourly number in Inst hours. The Inst Cost should be that number x 50.00. I have a text field that has made the computation and shows up. My issue is how do I get that over to the Inst Cost field so it will update my table. I have to leave my controls in place so I will update my table correct? That is why I used a text field to do the math as the control. That is where I am stuck at, getting that number to populate in my inst Cost field.
I have a field titled Inst Hours. I also have a field titled Inst Cost. Both write to my table. The Inst Cost should be Inst Hours x 50.00. I have an hourly number in Inst hours. The Inst Cost should be that number x 50.00. I have a text field that has made the computation and shows up. My issue is how do I get that over to the Inst Cost field so it will update my table. I have to leave my controls in place so I will update my table correct? That is why I used a text field to do the math as the control. That is where I am stuck at, getting that number to populate in my inst Cost field.
If I understand correctly, you have (example):
Inst Hours = 2 hours
Inst Cost = $50.00/hour
Total Cost = $100.00
In my example, is $100.00 the figure you are trying to save? If so, it is not necessary or desirable to save this figure. This can be calculated anywhere as long as you have Inst Hours & Inst Cost. Also, in the query, you can create a new field that is not bound to the table.
TotalCost:[Inst Hours] * [Inst Cost]
HTH
An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Inst Hours = 2 hours
Inst Cost = $50.00/hour
Total Cost = $100.00
In my example, is $100.00 the figure you are trying to save? If so, it is not necessary or desirable to save this figure. This can be calculated anywhere as long as you have Inst Hours & Inst Cost. Also, in the query, you can create a new field that is not bound to the table.
TotalCost:[Inst Hours] * [Inst Cost]
HTH
An investment in knowledge always pays the best dividends.
by Benjamin Franklin
- Thread starter
- #11
Correct but I run reports on jobs that have Install costs above "X" amount, so then i would need this in a table for the query to pick it up.
If this is the case then in the query field enter the following
TotalCost:[Inst Hours] * [Inst Cost]
Table would be blank and in the criteria indicate what X is using the = <> symbols. You are still not storing the Total Cost in the table. Just make sure in the report to have the field Total Cost.
An investment in knowledge always pays the best dividends.
by Benjamin Franklin
If this is the case then in the query field enter the following
TotalCost:[Inst Hours] * [Inst Cost]
Table would be blank and in the criteria indicate what X is using the = <> symbols. You are still not storing the Total Cost in the table. Just make sure in the report to have the field Total Cost.
An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Here is a grand suggestion: Change all your text fields that have numbers in them to number fields. Then you wont have to use the val function or anything. Now moving to the next problem. You want to add your sub-totals up on one line or in one textbox. Easily done.
First set a text box in the footer of the form. Then the the source to something like:
=sum([box1name]*10 + [box1name])
If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm
First set a text box in the footer of the form. Then the the source to something like:
=sum([box1name]*10 + [box1name])
If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm
- Thread starter
- #14
Ok, I have set my controls to look like this in my subform wo base1.
text14=Sum([Total Cost]*0.1+[Total Cost])
The only problem is it's blank. Nothing shows up in the field.
I took it to the main form and created a field like this for the control
= Forms![wo base1]![Text14]
I tried to just sum the total cost field itself and it is also blank. the Total Cost field is made up of Quantity*Unit Price
Everything computes except my =sum feature.
text14=Sum([Total Cost]*0.1+[Total Cost])
The only problem is it's blank. Nothing shows up in the field.
I took it to the main form and created a field like this for the control
= Forms![wo base1]![Text14]
I tried to just sum the total cost field itself and it is also blank. the Total Cost field is made up of Quantity*Unit Price
Everything computes except my =sum feature.
Okay... let's see... ohhhh yess!!!! Try coding "text14=Sum([Total Cost]*0.1+[Total Cost])" into an AfterUpdate, BeforeUpdate, or basically the last control that has information being passed through it. Ohh and make sure it is in the footer of the form you need it to be in and that the syntaxes are right. One small slip for humankind here is a big mess up for your program everywhere.
If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm
If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm
- Thread starter
- #16
Ok, I have to be missing the obvious here.
the control for total material is set to this
=[Total Cost]*0.1+[Total Cost]
I have added this to the before and the after update for the text 14 which I did place in the footer of the subform:
text14=Sum([Total Cost]*0.1+[Total Cost])
I even tried it in the control of the text14.
Now I get the #Name? error.
All I am trying to do is to get a line total of equipment, that is the unit price *quantity into a field. I have that called total cost with the unit price*quantity in the control field. The next text field is total material which is to be the total cost *.01 + total cost. that works for each line. I am trying to total that to another field. Which is what i have done above. Now i am getting the #Name? error for what is to be the grand total of total material (adding up each line).
the control for total material is set to this
=[Total Cost]*0.1+[Total Cost]
I have added this to the before and the after update for the text 14 which I did place in the footer of the subform:
text14=Sum([Total Cost]*0.1+[Total Cost])
I even tried it in the control of the text14.
Now I get the #Name? error.
All I am trying to do is to get a line total of equipment, that is the unit price *quantity into a field. I have that called total cost with the unit price*quantity in the control field. The next text field is total material which is to be the total cost *.01 + total cost. that works for each line. I am trying to total that to another field. Which is what i have done above. Now i am getting the #Name? error for what is to be the grand total of total material (adding up each line).
- Thread starter
- #18
Well I remade the form and wrote everything into text fields with only identifiers in my table of the record to search by. I have all fields working except the total of my subform. I have taken all the math and consolidated it to one field. That is field text18. I can't seem to get it to add up each line. I have tried to do a =sum([wo subform].Form![Text18]) . I have put this into both the control of the field outside of my subform that is to show it(text23) and also I put it into the afterupdate of text 23, and i have put it into the afterupdate of the text 18 only there i used =[wo].form![text23]=sum([text18]). Nothing worked. I can show the control as =[wosubform].form![text18] and it does show each line cost as i scroll thru each line, but I can not get a sum to save my hide. Any thoughts? in a nut shell...text 16 in my subform is the total for each line. there are multiple lines to each order. i am trying to get a total for all material on each order. I have tried putting it into the footer as well. The text 23 I just made the control as the field int he footer and I get an #error. Sorry i don't mean to be a pain.
Look at the following article, it might help you, especially where to put a control for summing of a subform.
Microsoft Knowledge Base Article - 208998
An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Microsoft Knowledge Base Article - 208998
An investment in knowledge always pays the best dividends.
by Benjamin Franklin
- Status
Similar threads
- Locked
- Question
