Solved How to make a form filter with Check boxes (1 Viewer)

[hrdpgajjar]

Hi I've created a data filter form in my access program. I have put 5 check boxes in the form which named as district (i.e. BK, SK, ARV, GN & NMD) now I've bound value to the check box as under, (note my first checkbox name is "Check0" and added code to after update event)

Private Sub Check0_AfterUpdate()
If Me.Check0.Enabled = True Then
Check0.Value = "BK"
Else
Check0.Value = ""


Me.Check0.Enabled = False
End If

what I have to do is if I select checkbox 1 then district will be "BK", if i select check box number two then district will be "SK" and so on.. I've to make data filter on basis of check box.

But the main problem happens here is "THE CHECK BOX GOT BLACK SQUERE AND I AM NOT ABLE TO UNCHECK THE CHECKBOX"

kindly help me how can i assign a value to a checkbox directly for filter data

thanks

 

[arnelgp]

do not make the Checkbox Bound.

on Each checkbox, add code to it's AfterUpdate Event:

Private Sub Check0_AfterUpdate()
    Call myFilter
End Sub
Private Sub Check1_AfterUpdate()
    Call myFilter
End Sub
Private Sub Check2_AfterUpdate()
    Call myFilter
End Sub
Private Sub Check3_AfterUpdate()
    Call myFilter
End Sub
Private Sub Check4_AfterUpdate()
    Call myFilter
End Sub
    
Public Function myFilter()
    Dim sCtlName As String
    Dim ctl As Control
    Dim bolValue As Boolean
    Dim sFilter As String
    sCtlName = Screen.ActiveControl.Name
    bolValue = Me(sCtlName).Value
    For Each ctl In Me.Controls
        If TypeOf ctl Is CheckBox Then
            ctl.Value = 0
        End If
    Next
    Me(sCtlName).Value = bolValue
    If bolValue Then
        Select Case sCtlName
        Case "Check0"
            sFilter = "FieldNameToFilter = 'BK'"
        Case "Check1"
            sFilter = "FieldNameToFilter = 'SK'"
        Case "Check2"
            sFilter = "FieldNameToFilter = 'ARV'"
        Case "Check3"
            sFilter = "FieldNameToFilter = 'GN"
        Case "Check4"
            sFilter = "FieldNameToFilter = 'NMD'"
        End Select
    End If
    If Len(sFilter) > 0 Then
        Me.Filter = sFilter
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
End Function

you need to repplace FieldNameToFilter with correct fieldname of your table/query.

 

[hrdpgajjar]

hey thanks for the prompt reply. will try it and reply

 

[Pat Hartman]

This would be more efficient as an option group. This would maintain the representation you have but since the option group is mutually exclusive, only ONE option is ever selected and so instead of having 5 controls to check, you would have 1.

My choice would be to use a combo or listbox though since although less appealing visually, the biggest benefit is that if you add options, you have no need to change any of the form's design.