Continuous Form Filtering

[Jonny45wakey]

Hi
Hoping someone can assist me please?

I've been trying to filter a continuous form using checkboxes and cmd button and am nearly there apart from the filters not filtering data properly.

On my form i have 9 x checkboxes as shown, these should filter the query field [FILTERYEAR] according to selection (Only 1 combobox can be selected at any one time!)

Checkbox named "chk2022", "chk2023" and so on.

When a combobox is checked the year is automatically entered in invisible text box "txt129"

In addition to this i have a further 5 checkboxes shown below which select the query field [CELL] according to selection (Only 1 combobox can be selected at any one time!)

Checkbox named "chkASPEN", "chkAJAX" and so on

Finally a cmd button "cmd103" which when clicked i want it to fire the filters (only ever 2 checkboxes selected) as selected above using the year in txt129 and the
manufacturing division selected.

Hope this makes sense?

Thanks in advance

Jonny

 

[arnelgp]

something like this:

Dim var As Variant
Dim sFilter As String
Dim i As Integer
var = Array("Aspen", "Ajax", "Arch", "Bespoke", "Imperial")
'loop though each year
For i = 2022 To 2030
    If Me("chk" & i) Then
        sFilter = sFilter & "[FiscalYear] = " & i & " And "
        Exit For
    End If
Next
'loop though each division
For i = 0 To UBound(var)
    If Me("chk" & var(i)) Then
        sFilter = sFilter & "[Division] = '" & var(i) & "' And "
        Exit For
    End If
Next
If Len(sFilter) Then
    sFilter = Left$(sFilter, Len(sFilter) - 5)
    Me.Filter = sFilter
    Me.FilterOn = True
Else
    Me.FilterOn = False
End If

 

[Jonny45wakey]

something like this:

Dim var As Variant
Dim sFilter As String
Dim i As Integer
var = Array("Aspen", "Ajax", "Arch", "Bespoke", "Imperial")
'loop though each year
For i = 2022 To 2030
    If Me("chk" & i) Then
        sFilter = sFilter & "[FiscalYear] = " & i & " And "
        Exit For
    End If
Next
'loop though each division
For i = 0 To UBound(var)
    If Me("chk" & var(i)) Then
        sFilter = sFilter & "[Division] = '" & var(i) & "' And "
        Exit For
    End If
Next
If Len(sFilter) Then
    sFilter = Left$(sFilter, Len(sFilter) - 5)
    Me.Filter = sFilter
    Me.FilterOn = True
Else
    Me.FilterOn = False
End If

arnelgp, your code was plug and play, thanks very much

I know you helped me earlier with this post and I'm really grateful for your support and expertise

Kindest Regards

Jonny

 

[Jonny45wakey]

something like this:

Dim var As Variant
Dim sFilter As String
Dim i As Integer
var = Array("Aspen", "Ajax", "Arch", "Bespoke", "Imperial")
'loop though each year
For i = 2022 To 2030
    If Me("chk" & i) Then
        sFilter = sFilter & "[FiscalYear] = " & i & " And "
        Exit For
    End If
Next
'loop though each division
For i = 0 To UBound(var)
    If Me("chk" & var(i)) Then
        sFilter = sFilter & "[Division] = '" & var(i) & "' And "
        Exit For
    End If
Next
If Len(sFilter) Then
    sFilter = Left$(sFilter, Len(sFilter) - 5)
    Me.Filter = sFilter
    Me.FilterOn = True
Else
    Me.FilterOn = False
End If

arnelgp

Just wondering what could cause this error please when clicking the cmd button?

On Debug it displays the following in Code window

Thanks

Jonny

 

[arnelgp]

there is disadvantange on this approach since everytime you add Year or Division, you also need
to modify the Form and also the code.

what is simpler is to use Listbox rather than checkbox.
the rowsource of the listbox is a Select query:

select FiscalYear from yourTable Group by FiscalYear
select [Division] from yourTable Group By [Division].

whether you add/delete fiscalyear/division, you do not modify the
form or the code.

i hope i express myself clearly.

''''''''''''''''''''''
for the error, Enclosed each inside Nz() function:

...
If Nz(Me("chk" & i), False) Then
...

...
If Nz(Me("chk" & var(i)), False) Then
...

 

[Jonny45wakey]

there is disadvantange on this approach since everytime you add Year or Division, you also need
to modify the Form and also the code.

what is simpler is to use Listbox rather than checkbox.
the rowsource of the listbox is a Select query:

select FiscalYear from yourTable Group by FiscalYear
select [Division] from yourTable Group By [Division].

whether you add/delete fiscalyear/division, you do not modify the
form or the code.

i hope i express myself clearly.

''''''''''''''''''''''
for the error, Enclosed each inside Nz() function:

...
If Nz(Me("chk" & i), False) Then
...

...
If Nz(Me("chk" & var(i)), False) Then
...

Fully understood arnelgp

If i utilised listbox for the fiscal year, how would i amend the cmd button code to reflect this whilst keeping the checkboxes for the divisions as these arent likely to change?

Thanks

Jonny

 

[arnelgp]

Private Sub cmdFilter_Click()
Dim sFilter As String
'listbox for years
If Me.lstFiscalYear.ListIndex <> -1 Then
    sFilter = sFilter & "[FilterYear] = " & Me.lstFiscalYear & " And "
End If
'listbox for divisions
If Me.lstDivision.ListIndex <> -1 Then
    sFilter = sFilter & "[CELL] = '" & Me.lstDivision & "' And "
End If
If Len(sFilter) <> 0 Then
    sFilter = Left$(sFilter, Len(sFilter) - 5)
    Me.Filter = sFilter
    Me.FilterOn = True
Else
    Me.FilterOn = False
End If
End Sub

 

[Jonny45wakey]

Private Sub cmdFilter_Click()
Dim sFilter As String
'listbox for years
If Me.lstFiscalYear.ListIndex <> -1 Then
    sFilter = sFilter & "[FilterYear] = " & Me.lstFiscalYear & " And "
End If
'listbox for divisions
If Me.lstDivision.ListIndex <> -1 Then
    sFilter = sFilter & "[CELL] = '" & Me.lstDivision & "' And "
End If
If Len(sFilter) <> 0 Then
    sFilter = Left$(sFilter, Len(sFilter) - 5)
    Me.Filter = sFilter
    Me.FilterOn = True
Else
    Me.FilterOn = False
End If
End Sub

Thanks arnelgp

I have noticed with the original code that after clicking the cmd box to set the chkbox filters its deleting entries from the Division field in the recordsource, i cant seem to figure out why?

Any thoughts welcomed please.

Jonny