CONTINUOUS MASK FILTERED BY CASCADING COMBOXES (MANY-TO-MANY RELATIONSHIP):

[Faoineag]

I have a database of products, I need to enter as many details as possible for each product, I found very useful the Nested SubForms solution, so I created 4 nested subforms. Suppose I am entering the details of an athletics starting block, first I determine whether it belongs to the category race blocks or training blocks, then in the subform "TipoProdotto" I write "Race Blocks"; then I want to define the types of materials (in the subform "TipiMateriali" I indicate the types of materials whose details I will indicate, so: the material of the structure, the coating of the blocks, etc.); then in the subform "TipoMateriale" (here the name is confusing, I could have used just Material, I don't know why I didn't) I indicate the corresponding items, so if in "TipiMateriali" I select "structure material" in the subform "TipoMateriale" I write "aluminum", "steel", if in the subform "TipiMateriali" I select "block coating" in the subform "TipoMateriale" I will write "rubber", "synthetic surface", finally in the last subform, the form "Product", after selecting the values I want in the previous subforms I will write the name of the product.
In another mask, "SubCategoriesTypeProduct," I have two combined drop-down boxes that filter the query on which the subform is based, and so far everything works. What I would like to do is to also add the other two fields, "TipiMateriali" and "TipoMateriale" so that they filter the submask. In "Maschera2" I tested and inserted the combobox "TipiMateriali" and "TipoMateriale" because, for example, I would like to see the list of "competition starting blocks" (TipoProdotto) whose guiding material (TipiMateriali) is Aluminum (TipoMateriale), unfortunately, however, the subform remains empty.
"Maschera2" is based on the query "qryProducts_ProductsSubCategories". I attach the screenshots. I thank those who can help me

 

[MajP]

Make sure that your comboboxes are returning the correct field value. They likely display a text description, but need to return the ID value. If the wrong field is bound in the combo or the combo does not include the ID in the rowsource you will not be able to return records.

FYI. I think Maschere does not translate well. I assume it means Forms not Masks. We would call it a subform not a submask. This is a little confusing since a Mask in english in Access means something completely different.

Control data entry formats with input masks - Microsoft Support

Learn how to add input masks in Access to table fields, queries, and to form and report controls to help people entering data.

support.microsoft.com

 

[mike60smart]

I have a database of products, I need to enter as many details as possible for each product, I found very useful the Nested SubForms solution, so I created 4 nested subforms. Suppose I am entering the details of an athletics starting block, first I determine whether it belongs to the category race blocks or training blocks, then in the subform "TipoProdotto" I write "Race Blocks"; then I want to define the types of materials (in the subform "TipiMateriali" I indicate the types of materials whose details I will indicate, so: the material of the structure, the coating of the blocks, etc.); then in the subform "TipoMateriale" (here the name is confusing, I could have used just Material, I don't know why I didn't) I indicate the corresponding items, so if in "TipiMateriali" I select "structure material" in the subform "TipoMateriale" I write "aluminum", "steel", if in the subform "TipiMateriali" I select "block coating" in the subform "TipoMateriale" I will write "rubber", "synthetic surface", finally in the last subform, the form "Product", after selecting the values I want in the previous subforms I will write the name of the product.
In another mask, "SubCategoriesTypeProduct," I have two combined drop-down boxes that filter the query on which the subform is based, and so far everything works. What I would like to do is to also add the other two fields, "TipiMateriali" and "TipoMateriale" so that they filter the submask. In "Maschera2" I tested and inserted the combobox "TipiMateriali" and "TipoMateriale" because, for example, I would like to see the list of "competition starting blocks" (TipoProdotto) whose guiding material (TipiMateriali) is Aluminum (TipoMateriale), unfortunately, however, the subform remains empty.
"Maschera2" is based on the query "qryProducts_ProductsSubCategories". I attach the screenshots. I thank those who can help me

Are you able to upload a copy of the database?

 

[Faoineag]

