Summing subform values on a mainform

[New2VB]

Greetings Gurus,

Perhaps you could help?

I have a Main form "A" with subforms "B" & "C". Both B & C contain time fields which are manually entered in hh:MM:ss format and TotalTime fields with a control source set as Sum([Timefield]).

Main form A contains a Grand TotalTime field which needs to be [SubformB].Form![TotalTime] + [SubformC].Form![TotalTime]. This works fine as long as the TotalTime fields have values but, where one of the fields has no value, A.GrandTotalTime = #Error (as expected).

I have tried setting A.GrandTotalTime = Nz([SubformB].Form![TotalTime]) + Nz([SubformC].Form![TotalTime]) but that makes no difference. I have tried using an event to set if isNull values to 0, "0", "0.00" & 0# but because there is no user interaction with the subforms none of the available events trigger the procedure - all user interaction takes place on the Main form and won't trigger the subforms

How can I get A.GrandTotalTime to accept the 0 value as valid input to the calculation?

 

[vbaInet]

You can use the IsError() function to check if it's an error or not and act accordingly.

IIF(IsError() ...)

 

[New2VB]

Hi vbaInet,

Is this correct (not sure what "true" part or "false" part is meant to be in the builder "IIf (IsError («varname») , «truepart», «falsepart»)"?

Setting MainformA.GrandTotalTime control source to =IIf(IsError([SubformA].[Form]![TotalTime]+[SubformB].[Form]![TotalTime]),0) results in "The method you tried to invoke on an object failed"...

 

[vbaInet]

You will need to test both.

=IIF(IsError([COLOR=Red]A[/COLOR]) Or IsError([COLOR=Red]B[/COLOR]), 0, [COLOR=Red]A[/COLOR] + [COLOR=Red]B[/COLOR])

Where A, B are the reference to your controls in subform's A and B.

fyi: Instead of this [SubformA].[Form]![TotalTime] you can just use [SubformA]![TotalTime]

 

[Uncle Gizmo]

Yfyi: Instead of this [SubformA].[Form]![TotalTime] you can just use [SubformA]![TotalTime]

Thanks for that!
I didn't know that!

 

[vbaInet]

Thanks for that!
I didn't know that!

You're welcome Uncle Gizmo!

 

[New2VB]

Thanks vbaInet,

That solves the immediate problem wherein the form no longer errors but now A.GrandTotalTime = 0.00 whereas it should read 3.17 (being the TotalTime of SubformA) + 0.00 (the TotalTime of SubformB).

Given that I am using control sources to get the values that I require, and that I can't get the Main form to retrieve the values, how would I set something like...

If isNull [SubformA]![TotalTime] then [SubformA]![TotalTime] = 0.00
End If
If isNull [SubformB]![TotalTime] then [SubformB]![TotalTime] = 0.00
End If
A.GrandTotalTime = [SubformA]![TotalTime] + [SubformB]![TotalTime]

 

[vbaInet]

In that case you would be better off using a function:

Public Function GetSubformVals() As Double
    Dim dblA As Double
    Dim dblB As Double

    If IsError(A) Then
        dblA = 0
    Else
        dblA = A
    End If

    If IsError(B) Then
        dblB = 0
    Else
        dblB = B
    End If

    GetSubformVals = dblA + dblB
End Function

 

[New2VB]

Hi vbaInet & thank you,

I found an "easier" and possibly more elegant way to achieve the goal.

Instead of adding the two subform's values in a textbox on the Main form, I created two textboxes on the Mainform using the subform's values as their control source, with your Iif(IsError) declaring their values as 0 if they didn't have values, then added those two textboxes values. Works every time.

Thanks for your help.

 

[vbaInet]

Good to see that you were thinking out of the box. They are actually the same, no improvement in effeciency. In your case you are using more controls, which means more memory will be consumed for the extra control and in my case it's just one control and few lines of code.

Good job!