By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

Filter using multiple combo boxes

100+
P: 161
Here is what I have for each combobox:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CmbLineSearch_AfterUpdate()
  2.  
  3.     DoCmd.ApplyFilter , "[LineNoID] = " & Str(Nz([Screen].[ActiveControl], 0))
  4.  
  5. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbSearchDate_AfterUpdate()
  2.  
  3.     DoCmd.ApplyFilter , "[LineDate] = " & "#" & Format([Screen].[ActiveControl], "mm/dd/yyyy") & "#"
  4.  
  5. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbShiftSearch_AfterUpdate()
  2.  
  3.     DoCmd.ApplyFilter , "[Shift] = " & Str(Nz([Screen].[ActiveControl], 0))
  4.  
  5. End Sub
How would I do it so I can filter using any combination of the three or two or just the one using a command button?

I found this on the net and modified for my needs but i get an error:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ApplyFilter_Click()
  2.  
  3. Dim strFilter As String
  4.  
  5. strFilter = ""
  6.  
  7.     ' see if there is data in combo box CmbLineSearch, if so add it to the filter
  8.     If Me!cmbSearchDate & vbNullStr <> vbNullStr Then
  9.         strFilter = strFilter & " AND [LineDate] = " & Me.cmbSearchDate
  10.     End If
  11.  
  12.     If Me!CmbLineSearch & vbNullStr <> vbNullStr Then
  13.  
  14.        strFilter = strFilter & " AND [LineNoID] = #" & Me.CmbLineSearch & "#"
  15.  
  16.     End If
  17.  
  18.     If Me!cmbShiftSearch & vbNullStr <> vbNullStr Then
  19.  
  20.        strFilter = strFilter & " AND [Shift] = #" & Me.cmbShiftSearch & "#"
  21.  
  22.     End If
  23.  
  24.     If strFilter <> "" Then
  25.  
  26.        ' trim off leading "AND"
  27.  
  28.        Me.Filter = Mid(strFilter, 4)
  29.        Me.FilterOn = True
  30.  
  31.     Else
  32.  
  33.    Me.Filter = ""
  34.    Me.FilterOn = False
  35.  
  36.     End If
  37.  
  38. End Sub
The Error is Variable not defined

It highlights vbNullstr

The site I got it from did not have it called out so I have no clue what it's for or if I even need it.

Thanks for any and all advise
1 Week Ago #1

✓ answered by twinnyfo

I know the problem - this is the challenge of working long distance.

Here (notice lines 13 and 20):

Expand|Select|Wrap|Line Numbers
  1. Private Sub ApplyFilter_Click()
  2.     Dim strFilter As String
  3.  
  4.     strFilter = ""
  5.     If Not IsNull(Me.cmbSearchDate) Then
  6.         strFilter = _
  7.             " AND [LineDate] = " & _
  8.             "#" & Format(Me.cmbSearchDate, "yyyy-mm-dd") & "#"
  9.     End If
  10.  
  11.     If Not IsNull(Me.CmbLineSearch) Then
  12.         strFilter = _
  13.             strFilter & _
  14.             " AND [LineNoID] = " & _
  15.             Me.CmbLineSearch
  16.     End If
  17.  
  18.     If Not IsNull(Me.cmbShiftSearch) Then
  19.         strFilter = _
  20.             strFilter & _
  21.             " AND [Shift] = " & _
  22.             Me.cmbShiftSearch
  23.     End If
  24.  
  25.     Debug.Print Mid(strFilter, 6)
  26.  
  27.     With Me.Form
  28.         .Filter = Mid(strFilter, 6)
  29.         .FilterOn = (Not strFilter = "")
  30.     End With
  31.  
  32. End Sub

Share this Question
Share on Google+
13 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,257
DJ,

This is another case of code (at least the source where you got it) over complexifying things just to make it less simple.

Your If...Thens should be:

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me!cmbSearchDate) Then
And so on. This could be tricky, because it looks like you are using a combo box, and many times combo boxes have a default value of 0 (at least if you build them that way) and it looks even more like you have very strange values being assigned to Combo Boxes (dates, etc.). Combo boxes usually refer to an index, which also make creating filters much easier.

