calculate fields

[quackslikeaduck]

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.

 

[dhookom]

What have you tried?

Can you provide the names of any of your objects?

Duane
Hook'D on Access
MS Access MVP

 

[quackslikeaduck]

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

 

[quackslikeaduck]

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

 

[dhookom]

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

 

[quackslikeaduck]

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!

 

[quackslikeaduck]

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

 

[dhookom]

You shouldn't have to run code. Try the solution from thread702-1709277.

Duane
Hook'D on Access
MS Access MVP

 

[quackslikeaduck]

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!

 

[dhookom]

Do you really have subform control named [tblitemlist subform]? Do you understand the difference between a subform control name and a subform source object?

Duane
Hook'D on Access
MS Access MVP

 

[quackslikeaduck]

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.

 

[quackslikeaduck]

I now have a total. I went in and checked and changed the subform control name in each instance.
Thanks!