Using cascading combo boxes and forms to filter data (1 Viewer)

Derhamo

New member
Local time
Today, 03:01
Joined
Apr 22, 2021
Messages
6
Hi all,

I’m pretty new to Access but not too shabby with Excel! I am hoping this is a simple query, but hoping someone can help me! I am using an arbitrary context to help simplify.

I have a form that will be used to pull data in the following way:

Combo box 1: Car Make (eg BMW)
Combo box 2: Car Model (eg 1 Series)
Combo box 3: Components (eg clutch)
Combo box 4: Requirement (eg safety feature)

I have succeeded in cascading the first 2 combo boxes so that combo box 2 only gives values dependent on the selection in combo box 1.

combo box 3 I’d like to populate with values determined by check boxes that are fields in one of my tables (if possible), that say whether the component is a part of the car model selected in combo box 2. I understand that the check box will give a 1 or 0 value but I don’t know how to relate that to selecting a table based on the value in combo box 2.

With combo boxes 1 to 3 then populated I’d then like to use a sub form that is a table of data pulled from those previous selections and then have combo box 4 act as a filter for one of the columns.

Further info:
Table1 contains car makes
Table2 contains car models and checkbox fields for components
Table 3 contains info on equipment requirements

I will need another table that has further information specific to the component in question from the combo box selection.

I’m sure I’ll need to answer a few more queries to get going but I’d appreciate some guidance if possible!

Thanks in advance,

Olli
 
Last edited:

mike60smart

Registered User.
Local time
Today, 03:01
Joined
Aug 6, 2017
Messages
780
Hi all,

I’m pretty new to Access but not too shabby with Excel! I am hoping this is a simple query, but hoping someone can help me! I am using an arbitrary context to help simplify.

I have a form that will be used to pull data in the following way:

Combo box 1: Car Make (eg BMW)
Combo box 2: Car Model (eg 1 Series)
Combo box 3: Components (eg clutch)
Combo box 4: Requirement (eg safety feature)

I have succeeded in cascading the first 2 combo boxes so that combo box 2 only gives values dependent on the selection in combo box 1.

combo box 3 I’d like to populate with values determined by check boxes that are fields in one of my tables (if possible), that say whether the component is a part of the car model selected in combo box 2. I understand that the check box will give a 1 or 0 value but I don’t know how to relate that to selecting a table based on the value in combo box 2.

With combo boxes 1 to 3 then populated I’d then like to use a sub form that is a table of data pulled from those previous selections and then have combo box 4 act as a filter for one of the columns.

Further info:
Table1 contains car makes
Table2 contains car models and checkbox fields for components
Table 3 contains info on equipment requirements

I will need another table that has further information specific to the component in question from the combo box selection.

I’m sure I’ll need to answer a few more queries to get going but I’d appreciate some guidance if possible!

Thanks in advance,

Olli
Hi Olli

Welcome to the Forum.

Can you upload a zipped copy of the database?
 

Derhamo

New member
Local time
Today, 03:01
Joined
Apr 22, 2021
Messages
6
Thanks! I hope what I have so far is on the right lines! Thoughts since my original post are:

2 separate forms
- First to search for brand then model (using Combo box 1 and 2) and produce a subform that uses the check box entries in tblModels to produce a subform, which is a filtered tblComponets.

- Second using stand alone combo box 3 and 4 tosearch through the components listed in tblComponents and produce a subform, which is a filtered table of the selected component, filtered by the selected requirement in Combobox 4.

Look forward to learning!
 

Attachments

  • Database1.zip
    80.3 KB · Views: 28

Derhamo

New member
Local time
Today, 03:01
Joined
Apr 22, 2021
Messages
6
Thaks @isladogs!

I think I can get to grips with the cascading combo boxes. I now have them populating with the values I want. I think it is now a question of how I translate that into a query that produces a subform table with entries filtered out based on my above briefs.
 

mike60smart

Registered User.
Local time
Today, 03:01
Joined
Aug 6, 2017
Messages
780
Thaks @isladogs!

