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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

calculate fields

Status
Not open for further replies.

quackslikeaduck

Technical User
Apr 16, 2013
46
US
I have two fields from two different subforms. I would like to have a total (on the main form) of the two fields from the two different subforms. Is there a way that the total (that's located on the main form to update as information has been added to the text boxes in the two different subfolder. Basically I don't want to click on "total" to get the calculation.
 
Sure my fields are Subform2, ItemList.Subform3PricePerUnit 1 - 5, tbPurchaseOrderItems.QuantityOrdered 1 - 5,

I used the following code in my original form and it worked fine. It doesn't work with the form I have now.

Me.Total_ = (Val(Nz(PricePerUnit, 0)) * Val(Nz(QuantityOrdered, 0))) + (Val(Nz(Price_PerUnit_2, 0)) * (Val(Nz(QuantityOrdered_2, 0))) + (Val(Nz(PricePerUnit_3, 0)) * Val(Nz(QuantityOrdered_3, 0))) + (Val(Nz(PricePerUnit_4, 0)) * Val(Nz(QuantityOrdered_4, 0))) + (Val(Nz(PricePerUnit_5, 0))) * Val(Nz(QuantityOrdered_5, 0)))

 
CORRECTION:

Sure my fields are tblPurchaseOrderItems subform.QuantityOrdered 1 - 5, tblItemList subform..PricePerUnit 1 - 5,

I used the following code in my original form and it worked fine. It doesn't work with the form I have now. The last form was based on same values but needed to be normalized.

Me.Total_ = (Val(Nz(PricePerUnit, 0)) * Val(Nz(QuantityOrdered, 0))) + (Val(Nz(Price_PerUnit_2, 0)) * (Val(Nz(QuantityOrdered_2, 0))) + (Val(Nz(PricePerUnit_3, 0)) * Val(Nz(QuantityOrdered_3, 0))) + (Val(Nz(PricePerUnit_4, 0)) * Val(Nz(QuantityOrdered_4, 0))) + (Val(Nz(PricePerUnit_5, 0))) * Val(Nz(QuantityOrdered_5, 0)))

Am I missing something here? Nothing happens??
 
You won't get a total until the records in the subform are saved.

It looks like you are using code rather than an expression in a control source. Where is the code running?

Duane
Hook'D on Access
MS Access MVP
 
The value will be in my total text box. The code is running on the "Private Sub Total__Click()" Event. I was actually just in the Expression builder trying to figure out how to use that instead because I know that I don't have the naming of the subtotals and fields laid out in the proper manner. I do notice also that there is a space in the naming of subforms. So it looks like the following: tblPurchaseOrderItems subform. I named them but then there is a space between the name of the tbl and subform and I know that I didn't put the space in it was done for me automatically. I don't know if that make a difference. Please advise?
Thanks!
 
I've finally got the code together in what I thought was right but now I'm coming up with Run-Time error 2450 Microsoft Access cannot find the referenced form 'tblitemlist subform'.

Here is the code that I used:

Private Sub Total_Click()
Me.Total = (Val(Nz(Forms![tblItemList subform]![PricePerUnit], 0)) * Val(Nz(Forms![tblPurchaseOrderItems subform]![QuantityOrdered], 0))) + (Val(Nz(Forms![tblItemList subform]![PricePerUnit_2], 0)) * (Val(Nz(Forms![tblPurchaseOrderItems subform]![QuantityOrdered_2], 0))) + (Val(Nz(Forms![tblItemList subform]![PricePerUnit_3], 0)) * Val(Nz(Forms![tblPurchaseOrderItems subform]![QuantityOrdered_3], 0))) + (Val(Nz(Forms![tblItemList subform]![PricePerUnit_4], 0)) * Val(Nz(Forms![tblPurchaseOrderItems subform]![QuantityOrdered_4], 0))) + (Val(Nz(Forms![tblItemList subform]![PricePerUnit_5], 0))) * Val(Nz(Forms![tblPurchaseOrderItems subform]![QuantityOrdered_5], 0)))
End Sub
 
Okay that looks reasonable. Can you tell me why the code that I did run doesn't work even if I do build the query. It worked in the total that I had on the form before I normalized it but doesn't work now. I'm not stuck on running it but would like to know why it doesn't work now. I looked all over the web for that specific error and none of them seemed to be relevant to my issue.
Thanks!
 
Ha good point. I did use the Expression Builder to make them but never gave it a second thought because they were the only expression categories.
 
I now have a total. I went in and checked and changed the subform control name in each instance.
Thanks!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top