DSum not providing correct amount on form (1 Viewer)

bpinto

New member
Local time
Yesterday, 22:39
Joined
May 8, 2010
Messages
2
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

Super Moderator
Staff member
Local time
Today, 15:39
Joined
Jan 20, 2009
Messages
12,849
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

New member
Local time
Yesterday, 22:39
Joined
May 8, 2010
Messages
2
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

Smeghead
Local time
Yesterday, 21:39
Joined
Jan 12, 2001
Messages
32,059
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

Super Moderator
Staff member
Local time
Today, 15:39
Joined
Jan 20, 2009
Messages
12,849
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
 

Users who are viewing this thread

Top Bottom