statler01
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
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.
np..
:)
<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.
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.
private sub form_open(cancel as integer)
if me.recordsetclone.recordco
msgbox "No records to display, form opening will be cancelled"
cancel=true
end if
end sub