Link to home
Start Free TrialLog in
Avatar of statler01
statler01Flag for United States of America

asked on

VBA - Empty recordset - close form

I have several forms that are populated by queries. If the query returns no records, the form becomes a solid gray box. None of the controls or anything display.

I tried having a message box appear stating that there are no records to display, and then closing the form.

I tried:
If IsNull(Me.Recordset) Then...

But, Access seems to skip over that, as if the record set is not empty.


Is there a way to have my form controls appear if there are no records to display, or what would be the correct code to use to get the form to close?
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
place this codes in the open event of the form

private sub form_open(cancel as integer)

if me.recordsetclone.recordcount=0 then
  msgbox "No records to display, form opening will be cancelled"
  cancel=true
end if

end sub
Avatar of statler01

ASKER

I also discovered the following works:
Private Sub Form_Open(Cancel As Integer)
If DCount("*", "My_Query") <= 0 Then
 MsgBox "No records found"
 DoCmd.Close acForm, "My_Form"
End If

Thanks for your help!
End Sub
Capricorn, sorry I didn't refresh before accepting... I would have split the points.
Check allow additions: Yes
<Is there a way to have my form controls appear if there are no records to display>

Also, if your recordsource query is updateable and you want to allow the user to enter new records, setting the AllowAdditions property to TRUE will show empty controls instead of a "solid grey box" when no records are present.
<DoCmd.Close acForm, "My_Form">

Just FYI, the cancel parameter in the forms OPEN event is perfect for this purpose (and possibly designed for it).

Setting Cancel = True prevents the form from opening.