odd form behavior when Form control source record set contains zero records

[Mercer999]

These are 2 examples.

In all cases, the form control source query returns 0 records.

In case 1 - Allow Additions is enabled on both subforms, and the main form calculation works as intended.

case 2 - Allow Additions is turned off on subform CF2. Now the CF2 subform subtotal returns NULL, which is not captured by either NZ() function on the subform, and the Main Form Total returns a #Type! error.
In my database, the queries are totaling rows, so you cannot add records.

So the New Record row in the dataset when the Allow Additions option is 'yes' allows the calculated textbox to work because a zero can be assigned on that record.


I've tried to use VBA to DCOUNT the records in the Query, but then I have the issue of a formula in the textbox.
I've tried using =IIf(Sum([PY3]) Is Null,0,Sum([PY3s])) ..
Tried checking if isNumeric. . nothing seems to work and I get #Type! returned.



Has anyone else ran into this? Is there a different approach you would take altogether to avoid the issue?



Please let me know if more info is needed. I've spend a lot of time trying correct this so any help is much appreciated.

[June7]

Nothing odd at all. There are no records to calculate on, not even a new record row, there is no Null to catch. I tested Debug.Print to return control value and it errors "You entered an expression that has no value." Code: Debug.Print Me.ctrUmpires.Form.UmpID

When I use your abbreviated syntax for referencing textbox, I get "Method or data member not found".

[Mercer999]

I added a copy of the database as an attachment. When the command button is pressed, it prints the textbox value to debug immediate window. Looks like you're right. I get the same error. Do you know how i can fix it? I'm trying to get a zero, or the sum of the record values. thanks.

[June7]

Form CF2 is set to allow additions. When I change this property, code triggers error message "You entered an expression that has no value.".

[pbaldy]

Was the suggestion in post 2 here not helpful?

https://www.accessforums.net/showthread.php?t=79396

[Mercer999]

so can i just write vba to catch that error and return zero if needed, or is there an easier way to handle it? If I use the vba to catch the error, how can I sum the query in vba and update the text box value? Right now, if catch error 2427 ("You entered an expression that has no value."), trying to make the textbox = 0 throws anther error saying i cannot assign value to the textbox. I'm guessing it's because there is a formula in the textbox already.

[Mercer999]

No, i used the NNZ() function, but it didn't change anything. Same results as using the NZ() function.

[June7]

Correct, textbox would have to be UNBOUND or BOUND to field, not an expression.

I've never seen NZZ() function. Is that a custom UDF?

[Mercer999]

yes below is the vba, but didn't work for my needs. I'm stumped on this one.

Code Courtesy of
'Keri Hardwick
'
Function nnz(testvalue As Variant) As Variant
'Not Numeric return zero
If Not (IsNumeric(testvalue)) Then
nnz = 0
Else
nnz = testvalue
End If
End Function

[pbaldy]

How did you use it, because it is made for the exact situation you described. Wrap any reference to a subform in it, and of course import it into a standard module.

[Mercer999]

I put the code in a module and then replaced the NZ() function in the form textbox calculations.

[pbaldy]

Just tested in your db, works as advertised. It goes in the textbox in the main form, not in the subforms.

=(nnz([Form2].[Form]![Text3])+nnz([CF2].[Form]![Text3]))

[Mercer999]

That worked!