Calculating totals on a main form from values contained in subforms

[John Baker]

Can anyone please help.

I am trying to calculate a total in a textbox on a main form where the
values I want are contained in a textbox in the form footer on each of three
subforms. Each subform has a total box in its form footer calculating the
sum of values in the subform.

I am able to make this work where there are records contained in the
subform, but as soon as any one of the subforms does not contain records
there is an error contained in the textbox on the main form. I have tried to
use the iserror function to try and get around the problem but it does not
seem to work.

I am using Access97 and I think this is part of the problem. I think I can
remember this problem from years ago but doesn't seem to occur in later
versions of Access. My client still uses Access97 and so I need a solution
in 97.

Any help greatly appreciated.

John Baker

 

[Al Borges]

Hi John:

You should use either the Nz() function or the If IsNull() then set the
amount to zero if null. That might be enough... That should fix your
problem, s.a. with this sample (code should be placed in the control source
property of a sum field of each recordset):

Nz(DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]"),0)

Then you could reference them:

zz = forms![parentform]![childform1].form![sum1] +
forms![parentform]![childform2].form![sum2] etc etc etc

Another method could be the recordsetclone... check each form for the
presence of records through a recordsetclone procedure (similar to the one
that I posted for conditional tabs just yesterday):

Dim xx as long, yy as long, zz as long
Dim rst as recordset
Set rst = Forms![YourFocusForm]![Subform1].form.RecordsetClone
rst.movefirst
rst.movelast
If rst.recordcount > 0 Then
xx = DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]")
else
xx = 0
End If

Set rst = Forms![YourFocusForm]![Subform2].form.RecordsetClone
rst.movefirst
rst.movelast
If rst.recordcount > 0 Then
yy = DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]")
else
yy = 0
End If

etc etc etc

zz = xx + yy

Let me know how it works for you.

Regards,
AL

 

[John Baker]

Thanks very much for your help Al.

I tried your first method without success. I was more or less already doing
what you had suggested.

I have had partial success with your recordset method. It works if I put
the code into a command button but ideally I would like to use the code with
the oncurrent event. However when I put the code into the oncurrent event
the code does not work. Not sure why. If I step over each line of code in
debug mode it seems to work but otherwise when I go from record to record
the code doesn't seem to work. It's like it does not refresh itself.

Any ideas?
John.

Hi John:

You should use either the Nz() function or the If IsNull() then set the
amount to zero if null. That might be enough... That should fix your
problem, s.a. with this sample (code should be placed in the control source
property of a sum field of each recordset):

Nz(DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]"),0)

Then you could reference them:

zz = forms![parentform]![childform1].form![sum1] +
forms![parentform]![childform2].form![sum2] etc etc etc

Another method could be the recordsetclone... check each form for the
presence of records through a recordsetclone procedure (similar to the one
that I posted for conditional tabs just yesterday):

Dim xx as long, yy as long, zz as long
Dim rst as recordset
Set rst = Forms![YourFocusForm]![Subform1].form.RecordsetClone
rst.movefirst
rst.movelast
If rst.recordcount > 0 Then
xx = DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]")
else
xx = 0
End If

Set rst = Forms![YourFocusForm]![Subform2].form.RecordsetClone
rst.movefirst
rst.movelast
If rst.recordcount > 0 Then
yy = DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]")
else
yy = 0
End If

etc etc etc

zz = xx + yy

Let me know how it works for you.

Regards,
AL

Can anyone please help.

I am trying to calculate a total in a textbox on a main form where the
values I want are contained in a textbox in the form footer on each of three
subforms. Each subform has a total box in its form footer calculating the
sum of values in the subform.

I am able to make this work where there are records contained in the
subform, but as soon as any one of the subforms does not contain records
there is an error contained in the textbox on the main form. I have

tried

to
use the iserror function to try and get around the problem but it does not
seem to work.

I am using Access97 and I think this is part of the problem. I think I can
remember this problem from years ago but doesn't seem to occur in later
versions of Access. My client still uses Access97 and so I need a solution
in 97.

Any help greatly appreciated.

John Baker

 

[Al Borges]

Hi John:

You can try calling up the clickbutton OnClick event...

Regards,
Al

Thanks very much for your help Al.

I tried your first method without success. I was more or less already doing
what you had suggested.

