Subform Calculated Sum with Error on Main form

[Dkline]

I have a main form with several subforms. Each subform has a total for all
of the current transactions. The main form displays these totals.

As I walk through the records, there are some records which don't have any
transactions. On one of the subforms for a particular customer, the
=sum([TransactionAmount]) is blank as he did not have that particular type
of transaction. On the main form it shows #Error. How can I get around the
"#Error" being displayed?

Also, as this application is not to be used to edit/enter data, what is the
best design for me to only show the information? Do I simply change the
RecordSetType to Snapshot?

 

[Allen Browne]

Where a form has no records, and none can be added (so there is not even a
new record row), attempting to refer to the non-existent text boxes
generates an error. Use IIf() to test IsError() or the RecordCount.

This kind of thing:
=IIf([Form].[RecordsetClone],[RecordCount]=0, Null, Sum([TransactionAmount])

To prevent the data being edited, just set these properties of the form to
No:
AllowEdits
AllowAdditions
AllowDeletions
Alternatively, set the Locked property of the controls to Yes.

 

[Dkline]

In my total on the subform I have this formula:
=IIf(Form.RecordsetClone.RecordCount=0,Null,Sum([Premium]))

I get a blank in that field and the main form field shows "#Error".

I've also tried having the value be zero instead of Null.

Any way to debug this? Something like Excel's "Evaluate"?

Where a form has no records, and none can be added (so there is not even a
new record row), attempting to refer to the non-existent text boxes
generates an error. Use IIf() to test IsError() or the RecordCount.

This kind of thing:
=IIf([Form].[RecordsetClone],[RecordCount]=0, Null, Sum([TransactionAmount])

To prevent the data being edited, just set these properties of the form to
No:
AllowEdits
AllowAdditions
AllowDeletions
Alternatively, set the Locked property of the controls to Yes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a main form with several subforms. Each subform has a total for all
of the current transactions. The main form displays these totals.

As I walk through the records, there are some records which don't have any
transactions. On one of the subforms for a particular customer, the
=sum([TransactionAmount]) is blank as he did not have that particular type
of transaction. On the main form it shows #Error. How can I get around the
"#Error" being displayed?

Also, as this application is not to be used to edit/enter data, what is the
best design for me to only show the information? Do I simply change the
RecordSetType to Snapshot?

 

[Allen Browne]

The Immediate Window is the best way to debug. Press Ctrl+G.

The "Form" bit is a reference to the current form. In the Immediate window,
you will need to use "Forms!Formname" instead. For a main form, that's:
? Forms![FormName].RecordsetClone.RecordCount
For a subform:
? Forms![FormName]![SubformControlName].Form.RecordsetClone.RecordCount

A zero in place of Null is fine.

Make sure this text box that displays the results does not have a Name the
same as one of your fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In my total on the subform I have this formula:
=IIf(Form.RecordsetClone.RecordCount=0,Null,Sum([Premium]))

I get a blank in that field and the main form field shows "#Error".

I've also tried having the value be zero instead of Null.

Any way to debug this? Something like Excel's "Evaluate"?

Where a form has no records, and none can be added (so there is not even a
new record row), attempting to refer to the non-existent text boxes
generates an error. Use IIf() to test IsError() or the RecordCount.

This kind of thing:
=IIf([Form].[RecordsetClone],[RecordCount]=0, Null, Sum([TransactionAmount])

To prevent the data being edited, just set these properties of the form to
No:
AllowEdits
AllowAdditions
AllowDeletions
Alternatively, set the Locked property of the controls to Yes.

I have a main form with several subforms. Each subform has a total for all
of the current transactions. The main form displays these totals.

As I walk through the records, there are some records which don't have any
transactions. On one of the subforms for a particular customer, the
=sum([TransactionAmount]) is blank as he did not have that particular type
of transaction. On the main form it shows #Error. How can I get around the
"#Error" being displayed?

Also, as this application is not to be used to edit/enter data, what

is
the

best design for me to only show the information? Do I simply change the
RecordSetType to Snapshot?

 

[Dkline]

Progress!

=IIf(Forms![Policy Inventory2]![frm2004
Premiums].Form.RecordsetClone.RecordCount=0,0,1)

Combo45 is the listbox from which I am selecting the active case. So I did
an AfterUpdate which displays a MsgBox with the result.

Private Sub Combo45_AfterUpdate()
Dim varSum As Long
varSum = Forms![Policy Inventory2]![frm2004
Premiums].Form.RecordsetClone.RecordCount
MsgBox varSum, , "Scheduled Premium"
varSum = Forms![Policy
Inventory2]![frmWDByPolicy].Form.RecordsetClone.RecordCount
MsgBox varSum, , "Withdrawal"
End Sub

The MsgBox shows either a 0 or the correct number of records.

BUT...

On the subform in the footer I have my total field.

=IIf(Forms![Policy Inventory2]![frm2004
Premiums].Form.RecordsetClone.RecordCount=0,0,1)

If there are 1 or more records, I get the 1 in the field and the Main Form
also shows the 1.

If there are 0 records, I don't get a 0 in the field, I get a blank. The
Main Form shows "#Error"

So I'm still missing something.

 

[Allen Browne]

The RecordCount reflects the number of records that have been loaded at the
time you ask for it. Once they are all loaded, you get the right count, but
initially you typically get 1 (because 1 has loaded) or zero (if there are
none).

Forcing the RecordsetClone to MoveLast, followed by a Recalc of the form
should give the correct count.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Progress!

=IIf(Forms![Policy Inventory2]![frm2004
Premiums].Form.RecordsetClone.RecordCount=0,0,1)

Combo45 is the listbox from which I am selecting the active case. So I did
an AfterUpdate which displays a MsgBox with the result.

Private Sub Combo45_AfterUpdate()
Dim varSum As Long
varSum = Forms![Policy Inventory2]![frm2004
Premiums].Form.RecordsetClone.RecordCount
MsgBox varSum, , "Scheduled Premium"
varSum = Forms![Policy
Inventory2]![frmWDByPolicy].Form.RecordsetClone.RecordCount
MsgBox varSum, , "Withdrawal"
End Sub

The MsgBox shows either a 0 or the correct number of records.

BUT...

On the subform in the footer I have my total field.

=IIf(Forms![Policy Inventory2]![frm2004
Premiums].Form.RecordsetClone.RecordCount=0,0,1)

If there are 1 or more records, I get the 1 in the field and the Main Form
also shows the 1.

If there are 0 records, I don't get a 0 in the field, I get a blank. The
Main Form shows "#Error"

So I'm still missing something.

The Immediate Window is the best way to debug. Press Ctrl+G.

The "Form" bit is a reference to the current form. In the Immediate window,
you will need to use "Forms!Formname" instead. For a main form, that's:
? Forms![FormName].RecordsetClone.RecordCount
For a subform:
? Forms![FormName]![SubformControlName].Form.RecordsetClone.RecordCount

A zero in place of Null is fine.

Make sure this text box that displays the results does not have a Name the
same as one of your fields.

 

[Don Kline]

I'll try this when I get into my office today. At the moment it's about 3:30
AM and - yes - I am losing sleep over this.

I'm still trying to figure out why when the RecordCount is zero, why the
subform field is blank and why the mainform shows the error. The MsgBox
tells me the value is zero if no records but the IIf on the subform results
in a blank.

The RecordCount reflects the number of records that have been loaded at the
time you ask for it. Once they are all loaded, you get the right count, but
initially you typically get 1 (because 1 has loaded) or zero (if there are
none).

Forcing the RecordsetClone to MoveLast, followed by a Recalc of the form
should give the correct count.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Progress!

=IIf(Forms![Policy Inventory2]![frm2004
Premiums].Form.RecordsetClone.RecordCount=0,0,1)

Combo45 is the listbox from which I am selecting the active case. So I did
an AfterUpdate which displays a MsgBox with the result.

Private Sub Combo45_AfterUpdate()
Dim varSum As Long
varSum = Forms![Policy Inventory2]![frm2004
Premiums].Form.RecordsetClone.RecordCount
MsgBox varSum, , "Scheduled Premium"
varSum = Forms![Policy
Inventory2]![frmWDByPolicy].Form.RecordsetClone.RecordCount
MsgBox varSum, , "Withdrawal"
End Sub

The MsgBox shows either a 0 or the correct number of records.

BUT...

On the subform in the footer I have my total field.

=IIf(Forms![Policy Inventory2]![frm2004
Premiums].Form.RecordsetClone.RecordCount=0,0,1)

If there are 1 or more records, I get the 1 in the field and the Main Form
also shows the 1.

If there are 0 records, I don't get a 0 in the field, I get a blank. The
Main Form shows "#Error"

So I'm still missing something.

The Immediate Window is the best way to debug. Press Ctrl+G.

The "Form" bit is a reference to the current form. In the Immediate window,
you will need to use "Forms!Formname" instead. For a main form, that's:
? Forms![FormName].RecordsetClone.RecordCount
For a subform:
? Forms![FormName]![SubformControlName].Form.RecordsetClone.RecordCount

A zero in place of Null is fine.

Make sure this text box that displays the results does not have a Name the
same as one of your fields.