Custom message box for an empty search

[JSDoyle]

I have an application that searches a database based on parameters input by th e user. They click a button to open a form that is tied to a query. It displays the results of the search.

Sometimes when a search is made there are no matches and I'd like to have Access pop up a custom message saying "Sorry your search parameters failed to match any records in the database. Please review your data and try again."

I'd like this to be presented rather than just an empty form.

Does anyone know of an If-then statement that would run prior to opening the form that would look at the query (Q1), and if records = null , open the message box, else open the form?

Thanks,

Scott

 

[spacepro]

Put the following code on the onopen event of your form:

'Set reference to DAO 3.5 or 3.6
Dim mydb As dao.Database
Dim rst As dao.Recordset
Dim numofrec As Long

Set mydb = CurrentDb()
Set rst = Me.Recordset

numofrec = rst.RecordCount

If numofrec = 0 Then
MsgBox "Try a new search", vbCritical + vbOKOnly, "No Records"
DoCmd.Close
End If

Set mydb = Nothing
Set rst = Nothing

There might be more streamlined code out there, but I tend to use the above code to achieve this.

Regards
Andy

 

[yhgtbfk]

I have an application that searches a database based on parameters input by th e user. They click a button to open a form that is tied to a query. It displays the results of the search.

Sometimes when a search is made there are no matches and I'd like to have Access pop up a custom message saying "Sorry your search parameters failed to match any records in the database. Please review your data and try again."

I'd like this to be presented rather than just an empty form.

Does anyone know of an If-then statement that would run prior to opening the form that would look at the query (Q1), and if records = null , open the message box, else open the form?

Thanks,

Scott

What about the Dlookup command.

variable = dlookup("field", "table", "criteria")
If isnull(variable) = true then

msgbox "Your message"

Else

Open report

End IF

 

[JSDoyle]

Thanks!

Like anything else I've gotten busy on a different project and I'll have to get back to this one in the next day or so.

I'll use your suggestions to try to get this working.

Thanks!

 

[ghudson]

This will do what you want in the forms OnOpen event...

Private Sub Form_Open(Cancel As Integer)
        
    If Me.RecordsetClone.RecordCount = 0 Then
        MsgBox "Sorry your search parameters failed to match any records in the database. Please review your data and try again.", vbInformation, "No Records Found"
        DoCmd.Close acForm, Me.name
    End If
    
End Sub

HTH

 

[Amaze14]

Related Question

Hello,

I have a similar question; in my case I have a form in which the user selects a region from a list box and then clicks a button that runs a query a produces a report for the selected region.When the user neglects to select a region the result is an empty report, but instead of having the empty report appear I would like to see a msgbox pop up that says "Please select a region".

Currently, the event procedure for my command button has a couple of DoCmd's that open the query and that open the report. I have tried to incorporate some of the above suggestions but haven't had any luck. I think there's a problem in that my record source for the form is not the same as that for the query/report, and I don't know how to specify that the recordset is not Me.Recordset. I do not have good knowledge of VB.

Thanks!

 

[DoubleDownAgain]

Why not check to make sure that the listbox's value <> "" before you run the code?

    If lbxRegion.Value <> "" then
         'Place your code here
    Else
        strMesg = "Please select a region and try again" & Chr(13) & "Click 'OK' to contine"
        strTitle = "Please Select a Region"
        CantCont = MsgBox(strMesg, vbOKOnly + vbCritical, Title:=strTitle)
    End If

 

[ghudson]

   If Not IsNull(lbxRegion) or lbxRegion <> "" Then

 

[Amaze14]

Thank you both .... I'm getting closer! I have found that when I use this:

If Not IsNull(lbxRegion) or lbxRegion <> "" Then

no messagebox pops up, but my code runs. When I use this:

If lbxRegion <> "" Then

the messagebox pops up even after I selected a region from my listbox. Lastly, this:

If lbxRegion.Value <> "" Then

returns a message in my form that says 'object required'.

Do I need to adjust the properties of my listbox in someway? or, Is it somewhere else along the way that I've likely have made an error? Thanks!
Attached is the code I'm currently using.