I have had partial success with your recordset method. It works if I put
the code into a command button but ideally I would like to use the code with
the oncurrent event. However when I put the code into the oncurrent event
the code does not work. Not sure why. If I step over each line of code in
debug mode it seems to work but otherwise when I go from record to record
the code doesn't seem to work. It's like it does not refresh itself.

Any ideas?
John.

Hi John:

You should use either the Nz() function or the If IsNull() then set the
amount to zero if null. That might be enough... That should fix your
problem, s.a. with this sample (code should be placed in the control source
property of a sum field of each recordset):

Nz(DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]"),0)

Then you could reference them:

zz = forms![parentform]![childform1].form![sum1] +
forms![parentform]![childform2].form![sum2] etc etc etc

Another method could be the recordsetclone... check each form for the
presence of records through a recordsetclone procedure (similar to the one
that I posted for conditional tabs just yesterday):

Dim xx as long, yy as long, zz as long
Dim rst as recordset
Set rst = Forms![YourFocusForm]![Subform1].form.RecordsetClone
rst.movefirst
rst.movelast
If rst.recordcount > 0 Then
xx = DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]")
else
xx = 0
End If

Set rst = Forms![YourFocusForm]![Subform2].form.RecordsetClone
rst.movefirst
rst.movelast
If rst.recordcount > 0 Then
yy = DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]")
else
yy = 0
End If

etc etc etc

zz = xx + yy

Let me know how it works for you.

Regards,
AL

Can anyone please help.

I am trying to calculate a total in a textbox on a main form where the
values I want are contained in a textbox in the form footer on each of three
subforms. Each subform has a total box in its form footer calculating the
sum of values in the subform.

I am able to make this work where there are records contained in the
subform, but as soon as any one of the subforms does not contain records
there is an error contained in the textbox on the main form. I have

tried

to
use the iserror function to try and get around the problem but it does not
seem to work.

I am using Access97 and I think this is part of the problem. I think I can
remember this problem from years ago but doesn't seem to occur in later
versions of Access. My client still uses Access97 and so I need a solution
in 97.

Any help greatly appreciated.

John Baker

 

[John Baker]

Al, I already tried that, but it made no difference.
I also converted the database to an XP version and that made no difference
either.
There seems to be a problem with the code working in the oncurrent event.
I have listed the code below: (NB: tbxOrdersTotal , tbxReceiptsTotal and
tbxAdjustmentTotal are not really required. I used them to see what was
happening.)

Public Sub GetSubformTotals()
On Error GoTo Err_ErrorCheck

Dim dbMyDB As DAO.Database, rst As DAO.Recordset
Set dbMyDB = CurrentDb

Dim varOrdersTotal As Long
Dim varReceiptsTotal As Long
Dim varAdjustmentTotal As Long

Set rst = Forms![frmCustomer]![frmCustomerOrderSubform].Form.RecordsetClone
rst.MoveFirst
rst.MoveLast
If Nz(rst.RecordCount, 0) > 0 Then
varOrdersTotal =
Forms![frmCustomer]![frmCustomerOrderSubform].Form.tbxSumOrderTotal
Else
varOrdersTotal = 0
End If
Me.tbxOrdersTotal = varOrdersTotal

Set rst =
Forms![frmCustomer]![frmCustomerReceiptSubform].Form.RecordsetClone
rst.MoveFirst
rst.MoveLast
If Nz(rst.RecordCount, 0) > 0 Then
varReceiptsTotal =
Forms![frmCustomer]![frmCustomerReceiptSubform].Form.tbxSumReceiptsAmount
Else
varReceiptsTotal = 0
End If
Me.tbxReceiptsTotal = varReceiptsTotal

Set rst =
Forms![frmCustomer]![frmCustomerAdjustmentSubform].Form.RecordsetClone
rst.MoveFirst
rst.MoveLast
If Nz(rst.RecordCount, 0) > 0 Then
varAdjustmentTotal =
Forms![frmCustomer]![frmCustomerAdjustmentSubform].Form.tbxSumAdjustmentTota
l
Else
varAdjustmentTotal = 0
End If
Me.tbxAdjustmentTotal = varAdjustmentTotal

rst.Close
dbMyDB.Close

