VBA - Empty recordset - close form

statler01
statler01 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
The following code, placed in the Open Event of your form will check for an empty recordset.

If no records are present, the Open Event gets cancelled, and the form does not open:

If Me.Recordset.RecordCount = 0 Then
    MsgBox "no records"
    cancel = true
End If
Top Expert 2016

Commented:
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

Author

Commented:
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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Capricorn, sorry I didn't refresh before accepting... I would have split the points.
Hamed NasrRetired IT Professional

Commented:
Check allow additions: Yes
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
<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.
Top Expert 2016

Commented:
np..
Hamed NasrRetired IT Professional

Commented:
:)
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
<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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial