Multiple Combo Boxes to Filter a Form

[jlgray0127]

I have a form and am trying to use to combo boxes to filter a form.
I need the form to filter by one or both combo boxes. If one is blank, then only filter by the other...
Any assistance is greatly appreciated!

I am not sure what I am doing incorrectly here.

I have two combo boxes:
[SCategory] Number Field
[GenerateDate] Date and Time Field

I am receiving a Run time error






This is the Code I've adhoc'd from AllenBrown's search2000 sample database.


Private Sub SCategory_AfterUpdate()
If Not IsNull(Me.SCategory) Then
strWhere = strWhere & "([SOP_Category] = ' " & Me.SCategory & " ' ) AND "
Else
Me.Filter = strWhere
Me.FilterOn = True
End If
If Not IsNull(Me.GenerateDate) Then
strWhere = strWhere & "([Generated] = & Me.GenerateDate & "
Else
Me.Filter = strWhere
Me.FilterOn = True


End If


End Sub

[moke123]

where do you declare Dim strWhere as string?

I would write it as a separate procedure. Something like (aircode)

Private Sub MyFilter()

    Dim strFilter As String

    If Not IsNull(Me.SCategory) Then

        strFilter = strFilter & " And " & "SOP_Category =""" & Me.SCategory & """"
 
    End If

    If Not IsNull(Me.GenerateDate) Then

        strFilter = strFilter & " And " & "Generated = #" & Me.GenerateDate & "#"

    End If

    strFilter = Mid(strFilter, 5)

    debug.print strFilter

If nz(strFilter,"")<>"" then  
  
     Me.Filter = strFilter
     Me.FilterOn = True

end if


End Sub

Then you can call it in the afterupdate of both combos

[jlgray0127]

Thank you. I am not 100% sure I am following this.
I do not understand the logic behind this step, if you could help me understand what the code is doing and why I would need this, I would really appreciate it!



I'm not sure how this works.

[Bob Fitz]

Thank you. I am not 100% sure I am following this.
I do not understand the logic behind this step, if you could help me understand what the code is doing and why I would need this, I would really appreciate it!



I'm not sure how this works.

Have you tried stepping through the code at runtime and examining the various values?

[Bob Fitz]

BTW Just wondering if the code might need amending to turn off the filter when both combos are null.

[June7]

Do you want to allow filter by either or both or none?

Review http://allenbrowne.com/ser-62.html

[moke123]

BTW Just wondering if the code might need amending to turn off the filter when both combos are null.

probably but that was just air code to give some guidance.

Here's a simple example

[jlgray0127]

hi June7!
Yes, that is exactly what I am trying to accomplish. Filter either, both or none.

Unfortunately, the code I am trying to use provided by Moke is not working. It's capturing the field names, as well as the value. We keep updating the statement but just can not seem to get it to provide the values only. I will take a look at the Allenbrowne link you provided, as well. I was attempting to modify his Search2000 code orignially, to work this. I use his samples alot! Thank you for reaching out!!!

[jlgray0127]

probably but that was just air code to give some guidance.

Here's a simple example

Thank you, Moke. I downloaded this and tried it, and it is not filtering for me.
After I update and move to Lname, nothing happens. Then when I select a value in Lname and tab out, nothing is filtering.
I double checked all my options to ensure it was not something on my end where I was blocking the code from running, and I'm basically running allowing all code, activeX and macros to run right now.

[jlgray0127]

Do you want to allow filter by either or both or none?

Review http://allenbrowne.com/ser-62.html

This is the one I downloaded and attempted to modify to work on my database.
Thank you for sharing, though!

[Bob Fitz]

@jlgray0127

Can you post a copy of your db with a few fictitious records

[moke123]

Thank you, Moke. I downloaded this and tried it, and it is not filtering for me.
After I update and move to Lname, nothing happens. Then when I select a value in Lname and tab out, nothing is filtering.
I double checked all my options to ensure it was not something on my end where I was blocking the code from running, and I'm basically running allowing all code, activeX and macros to run right now.

Did you remember to remove the Mark of the web?
Right click on the file and check the unblock box?

[Welshgasman]

Moke's DB in post 7 works for me?

[moke123]

Moke's DB in post 7 works for me?

Did you have any doubts, GMan?

Thanks for the confirmation. It always freaks me out a little when someone says my example doesn't work.