What do I do if my subform has no data?

[Stapes]

Hi

I have a form Contact Statistics, with a subform
FMS_ContactTotalSpend. The subform has a Form Footer with a field
TXT_Total, Control Source: =IIf(IsNull(Sum([SumOfTotalSpend])),
0,Sum([SumOfTotalSpend])). This works fine unless there is no data in
the subform. There does not appear to be an On NoData control
available, so what do I do? How can I tell if there is no data? At the
moment I just get crappy #name or #error.

Stapes

 

[Allen Browne]

See:
Avoid #Error in form/report with no records
at:
http://allenbrowne.com/RecordCountError.html

 

[Brendan Reynolds]

Hi

I have a form Contact Statistics, with a subform
FMS_ContactTotalSpend. The subform has a Form Footer with a field
TXT_Total, Control Source: =IIf(IsNull(Sum([SumOfTotalSpend])),
0,Sum([SumOfTotalSpend])). This works fine unless there is no data in
the subform. There does not appear to be an On NoData control
available, so what do I do? How can I tell if there is no data? At the
moment I just get crappy #name or #error.

Stapes

I haven't tested this, but just from looking at your expression, I think you
may simply need to move the test for null further down the chain, so to
speak. Test the value of the field for null rather than the result of the
Sum() function.Try this ...

=IIf(IsNull(Sum([SumOfTotalSpend])), 0,Sum([SumOfTotalSpend]))

=Sum(IIf(IsNull([SumOfTotalSpend]),0,[SumOfTotalSpend]))

.... or ...

=Sum(NZ([SumOfTotalSpend],0))

 

[Stapes]

See:
    Avoid #Error in form/report with no records
at:
   http://allenbrowne.com/RecordCountError.html

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I have a form Contact Statistics, with a subform
FMS_ContactTotalSpend. The subform has a Form Footer with a field
TXT_Total, Control Source: =IIf(IsNull(Sum([SumOfTotalSpend])),
0,Sum([SumOfTotalSpend])). This works fine unless there is no data in
the subform. There does not appear to be an On NoData control
available, so what do I do? How can I tell if there is no data? At the
moment I just get crappy #name or #error.

Stapes- Hide quoted text -

- Show quoted text -

None of those options work. ([Form].[Recordset].[RecordCount] gives
#name also. I am using Access 2003.

 

[Allen Browne]

This should work in Access 2003, provided it is a bound form.
=[Form].[RecordsetClone].[RecordCount]

If it's a subform that contains the data, you need to refer to the subform:
http://allenbrowne.com/casu-04.html

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

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

See:
Avoid #Error in form/report with no records
at:
http://allenbrowne.com/RecordCountError.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I have a form Contact Statistics, with a subform
FMS_ContactTotalSpend. The subform has a Form Footer with a field
TXT_Total, Control Source: =IIf(IsNull(Sum([SumOfTotalSpend])),
0,Sum([SumOfTotalSpend])). This works fine unless there is no data in
the subform. There does not appear to be an On NoData control
available, so what do I do? How can I tell if there is no data? At the
moment I just get crappy #name or #error.

Stapes- Hide quoted text -

- Show quoted text -

None of those options work. ([Form].[Recordset].[RecordCount] gives
#name also. I am using Access 2003.

 

[Stapes]

This should work in Access 2003, provided it is a bound form.
    =[Form].[RecordsetClone].[RecordCount]

If it's a subform that contains the data, you need to refer to the subform:
   http://allenbrowne.com/casu-04.html

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

See:
Avoid #Error in form/report with no records
at:
http://allenbrowne.com/RecordCountError.html

news:3ea6fa7e-57b1-4325-907a-2d784c78a639@s13g2000prd.googlegroups.com...

I have a form Contact Statistics, with a subform
FMS_ContactTotalSpend. The subform has a Form Footer with a field
TXT_Total, Control Source: =IIf(IsNull(Sum([SumOfTotalSpend])),
0,Sum([SumOfTotalSpend])). This works fine unless there is no data in
the subform. There does not appear to be an On NoData control
available, so what do I do? How can I tell if there is no data? At the
moment I just get crappy #name or #error.
Stapes- Hide quoted text -

- Show quoted text -

None of those options work. ([Form].[Recordset].[RecordCount] gives
#name also. I am using Access 2003.- Hide quoted text -

- Show quoted text -

I think the problem is, if there are no records, the field name
itself, SumOfTotalSpend, isn't recognised - hence the #Name? error, in
the following sentence:

=IIf([NUM_Recs]=0,0,Sum(NZ([SumOfTotalSpend],0)))

Where NUM_Recs is the record count.

I could not get that to work properly either, so I have done this:

Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim NUM_Total As Integer
Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.MoveLast
NUM_Total = rst.RecordCount
Me.NUM_Recs = NUM_Total
exit_Form_Load:
Exit Sub
Err_Form_Load:
If Err.Number = 3021 Then
Me.NUM_Recs = 0
Me.TXT_Total.ControlSource = "0"
Else
MsgBox Err.Number & " " & Err.Description
End If
End Sub