Me.tbxBalanceOwing = varOrdersTotal - varReceiptsTotal + varAdjustmentTotal
Me.tbxBalanceOwing.Visible = True
Me.tbxName.SetFocus
Me.CmdGetCustomerBalance.Visible = False

Exit_ErrorCheck:
Exit Sub

Err_ErrorCheck:
' MsgBox Err.Description
Resume Next
Resume Exit_ErrorCheck


End Sub


John.

Hi John:

You can try calling up the clickbutton OnClick event...

Regards,
Al

Thanks very much for your help Al.

I tried your first method without success. I was more or less already doing
what you had suggested.

I have had partial success with your recordset method. It works if I put
the code into a command button but ideally I would like to use the code with
the oncurrent event. However when I put the code into the oncurrent event
the code does not work. Not sure why. If I step over each line of code in
debug mode it seems to work but otherwise when I go from record to record
the code doesn't seem to work. It's like it does not refresh itself.

Any ideas?
John.

Hi John:

You should use either the Nz() function or the If IsNull() then set the
amount to zero if null. That might be enough... That should fix your
problem, s.a. with this sample (code should be placed in the control source
property of a sum field of each recordset):

Nz(DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]"),0)

Then you could reference them:

zz = forms![parentform]![childform1].form![sum1] +
forms![parentform]![childform2].form![sum2] etc etc etc

Another method could be the recordsetclone... check each form for the
presence of records through a recordsetclone procedure (similar to the one
that I posted for conditional tabs just yesterday):

Dim xx as long, yy as long, zz as long
Dim rst as recordset
Set rst = Forms![YourFocusForm]![Subform1].form.RecordsetClone
rst.movefirst
rst.movelast
If rst.recordcount > 0 Then
xx = DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]")
else
xx = 0
End If

Set rst = Forms![YourFocusForm]![Subform2].form.RecordsetClone
rst.movefirst
rst.movelast
If rst.recordcount > 0 Then
yy = DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]")
else
yy = 0
End If

etc etc etc

zz = xx + yy

Let me know how it works for you.

Regards,
AL


Can anyone please help.

I am trying to calculate a total in a textbox on a main form where the
values I want are contained in a textbox in the form footer on each of
three
subforms. Each subform has a total box in its form footer

calculating
the

sum of values in the subform.

I am able to make this work where there are records contained in the
subform, but as soon as any one of the subforms does not contain records
there is an error contained in the textbox on the main form. I have tried
to
use the iserror function to try and get around the problem but it

does
not

seem to work.

I am using Access97 and I think this is part of the problem. I think

I
can

remember this problem from years ago but doesn't seem to occur in later
versions of Access. My client still uses Access97 and so I need a solution
in 97.

Any help greatly appreciated.

John Baker

 

[Al Borges]

Hi John:

I've set up a test Parent/2 Child forms and used the following code in the
OnCurrent event, and it worked:

Private Sub Form_Current()
Dim xx As Long, yy As Long, zz As Long
Dim rst As Recordset
Set rst = Forms![zzzParent]![zzzzChild1].Form.RecordsetClone
rst.MoveFirst
rst.MoveLast
If rst.RecordCount > 0 Then
xx = DSum("balance", "***accounts", "[ACCT] = Forms![zzzParent]![ACCT]")
Else
xx = 0
End If
MsgBox xx ' total for subform 1

Set rst = Forms![zzzParent]![zzzzChild2].Form.RecordsetClone
rst.MoveFirst
rst.MoveLast
If rst.RecordCount > 0 Then
yy = DSum("balance", "accounts", "[ACCT] = Forms![zzzParent]![ACCT]")
Else
yy = 0
End If
MsgBox yy ' total for subform 2

zz = xx + yy
MsgBox zz 'msgbox zz will show total for both subforms
End Sub

Now, looking at your code, you made a slight (but critical) mistake:

change all 3 of your Me.etc statements to Me!etc, like-

Me.tbxReceiptsTotal = varReceiptsTotal

to

Me!tbxReceiptsTotal = varReceiptsTotal

See if that does it...

Regards,
Al

Al, I already tried that, but it made no difference.
I also converted the database to an XP version and that made no difference
either.
There seems to be a problem with the code working in the oncurrent event.
I have listed the code below: (NB: tbxOrdersTotal , tbxReceiptsTotal and
tbxAdjustmentTotal are not really required. I used them to see what was
happening.)

