DSum not providing correct amount on form

[bpinto]

I have a Purchase form with a subform for products purchased. The subform contains some calculated fields, one is for consignment fees (based on a percentage rate * extended cost). The calculated field for the consignment fee works fine (in subform). I tried to create a calculated field to sum the consignment fees, but I get errors in ALL calculated fields. So, I have tried a user-defined function that uses DSum() to total the consignment fees based on the "PurchID". When called, it calculates, but gives me the wrong total and is off by quite a bit (showing on main form). Or is there another way I can get a correct total based on a calculated field.

Calculated field (txtFee) control source: =IIf([OnConsign],Round(([ConsignRate]/100),2)*[txtConsignAmt],0)

VBA code is as follows:

Private Function SumFees(iPurID As Integer) As Double
SumFees = DSum(Me![txtFee], "tblPurchItems", "[PurchID] = " & iPurID)
End Function

Fees calculated on datasheet show: $18.53, 16.50, 18.53, 13.20
This should total $66.76
SumFees function shows $52.80

Don't understand why the total is not correct. Any help is greatly appreciated.

bp

 

[Galaxiom]

Sum in the footer of a form applies to the recordset fields.
You can Sum fields in the recordset of the form.
You cannot Sum calculated controls on the form.

Do the calculations in the Record Source query then apply the Sum to that calculated field.

Try to use the correct terminology because it will help you understand.
Fields are in tables and queries. Controls are on forms.

 

[bpinto]

I followed your recommendation GalaxiomAtHome. Created a query with calculated fields instead of using calculated controls, then summed these on the form. Now it works!

Thanks

 

[boblarson]

Also, just to note that your DSum was not right anyway.

SumFees = DSum(Me![txtFee], "tblPurchItems", "[PurchID] = " & iPurID)

Me![txtFee] is not correct because it is looking for the FIELD within tblPurchItems that you want summed. Not referring to a control on a form.

 

[Galaxiom]

Me![txtFee] is not correct because it is looking for the FIELD within tblPurchItems that you want summed. Not referring to a control on a form.

Yes you have to be careful what object you are addressing.

The other thing to watch out for on forms is the difference between controls on form and field in the recordset of the form. The Wizard gives them the same name even though they are only related through control source.

It can lead to confusion when a Wizard designed form when a controlsource is set differently from the default.

The default expanded reference on a form for Me!somename is :
Forms!formname.Form!controlname.Value

The Form and Value properties are default so can be omitted.

Where Access cannot find a control it will then move the default to:
Forms!formname.Form.Recordset!Field