Solved

Form Filter No Criteria Message Box

Posted on 2013-01-25
18
697 Views
Last Modified: 2013-01-25
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
0
Comment
Question by:IEHP1
  • 8
  • 6
  • 4
18 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 

Author Comment

by:IEHP1
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 

Author Comment

by:IEHP1
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Ah, I understand your problem now. Can you upload your db?
0
 

Author Comment

by:IEHP1
Comment Utility
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
 

Author Comment

by:IEHP1
Comment Utility
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
 

Author Comment

by:IEHP1
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Miriam's last post is probably what would work best.  That way you can leave everything the way it is.  Cool idea, Miriam.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 

Author Comment

by:IEHP1
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 

Author Comment

by:IEHP1
Comment Utility
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
 

Author Closing Comment

by:IEHP1
Comment Utility
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now