I think this will get you started in the right direction, but there are certainly more things that may need to be addressed than just this solution.

Hope this hepps.
1 Week Ago #2

100+
P: 161
These comboboxes are unbound and is only pulling data from what is entered. I have used the individual filters with no issues at this point, but you never know. Thanks for the direction, I will try this as soon as I can, if I have have any questions I will let you know.
1 Week Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,257
DJRhino1175:
These comboboxes are unbound and is only pulling data from what is entered.
Got it--but from a design perspective, I think you may be missing something with how these combo boxes get their data. This is an aside issue, but one of hte goals of this forum is not "just give solutions" but to guide toward better design. Hope that makes sense.
1 Week Ago #4

100+
P: 161
Expand|Select|Wrap|Line Numbers
  1. Private Sub ApplyFilter_Click()
  2.  
  3.     If Not IsNull(Me!cmbSearchDate) Then
  4.     DoCmd.ApplyFilter , "[LineDate] = " & "#" & Format([Screen].[ActiveControl], "mm/dd/yyyy") & "#"
  5.     End If
  6.  
  7.     If Not IsNull(Me!CmbLineSearch) Then
  8.     DoCmd.ApplyFilter , "[LineNoID] = " & Str(Nz([Screen].[ActiveControl], 0))
  9.     End If
  10.  
  11.     If Not IsNull(Me!cmbShiftSearch) Then
  12.     DoCmd.ApplyFilter , "[Shift] = " & Str(Nz([Screen].[ActiveControl], 0))
  13.  
  14.     End If
  15.  
  16. End Sub
It throughs an error on the apply filter section. I know that code works as I was able to do them individually. So I think I have it structured wrong.

I " ' " the code for the comboboxes to perform them as a group per the above code.

The error I get is Run-time error 438 "Object doesn't support this property or method."
1 Week Ago #5

100+
P: 161
Twinny, I understand what your saying. I followed a procedure posted by microsoft on how to filter per a combobox.

Expand|Select|Wrap|Line Numbers
  1. SELECT [QryLine Calc].LineDate FROM [QryLine Calc] GROUP BY [QryLine Calc].LineDate ORDER BY [QryLine Calc].LineDate; 
Here is what I have for a row source for one of the combo's.
1 Week Ago #6

twinnyfo
Expert Mod 2.5K+
P: 3,257
I prefer to filter a form is to use the Form's Filter and FilterOn Settings, as it is more explicit (but you could use the ApplyFilter Method as well):

This should get you more closer:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ApplyFilter_Click()
  2.     Dim strFilter As String
  3.  
  4.     strFilter = ""
  5.     If Not IsNull(Me.cmbSearchDate) Then
  6.         strFilter = _
  7.             " AND [LineDate] = " & _
  8.             "#" & Format(Me.cmbSearchDate, "yyyy-mm-dd") & "#"
  9.     End If
  10.  
  11.     If Not IsNull(Me.CmbLineSearch) Then
  12.         strFilter = _
  13.             " AND [LineNoID] = " & _
  14.             Me.CmbLineSearch
  15.     End If
  16.  
  17.     If Not IsNull(Me.cmbShiftSearch) Then
  18.         strFilter = _
  19.             " AND [Shift] = " & _
  20.             Me.cmbShiftSearch
  21.     End If
  22.  
  23.     Debug.Print Mid(strFilter, 6)
  24.  
  25.     With Me.Form
  26.         .Filter = Mid(strFilter, 6)
  27.         .FilterOn = (Not strFilter = "")
  28.     End With
  29.  
  30. End Sub
Again, I don't know if you are dealing with strings, numbers or all dates in your Combo Boxes--So this code might not work just yet. However, since we are building a String, I've added a line to print the string in your immediate window, which will hepp in troubleshooting (always a good practice).

Hope this hepps!
1 Week Ago #7

100+
P: 161
Twinny,

2 combo boxes are numbers and one combo box is dates.

cmbSearchDate is a date field
cmbLinesearch is a number
cmbShiftSearch is a number

