Determining if Form is empty

[FuzMic]

Hi there

To find out if a form is empty i resolve the recordsource SQL Query Statement as an additional recordset and if the RecSet.recordcount is zero, it tells me the form is empty.

I can't use Form.HasData property as it applies to Report, it seems.

Any alternative without using a DAO.RecSet?

 

[SteveSchapel]

Fuz,

If DCount("*","QueryFormIsBasedOn") = 0 Then ...

 

[FuzMic]

Period: Determining if Form is empty

Thanks Kiwi Steve, will tinker with the DCount(). Cheers!

 

[gemma-the-husky]

as Steve says

before you open the form you can say

if dcount("*","somequery") = 0 then
   msgbox("No Data")
else
  docmd.openform "yourform"
end if

---------------
or WITHIN the form open event you can say

if dcount("*",me.recordsource) =0
'note that me.recordsource is generic - you dont need to specify the actual query
then
  msgbox("Nothing to display")
  cancel = true
  exit sub
end if

NOW, doing it this way stops the form opening, but also causes an error 2501 on the docmd.openform line, which you THEN need to handle

 

[FuzMic]

Thanks Dave for the further elaboration. I have since a week ago moved away from recordset and use More of the Aggregate Functions such as DMax, etc.

 

[MarkK]

If the form has a recordsource then it has a recordset delivering the data. Check out the Form.Recordset property.

Private Sub Command0_Click()
  MsgBox Me.Recordset.RecordCount
End Sub

 

[FuzMic]

Thanks LBolt
Will certainly put your tip to the test and store it into my tool box. Never thought abt it this way, great!!!

 

[SteveSchapel]

You can only access the recordset if the form is open, so if you are trying to assess whether to open the form depending on whether there are records ...

 

[vbaInet]

Just adding to this. Might be best to always fully populate a recordset object before counting:

Dim rst as recordset
 
 set rst = me.recordsetclone
 if rst.recordcount <> 0 then
      rst.movelast
      rst.movefirst
 end if
 msgbox rst.recordcount

 

[FuzMic]

Thanks to all of you for the sharing. Cheers!!!