Form with subform filtered by two cascading combo boxes and a checkbox

Faoineag · 2025-05-11T12:58:18+0100

Good morning everyone, I would like to filter a form with a datasheet subform with two cascading combo boxes and a checkbox. The form is called "SottoCategorie_Attrezzature_Atetica", the subform smProdotti_Attrezzature_Atetica; in the header of the form I inserted the two combo boxes and the checkbox; the first combo box is called cboSceltaSottoCategorie, the second combo box is called "cboSceltaTipo", the checkbox is called chkCatEmmeti.
On the After Update event of cboSceltaSottoCategorie I inserted this code:
Private Sub cboSceltaSottoCategorie_AfterUpdate()

Me.cboSceltaTipo.RowSource = "SELECT IDTipoProdotto, TipoProdotto FROM qrySottoCategorie_TipoProdotto02 WHERE IDSottoCategorie = " & Nz(Me.cboSceltaSottoCategorie, 0)
Me.cboSceltaTipo.Requery
Call FiltraSottomaschera

End Sub

On the After Update event of cboSceltaTipo and chkCatEmmeti I inserted this code:
Private Sub cboSceltaTipo_AfterUpdate()
Call FiltraSottomaschera
End Sub

Private Sub chkCatEmmeti_AfterUpdate()
Call FiltraSottomaschera
End Sub

In the header of the form I inserted this code:

Private Sub FilterSubMask()
Dim filter As String
filter = ""

' Filter by SubCategories
If Not IsNull(Me.cboChoiceSubCategories) Then
filter = filter & "IDSubCategories = " & Me.cboChoiceSubCategories & " AND "
End If

' Filter by Product Type
If Not IsNull(Me.cboChoiceType) Then
filter = filter & "IDTypeProduct = " & Me.cboChoiceType & " AND "
End If

' Filter by Checkbox
If Me.chkCatEmmeti = True Then
filter = filter & "CatEmmeti = True AND "
End If

' Remove the last " AND " if present
If filter <> "" Then
filter = Left(filter, Len(filter) - 5)
End If

' Apply the filter to the subform
Me.Filter = filter
Me.FilterOn = (filter <> "")

End Sub

The filter does not work, in the sense that the second combo box (cboSceltaTipo) does not filter and in the subform all the values filtered by the first box cboSceltaSottoCategorie are returned. Where am I going wrong?
Thanks

cheekybuddha · 2025-05-11T13:04:25+0100

Code:
Code:

Dim filter As String

Try changing to:

Code:

Dim strfilter As String

and change all instances of the variable filter to strfilter
It's unwise to use a reserved word as a variable name.

CJ_London · 2025-05-11T13:19:03+0100

In addition, use debug.print to verify the code is creating the filter correctly

arnelgp · 2025-05-11T13:35:52+0100

if you are not using combobox cboSceltaTipo in your code, then how will it apply the filter?

Faoineag · 2025-05-11T13:40:36+0100

@cheekybuddha I changed the filter name as you indicated, but the result does not change;
@CJ_London: I inserted in the sub FilterSubform Debug.Print strfiltro and in the immediate window this appeared:
IDSottoCategorie = 348 AND
IDSottoCategorie = 348 AND IDTipoProdotto = 113 AND
IDSottoCategorie = 348 AND IDTipoProdotto = 113 AND CatEmmeti = True AND
so it seems correct to me, therefore I do not understand why the submask is not filtered

Faoineag · 2025-05-11T13:43:38+0100

arnelgp said:
if you are not using combobox cboSceltaTipo in your code, then how will it apply the filter?

@arnelgp: maybe I don't understand the question, but the filter on cboSceltaTipo is not this:
' Filter for Product Type
If Not IsNull(Me.cboSceltaTipo) Then
strfiltro = strfiltro & "IDTipoProdotto = " & Me.cboSceltaTipo & " AND "
End If

