Query Has No Records

[kh59743]

How can I set up a form to NOT open if the underlying query has no data? This feature is nicely available in reports, but how can I do it with forms, too?

Thanks in advance!

 

[st3ve]

Using Dcount to not open blank form

Using DCount you can count the number of records in the table, by referencing the same query as you use in the forms recordsource. ie. Put this code behind the button which launches your form at present:

If Dcount("RecordIdNo","qryYourFormsUnderlyingQuery") <>0 then
docmd.openform "YourFormName"
else
msgbox "Form has no data and will not open"
end if.

 

[ghudson]

Another method...Put this in the forms OnOpen event...

Private Sub Form_Open(Cancel As Integer)
    
    If Me.RecordsetClone.RecordCount = 0 Then
        MsgBox "There are zero records in the data source!", vbInformation, "No Records Found"
        DoCmd.Close acForm, Me.name
    End If
    
End Sub

The Search function on this forum really does work and members are encouraged to use it before posting.

 

[kh59743]

Thank you so much. The code you provided worked great.

Unfortunately, I was unable to find any when I ran a search of the forums before posting my thread. So it's a good thing you were able to help me out!

 

[alexlancaster]

Hi!
I´m using a Combobox with an AfterUpdate event to bring up a query with the selected criteria as a filter. I´m getting blank forms when the selected criteria leaves no records. I tried using:

Private Sub Form_Open(Cancel As Integer)

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No Results From Search!", vbInformation, "No Records Found"
DoCmd.OpenForm "CatSearch"
DoCmd.Close acForm, Me.Name

End If

End Sub

For some reason, it´s ignoring the 'OpenForm'. It does close the query and show the message box. I would like it to open the "CatSearch" form after displaying the message box so that the user can immediately select another criteria.
I guess it´s a rather simple mistake I´m making, but I still haven't seen what it is.
Thanks in Advance