Expand|Select|Wrap|Line Numbers
  1. Private Sub ApplyFilter_Click()
  2.  
  3.     Dim strFilter As String
  4.  
  5.     If Not IsNull(Me!cmbSearchDate) Then
  6.  
  7.      strFilter = " AND [LineDate] = ""#" & _
  8.         Format(Me.cmbSearchDate, "mm/dd/yyyy") & "#"
  9.  
  10.     End If
  11.  
  12.     If Not IsNull(Me.CmbLineSearch) Then
  13.  
  14.         strFilter = " AND [LineNoID]" _
  15.             = "Me.CmbLineSearch"
  16.  
  17.     End If
  18.  
  19.     If Not IsNull(Me.cmbShiftSearch) Then
  20.  
  21.         strFilter = " AND [Shift]" _
  22.             = "Me.cmbShiftSearch"
  23.  
  24.     End If
  25.  
  26.     Debug.Print Mid(strFilter, 6)
  27.  
  28.     With Me.Form
  29.         .Filter = Mid(strFilter, 6)
  30.         .FilterOn = (Not strFilter = "")
  31.     End With
  32.  
  33. End Sub
Code compiles but nothing happens when I click the button. I did a compact and repair and tried again and nothing happens.
1 Week Ago #8

twinnyfo
Expert Mod 2.5K+
P: 3,257
I corrected my code to include the ampersands (&)--which should be something you look for during your own troubleshooting and fixed the date format to the internationally recognized date format. It's impossible for me to troubleshoot this code from long distance. However, the quotes around the arguments should have indicated something to you when you saw it.

Please copy and paste the code above and try again.
1 Week Ago #9

100+
P: 161
Sorry Twinny, I do not know enough to know when something needs an & or " or ' in it to even trouble shoot for it.

The code as it is now works almost. I think it needs to be looped, but that's just a guess. If I put just the Date it finds it, if I just put the lineno I want it works and so on. But if I put all 3 in it only searches the last one.

So I'm going to search the loop procedure for something like this. I have a couple of DB's that have loops in them, so I'll look at them to see how its structured.
1 Week Ago #10

100+
P: 161
I found an Allen Brown example I'm going to try tomorrow. Just looking at it we are close, just need to add some and's after each string, but I'm not 100% clear on it at this point.

Here's the link:http://allenbrowne.com/ser-62code.html
1 Week Ago #11

twinnyfo
Expert Mod 2.5K+
P: 3,257
I know the problem - this is the challenge of working long distance.

Here (notice lines 13 and 20):

Expand|Select|Wrap|Line Numbers
  1. Private Sub ApplyFilter_Click()
  2.     Dim strFilter As String
  3.  
  4.     strFilter = ""
  5.     If Not IsNull(Me.cmbSearchDate) Then
  6.         strFilter = _
  7.             " AND [LineDate] = " & _
  8.             "#" & Format(Me.cmbSearchDate, "yyyy-mm-dd") & "#"
  9.     End If
  10.  
  11.     If Not IsNull(Me.CmbLineSearch) Then
  12.         strFilter = _
  13.             strFilter & _
  14.             " AND [LineNoID] = " & _
  15.             Me.CmbLineSearch
  16.     End If
  17.  
  18.     If Not IsNull(Me.cmbShiftSearch) Then
  19.         strFilter = _
  20.             strFilter & _
  21.             " AND [Shift] = " & _
  22.             Me.cmbShiftSearch
  23.     End If
  24.  
  25.     Debug.Print Mid(strFilter, 6)
  26.  
  27.     With Me.Form
  28.         .Filter = Mid(strFilter, 6)
  29.         .FilterOn = (Not strFilter = "")
  30.     End With
  31.  
  32. End Sub
1 Week Ago #12

100+
P: 161
That work perfectly. I appreciate all your help. Now I need to go debug another issue I'm having.

Thanks again you are so awesome for helping me.
1 Week Ago #13

twinnyfo
Expert Mod 2.5K+
P: 3,257
Anytime, DJ!

Glad we could be of service.
1 Week Ago #14

Post your reply

Sign in to post your reply or Sign up for a free account.