cheekybuddha · 2025-05-11T14:25:31+0100

Faoineag said:
so it seems correct to me, therefore I do not understand why the submask is not filtered

Are you doing the Debug.Print before you chop off the trailing 'AND'?

(All the strings you posted still have 'AND' at the end)

cheekybuddha · 2025-05-11T14:29:34+0100

I think this might be the problem:

Code:

' ...
' Apply the filter to the subform
  Me.Filter = filter
  Me.FilterOn = (filter <> "")
' ...

Surely it should be:

Code:

' ...
' Apply the filter to the subform
  Me.smProdotti_Attrezzature_Atetica.Form.Filter = filter
  Me.smProdotti_Attrezzature_Atetica.Form.FilterOn = (filter <> "")
' ...

(Also double-check that the subform *control* has the name 'smProdotti_Attrezzature_Atetica' - it may be different (eg 'Child0') in which case you must use that name.)

Eugene-LS · 2025-05-11T14:47:02+0100

Faoineag said:
I would like to filter a form with a datasheet subform ... The filter does not work

Instead of Me.Filter you should use: Me.[Sub Form Object Name].Form.Filter

Code:

Private Sub FilterSubMask()
Dim sFilter As String

' Filter by SubCategories
    If Me.cboChoiceSubCategories.ListIndex > -1 Then ' Value selected
        sFilter = sFilter & " AND (IDSubCategories = " & Me.cboChoiceSubCategories & ")"
    End If

' Filter by Product Type
    If Me.cboChoiceType.ListIndex > -1 Then ' Value selected
        sFilter = sFilter & " AND (IDTypeProduct = " & Me.cboChoiceType & ")"
    End If

' Filter by Checkbox
    If Me.chkCatEmmeti = True Then
        sFilter = sFilter & " AND (CatEmmeti = -1)"
    End If

' Remove the First " AND " if present
    If Len(sFilter) > 0 Then sFilter = Mid(sFilter, 6)

' Instead of Me.Filter you should use: Me.[Sub Form Object Name].Form.Filter
    With Me.[Sub Form Object Name].Form
        .Filter = sFilter ' Apply the sFilter to the subform
        .FilterOn = (Len(sFilter) > 0)
    End If
  
End Sub

... Replace "[Sub Form Object Name]" by your obect name.

Faoineag · 2025-05-11T14:57:14+0100

@cheekybuddha
I checked the name of the subform control and it is Prodotti_Attrezzature_Atletica; after changing the code like this:
Private Sub FiltraSottomaschera()
Dim strfiltro As String
strfiltro = ""

' Filtro per SottoCategorie
If Not IsNull(Me.cboSceltaSottoCategorie) Then
strfiltro = strfiltro & "IDSottoCategorie = " & Me.cboSceltaSottoCategorie & " AND "
End If

' Filtro per Tipo Prodotto
If Not IsNull(Me.cboSceltaTipo) Then
strfiltro = strfiltro & "IDTipoProdotto = " & Me.cboSceltaTipo & " AND "
End If

' Filtro per casella di controllo
If Me.chkCatEmmeti = True Then
strfiltro = strfiltro & "CatEmmeti = True "
End If

Debug.Print strfiltro

' Rimuovi l'ultimo " AND " se presente
If strfiltro <> "" Then
strfiltro = Left(strfiltro, Len(strfiltro) - 5)
End If

' Applica il filtro alla sottomaschera

Me.Prodotti_Attrezzature_Atletica.Form.Filter = Filter
Me.Prodotti_Attrezzature_Atletica.Form.FilterOn = (Filter <> "")

End Sub

when I select an item from the first combo box the subform becomes completely empty. In the immediate window it says this:
IDSottoCategorie = 348 AND
IDSottoCategorie = 348 AND IDTipoProdotto = 113 AND
IDSottoCategorie = 348 AND IDTipoProdotto = 113 AND CatEmmeti = True

