Error message when no data found in query (1 Viewer)

Hi All,

Bear with me as I'm am relatively new to access.

I have a search query that searches for different results based on 3 criterior. I have set up a form so that the user can input the text into the form and then once the Submit button (that i created) is clicked the query table will be shown.

My question is:

Is it possible to have an error message box appear when i click submit and no data is returned by the search query?

I'm hoping for the message box to say "No corresponding records to your search criteria. Do you want to try again?"

Then the options given in the message box are Yes (where they should be taken back to the search form) and No (Where they are taken to another form).

If anyone has any idea on how to create this you would be a life saver.

Thanks in advance.

J.

That code is exactly what I'm looking for! Thank you.

But, there is one small problem. When i search for a record that is 100% in the database, the search button now doesn't search.

Any help on that?

Thanks,

J.

Show all code behind the search button

Not quite sure what you mean. Are you askign me to show you the code i have for the button?

If so here it is:

Private Sub Search_Click()
Dim Msg As VbMsgBoxResult
If DCount("*", "Search Form Query") > 0 Then
DoCmd.OpenQuery ("Search Form Query")

DoCmd.Close acForm, Me.Name
End If
If DCount("*", "Search Form Query") = 0 Then
Msg = MsgBox("No corresponding records to your search criteria." & vbCrLf & vbCrLf & _
"Do you want to try again?", vbCritical + vbYesNo)
If Msg = vbYes Then

Else
DoCmd.Close
End If
End If

End Sub


I have added to it. Now when you search for a record it does come up. BUT, I want the form to close if the search has been successful. For example, if the search result comes back with at least one record i want the search form to close, but if the search brings back no records i want the user te be shown the message box which gives them the option to re try the search or exit the form. (The latter currently works fine).

ALSO...

An error message pops up when a search has been complated that finds at least 1 record:

"Run-time error '2450': Microsft Access cannot find the referenced form 'Search For a Complaint'."

I think this may be down to the fact that if a search brings back records, the form closes and the code can't finish it's job?

Not sure.

Thanks,

J.

If still don't work then the problem is in query. The query not find that record.
Keep the form open and manually run the query (double click). Have you any record returned ?

As an advice, rename the query as you see: SearchFormQuery.
DO NOT use spaces in the names (for fields, forms, modules, controls etc etc etc)

Works an absolute treat!

Thanks for your help.

As for the spaces in the forms/queries etc.. I will change them right away, thanks for the heads up.

Best,

J.