DSum() formula field on Form shows #Error as Form open with no record entry

[wendywu]

How to solve this issue?
I have a DSum() formula field on the purchase order form, which calculates sum of items in puchase detail subform.
It shows amount when data entry on the form.
But when the form popped with no data entry, this field show #Error, how to solve the issue?

 

[June7]

Does the DSum() expression reference controls on subform? Why use DSum()? Do Sum() in subform footer textbox then textbox on main form references subform textbox. Regardless, subform must display at least New Record row. Why does subform not show New Record row?

 

[LarryE]

How to solve this issue?
I have a DSum() formula field on the purchase order form, which calculates sum of items in puchase detail subform.
It shows amount when data entry on the form.
But when the form popped with no data entry, this field show #Error, how to solve the issue?View attachment 120439View attachment 120440

=Iff(DSum>0,DSum,0) instead of just the DSum formula.

 

[wendywu]

Does the DSum() expression reference controls on subform? Why use DSum()? Do Sum() in subform footer textbox then textbox on main form references subform textbox. Regardless, subform must display at least New Record row. Why does subform not show New Record row?

When I want to create new purchase order, I click a creat button on Dashboard Form , than a Blank purchase order form pops ,so subform not show New Record row nor the main Purchase Order Form not shows record but shows #Error in the "Total Amount" field ,which is embeded with DSum().
The sequence is that I create new PO first, using vba for adding new record like PurchaseID etc, than I can use the "AddProduct" button click event to add items to details subform, there is "TotalCost" column for each item in the Purchase Details subform.

Expression in the "Total Amount" field in the PO Form is: DSum("TotalCost","PurchaseDetails","[PurchaseID] =" & [PurchaseID]

Expression is to sum the "TotalCost" for each item in the purchase details subform.

The "Total Amount" field shows amount when there are records in the Purchase Details subform but shows #Error when no record in details subform.

 

[arnelgp]

you can use:

 =DSum("TotalCost","PurchaseDetails","[PurchaseID] =" & Nz([PurchaseID], 0))

 

[June7]

LarryE, The function is IIf, not Iff. I doubt that expression will resolve the #Error.

Now I understand the DSum(). Arnelgp's suggestion should work.

 

[LarryE]

LarryE, The function is IIf, not Iff. I doubt that expression will resolve the #Error.

Now I understand the DSum(). Arnelgp's suggestion should work.

Yes my eyesight...ha ha