Error with sum of none existing records

[daze]

Hi!

I get an error when summarize a field in datasheet when no record is showed.

Let me clarify:

I have main form with datasheet subform in which I have unbound textbox that calls another unbound textbox in subform (this one summarizes how many items one customer has)
So you can search through various customers of main form and in subform it shows how many items each customer has.
I wanted to summarize those items to know the totals.
But… An error appears in both of unbound boxes when there are NO RECORDS to show. (I do understand for unbound textbox in main form – it just calls other unbound textbox)

How to make this error not to appear and to make it just show ZERO?

 

[wiklendt]

what is the code you are using to sum the values?

 

[MarkK]

unbound textbox that calls another unbound textbox

I would avoid this condition. I try to make each object manage it's own data.

 

[daze]

So the code is very simple:

=Sum([kom]) - kom is equivalent to each (item)


And as for one unbound textbox shows (calls) another in subform - well this is the only way you can actually see values on main form from subform.

The error appears in situation when there are NONE of the records in subform.

So I wolud like to make it show zero when there is no record in subform.

 

[wiklendt]

you might have to do this with code first. on the 'on current' event of the parent form, use VBA to count the number of records in the subform, and return that number.

this sample database by ghudson might help give you the gist of it

 

[wiklendt]

i also did a quick search on these forums using the key words "count records subform" and got a couple of threads that look relevant to you.

thread 1
thread 2

searching the forums is a powerful way of finding what you want quickly.

 

[MarkK]

this is the only way you can actually see values on main form from subform

Your situation is that the unexpected behaviour of a component A disrupts the correct functioning of a component B. In this case B, your Form, is dependent on A, your SubForm. You have two options:
1) Complicate the functioning of the dependency, which is your plan, or
2) Eliminate the depency, which is what I suggest.
Get the main form to do the same calculations you do on the subform, drawing data from the same tables and sources and so on. This will always provide you with a more robust, less fragile system; it will be more 'scalable' and far easier to maintain in the future.

 

[wiklendt]

OR a null handler might do the trick in the interim.

Sum(Nz([koz],0)
...

 

[daze]

So,

After long time not using this the way I want it, I managed to get it right!

This is what I used:

I created unbound textbox in main form and entered:

=IIf(IsError([Isporuka2 subform].[Form]![totalsub]),0,[Isporuka2 subform].[Form]![totalsub])


As a result when there are NO records in subform instead showing "error", is shows 0.

Thank you all for helping me!

 

[wiklendt]

thank you for posting the solution glad you got it sorted