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

 

[Pat Hartman]

First, this is a maintenance problem because every year, you need to add a new year so I would not use this method. I would either just let the user type in the year or generate a list using the current year and the last x years or whatever.

However, if you like the visual presentation and don't mind the annual maintenance, then do NOT use individual checkboxes. Use an OptionGroup since you are selecting only ONE of the options. That gives you the same visual but instead of having 9 controls to test in the query, you have only one. The Option group requires a numeric value. It automatically assigns 1,2, etc but you can go back and change them to be the actual year. Then your query would be Where Year(SomeDate) = Forms!myform!OptYear

Same for that other option list except there it is more complicated. I would NOT use an option group for this list, I would use a combo. If you use an option group, the easiest solution is a second hidden field. There you would use a Case statement in the Click evento of the option group to populate the hidden field. case 1 = "Aspen", 2 = "Ajax", etc. Then the query would be Where SomeField = Forms!myform!HiddenOption

PS, I don't use filters. My forms are always filtered using the where clause of a query. This is because most of my database applications either use SQL Server from the start or might be converted later as the user count/data size grow. Using this technique saves a lot of work in the actual conversion phase and ensures that my Access apps are not like watching paint dry as they populate a form which yours would be using the filter method on a recordset of any size.