Filtering a Form using VBA code

[Bkper087]

I have a split form, frmEntry with a query qryEntry as the record source. I am attempting to make a series of filters using VBA code. I know I can use the criteria field of the query to do some of this, but I have design reasons why I would prefer not to. I have done more research than I care to admit on this topic and I am still a bit confused. I understand I should be using Me.Filter, but I don’t understand how I connect that to the bound column of a listbox.



For instance, in the example below, I am attempting to use a list box as a selector in the header of my forum. When the option is chosen where the bound column = 1, I want it the filter to show all records where CategoriesID is 1. This however doesn’t seem to work. Any guidance would be appreciated.


Private Sub listboxCategories_AfterUpdate
Me.Filter = “CategoriesID = ‘1’”
Me.FilterOn = True
End Sub

[aytee111]

If CategoryID is a number then lose the single quotes.
Add Me.Requery at the end

[andy49]

'Always use a myfilter string

Dim myfilter as string
Myfilter = "categoriesID = " & lstbox.value

Debug.print "myfilter is " & myfilter

Me.filter =myfilter
Me.filteron = true
End sub

If it doesn't work, look in the immediate window to check the actual filter.

Makes it easier IMO

[Bkper087]

Ok, that did work. I am also trying to use this same principle with toggle buttons to filter the data based on one field. The data is stored as a number 1, 2, or 3. I want the user to be able to turn on or off the filter based on the toggles they have selected. For instance if toggle "A" is turned on, I want the data to filter down to show only data that is equal to "1". If toggle "A" & "B" are selected, I want the filter to show data for both "1" and "2". I thought I would be able to translate the method, but it doesn't seem to work that way.

[andy49]

An option group can only have one value (you can only select one button)

Do you have this?

[Bkper087]

No, I don't want an option group as it does limit you to only 1 option. I want the user to be able to select 1 or as many as 3 and have it filter the data accordingly. I created 3 separate toggle buttons.

[andy49]

Then you need a command button too to start vba code.

Something similar to

Private sub Cmdsearch_afterupdate

Dim myfilter as string
Dim counttgl as single
Myfilter = "categoryid="


For each ctl in me.controls
If (left(ctl.name,3) = "tgl") and ctl.value = -1 then
myfilter= myfilter & cint(right(ctl.name,1) & " or "
Counttgl = counttgl+1
End if
Next ctl

If counttgl =0 then myfilter=""

Myfilter = left(myfilter,(len(myfilter)-4))

Debug.print myfilter

Me.filter = myfilter
Me.filterOn = true

End sub


This is ok if your toggles are name tgl1,tgl2,tgl3

[ssanfu]

I tried the code above and found errors, so I rewrote it. Based on Andy49's code

There are 1 to 9 togglebuttons on the form.
Each togglebutton has a name the begins with 3 letters - "tgl" and then has a number 1 to 9.
Example name: tgl1

There is code in the click event of each togglebutton:

Private Sub tgl1_Click()
    Call SetFilter
End Sub

Private Sub tgl2_Click()
    Call SetFilter
End Sub

Private Sub tgl3_Click()
    Call SetFilter
End Sub

The code to set/clear the filter

Private Sub SetFilter()
    Const conFilterField As String = "categoryid = "

    Dim sFilter As String
    Dim myfilter As String
    Dim ctl


    For Each ctl In Me.Controls
        If ctl.ControlType = acToggleButton Then   ' is controltype a togglebutton?
            If (Left(ctl.Name, 3) = "tgl") And ctl.Value = True Then
                myfilter = myfilter & conFilterField & CInt(Right(ctl.Name, 1)) & " or "
            End If
        End If
    Next ctl

    If Len(Trim(myfilter)) = 0 Then
        Me.Filter = ""
        Me.FilterOn = False
    Else
        myfilter = Left(myfilter, (Len(myfilter) - 4))
        Me.Filter = myfilter
        Me.FilterOn = True
    End If

    '    Debug.Print Me.Filter

End Sub

Could also add a button (not toggle) that would clear the filter.
Button name is "btnClearFilter".
The button click event would be

Private Sub btnClearFilter_Click()
    Me.Filter = ""
    Me.FilterOn = False
    Me.tgl1 = False
    Me.tgl2 = False
    Me.tgl3 = False
End Sub

Had to add a check for control type because some controls do not have a Value property.

[Bkper087]

I am getting a "type mismatch" error on this line of code:

myfilter = myfilter & conFilterField & CInt(Right(ctl.Name, 1)) & " or "

[andy49]

Leave out cint and the first and last brackets