Search Multiple Columns in a subform linked to search textbox on the main form (1 Viewer)

[Mari111]

I want to search multiple fields of a subform from an unbound textbox on the main form, but it doesn't seem to be filtering anything. No error messages come up either. My code is:

Private Sub btnSearch2_Click()
Dim strfilter As String
If Me.GlobalSearch.Value <> "" ThenMe.FilterOn = False
Me.GlobalSearch.SetFocus
Else strfilter = "(HarvestId LIKE '*" & Me.GlobalSearch.Value & "*' OR "strfilter = strfilter & "AssignedTo LIKE '*" & Me.GlobalSearch.Value & "*' OR "strfilter = strfilter & "HarvestStatus LIKE '*" & Me.GlobalSearch.Value & "*')"
End If
If strfilter <> "" Then Me.Filter = strfilter Me.FilterOn = True
Else Me.FilterOn = False
End If
End Sub

Where GlobalSearch is the search textbox and HarvestId, AssignedTo and HarvestStatus the fields I want to search on in the subform. Why isn't the code working? Thanks Mari

 

[isladogs]

Welcome to AWF.
If you haven't already done so, an introductory post outlining your skill level & background would be useful so we know the level of knowledge to assume for future replies

Try this
Changed lines shown in RED
You need to separate each item of code onto a new line
StrFilter code amended - hopefully I've got it correct first time
It helps to indent code as shown

Apart from all of that, your code was fine ....

Private Sub btnSearch2_Click()
Dim strfilter As String

[COLOR="Red"]If Me.GlobalSearch.Value <> "" Then
       Me.FilterOn = False[/COLOR]
       Me.GlobalSearch.SetFocus
[COLOR="red"]Else 
         strfilter = "((HarvestId LIKE '*" & Me.GlobalSearch.Value & "*') OR (AssignedTo LIKE '*" & Me.GlobalSearch.Value & "*') OR (HarvestStatus LIKE '*" & Me.GlobalSearch.Value & "*'))"[/COLOR]
End If

[COLOR="red"]If strfilter <> "" Then
     Me.Filter = strfilter
     Me.FilterOn = True
Else 
     Me.FilterOn = False
End If[/COLOR]
End Sub

 

[Mari111]

Hi Colin,

Thank you for your response. I've been using MS Access 2010 for building and managing queries for about a year but a bit of a novice in forms and VBA.

I tried your amended code but it didn't work. When I typed in text into the search box and clicked 'search', the text was highlighted black and flashed once, but nothing was filtered in the sub form. It stayed the same.

I then thought that perhaps I hadn't referenced by sub form name anywhere in the actual code for the search button, so I came up with this:

Private Sub btnSearch_Click()
If Me.GlobalSearch2.Value <> "" Then
Me.FilterOn = False
Me.GlobalSearch2.SetFocus
Else
Me![FrmSearchHarvestsSubform].Form.Filter = "((Volume LIKE '*" & Me.GlobalSearch2.Value & "*') OR (AssignedTo LIKE '*" & Me.GlobalSearch2.Value & "*') OR (HarvestAnalysisStatus LIKE '*" & Me.GlobalSearch2.Value & "*'))"
Me![FrmSearchHarvestsSubform].Form.FilterOn = True
End If
End Sub

However, this also didn't work and had the exact same result as your amended code did. Do you have any more suggestions? More help would be much appreciated, thanks for your efforts so far.

Mari

 

[Mari111]

Also, I've just noticed that when I click the 'Search' button with a search term typed into the search box, the sub form says 'unfiltered' at the bottom of it. However, when I clear the search box so it is empty and click the 'Search' button, the sub form says 'filtered' page 1 of 2 at the bottom of it, and I can see the sub form responding to the action of clicking the 'Search' button even though nothing is filtered as the search box is empty.

What is going on?!

 

[MarkK]

Check the LinkMasterFields and LinkChildFields properties of the subform control in design view. Access will commonly set those automatically if both forms are bound, and you drop the subform on the mainform in design view. Those properties automatically synchronize the two forms, and they may be colliding with the filter you are trying to set...
hth
Mark

 

[isladogs]

Somehow I missed 2 more big issues in your code!!!!:banghead:

1. Your logic is the wrong way round...
The If section as written turns OFF the filter when GlobalSearch2 <>""
It should be OFF when the search value is EMPTY/NULL

2. The filter string has single quotes - all should be double quotes

Assuming your subform code is correct, this should work (I hope):

Private Sub btnSearch_Click()
[COLOR="Red"]If Nz(Me.GlobalSearch2.Value,"") = "" Then[/COLOR]
       Me.FrmSearchHarvestsSubform.Form.FilterOn = False
       Me.GlobalSearch2.SetFocus [COLOR="SeaGreen"]'NOT SURE YOU NEED THIS LINE[/COLOR]
