Empty Query Behind Form

[LittleGlory]

Hoping someone can please help. I am pretty good in Access but this is one that I can't get to work.

I have a form that has sub forms. I am performing calculations on my main form using fields from the sub forms. Normally this works fine. I have trouble with this when the query behind my sub form is empty. When this occurs, my main form shows an error message in the calculated fields.

For instance, I am calculating the total downtime on a machine. My sub form is using a query linked to another database and pulling in the amount of downtime. On my main form, the user can enter additional downtime. Then I add the 2 together (the number from the sub form and the main form).

I need the form to first check and see if the sub form is empty and if so, I need the main form to display only the number entered on itself, not try to add the subform number in as well. Basically, if there is nothing in the sub form I want my total downtime calculation to be just the number entered on the main form, and if the sub form is not empty, then it should be the sub form number and the mainform number added together.

Hopefully someone can help. I am working on this project for work and this is my final obstacle to getting it out.

Thanks.

 

[pbaldy]

I use the following function within the formula. In your case, your formula might be:

=MainFormFieldReference + nnz(SubformFieldReference)

'This code was originally written by Keri Hardwick. 
'It is not to be altered or distributed, 
'except as part of an application. 
'You are free to use it in any application,  
'provided the copyright notice is left unchanged.
'
'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

 

[Rich]

=Iif(IsNumeric(Forms!MainFormName!SubFormName!Control,Forms!MainFormName!SubFormName!Control +MainFormControl,MainFormControl))