Form Filter No Criteria Message Box

So regarding the same form which filters based on the search criteria in the form header, when it has no criteria in any of the fields, it will pop up a Message Box saying:

'No Criteria' with the title 'Nothing to do' and I believe that this section of the VBA code is in charge of this issue:

'***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
       
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

I don't really know how to get it to stop behaving this way. I have tried to just delete that section, but that just screwed up the form and I had to revert to a previous version of the form.

Can someone tell me how to fix this or I can just upload my sample database and see what someone does to fix it?

Capricorn1 is the one who helped perfectly construct this database, but all others are welcome to help (I really wish I can find something by "Googling" the issue, but it is very hard to find and just trying to learn VBA as I go).

Thank you in advance
IEHP1Asked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Ok - I just tested that in your database, and it seems to do the trick.


You simply need too change this line:
---->>> MsgBox "No criteria", vbInformation, "Nothing to do."

to this:

If Me.ActiveControl.Name = "cmdFilter" Then MsgBox "No criteria", vbInformation, "Nothing to do."

Open in new window


(no other changes are needed)
0
 
mbizupCommented:
Are you simply trying to not show the message when there are no criteria?

You can simply comment out the msgbox:


    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
       ' MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub 

Open in new window


Or if you actually want to remove that part... take out the ELSE and change the IF to " If lngLen >  0 Then" :

    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen >  0 Then   'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub 

Open in new window

0
 
IEHP1Author Commented:
I see and that works, but what I am trying to make a distinction about that I forgot to mention initially is that I have added some more functionality to the form than my sample (and I can quickly update it to function the same way). The functionality of it is that I have added an AfterUpdate event for every combo box and text box in the form header so that when I press Enter, select a value from the combo boxes, or tab over, it will update the results section automatically.

However, the problem is when I delete the criteria I search on previously and then go to another field (tab over, click in it, or whatever) the Message Box pops up.

So I like the button popping up the Message Box, but I don't want it to pop up when I just go into another field..........
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
IrogSintaCommented:
I'm not clear on what you want either but if Miriam's answer is still not what you're after, upload a copy of your database.
0
 
mbizupCommented:
That makes sense.  Try checking the active control (look at my code change and substitute your actual command button name where I have "mycommandbuttonname")
'***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        If Me.ActiveControl.Name = "mycommandbuttonname" then MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub 

Open in new window

0
 
IEHP1Author Commented:
So I am thinking along the lines of modifying my AfterUpdate event procedure as follows:

Right now it says Call cmdFilter_Click (which is just calling the button's OnClick event)

But if I actually put in the If statements for every text and combo box in the Form Header instead of calling out the button's whole OnClick event, it shouldn't include that code you helped me understand a little bit, right?

Do you see what I am saying? I hope I am right about this?
0
 
IrogSintaCommented:
Ah, I understand your problem now. Can you upload your db?
0
 
IEHP1Author Commented:
So in response to the code change you are showing me Miram, what change is there? I'm sorry, my eyes aren't as sharp as yours in spotting out changes in VBA code (if it's not too much to ask, can you possibly highlight the change(s))?
0
 
IEHP1Author Commented:
So here is a very rough sample database (I have polished it up a lot for the production version, but the concepts remain true).

Also, I would like the Message Box to display if the form header search criteria fields are blank and the user hits the Enter key.....???
Form-Building-Using-FiltersRev-2.accdb
0
 
IEHP1Author Commented:
IrogSinta, perhaps the AfterUpdate event isn't what is needed here when I come to think about it...........because I want the users to be able to hit the Tab key without it querying and finding the results. I would like it to query and get the Results section only when they:

1. hit the Enter key
2. hit the Filter button
0
 
mbizupCommented:
I've highlighted the section I changed... it's just verifying that the command button was clicked before displaying the messagebox:



'***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        If Me.ActiveControl.Name = "mycommandbuttonname" then MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
       
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub
0
 
IrogSintaCommented:
Unfortunately I only have 2007 so I can't open your database, so Miriam could probably help you here.  However, I think here 2nd sample in her 1st post is what you need.

Since you don't want this to happen when the TAB key is pressed, you should use the OnKeyDown or OnKeyUp event instead so you can test for the Enter key.
0
 
IrogSintaCommented:
Miriam's last post is probably what would work best.  That way you can leave everything the way it is.  Cool idea, Miriam.
0
 
mbizupCommented:
Thanks, Ron :)

We've got a filter like this one at my place of work... minus the messagebox.  It has been a real workhorse over the years.
0
 
IEHP1Author Commented:
So I tried to put that code in Miriam, but see the below screenshots I took to show you the error I got:

vba error
vba debug
Please let me know if I am doing something wrong??
0
 
mbizupCommented:
Everything there looks good, and I think that problem is unrelated to the actual code.

Did you switch to a computer with a different version of MS Access, perhaps?

From the VBA Editor:

Tools --> References

In the dialog box, replace any references that are labeled as MISSING with the current version from the list.

If that doesn't help, compact/repair your database.

In the meantime, here is the sample with the fix in it.  This works for me... for now test it in Access 2010.  

I'm heading to bed but will check back in the morning.
Form-Building-Using-FiltersRev-2.accdb
0
 
IEHP1Author Commented:
I had replaced the first If statement with the If statement you have suggested.
If lngLen <= 0 Then

was included in yours you most recently sent, but I had deleted over it with your next line down (the If statement with the Me.ActiveControl.Name = "cmdFilter")

So I opened it up, tested it and it worked fine.

If no criteria in the form header search boxes, then Message Box displays when click Filter button.
If no criteria in the form header search boxes and tab over to next search box, no Message Box displays and now I can hit Enter with multiple search box values filled in and it will query the results based on that rather than having to click OK to the Message Box every time I want to tab over to next search box.

Thank you so much Miriam!!!

Please keep advised that I have more issues with the form that I need to be able to accomplish that I will post right now after I click Submit and award the points. If you can stay with me on this form,

#1. I will accomplish my goal of making the form work as it needs to
#2. I will continue to learn a little bit about VBA and how to overcome issues like this form
0
 
IEHP1Author Commented:
Simple one-line edit to the VBA code made it a lot easier than having to figure out another method of doing it. I was thinking to put the If statements into each of the search boxes AfterUpdate events instead of using Call cmdFilter_Click.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.