access 2016

Question

I am trying to add values in three text boxes in there subforms which have a control source of "sum of the numbers in a column". when there are numbers in the column I get an correct answer. But when one subform does not have any value I get a #Type! error in my final text box. I tried to use if command to convert a null value in the text boxes to zero. but it did not work out well.

Answer 1

Hi,

Can you please post the code you're using? Thanks. Have you tried using the Nz() function?

Answer 2

If a subform is completely empty, e.g. if its recordset is not updatable and is empty, and/or if the subform's AllowAdditions property is False and its recordset is empty, then referencing a control which aggregates values in the subform will result in an error.  This can be avoided by returning a value directly from the table or query in question.

Say, for example, a control in an order details subform has a ControlSource of:

=Sum([UnitPrice]*[Quantity])

A control in the parent orders form can return the same value with:

=Nz(DSum("[UnitPrice]*[Quantity]","[OrderDetails]","[OrderID]=" & Nz([OrderID],0)),0)

In this expression the DSum function is used to return the aggregated value from the OrderDetails table directly.  The Nz function is called twice in the expression, once to avoid an error if the OrderID column in the  parent form's recordset is Null, i.e. it is at an empty new record, once to return a zero if the subform contains no rows, in which case the DSum function would return a Null.

In your case you would simply add the return values of three such expressions to return the total value from the three subforms, regardless of whether any is empty or not.

---

Ken Sheridan, Stafford, England

Answer 3

Hi Udesha,

I can see that after creating this thread , you did not follow up this thread again.

this thread is still open.

if you find the solution by your self then I post the solution and mark it as an answer.

if the issue is still exist then refer the suggestion given by the community member. that suggestion can solve your issue.

if you have any further question regarding this issue then let us know about that. we will try to provide you further suggestions.

Regards

Deepak 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].