Solved

Form Filter No Criteria Message Box

Posted on 2013-01-25
18
707 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
ID: 38821266
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
ID: 38821286
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
ID: 38821293
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 61

Expert Comment

by:mbizup
ID: 38821294
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
ID: 38821295
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
ID: 38821301
Ah, I understand your problem now. Can you upload your db?
0
 

Author Comment

by:IEHP1
ID: 38821303
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
ID: 38821313
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
ID: 38821328
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38821340
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
ID: 38821346
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
ID: 38821354
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
ID: 38821359
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
ID: 38821364
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
ID: 38821404
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
ID: 38821427
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
ID: 38821538
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
ID: 38821539
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

808 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