Else
       Me.FrmSearchHarvestsSubform.Form.Filter = "((Volume LIKE [COLOR="Red"]"[/COLOR]*" & Me.GlobalSearch2.Value & "*[COLOR="red"]"[/COLOR])" & _
         " OR (AssignedTo LIKE [COLOR="red"]"[/COLOR]*" & Me.GlobalSearch2.Value & "*[COLOR="red"]"[/COLOR])" & _
         " OR (HarvestAnalysisStatus LIKE [COLOR="red"]"[/COLOR]*" & Me.GlobalSearch2.Value & "*[COLOR="red"]"[/COLOR]))"
       Me.FrmSearchHarvestsSubform.Form.FilterOn = True
End If
End Sub

3. Hopefully Mark has answered your other question

 

[Mari111]

Thank you both Mark and ridders. Now when I click the 'Search' button it comes up with a Run Time Error 13: Type Mismatch. When I click debug, it highlights the LIKE "*" section of code (all of it). I did have trouble putting in the underscores _ after the & as it highlighted the code red and said it expected the end of statement, so I removed them and put it all into one string. My code is currently:

Private Sub btnSearch_Click()
If Nz(Me.GlobalSearch2.Value, "") = "" Then
Me.FrmSearchHarvestsSubform.Form.FilterOn = False
Else
Me.FrmSearchHarvestsSubform.Form.Filter = "((Volume LIKE " * " & Me.GlobalSearchValue2.Value & " * ") OR (AssignedTo LIKE " * " & Me.GlobalSearch2.Value & " * ") OR (HarvestAnalysisStatus LIKE " * " & Me.GlobalSearch2.Value & " * "))"
Me.FrmSearchHarvestsSubform.Form.FilterOn = True
End If
End Sub

Does the run time error have to do with the small _ removal or with my data types that the code is searching on? They are all set to text data types in MS Access. Volume does have _ in it though, like AB_NHISK02_SRD for example. Is this causing a problem?

I'm sure we're getting closer! Thanks so far to you both.

 

[isladogs]

Thank you both Mark and ridders. Now when I click the 'Search' button it comes up with a Run Time Error 13: Type Mismatch. When I click debug, it highlights the LIKE "*" section of code (all of it). I did have trouble putting in the underscores _ after the & as it highlighted the code red and said it expected the end of statement, so I removed them and put it all into one string. My code is currently:

Private Sub btnSearch_Click()
If Nz(Me.GlobalSearch2.Value, "") = "" Then
Me.FrmSearchHarvestsSubform.Form.FilterOn = False
Else
Me.FrmSearchHarvestsSubform.Form.Filter = "((Volume LIKE " * " & Me.GlobalSearchValue2.Value & " * ") OR (AssignedTo LIKE " * " & Me.GlobalSearch2.Value & " * ") OR (HarvestAnalysisStatus LIKE " * " & Me.GlobalSearch2.Value & " * "))"
Me.FrmSearchHarvestsSubform.Form.FilterOn = True
End If
End Sub

Does the run time error have to do with the small _ removal or with my data types that the code is searching on? They are all set to text data types in MS Access. Volume does have _ in it though, like AB_NHISK02_SRD for example. Is this causing a problem?

I'm sure we're getting closer! Thanks so far to you both.

Sorry - this got overlooked ....as did the missing quotes
The clue that it was still wrong was the spaces around the wildcards " * "
There needs to be an outer set of quotes for each item

Private Sub btnSearch_Click()
If Nz(Me.GlobalSearch2.Value, "") = "" Then
Me.FrmSearchHarvestsSubform.Form.FilterOn = False
Else
Me.FrmSearchHarvestsSubform.Form.Filter = "((Volume LIKE[COLOR="Red"] "[/COLOR]"*" & Me.GlobalSearchValue2.Value & "*"[COLOR="red"]"[/COLOR]) OR (AssignedTo LIKE [COLOR="red"]"[/COLOR]"*" & Me.GlobalSearch2.Value & "*"[COLOR="red"]"[/COLOR]) OR (HarvestAnalysisStatus LIKE [COLOR="red"]"[/COLOR]"*" & Me.GlobalSearch2.Value & "*"[COLOR="red"]"[/COLOR]))"
Me.FrmSearchHarvestsSubform.Form.FilterOn = True
End If
End Sub

NOTE: I've not tested this but have compared it with Allen Browne's excellent reference guide http://allenbrowne.com/ser-62code.html

 

[Mark_]

To assist with the SQL issues:

1) add a string to hold the SQL
DIM asSQL as STRING

2) Fill the string with the SQL you are going to use

3) Add something to show you what the SQL will be prior to setting the filter
debug.print asSQL
OR
MsgBox "SQL is " & asSQL
OR
Me.TxtToShowSQL = asSQL (if You want to add a large text box to show it)

4) use asSQL for your filter instead of a string you are trying to build
Me.FrmSearchHarvestsSubform.Form.Filter = asSQL

for future reference, every time you think you'll only have ONE text that you will be passing to a filter like this, you'll find you need to amend it based on users requests. I can see you having a set of check boxes over EACH field that is displayed and having to set which fields you search on based on which check boxes are checked. Building the SQL separate from the filter makes doing this far easier.

 

