Best way to calc total on a form from a bunch of subforms

[boooney]

Hi-

I have a parent datasheet with a bunch of subforms. Here is the current
system I'm using and it doesn't seem to work all of the time (i.e. the parent
datasheet text box doesn't always fill)

Parent Datasheet
text box with control source =[Contracts subform].Form![Scope Total]

Subform Footer
text box with control source = =Nz(Sum([Contract Amount]),0)

Is there a better way to get a total on a datasheet from a bunch of child
subforms that is accurate and updates on the fly?

Thanks.

Dan

 

[Stefan Hoffmann]

hi Dan,

Parent Datasheet
text box with control source =[Contracts subform].Form![Scope Total]

Subform Footer
text box with control source = =Nz(Sum([Contract Amount]),0)

Is there a better way to get a total on a datasheet from a bunch of child
subforms that is accurate and updates on the fly?

No, not really. You may use a proxy function on your parent form, which
may under some circumstance a better approach, e.g. when you need it
twice or more:

Public Function GetScopeTotal() As Currency

GetScopeTotal = Forms![Contracts subform].Form![Scope Total]

End Function

Then you can switch later to a version without subforms and without the
need to touch the controls:

Private m_Total As DAO.Recordset

Private Sub Form_Close()

If Not m_Total Is Nothing Then
m_Total.Close
Set m_Total = Nothing
End If

End Sub

Private Sub Form_Current()

Dim Sql As String

Sql = "SELECT Sum(a) As Scope, Sum(b) As Scope2 FROM Table WHERE .."

If Not m_Total Is Nothing Then
m_Total.Close
End If
Set m_Total = CurrentDb.OpenRecordset(Sql)

End Sub

Public Function GetScopeTotal() As Currency

GetScopeTotal = m_Total![Scope]

End Function


mfG
--> stefan <--

 

[boooney]

Stefan-

Thanks for your help. Can you please explain more about what you mean "
Then you can switch later to a version without subforms and without the need
to touch the controls:"?

Dan

hi Dan,

Parent Datasheet
text box with control source =[Contracts subform].Form![Scope Total]

Subform Footer
text box with control source = =Nz(Sum([Contract Amount]),0)

Is there a better way to get a total on a datasheet from a bunch of child
subforms that is accurate and updates on the fly?

No, not really. You may use a proxy function on your parent form, which
may under some circumstance a better approach, e.g. when you need it
twice or more:

Public Function GetScopeTotal() As Currency

GetScopeTotal = Forms![Contracts subform].Form![Scope Total]

End Function

Then you can switch later to a version without subforms and without the
need to touch the controls:

Private m_Total As DAO.Recordset

Private Sub Form_Close()

If Not m_Total Is Nothing Then
m_Total.Close
Set m_Total = Nothing
End If

End Sub

Private Sub Form_Current()

Dim Sql As String

Sql = "SELECT Sum(a) As Scope, Sum(b) As Scope2 FROM Table WHERE .."

If Not m_Total Is Nothing Then
m_Total.Close
End If
Set m_Total = CurrentDb.OpenRecordset(Sql)

End Sub

Public Function GetScopeTotal() As Currency

GetScopeTotal = m_Total![Scope]

End Function


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Thanks for your help. Can you please explain more about what you mean "
Then you can switch later to a version without subforms and without the need
to touch the controls:"?

Public Function GetScopeTotal() As Currency

GetScopeTotal = m_Total![Scope]

End Function

Your control is bound to above function ("=GetScopeTotal()"). You can
now change this function, without changing or touching the control source.
So you can easily change the calculation to what ever you want.


mfG
--> stefan <--