I am having problems with the search code that I am using in my database.
First I have code that outputs a msgbox when no search criteria has been entered into all 3 search criteria boxes on the userform - the msgbox tells the user they need to enter something in at least one box.
The code works however when it finishes running it is opening the underlying query/datasheet and displaying it - I do not want this to happen and cannot figure out how to stop it - all I want is for the msgbox to popup and then when ok button clicked it returns to the search form.
Code:
Private Sub Command7_Click()
DoCmd.OpenQuery "search"
If Len(Volume) < 1 And Len(movie) < 1 And Len(Rating) < 1 Then
MsgBox ("Please enter search criteria before attempting to search the database")
End If
DoCmd.OpenForm "search"
End Sub
Second - I also have another set of code that I want to run (in conjunction with the above code) that also outputs a msgbox telling the user that there are no search results that match the criteria they have entered. The code must validate based upon entries in any combinations of the search boxes (any 1, 2 or 3).
Code:
Private Sub Command7_Click()
If Nz(Me.Title, "") > "" Then
If DCount("*", "search", "[Movie Title] = '" & Me.Title & "'") Then
MsgBox ("No movies matching your search. Please refine your search and try again.")
Else
DoCmd.OpenQuery ("search")
If Len(Volume) < 1 And Len(movie) < 1 And Len(Rating) < 1 Then
MsgBox ("Please enter search criteria before attempting to search the database")
DoCmd.OpenForm "search"
Else: DoCmd.OpenForm "search results"
End If
End If
End If
End Sub
When I try to search with this code active I get the following issues:
When searching by volume or by rating the button is doing nothing at all.
When searching by title it shows results however it is still also opening the search query datasheet as well.
When I deliberately search for something that I know is not in the current list all it does is display the "pls input search criteria" msgbox and also the query window behind it.