Public Sub GetSubformTotals()
On Error GoTo Err_ErrorCheck

Dim dbMyDB As DAO.Database, rst As DAO.Recordset
Set dbMyDB = CurrentDb

Dim varOrdersTotal As Long
Dim varReceiptsTotal As Long
Dim varAdjustmentTotal As Long

Set rst = Forms![frmCustomer]![frmCustomerOrderSubform].Form.RecordsetClone
rst.MoveFirst
rst.MoveLast
If Nz(rst.RecordCount, 0) > 0 Then
varOrdersTotal =
Forms![frmCustomer]![frmCustomerOrderSubform].Form.tbxSumOrderTotal
Else
varOrdersTotal = 0
End If
Me.tbxOrdersTotal = varOrdersTotal

Set rst =
Forms![frmCustomer]![frmCustomerReceiptSubform].Form.RecordsetClone
rst.MoveFirst
rst.MoveLast
If Nz(rst.RecordCount, 0) > 0 Then
varReceiptsTotal =
Forms![frmCustomer]![frmCustomerReceiptSubform].Form.tbxSumReceiptsAmount
Else
varReceiptsTotal = 0
End If
Me.tbxReceiptsTotal = varReceiptsTotal

Set rst =
Forms![frmCustomer]![frmCustomerAdjustmentSubform].Form.RecordsetClone
rst.MoveFirst
rst.MoveLast
If Nz(rst.RecordCount, 0) > 0 Then
varAdjustmentTotal =
Forms![frmCustomer]![frmCustomerAdjustmentSubform].Form.tbxSumAdjustmentTota
l
Else
varAdjustmentTotal = 0
End If
Me.tbxAdjustmentTotal = varAdjustmentTotal

rst.Close
dbMyDB.Close

Me.tbxBalanceOwing = varOrdersTotal - varReceiptsTotal + varAdjustmentTotal
Me.tbxBalanceOwing.Visible = True
Me.tbxName.SetFocus
Me.CmdGetCustomerBalance.Visible = False

Exit_ErrorCheck:
Exit Sub

Err_ErrorCheck:
' MsgBox Err.Description
Resume Next
Resume Exit_ErrorCheck


End Sub


John.

Hi John:

You can try calling up the clickbutton OnClick event...

Regards,
Al

Thanks very much for your help Al.

I tried your first method without success. I was more or less already doing
what you had suggested.

I have had partial success with your recordset method. It works if I put
the code into a command button but ideally I would like to use the

code
with

the oncurrent event. However when I put the code into the oncurrent event
the code does not work. Not sure why. If I step over each line of code in
debug mode it seems to work but otherwise when I go from record to record
the code doesn't seem to work. It's like it does not refresh itself.

Any ideas?
John.


"Al Borges" <alborgmd at yahoo dot com> wrote in message
Hi John:

You should use either the Nz() function or the If IsNull() then set the
amount to zero if null. That might be enough... That should fix your
problem, s.a. with this sample (code should be placed in the control
source
property of a sum field of each recordset):

Nz(DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]"),0)

Then you could reference them:

zz = forms![parentform]![childform1].form![sum1] +
forms![parentform]![childform2].form![sum2] etc etc etc

Another method could be the recordsetclone... check each form for the
presence of records through a recordsetclone procedure (similar to

the
one

that I posted for conditional tabs just yesterday):

Dim xx as long, yy as long, zz as long
Dim rst as recordset
Set rst = Forms![YourFocusForm]![Subform1].form.RecordsetClone
rst.movefirst
rst.movelast
If rst.recordcount > 0 Then
xx = DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]")
else
xx = 0
End If

Set rst = Forms![YourFocusForm]![Subform2].form.RecordsetClone
rst.movefirst
rst.movelast
If rst.recordcount > 0 Then
yy = DSum("balance", "accounts", "[ACCT] = Forms![*Ledger form]![ACCT]")
else
yy = 0
End If

etc etc etc

zz = xx + yy

Let me know how it works for you.

Regards,
AL


Can anyone please help.

I am trying to calculate a total in a textbox on a main form where the
values I want are contained in a textbox in the form footer on

each

of

think

I