HasData Property Relating to Subform 1

[kentover]

I have a form that will show information regarding business areas including subtotals for each area. The subtotals are calulated from an invisible subform that is tied to a query. Not all areas will return data and this gives me the #NAME? error. When I reference the subforms field as the calculated fields controlsource it works till there is no data and then gives me the #ERROR result.

I have used the HasData property in reports for the same information and it works well. In the report fields controlsource I placed the following code:

=IIf([rptActHoldSubRpt].Report.HasData=True,[rptActHoldSubRpt].Report!Text6,0)

The report returns a "0" when there is no data. I am trying to use the same basic controlsource for a form changing the code to:

=IIf([frmBusActHoldSubform].Form.HasData=True,[frmBusActHoldSubform].Form.Text6,0)

I have tried several variations on the syntax but still receive the #NAME? error. All documentation found has been very vague (

http://www.microsoft.com,

http://www.msdn.com,

and here) if available. My VB skills are lacking to say the least... Any ideas on why this does not work for the form or how to make it work??? Thank you in advance for the help!

 

[thornmastr]

Has data is a report property and is not available in any way within a forms construct. However, you can find out if you have data by simply defining a recordset as the recordset clone of your subform. Then you get the recordcount of the record set, something like

If rs.recordcount = 0 then
‘ there are no records
else
‘ we have data]
endif

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[kentover]

OK, I think that I am close... I have coded this into the OnCurrent section of the subform hoping to pass a "0" value to the mainform field when there are no records.


Private Sub Form_Current()

Dim rs As Object

Set rs = Me.RecordsetClone

If rs.RecordCount = 0 Then
Text6.Value = 0
Else
Text6.Value = rs.RecordCount
End If

End Sub


I have tried many other ways in Access and in code but have not had much luck. Please help me get another step closer.

Thanks
Kent

 

[thornmastr]

This should get you to where you want to be. Good luck.

Private Sub Form_Current()

Dim rs As DAO.recordset

Set rs = Me.RecordsetClone

if rs.recordcount > 0 then
rs.movelast
endif
me.text6.value = rs.recordcount

rs.close
set rs = nothing

End Sub




Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[kentover]

Thanks for your help. I am much closer to the results that I am looking for.