I think I can get to grips with the cascading combo boxes. I now have them populating with the values I want. I think it is now a question of how I translate that into a query that produces a subform table with entries filtered out based on my above briefs.
Hi Derhamo
Can you explain the process where you intend to use these Cascading Combobox's?
Also, I would not structure the Models Table with the multiple Yes/No Fields for the Components.
The many Components are related to a specific Model and need to be in their own table.
You would normally have a Main Form based on Brands
Then a Subform based on Models
Then a Subform Based on Components

This Form would then allow you to do the Data Input for Brands, Models & Components.
 

Derhamo

New member
Local time
Today, 03:01
Joined
Apr 22, 2021
Messages
6
Hi @mike60smart, sure thing.

So on one form I would Select Combo box 1to choose a brand. Combo box 2 then populates with the models relating to that brand with the cascade idea.
From that I need to have something that shows all of the components relating to that model. The way I was hoping to do this was using columns 4 onward in tblModels to basically say which components apply to the selected model with check boxes as things may change. If a component is ticked then in the form/ or subform I wanted a list of the checked components, with the info from tblComponents.

The second form is similar, except combo box 3 was going to choose a component to pull a list of information from tbl"componentname"Materials, with combo box 4 filtering for a specific requirement (e.g. performance or safety).

If there's a better way i'm keen to learn how to do it! Not sure I can visualise what you mean by the multiple sub forms in terms of filtering etc.

Thanks for helping!
 

mike60smart

Registered User.
Local time
Today, 03:01
Joined
Aug 6, 2017
Messages
780
Hi @mike60smart, sure thing.

So on one form I would Select Combo box 1to choose a brand. Combo box 2 then populates with the models relating to that brand with the cascade idea.
From that I need to have something that shows all of the components relating to that model. The way I was hoping to do this was using columns 4 onward in tblModels to basically say which components apply to the selected model with check boxes as things may change. If a component is ticked then in the form/ or subform I wanted a list of the checked components, with the info from tblComponents.

The second form is similar, except combo box 3 was going to choose a component to pull a list of information from tbl"componentname"Materials, with combo box 4 filtering for a specific requirement (e.g. performance or safety).

If there's a better way i'm keen to learn how to do it! Not sure I can visualise what you mean by the multiple sub forms in terms of filtering etc.

Thanks for helping!
Hi

In the attached I have given you an example of how you would create your Forms for basic Data Input of the Brands, Models & Components.

You would then use these to manage your main Process.
 

Attachments

  • Database11.zip
    40.9 KB · Views: 29

Derhamo

New member
Local time
Today, 03:01
Joined
Apr 22, 2021
Messages
6
Hi

In the attached I have given you an example of how you would create your Forms for basic Data Input of the Brands, Models & Components.

You would then use these to manage your main Process.
Ah I think I see what you meant with the subforms. However, I don't want them to act as data entry points, purely just read only lists. I may just not be understanding correctly!
 

Derhamo

New member
Local time
Today, 03:01
Joined
Apr 22, 2021
Messages
6
Not sure if this illustration helps but I find it easier to see it visually and then connect the dots 😊
742F5C5C-0987-4200-A9D4-C3977E4EB81B.jpeg
 

Attachments

  • D2312383-FFA9-4F9B-9D11-4688BEC17719.jpeg
    D2312383-FFA9-4F9B-9D11-4688BEC17719.jpeg
    1.6 MB · Views: 18

mike60smart

Registered User.
Local time
Today, 03:01
Joined
Aug 6, 2017
Messages
780
Hi

The Data Input Forms are just the means of specifying for a Brand a number of Models and each Model has a number of Components.

Now you can expand your initial UnBound Form

Select a Brand in Combo1
Combo2 only displays Models associated with the Brand selected.
Combo3 only displays Components for the Model selected.

The Combobox's allow you to make a selection.

What do you then need to do with the selections made?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:01
Joined
Feb 19, 2002
Messages
31,957
A combo works with a SINGLE column. Your table is not normalized. You would need a separate control for each of the "flattened" options. OR normalize the table so that the options are rows in a table rather than columns in a table.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 03:01
Joined
Jul 9, 2003
Messages
13,038
Because your question is about Cars, I thought you might be interested in this:-

 

Users who are viewing this thread

Top Bottom