[Mari111]

Thank you, the subform now responds to the search box but not the way I want it too!
i.e. when I enter a search term in the search box and click 'search', the subform now goes blank.
The list in the sub form appears again if I clear the search box so that it's empty and click search.

Any suggestions as to why it's doing this?
Thanks
Mari

 

[isladogs]

Thank you, the subform now responds to the search box but not the way I want it too!
i.e. when I enter a search term in the search box and click 'search', the subform now goes blank.
The list in the sub form appears again if I clear the search box so that it's empty and click search.

Any suggestions as to why it's doing this?
Thanks
Mari

Are you using my code or Mark's?

 

[Mark_]

Two things;
1) What is the actual filter you are passing?
2) Are you sure you have matching values for the passed filter?

 

[Mark_]

Colin,

Just for clarification, I'm not suggesting something other than your fix, more of a "And here's how you can tell what you are really passing" type assistance. Hopefully they are using both so they can not only use the correct logic for their conditional filter but also have a way to check what they are passing.

I've noted a few times where I've had an error simply because I could see what was being passed all at one go rather than trying to piece it together. Hopefully the OP can do the same and see if there is an error in concatenation.

 

[isladogs]

Hi Mark

I'm perfectly happy with your comments & indeed your last 2 questions
Those are EXACTLY the questions she needs to be checking

 

[Mark_]

Hi Mark

I'm perfectly happy with your comments & indeed your last 2 questions
Those are EXACTLY the questions she needs to be checking

Great minds, much like great bladders, tend to think alike!

 

[Mari111]

Hi, I was referring to ridders/Colin's code. I guess the filter I'm passing is exactly what is described in the code - I want to search the subform for text content in the columns Volume, AssignedTo and HarvestAnalysisStatus, full or partially (hence the LIKE and wildcard *).

For example, in AssignedTo I might have a full name like John Smith. I have typed in the first name e.g. John, which is showing in the subform, but it isn't bringing up the record.

My code is now:

Private Sub btnSearch_Click()
If Nz(Me.GlobalSearch2.Value, "") = "" Then
Me.FrmSearchHarvestsSubform.Form.FilterOn = False
Else
Me.FrmSearchHarvestsSubform.Form.Filter = "((Volume LIKE "" * " & Me.GlobalSearch2.Value & " * "") OR (AssignedTo LIKE "" * " & Me.GlobalSearch2.Value & " * "") OR (HarvestAnalysisStatus LIKE "" * " & Me.GlobalSearch2.Value & " * ""))"
Me.FrmSearchHarvestsSubform.Form.FilterOn = True
End If
End Sub

Sorry for my slow response, it's been very busy at my end!

 

[Mark_]

I would recommend the following;

Private Sub btnSearch_Click()
[COLOR="blue"]DIM asFilter as String[/COLOR]
   If Nz(Me.GlobalSearch2.Value, "") = "" Then
      Me.FrmSearchHarvestsSubform.Form.FilterOn = False
   Else
   [COLOR="blue"]   asFilter = "((Volume LIKE "" * " &  Me.GlobalSearch2.Value & " * "")"
      asFilter = asFilter & " OR (AssignedTo LIKE "" * " & Me.GlobalSearch2.Value & " * "")"
      asFilter = asFilter & " OR (HarvestAnalysisStatus LIKE "" * " & Me.GlobalSearch2.Value & " * ""))"

      msgbox "Filter will be " & asFilter 'Check filter FIRST[/COLOR] 

      Me.FrmSearchHarvestsSubform.Form.Filter = [COLOR="blue"]asFilter[/COLOR]
      Me.FrmSearchHarvestsSubform.Form.FilterOn = True
   End If
End Sub

This way you can make sure of what you are passing.
P.S. I THINK I counted out the right number of quotes... I normally break up strings like this to make it easier for me to see what I am putting where. Also makes it easier to try and count things like quotes and parenthesis.

 

[isladogs]

Mari

Compare my code in post 8 with yours in post 16
The difference is I have "*" and you have " * " including spaces

Remove the spaces & try again ... cross fingers, legs & anything else available

Private Sub btnSearch_Click()
If Nz(Me.GlobalSearch2.Value, "") = "" Then
   Me.FrmSearchHarvestsSubform.Form.FilterOn = False
Else
   Me.FrmSearchHarvestsSubform.Form.Filter = "((Volume LIKE ""*" & Me.GlobalSearch2.Value & "*"")
      OR (AssignedTo LIKE ""*" & Me.GlobalSearch2.Value & "*"")
      OR (HarvestAnalysisStatus LIKE ""*" & Me.GlobalSearch2.Value & "*""))"
   Me.FrmSearchHarvestsSubform.Form.FilterOn = True
End If
End Sub

 

[Mari111]

Hurray, it works! You were right about the spaces Colin. I removed them and now it works fine. A huge thank you!

Thanks for your efforts Mark too.

Mari

 

[isladogs]

Your're welcome - if you are happy to do so, please also click the scales button at bottom left & complete as appropriate