What do I do if my subform has no data?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stapes - 22 Jan 2008 14:08 GMT

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

Reply to this Message

Allen Browne - 22 Jan 2008 14:16 GMT

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

Signature

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
[quoted text clipped - 5 lines]
>
> Stapes

Reply to this Message

Stapes - 22 Jan 2008 14:45 GMT

> See:
> Avoid #Error in form/report with no records
[quoted text clipped - 17 lines]
>
> - Show quoted text -

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

Reply to this Message

Allen Browne - 22 Jan 2008 14:52 GMT

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

Signature

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.

On 22 Jan, 14:16, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:

> See:
> Avoid #Error in form/report with no records
[quoted text clipped - 21 lines]
>
> - Show quoted text -

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

Reply to this Message

Stapes - 22 Jan 2008 15:32 GMT

> This should work in Access 2003, provided it is a bound form.
> =[Form].[RecordsetClone].[RecordCount]
[quoted text clipped - 39 lines]
>
> - 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

Reply to this Message

Brendan Reynolds - 22 Jan 2008 14:18 GMT

> Hi
>
[quoted text clipped - 7 lines]
>
> 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))

Signature

Brendan Reynolds

Reply to this Message