Now the query is filtered, however, all the products that belong to the filters are listed (I still don't understand with what criteria) and the number of products doesn't change, whereas it doesn't. For example, if you look at the two screenshots, the products are always 13 regardless of what I select in the last two comboboxes.

 

[Faoineag]

Are you able to upload a copy of the database?

It's a bit complicated because I have to remove a lot of data, but I'll try, thanks

 

[MajP]

You may just need to scramble any private information fields. Here is a technique

Thread 'Scramble Text'

Nov 21, 2019

This function replaces every character with a random similar character.
I use this function to anonymize data in tables.

Almost but not quite entirely unlike the post made by Micron a few months ago.

Share & Enjoy!

Option Compare Binary
Option Explicit

Public Function ScrambleIt(strString As String) As String
    'Replace matching case and numbers
    'ScrambleIt("Kerkstraat 45, 8194AZ, Baarn")
    '           "Gibmfwptyi 50, 6491YQ, Jdmfz"
    Const cUAlpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Const cLAlpha As String = "abcdefghijklmnopqrstuvwxyz"
    Const cNum As...

• Guus2005
• Replies: 5
• Forum: Code Repository

 

[Faoineag]

I thank you for the help you gave me, but I decided to give up: I tried to recreate another database but it is very complicated, as is the other solution. However, I would ask you if you can show me some tutorials or videos or anything that can suggest some solution to filter a continuous mask with multiple cascading combo boxes linked together by many-to-many relationships. Thanks again

 

[MajP]

I tend not to do this in a query that uses control parameters. It gets overly complicated and it is not flexible.
Since I often build searchable forms with lots of controls, I have built a module that does all the work. I can add any number of controls with no problem.

Thread 'Get Filter from Controls, CSQL, and Combine Multiple Filters'

May 19, 2020

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require a lot of work up front, but in the long run it makes building applications so much easier.
Probably the biggest area I see people struggle and often painfully, is writing proper SQL to use in filters or other SQL code. The biggest issue is how to properly delimit literal values and pulling this...

• MajP
• Replies: 19
• Forum: Modules & VBA

• 

Solved Post in thread 'Multi combo box and textbox search'

Oct 14, 2023

OK here is my entire code if using my module

Public Function FilterForm()
 Dim BetweenFilter As String
 Dim DepFilter As String
 Dim CharterFilter As String
 Dim typeFilter As String
 Dim PersonFilter As String
 Dim supplierFilter As String
 Dim invoiceFilter As String
 Dim invTotalFilter As String
 Dim fltr As String
 
 DepFilter = GetFilterFromControl(Me.DepartmentS, , , , "Department")
 CharterFilter = GetFilterFromControl(Me.CharterS, , , , "Charter")
 typeFilter = GetFilterFromControl(Me.TypeS, , , , "Type")
 PersonFilter = GetFilterFromControl(Me.PersonS, , , , "Person")...

• MajP

• 

 

[Faoineag]

I recreated the same db from which I copied the idea of the nested subforms and here too the error occurs again: if you go to the TipoProdotto form, select:
Competition
Spar material
Aluminum
you see
PBS17
or
Competition
Spar material
Steel
you see
PBS15

then if you go to the "frmProdotti" form and in the "Product Type" combos select Competition Starting Blocks, in "TipoMateriali" select Spar Material and in "Material" select Aluminium, 6 products appear.


@MajP Now I'll look at your answer, thanks

 

[mike60smart]

I recreated the same db from which I copied the idea of the nested subforms and here too the error occurs again: if you go to the TipoProdotto form, select:
Competition
Spar material
Aluminum
you see
PBS17
or
Competition
Spar material
Steel
you see
PBS15

then if you go to the "frmProdotti" form and in the "Product Type" combos select Competition Starting Blocks, in "TipoMateriali" select Spar Material and in "Material" select Aluminium, 6 products appear.

Hi

Hi When i go into the Prodotti Subform there is only an option to select up to 5 Products?
There is no need for any Cascading for anything.
You have a straight Main Form with Multiple Subforms

I think you need to define which materials belong with each Product.

Can you let us know which materials belong with Each Product?

PBS17 - list Materials available
PBS15 - same
PBS15-T - Same
10116 - same

@MajP Now I'll look at your answer, thanks

 

[MajP]

This works for me. You will have to change [Forms] back to [Maschere]. I had to change it to work. This uses the technique from my thread.
Code:

Dim fltrProdotto As String
  Dim fltrTM As String
  Dim fltrMat As String
  Dim fltr As String
  'one per control
  fltrProdotto = GetFilterFromControl(Me.cboTipiMateriali)
  fltrTM = GetFilterFromControl(Me.cboTipiMateriali)
  fltrMat = GetFilterFromControl(Me.cboMateriale)
 
  fltr = CombineFilters(ct_And, fltrProdotto, fltrTM, fltrMat)
  If fltr <> "" Then
    Me.Filter = fltr
    Me.FilterOn = True
  Else
    Me.Filter = ""
    Me.FilterOn = False
  End If

As I stated in my thread, you can add as may controls as you want the code grows by one variable and one line per each control.

 

[Faoineag]

Hi When i go into the Prodotti Subform there is only an option to select up to 5 Products?
There is no need for any Cascading for anything.
You have a straight Main Form with Multiple Subforms

I think you need to define which materials belong with each Product.

Can you let us know which materials belong with Each Product?

PBS17 - list Materials available
PBS15 - same
PBS15-T - Same
10116 - same

Thanks Mike, so I can use the "nested forms" form not only to insert the characteristics of each product (I inserted this subform in the product sheet so that I can insert, for each product, all the characteristics that interest me), but also to do a product search starting from the "Product Type" value (i.e., by selecting the ProductType, the MaterialTypes and the Material, I can see which and how many products have this characteristics)? Out of curiosity but above all my personal Access culture, could you tell me what the error is in my method? that is, use a continuous mask with all the combos filtering. Thanks again

 

[mike60smart]

Thanks Mike, so I can use the "nested forms" form not only to insert the characteristics of each product (I inserted this subform in the product sheet so that I can insert, for each product, all the characteristics that interest me), but also to do a product search starting from the "Product Type" value (i.e., by selecting the ProductType, the MaterialTypes and the Material, I can see which and how many products have this characteristics)? Out of curiosity but above all my personal Access culture, could you tell me what the error is in my method? that is, use a continuous mask with all the combos filtering. Thanks again

MajP has sorted out your Search Form
For your Data Input Forms I added a Combobox for materials
Now when you select a Product only those materials associated with that product are displayed.

 

[Faoineag]

This works for me. You will have to change [Forms] back to [Maschere]. I had to change it to work. This uses the technique from my thread.
Code:

Dim fltrProdotto As String
  Dim fltrTM As String
  Dim fltrMat As String
  Dim fltr As String
  'one per control
  fltrProdotto = GetFilterFromControl(Me.cboTipiMateriali)
  fltrTM = GetFilterFromControl(Me.cboTipiMateriali)
  fltrMat = GetFilterFromControl(Me.cboMateriale)
 
  fltr = CombineFilters(ct_And, fltrProdotto, fltrTM, fltrMat)
  If fltr <> "" Then
    Me.Filter = fltr
    Me.FilterOn = True
  Else
    Me.Filter = ""
    Me.FilterOn = False
  End If

As I stated in my thread, you can add as may controls as you want the code grows by one variable and one line per each control.

Thanks MajP, I'm still too ignorant to understand your code, but in the database I noticed that, by selecting the same values in the "Product Type" frm and in the "Products frm", the result is different, as can be seen in the screenshot, or in the frm "Product Type" there is only one product, while in the "Products" form there are 4 products (but only one correct one, the PBS17)

 

[MajP]

You forms query does not make any sense as far as I can tell. You are missing a join so you have created a cartesian product.

 

[Faoineag]

Thanks MajP, I added the join, I modified the relationships, but now the query no longer returns any value, where am I wrong? Thank you

 

[MajP]

As far as I can tell this is not correctly designed. I can not make any sense out of it.
Can you explain what is in each table and how they relate. The relationship does not look correct and neither does the query. You have foreign keys in multiple tables that are not used.

I assume Prodotti (Products) is the top level table of Products that are made

I think that junction table (ProdottoMateraile) might have too many foreign keys
1. If TipoProdtto is related to TipiMateriali then you make the relationship through those tables and there is no foreign key in the ProdottoMateriale
2. If Materiale relates to TipiMateriali you relate through those tables and no foreign key in Prodotto

My first guess is this is the correct design.

 

[Faoineag]

As far as I can tell this is not correctly designed. I can not make any sense out of it.
Can you explain what is in each table and how they relate. The relationship does not look correct and neither does the query. You have foreign keys in multiple tables that are not used.

View attachment 115094

I assume Prodotti (Products) is the top level table of Products that are made

I think that junction table (ProdottoMateraile) might have too many foreign keys
1. If TipoProdtto is related to TipiMateriali then you make the relationship through those tables and there is no foreign key in the ProdottoMateriale
2. If Materiale relates to TipiMateriali you relate through those tables and no foreign key in Prodotto

My first guess is this is the correct design.
View attachment 115095

@MajP Your hypothesis is correct, in the sense that it works on the example database I sent you. Unfortunately, not in the original database, but having your trace, I try to understand the reason for the error. Thanks again

 

[MajP]

The code works fine, it is the query that is incorrect and likely the table design. If you get a correct query the code will still work.