Filter Continuous Form

[milkman2500]

Hi,

I'm trying to create a continuous form that allows users to filter. There are 2 combo boxes, "cbo_Field" and "cbo_Filter".

I want "cbo_Field" to be a list of the available fields to apply the filter.
I want "cbo_Filter" to be the value that is filtered (with wildcards at the front and back).

This video has a great example, however it's not working for me and I would prefer to complete it through VBA rather than a Macro.
http://www.youtube.com/watch?v=AFPe0Tv35bA

I created the event procedure "After Update()" in "cbo_Filter".

Private Sub cbo_Filter_AfterUpdate()
  ' If the combo box is cleared, clear the form filter.
  If Nz(Me.cbo_Filter.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
    
  ' If a combo box item is selected, filter for an exact match.
  ' Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.cbo_Filter.ListIndex <> -1 Then
    Me.Form.Filter = "[Survey_Job_Title] = '" & _
                     Replace(Me.cbo_Filter.Text, "'", "''") & "'"
    Me.FilterOn = True
  End If
  
  ' Move the cursor to the end of the combo box.
  Me.cbo_Filter.SetFocus
  Me.cbo_Filter.SelStart = Len(Me.cbo_Filter.Text)
End Sub

 

[TJPoorman]

Try making the changes below in red.

Hi,

I'm trying to create a continuous form that allows users to filter. There are 2 combo boxes, "cbo_Field" and "cbo_Filter".

I want "cbo_Field" to be a list of the available fields to apply the filter.
I want "cbo_Filter" to be the value that is filtered (with wildcards at the front and back).

This video has a great example, however it's not working for me and I would prefer to complete it through VBA rather than a Macro.
http://www.youtube.com/watch?v=AFPe0Tv35bA

I created the event procedure "After Update()" in "cbo_Filter".

Private Sub cbo_Filter_AfterUpdate()
  ' If the combo box is cleared, clear the form filter.
  If Nz(Me.cbo_Filter.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
 
  ' If a combo box item is selected, filter for an exact match.
  ' Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.cbo_Filter.ListIndex <> -1 Then
    Me.Form.Filter = "[[COLOR=red]" [/COLOR][COLOR=red]& Me.cbo_Field &[/COLOR][COLOR=red] "[/COLOR]] [COLOR=red]Like[/COLOR] '[COLOR=red]*[/COLOR]" & _
                     Replace(Me.cbo_Filter.Text, "'", "''") & "[COLOR=red]*[/COLOR]"
    Me.FilterOn = True
  End If
 
  ' Move the cursor to the end of the combo box.
  Me.cbo_Filter.SetFocus
  Me.cbo_Filter.SelStart = Len(Me.cbo_Filter.Text)
End Sub