Full Version: Msgbox And Search Code Not Working Properly
UtterAccess Forums > Microsoft® Access > Access Forms
vampyr07au
I am having problems with the search code that I am using in my database.
irst 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.
Doug Steele
Welcome to UtterAccess.
If I'm understanding you correctly, the query is appearing because you're always opening the query and always opening the form, regardless of whether or not the 3 criteria have been entered. I'm confused, though, because you're opening the query before you check the values, and then you're opening the form. If that's in fact backwards, try:
CODE
Private Sub Command7_Click()
  
  DoCmd.OpenForm "search"
  If Len(Volume & vbNullString) < 1 And Len(movie & vbNullString) < 1 And Len(Rating & vbNullString) < 1 Then
    MsgBox ("Please enter search criteria before attempting to search the database")
  Else
    DoCmd.OpenQuery "search"
  End If
End Sub

Note how I've added the & vbNullString to your Len function calls: if the field contains Null, Len doesn't return a numeric value.
I'm afraid I really don't understand what you're saying in the second point, but again, I think it's a case that you're not structuring your IF and ELSE correctly.
vampyr07au
Thanks for answering.
'll have a look at it shortly.
Omay not have explained myself properly.
What happens is this - from the main menu the user selects search which opens the search form.
The search form has 3 boxes and a search button.
if the user enters criteria into either one, two or all three search boxes then the search string needs to run and display any results in another new form.
If there are NO search results that match the criteria then the user will see a msgbox stating "no results found, pls try again".
If the user tries to press the search button without entering anything into any of the 3 search boxes then they get a msgbox stating "you must provide at least one search criteria before attempting a search"
After clicking OK on the msgboxes it needs to return to the search menu form that the user originally started from.
At no point do I want any other forms or tables or queries displayed.
I have attached a copy of the database itself for you to see
Doug Steele
Sorry, I don't do downloads.
You say "At no point do I want any other forms or tables or queries displayed.". Assuming that Command7 is your Submit button (btw, you should assign it a meaningful name!), why did you have a DoCmd.OpenQuery command in its Click event?
BruceM
Just add Exit Sub after the message box line of code.
It's rather confusing when you have a table, form, and query all with the same name. In the end it will probably confuse you, too.
You may be able to do something like the following. However, it isn't clear how you are passing the criteria. Is the search results form based on a query that gets its criteria from the current form?
CODE
Private Sub Command7_Click()
  If Nz(Me.Title, "") > "" Then
    If Len(Volume) < 1 And Len(movie) < 1 And Len(Rating) < 1 Then
        MsgBox ("Please enter search criteria before attempting to search the database")
        Exit Sub
      Else
        If DCount("*", "search", "[Movie Title] = '" & Me.Title & "'") Then
          MsgBox ("No movies matching your search. Please refine your search and try again.")
          Exit Sub
        End If
    Else
      DoCmd.OpenForm "search results"
  End If
End Sub

