Filter code won’t work on pop-up form but will with Input boxes?

[Redbeard]

I created a keyword filter to filter my form in 5 fields. I tried this code using two input boxes first and it works. Then tried it with a pop-up form and 2 unbound text boxes, and it doesn’t work now? What am I doing wrong? The code is supposed to filter through 5 fields if you fill in the first box but only 3 fields if you fill in both boxes. That is because the other two fields only have one word in them.

Code with Input Boxes:

Private Sub Apply_Filter_Click()
Dim MyValue As Variant
Dim MyValue2 As Variant
 
MyValue = InputBox("Searches Fields: Object Name, Object Type, Subject/Image, Description, History", "Enter Keyword")
MyValue2 = InputBox("Optional, Searches Fields: Subject/Image, Description, History", "Enter Second Keyword")
 
    If MyValue2 = "" Then
 
    Forms!frmDescription.Filter = "((Description) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                  "((History) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                  "(([Object Name]) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                  "(([Object Type]) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                  "(([Subject/Image]) Like '*" & MyValue & "*')"
 
    Forms!frmDescription.FilterOn = True
 
    Else
 
    Forms!frmDescription.Filter = "(((Description) Like '*" & MyValue & "*" & MyValue2 & "*' Or (Description) Like '*" & MyValue2 & "*" & MyValue & "*')) OR " & vbCrLf & _
                                  "(((History) Like '*" & MyValue & "*" & MyValue2 & "*' Or (History) Like '*" & MyValue2 & "*" & MyValue & "*')) OR " & vbCrLf & _
                                  "((([Subject/Image]) Like '*" & MyValue & "*" & MyValue2 & "*' Or ([Subject/Image]) Like '*" & MyValue2 & "*" & MyValue & "*'))"
 
    Forms!frmDescription.FilterOn = True
 
    End If
 
End Sub

Code with pop-up form:

Private Sub Imput2_LostFocus()
 
Dim MyValue As Variant
Dim MyValue2 As Variant
 
MyValue = Imput1
MyValue2 = Imput2
 
 
    If MyValue2 = "" Then
 
    Forms!frmDescription.Filter = "((Description) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                  "((History) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                  "(([Object Name]) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                  "(([Object Type]) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                  "(([Subject/Image]) Like '*" & MyValue & "*')"
 
    Forms!frmDescription.FilterOn = True
 
    Else
 
    Forms!frmDescription.Filter = "(((Description) Like '*" & MyValue & "*" & MyValue2 & "*' Or (Description) Like '*" & MyValue2 & "*" & MyValue & "*')) OR " & vbCrLf & _
                                  "(((History) Like '*" & MyValue & "*" & MyValue2 & "*' Or (History) Like '*" & MyValue2 & "*" & MyValue & "*')) OR " & vbCrLf & _
                                  "((([Subject/Image]) Like '*" & MyValue & "*" & MyValue2 & "*' Or ([Subject/Image]) Like '*" & MyValue2 & "*" & MyValue & "*'))"
 
    Forms!frmDescription.FilterOn = True
 
    End If
 
Me.Visible = False
 
End Sub

When I run this code there in no error but it just skips the first filter and runs the second one. If I comment out the second filter then it does nothing. I assume the problem is with the “IF” statement but I don’t know what? Any suggestions?

[TheSmileyCoder]

Line 6 and 7 of your second code:

MyValue = Imput1
MyValue2 = Imput2

I don't see imput1 and imput2 defined anywhere?

Remember to turn on option Explicit, it will catch many errors during compile.

[zmbd]

Just a follow up on TSC's post: >> Before Posting (VBA or SQL) Code. The information here will save you many a headache.

[Redbeard]

Hi Guys thanks for the help.

I do have “Option Explicit” on and “Imput1 and Imput2” are my Unbound text boxes on the pop-up form. I am not sure how else to “Define” them? But I am sort of new at this, please let me know if I am doing it wrong. I have figured out the problem with my code, although I am not sure why it didn’t work and now it does. Especially since it worked with the input boxes? Here is what I changed on line 10:

If MyValue2 = "" Then

to

If IsNull(Imput2) Then

Can anyone tell me why this works? It is great to have it working but I want to know why?

[zmbd]

me!imput1
Me is the shortcut reference to the form wherein the control is immediately located.

The reason it worked with the "input" code is because these returned the values from the user entry. In the case with the control/field "imput*", it is not being referenced correctly. The compiler is treating it as an undefined variable.

If you had ran the compile, the "imput1" should have flagged as an undefined variable, unless you have a Dim imput1 As (classtype) - in which case the variable would not have flagged as undefined.

This is what Rabbit mentioned within post#1.

It is essential that you follow the basic debugging steps as outlined in the link I provided you in post #3. We can not do this for you... we're not in possession of your PC/Project.

[Redbeard]

Hi Zmbd
Thanks for your help. I did read the link you sent and I did run “Complie” and nothing happened. So I have now just done it again and nothing happen? It does not flag “Imput1 or Imput2” as undefined and the code works fine without putting “Me.” in front. I also don’t have “Dim Imput1 As (classtype)” in my code. So I guess one, I am confused as to why I didn’t have a problem without using either of these methods? And two, back to my previous question, why did I need to change Line 10 to get the code to recognize a Null in “Imput2”? I am trying to understand so I don’t make the same mistakes again which will lead to unnecessary posts.

[zmbd]

Could you please do the following:
Open the VBA Editor <Alt><F11>
Tools/Options
List what options are selected in the "Editor" tab in the "Code Settings" group.

[Redbeard]

I am running Access 2010 if that makes a difference. The options under the “Editor” tab that are checked are all of them. They are: Auto Syntax Check, Require Variable Declaration, Auto List Members, Auto Quick Info, Auto Data Tips, Drag-and-drop Text Editing, Default to Full Module View, Procedure Separator, Auto Indent and Tab Width: 4. I am also posting my completed code with the changes below. I have add a few more steps but it all runs fine.

Private Sub Imput2_LostFocus()
 
Dim MyValue As Variant
Dim MyValue2 As Variant
 
MyValue = Imput1
MyValue2 = Imput2
 
 
    If IsNull(MyValue2) Then
 
        If CurrentProject.AllForms("frmDescription").IsLoaded Then
            Forms!frmDescription.Filter = "((Description) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                          "((History) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                          "(([Object Name]) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                          "(([Object Type]) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                          "(([Subject/Image]) Like '*" & MyValue & "*')"
 
            Forms!frmDescription.FilterOn = True
 
         ElseIf CurrentProject.AllForms("frmDescriptionPhoto").IsLoaded Then
            Forms!frmDescriptionPhoto.Filter = "((Description) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                          "((History) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                          "(([Object Name]) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                          "(([Object Type]) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                          "(([Subject/Image]) Like '*" & MyValue & "*')"
 
 
            Forms!frmDescriptionPhoto.FilterOn = True
 
         ElseIf CurrentProject.AllForms("frmDescriptionPublic").IsLoaded Then
            Forms!frmDescriptionPublic.Filter = "((Description) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                          "((History) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                          "(([Object Name]) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                          "(([Object Type]) Like '*" & MyValue & "*') OR " & vbCrLf & _
                                          "(([Subject/Image]) Like '*" & MyValue & "*')"
 
 
            Forms!frmDescriptionPublic.FilterOn = True
        End If
    Else
        If CurrentProject.AllForms("frmDescription").IsLoaded Then
            Forms!frmDescription.Filter = "(((Description) Like '*" & MyValue & "*" & MyValue2 & "*' Or (Description) Like '*" & MyValue2 & "*" & MyValue & "*')) OR " & vbCrLf & _
                                          "(((History) Like '*" & MyValue & "*" & MyValue2 & "*' Or (History) Like '*" & MyValue2 & "*" & MyValue & "*')) OR " & vbCrLf & _
                                          "((([Subject/Image]) Like '*" & MyValue & "*" & MyValue2 & "*' Or ([Subject/Image]) Like '*" & MyValue2 & "*" & MyValue & "*'))"
 
            Forms!frmDescription.FilterOn = True
 
        ElseIf CurrentProject.AllForms("frmDescriptionPhoto").IsLoaded Then
            Forms!frmDescriptionPhoto.Filter = "(((Description) Like '*" & MyValue & "*" & MyValue2 & "*' Or (Description) Like '*" & MyValue2 & "*" & MyValue & "*')) OR " & vbCrLf & _
                                          "(((History) Like '*" & MyValue & "*" & MyValue2 & "*' Or (History) Like '*" & MyValue2 & "*" & MyValue & "*')) OR " & vbCrLf & _
                                          "((([Subject/Image]) Like '*" & MyValue & "*" & MyValue2 & "*' Or ([Subject/Image]) Like '*" & MyValue2 & "*" & MyValue & "*'))"
 
            Forms!frmDescriptionPhoto.FilterOn = True
 
        ElseIf CurrentProject.AllForms("frmDescriptionPublic").IsLoaded Then
            Forms!frmDescriptionPublic.Filter = "(((Description) Like '*" & MyValue & "*" & MyValue2 & "*' Or (Description) Like '*" & MyValue2 & "*" & MyValue & "*')) OR " & vbCrLf & _
                                          "(((History) Like '*" & MyValue & "*" & MyValue2 & "*' Or (History) Like '*" & MyValue2 & "*" & MyValue & "*')) OR " & vbCrLf & _
                                          "((([Subject/Image]) Like '*" & MyValue & "*" & MyValue2 & "*' Or ([Subject/Image]) Like '*" & MyValue2 & "*" & MyValue & "*'))"
 
            Forms!frmDescriptionPublic.FilterOn = True
 
        End If
    End If
 
Me.Visible = False
 
End Sub

[Mihail]

I think that the answer for you is here

[zmbd]

Mihail,
I'm not sure how the link you provided applies to this issue.

Redbeard,
At Line # 8 of the code as given in post# 8
Please insert

debug.print "From Imput#1 = " & _ 
     nz(MyValue,"null") & _
     vbcrlf & "From Imput#2 = " & _ 
     nz(MyValue2,"null")

Once your code has ran or if it fails and goes into the debug mode press <Ctrl+G>
You should see the resolved values for your variables in the immediate window. Please do a standard copy and paste into post within this thread. Please run it a few times with differing user input - I'd like to see what value(s) you are returning, if anything.

Because you are building your criteria string directly within in the filter you've made it impossible to troubleshoot what is happening - not really your fault per say, it is the way that most of the examples show to do it... and (IMHO) it is teaching new programmers a lazy coding method as one doesn't define a string variable first and then work thru the resolved string if trouble develops.

[Mihail]

@ZMBD
I have provide that link because I think that is probably that Redbeard to have the same problem as me with the bounded textboxes.

I spent 2 hours to discover the warm water:
The Textbox.Value remain equal with the record's field value (whatever you type in) until the record is updated (saved).
So, if the field value in a record is NULL, the Textbox.Value will be NULL even if the user will type one thousand characters. Only the TextBox.Text has been changed as you type.

Of course: not sure that this is the problem here. Just I suspect that.

[zmbd]

MiHail,
I can see where you would think the issues related.

Please regard the code in both OP and #8 - where in fact are "Imput1" and "Imput2" defined?
As used in #8, these are not defined at any-point as being related to the unbound control in that neither the me!imput* nor the more formal "Forms!...." method are used (or the "dot" nomenclature). So the question(s) become: why isn't the compiler failing this and what values are actually being returned by "imput*"

[Redbeard]

Hi zmdb
I have put your code in the appropriate place and I can't get your code to run... it keeps getting stuck on Line 2. Specifically on "Nz" it is highlighted and I get a “Compile Error” that reads "Wrong Number of arguments or invalid property assignment". When I hit <CtrlG> it goes to the “Immediate” window but nothing comes up? I assume that is because it won't go pass the “Nz”?

[zmbd]

Opps, sorry about that, I was away from my normal PC and working on a different project and trying to handle my four kids... gota-love'em!

I've fixed the code in post#10.... please try it again using the correct code :)

[Redbeard]

Hey no problem... I know what kids are like.
Here are the value that I got, they are exactly what I typed into the unbound text boxes:

From Imput#1 = print
From Imput#2 = black

From Imput#1 = print
From Imput#2 = null

From Imput#1 = null
From Imput#2 = print

From Imput#1 = john
From Imput#2 = smith

Code is still running fine.

[zmbd]

Ok,
I have recreated to some extent what you are seeing...
I am impressed that V2010 has this much forgivness in the coding!

What I've done to recreate the issue created a form with two unbound textboxes, named them imput1 and imput2, then used my basic default print this...

Note in the VBA code that imput1 and imput2 are not defined... the compiler is finding the names in the form and using that information.
If I change either the control names, or the variable names (say change the variable from imput1 to imput5 or change the control name to "z_txt_imput1") without changing the other to match then the compiler chokes.

I've not seen this before, yet here's the proof:

[Redbeard]

So does that mean that "Me." is now not needed in the code since it will pick up any name that is listed on the form and use it? Or would there be an instance where you could get fouled up by not using "Me."? I would like to use best practices, to avoid any problems in the future.

By the way zmbd, thanks for following through and investigating this, it is appreciated.

[zmbd]

I'd still use the "Me." and "Me!" constructs. What happens if we place this into a Form/SubForm situation... Say in this case we have a control named imput99 on both the parent form and the subform, now what? Given the code is in the SubForm class module would this pull the value fromt he SubForm, Parent, or will there be a compiler error?

IMHO: It is always better to be explicit in how things are defined and named. It makes the code much easier to follow for both yourself and anyone that comes behind you later that has to maintain it.

It very well could be that this is a bug in the V2010 compiler that might be fixed via an update or in the V2013 that is already in the wild (or future release).

[Redbeard]

Thanks zmbd for all the help! I will add the "Me." and continue that as my practice.