How do I enter the code correctly here on the forum?

Faoineag · 2025-05-11T15:02:12+0100

Eugene-LS said:

Instead of Me.Filter you should use: Me.[Sub Form Object Name].Form.Filter

Code:

Private Sub FilterSubMask()
Dim sFilter As String

' Filter by SubCategories
    If Me.cboChoiceSubCategories.ListIndex > -1 Then ' Value selected
        sFilter = sFilter & " AND (IDSubCategories = " & Me.cboChoiceSubCategories & ")"
    End If

' Filter by Product Type
    If Me.cboChoiceType.ListIndex > -1 Then ' Value selected
        sFilter = sFilter & " AND (IDTypeProduct = " & Me.cboChoiceType & ")"
    End If

' Filter by Checkbox
    If Me.chkCatEmmeti = True Then
        sFilter = sFilter & " AND (CatEmmeti = -1)"
    End If

' Remove the First " AND " if present
    If Len(sFilter) > 0 Then sFilter = Mid(sFilter, 6)

' Instead of Me.Filter you should use: Me.[Sub Form Object Name].Form.Filter
    With Me.[Sub Form Object Name].Form
        .Filter = sFilter ' Apply the sFilter to the subform
        .FilterOn = (Len(sFilter) > 0)
    End If
 
End Sub

... Replace "[Sub Form Object Name]" by your obect name.

Even if I add square brackets to the subform name, when I select an item from the first combo box the subform becomes completely empty.

cheekybuddha · 2025-05-11T15:08:39+0100

Check this part:

Code:

' ...
' Filtro per casella di controllo
  If Me.chkCatEmmeti = True Then
    strfiltro = strfiltro & "CatEmmeti = True "
  End If
' ...

Adjust to:

Code:

' ...
' Filtro per casella di controllo
  If Me.chkCatEmmeti = True Then
    strfiltro = strfiltro & "CatEmmeti = True AND "  ' <-- include AND since you will be chopping it off
  End If
' ...

cheekybuddha · 2025-05-11T15:09:20+0100

Faoineag said:
How do I enter the code correctly here on the forum?

Click the button on the reply editor that looks like:
</>

Eugene-LS · 2025-05-11T15:11:51+0100

Faoineag said:
becomes completely empty.

Okay, post your form and some data - we need to see what's wrong with it.

Faoineag · 2025-05-11T21:24:29+0100

I have eliminated as much useless data as possible for the purposes of this filtering research that I am doing, I hope it is sufficient. Thanks for your time.
I inserted the file on WeTransfer but after inserting the link here on the forum I cannot send, I do not remember how I did it last year for a similar situation, how can I get you the file?

cheekybuddha · 2025-05-11T21:25:53+0100

Can you attach directly?

Otherwise you will need 10 more posts before you can post a link! (You now need to have 100 before you can post a link)

CJ_London · 2025-05-11T22:48:02+0100

Make sure you compact then zip your file before uploading

Faoineag · 2025-05-12T09:17:03+0100

In the first attempt to send it I forgot to compact the db, I attach the screenshot of the new link from which to download the db. Thank you

CJ_London · 2025-05-12T09:28:57+0100

I and many others will not use links, particularly from someone we don’t know. So I will not contribute further unless you attach the file

CJ_London · 2025-05-12T09:37:05+0100

As a tip for future filter/criteria building put the AND first rather than last
FltrStr=fltrstr & “ AND field1=true”

Then once completed remove the first AND using the mid function

Filter=mid(fltrStr, 6)

Form with subform filtered by two cascading combo boxes and a checkbox

Registered User.

AWF VIP

Super Moderator

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

AWF VIP

AWF VIP

Registered User.

Registered User.

Registered User.

AWF VIP

AWF VIP

Registered User.

Registered User.

AWF VIP

Super Moderator

Registered User.

Attachments

Super Moderator

Super Moderator

Similar threads

Users who are viewing this thread