Solved

Form Filter No Criteria Message Box

Posted on 2013-01-25
18
708 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

685 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