Note that the DCount criteria argurment ("[Movie Title] = '" & Me.Title & "'") will fail if the Me.Title value includes an apostrophe. You could use:
"[Movie Title] = """ & Me.Title & """")
However, that will fail if there is a quote mark in the title. Here is some code Stefan Hoffman provided several years ago in a newsgroup posting, and which I have been using since then. Place the following code in a standard module. One way to do this to go to the database window (the thing with tabs for Forms, Queries, etc.). On the Module tab, click New, and give it a name such as mdlGeneralCode. Open the module and insert the following code (if you already have a standard module you can just insert the code there):
CODE
Public Function SQLQuote(AString As String) As String
   Const Delimiter As String = "'"
   SQLQuote = Delimiter & Replace(AString, Delimiter, Delimiter & Delimiter) & Delimiter
End Function

To use the code, something like this in the DCount:
"[Movie Title] = " & SQLQuote(Me.Title)
vampyr07au
Sorry ... not clear enough again .... when clicking search it either displays the search results ... in which case it opens a form to display the results in ... or it displays the msg box relevant to either nil search results found OR nil search criteria entered ... whichever is the correct instance at the time .... I don't want it displaying an underlying query/datasheet/table AND the msgbox.
At this point in time when the msgbox pops up it is still also reverting to displaying another window which is the query/datashee
BruceM
Did you try Exit Sub as suggested?
vampyr07au
Not as yet Bruce, I was actually in the process of replying to previous posts when you added your suggestion and it was too late last night for me to play with it then.
ill try it today during the day and get back to you.
vampyr07au
OK - I tried the exit sub as suggested (I used the code as you typed it in above) but this returned a debug error stating that there was an Else without an If and highlighted the bottom Else statement (the open search form).
o then I tried removing the End If just above that and got a block If error.
Oended up trying the fllowing code:
CODE
Private Sub Command7_Click()
  If Nz(Me.Title, "") > "" Then
    If Len(Volume) < 1 And Len(movie) < 1 And Len(Rating) < 1 Then
        MsgBox ("Please enter search criteria before attempting to search the database")
        Exit Sub
      Else
        If DCount("*", "search", "[Movie Title] = '" & Me.Title & "'") Then
          MsgBox ("No movies matching your search. Please refine your search and try again.")
          Exit Sub
            Else
      DoCmd.OpenForm "search results"
  End If
  End If
  End If
End Sub

However this is still giving me the same issues as before (search button is not working and now the msgbox for no criteria entered is also not working.)
The query that runs when the search button is clicked gets its data from the main table in the database. (hope thats what you were asking)
Doug Steele
When you say "search button is not working and now the msgbox for no criteria entered is also not working", what exactly do you mean? If you're getting an error message, what's the error message. If you're not getting an error message, what are you getting, and what do you expect to get instead?
noticed you ignored my comment about the Len function not working properly on Null values. Also, your use of the DCount statement looks incorrect: remember that a value of 0 means Flase and non-zero means True in VBA.
Does this work any better?
CODE
Private Sub Command7_Click()
  
  If Nz(Me.Title, "") > "" Then
    If Len(Volume & "") < 1 And Len(movie & "") < 1 And Len(Rating & "") < 1 Then
      MsgBox "Please enter search criteria before attempting to search the database"
      Exit Sub
    Else
      If DCount("*", "search", "[Movie Title] = '" & Me.Title & "'") = 0 Then
        MsgBox "No movies matching your search. Please refine your search and try again."
        Exit Sub
      Else
        DoCmd.OpenForm "search results"
      End If
    End If
  End If
  
End Sub
vampyr07au
Sorry Bruce.
I'm a bit up in the air and confused with all of this code i'm afraid.
didnt intentionally ignore your comment about the null values, I was just trying to address things sorta one at a time.
If the null values issue is important and needs to be addressed at the same time then I will do so.
OK to clarify, When I click the search button after criteria have been entered I am expecting one of two things - either the "search results" form opens and shows matching results - or the msgbox fires stating that no results were found.
What I am actually seeing is absolutely NO response to the clicking of the msg at all when I enter search criteria in either the volume or the ratings fields.
If I enter something in the title field then I get a search result that matches OR if there is no matching criteria then I get the msgbox stating that I need to enter search criteria - which s of course the WRONG msgbox for this particular instance.
When I click the search/find button without entering any criteria then I am expecting the msgbox to fire telling me i need to input search criteria and try again.
What I am getting is also a nil response of any kind here.
vampyr07au
No .. No better at al I'm afraid.
If I enter something in the title field and click search I get the "pls enter search criteria msgbox"
Entering something in any other field gives me no response at all - no results displayed, no msgboxes, nothing.
vampyr07au
OK ... after having a further read of everything I decided I would try the above piece of code.
I have found that using the above code now returns part of what I am wanting and expecting.
This is my code:
CODE
Private Sub Command7_Click()
  
  DoCmd.OpenForm "search"
  If Len(Volume & vbNullString) < 1 And Len(movie & vbNullString) < 1 And Len(Rating & vbNullString) < 1 Then
    MsgBox ("Please enter search criteria before attempting to search the database")
  Else
    DoCmd.OpenForm "search results"
  End If
End Sub

and the results are:
search by volume returns the correct results as expected.
search by rating returns the correct results as expected.
search by title returns the msgbox "pls make sure you enter search criteria and try again"
Further Testing:
Updating the code to this:
CODE
Private Sub Command7_Click()
  
    If Len(Volume & vbNullString) < 1 And Len(movie & vbNullString) < 1 And Len(Rating & vbNullString) < 1 Then
    MsgBox ("Please enter search criteria before attempting to search the database")
  Else
      If DCount("*", "search", "[Movie Title] = '" & Me.Title & "'") = 0 Then
        MsgBox "No movies matching your search. Please refine your search and try again."
        Exit Sub
      Else
        DoCmd.OpenForm "search results"
      End If
    End If
  
    End Sub

Changes the results I get.
Now when I search by volume or rating I get the "no matching results" msgbox and when I search by title I get the "no criteria entered" msgbox.
One thing that I did just notice which I kinda missed before is that the Dcount code only deals with the one field "title"
How can I extend it to cover the other 2 fields effectively ? ("volume" and "rating")
Doug Steele
Not sure whether it's a typo, but you're looking for Len(movie & vbNullString) < 1 in your first check, but you're using Me.Title in your DLookup.
Are you sure it's reasonable to make them type in a complete title? Might you want to let them type in part of the title, and let them return all matches? If so, then use
CODE
  If DCount("*", "search", "[Movie Title] Like '*" & Me.Title & "*'") = 0 Then

What are typical Volume and Rating values? How you incorporate them into the DLookup depends on that. As well, what you want to do when they don't supply a value? Will every entry have a value for Volume and Rating?
Assuming that you should have been checking for Title and not Movie, try the following instead:
CODE
Dim strCriteria As String
  
  If Len(Me.Volume & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Volume] = '" & Me.Volume & "' AND "
  End If
  
  If Len(Me.Title & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Movie Title] LIKE '*" & Me.Title & "*' AND "
  End If
  
  If Len(Me.Rating & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Rating] = '" & Me.Rating & "' AND "
  End If
  
  If Len(strCriteria) < 1 Then
    MsgBox ("Please enter search criteria before attempting to search the database")
  Else
    strCriteria = Left(strCriteria, Len(strCriteria) - 5)
    If DCount("*", "search", strCriteria) = 0 Then
      MsgBox "No movies matching your search. Please refine your search and try again."
      Exit Sub
    Else
      DoCmd.OpenForm "search results"
    End If
  End If

I've assumed both Volume and Rating are text values.
One question: how are you controlling what shows up on the search results form?
vampyr07au
The search form uses the query as its record source.
The query uses "like" search criteria etc to define what results are returned.
And the search was already returning results based on partial searches ... probably because of the "like"in the query that the form is based on
Volume is a numeric value and rating is text.
The rating value is a defined list from a table.
The volume value is defined by me.
Every entry will have a rating value but not necessarily a volume value.
HAs previously stated the search does not necessarily require a value in all 3 search boxes.
Ostill want a value returned if they search only by full or partial title or only by rating or only by volume or by any possible combinations of the 3.
Doug Steele
Did you try my suggested code then? I believe it does exactly what you've described ("I still want a value returned if they search only by full or partial title or only by rating or only by volume or by any possible combinations of the 3.") The only change you'd need to make is to accomodate the fact that Volume is numeric, and I assumed text:
!--c1-->
CODE
Dim strCriteria As String
  
  If Len(Me.Volume & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Volume] = " & Me.Volume & " AND "
  End If
  
  If Len(Me.Title & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Movie Title] LIKE '*" & Me.Title & "*' AND "
  End If
  
  If Len(Me.Rating & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Rating] = '" & Me.Rating & "' AND "
  End If
  
  If Len(strCriteria) < 1 Then
    MsgBox ("Please enter search criteria before attempting to search the database")
  Else
    strCriteria = Left(strCriteria, Len(strCriteria) - 5)
    If DCount("*", "search", strCriteria) = 0 Then
      MsgBox "No movies matching your search. Please refine your search and try again."
      Exit Sub
    Else
      DoCmd.OpenForm "search results"
    End If
  End If
vampyr07au
Could you pls tell me how I define a numeric value as opposed to the text value ?
Doug Steele
Sorry, I don't understand your question. Define a numeric value where?
If you're talking about in the criteria, take a look at what I put above. Rating is text, so there need to be quotes around the value:
CODE
    strCriteria = strCriteria & "[Rating] = '" & Me.Rating & "' AND "

Volume is numeric, so there must not be quotes around the value:
CODE
   strCriteria = strCriteria & "[Volume] = " & Me.Volume & " AND "

(Note that I'm talking about the single quotes.)
vampyr07au
ahh yes i see
yvm for your help
i'll post back once i have tested it all
vampyr07au
I tried to use the code as follows:
CODE
rivate Sub Command7_Click()
  
    Dim strCriteria As String
  
  If Len(Me.Volume & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Volume] = " & Me.Volume & " AND "
  End If
  
  If Len(Me.Title & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Movie Title] LIKE '*" & Me.Title & "*' AND "
  End If
  
  If Len(Me.Rating & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Rating] = '" & Me.Rating & "' AND "
  End If
  
  If Len(strCriteria) < 1 Then
    MsgBox ("Please enter search criteria before attempting to search the database")
  Else
    strCriteria = Left(strCriteria, Len(strCriteria) - 5)
    If DCount("*", "search", strCriteria) = 0 Then
      MsgBox "No movies matching your search. Please refine your search and try again."
      Exit Sub
    Else
      DoCmd.OpenForm "search results"
    End If
  End If
    End Sub

It is now giving me a debug error - Data type mismatch in criteria expression - this happens when I try searching using a numerical criteria in the volume field.
all the other search stuff and msgboxes work perfectly
"If DCount("*", "search", strCriteria) = 0 Then" is the line that is highlighted when the debug error appears
Doug Steele
The implication, then, is that Volume is a Text field, even if it's holding a numeric value (or else you didn't make the change I said you needed to make!)
If it's numeric, you need
CODE
    strCriteria = strCriteria & "[Volume] = " & Me.Volume & " AND "

If it's text, you need
CODE
    strCriteria = strCriteria & "[Volume] = '" & Me.Volume & "' AND "
vampyr07au
I found the problem ... thanks Doug.
I actually had the volume field in the table set as text by mistake.
All fixed now.
How do I mark as solved ?
Doug Steele
Glad you got it working.
here's actually no concept of "marking as correct" here at UtterAccess. However, any future reader of this thread will see your comment.
vampyr07au
okay cool ... tyvm for all ur help <
One more quick question Doug,
The code you mentioned to use in the module that would prevent issues with special characters llike " ...
Should I still be using this ?
And does it need to have seperate pieces of code for seperate special chars ? eg: do I have to have a different piece of code for " as compared to ' ?
Doug Steele
Take a look at the second entry in my June, 2004 Access Answers column in Pinnacle Publication's "Smart Access" for an explanation of how to handle quotes.
vampyr07au
I had a look at your tutorial but i'm afraid it kinda went straight over my head.
I'm not really all that code s
vampyr07au
I tried to follow some of what you said both in this topic and in the tutorial and thus I tried the following code:
!--c1-->
CODE
Private Sub Command7_Click()
  
    Dim strCriteria As String
  
  If Len(Me.Volume & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Volume] LIKE '*" & SQLQuote(Me.Volume) & " AND "
  End If
  
  If Len(Me.Title & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Movie Title] LIKE '*" & SQLQuote(Me.Title) & "*' AND "
  End If
  
  If Len(Me.Rating & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Rating] LIKE '*"" & SQLQuote(Me.Rating) & " ' AND "
  End If
  
  If Len(strCriteria) < 1 Then
    MsgBox ("Please enter search criteria before attempting to search the database")
  Else
    strCriteria = Left(strCriteria, Len(strCriteria) - 5)
    If DCount("*", "search", strCriteria) = 0 Then
      MsgBox "No movies matching your search. Please refine your search and try again."
      Exit Sub
    Else
      DoCmd.OpenForm "search results"
    End If
  End If
    End Sub

With also a module:
CODE
Public Function SQLQuote(AString As String) As String
   Const Delimiter As String = "'"
   SQLQuote = Delimiter & Replace(AString, Delimiter, Delimiter & Delimiter) & Delimiter
End Function

But it is falling down into debug mode when I try and search a string such as O'Malley or anything with ' in it.
The debug line is this one:
CODE
If DCount("*", "search", strCriteria) = 0 Then
Doug Steele
Unfortunately, you've missed the fact that that the SQLQuote function puts quotes around the string, meaning that if your code is
!--c1-->
CODE
  If Len(Me.Title & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Movie Title] LIKE '*" & SQLQuote(Me.Title) & "*' AND "
  End If

and Me.Title is O'Malley, you're going to end up with
CODE
  [Movie Title] LIKE '*'O''Malley'*' AND

Just go with
CODE
  If Len(Me.Title & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Movie Title] LIKE '*" & Replace(Me.Title, "'", "''") & "*' AND "
  End If

Another option would be to change the SQLQuote function so that it returns the operator as well (i.e. it returns = 'O''Malley' or LIKE '*O''Malley*'), something like:
CODE
Public Function SQLQuote(AString As String, PrecedingWildcard As Boolean, TrailingWildcard As Boolean) As String
  
Const Delimiter As String = "'"
  
  SQLQuote = IIf(PrecedingWildcard OR TrailingWildcard, " LIKE ", " = ") & _
    Delimiter & _
    IIf(PrecedingWildcard, "*", "") & _
    Replace(AString, Delimiter, Delimiter & Delimiter) & _
    IIf(TrailingWildcard, "*", "") & _
    Delimiter
End Function

in which case your code would be
CODE
  If Len(Me.Title & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Movie Title] " & SQLQuote(Me.Title, True, True) & " AND "
  End If
vampyr07au
woohooo thank you ... i went with this:
and it works.
another question now though.
For me to do the same thing for "" do I need to do another module code or can I just add some lines to the existing one ?
And would something similar be needed for other special chars like & * $ or ? etc etc ???
Doug Steele
If you're using =, you don't need to do anything for other special characters such as *, %, ? or _. If you're using LIKE, then those are wildcard characters (* and ? are for DAO, % and _ are for ADO), so you'd need to escape them by putting square brackets around them. In other words, if you're looking for a title that contains an asterisk anywhere in the title, you'd need to use [Movie Title] LIKE '*[*]'*' AND.
I'm not sure what you're asking about "".
vampyr07au
If the title contains double quotes " like that .. does it need separate module code or can it be incorporated into the same module code that I used for the single quote ?
Doug Steele
Hmm. That's why I pointed you to my article on quotes. Are you saying it wasn't clear enough that you only need to worry about ' if you're using ' as a delimiter in your SQL statements, and you only need to worry about " if you're using " as a delimiter in your SQL statements.
vampyr07au
lol sorry I mustn't have read it all properly .... Now I get it ... all good .... thanks.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.