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

[Derhamo]

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

 

[mike60smart]

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]

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!

 

[isladogs]

Hi
No time to give a detailed response now but you might find my example app useful. It has 5 cascading combos:
Cascading Combo Boxes - Mendip Data Systems

 

[Derhamo]

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]

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]

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]

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.

 

[Derhamo]

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]

Not sure if this illustration helps but I find it easier to see it visually and then connect the dots

 

[mike60smart]

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]

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]

Because your question is about Cars, I thought you might be interested in this:-

Make Excel Sheets From Access Table – Nifty Access

www.niftyaccess.com