Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cascading Combos

Posted on 2012-03-12
6
Medium Priority
?
341 Views
Last Modified: 2012-03-13
I'm trying to create a simple Access database that allows me to use a form to enter vehicle information quickly by using cascading combo boxes. I need a "Year" combo box to drop down a list of vehicle years (say 1987 thru 2012), a "Make" combo box to drop down all of the diferent vehicle manufacturers available for the chosen "Year", and a "Model" combo box to drop down the available models for the chosen "Make".

I'm lost on the task of getting the data in the "Make" combo box to update based on the chosen "Year", and the data in the "Model" combo box to update based on the chosen "Make". I'm just starting out on this project and I have limited experience working with Access (or any other database software), and very limited experience working with code. Should I be using a different application for this project, say Visual Studio?
0
Comment
Question by:DavidGab
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 40

Assisted Solution

by:als315
als315 earned 1000 total points
ID: 37713181
Read this Article:
http://www.fontstuff.com/access/acctut10.htm
May be you can upload your DB with sample Data?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 37713538
Pesonally I don't see how Year leads to Make.   I would regard Year as a piece of data about the vehicle.

But you must have a table that includes all the combinations of Make and Model that you want to see in your list.  You can also have a separate table of Make as well but that is not strictly necessary for this question.

You base the Make combo (cboMake) on a query which is :
Select Distinct Make from tblMakeModel order by Make

You then base the Model combo (cboModel) on a query which is your version of:
Select Model from tblMakeModel where Make = Forms!YOURFormName!cboMake order by Model

To get the cboModel to update its list when cboMake changes you must use the Afterupdate event procedure of cboMake.
You need one  line of code added to the procedure.

Me!cboModel.Requery

As I said, I think Year is a piece of data about the vehicle.  It can be selected from a simple list of year numbers (stored in a table) although I'm not sure why you are not recording the registration date in full.
0
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 1000 total points
ID: 37716711
Fancy Filters formHere is a typical linked (or cascading) combo box setup:

cboSelectCustomer has tblCustomers as its row source.  Its AfterUpdate event sets cboSelectOrder to Null or "", and requeries cboSelectOrder.
cboSelectOrder has tblOrders as its row source, with a criterion of [Forms]![frmSelectOrder]![cboSelectCustomer]

However, as others have said, this may not be what you need.  See my Fancy Filters sample database for examples of creating concatenated filters with data of various types:

http://www.helenfeddema.com/Files/accarch129.zip

I think you might need a set of 4 linked combo boxes -- one for Year, one for Manufacturer (you don't have this now), one for Make and one for Model.  It depends on whether Year is an independent value, or the years that are selected are each linked to a set of manufacturers for that year.
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 37716722
For the 4 linked combo boxes, selecting Year would requery the list of manufacturers, selecting the manufacturer would requery the list of makes, and selecting a make would requery the list of models.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37716870
Pete.
<Pesonally I don't see how Year leads to Make.   I would regard Year as a piece of data about the vehicle.>

I agree.

What I think the OP needs here is a system that mimics the actual line of questioning a Customer might have to go through.
Typically when you bring your car in for service, you are asked for :
Year, Make, then Model
Most people in the US, when asked what kind of car they drive will reply in that order.
For example, If someone asked me, I would reply: 2005, Ford, Taurus

<LOL, but that is for the US.
Remember we are the ones who use the rather odd "mm/dd/YYYY" date format...>
;-)

So I think they just needed to understand the logic behind a three level cascading combobox system.

But yes, I do agree that it would be more logical to select the Make, Model, then Year...

;-)

Jeff
0
 

Author Closing Comment

by:DavidGab
ID: 37718110
Excellent solutions! The three level cascade is exactly what I want (Year, Make, Model), in that order if possible (I originally wrote manufacturer, but I meant "make"). Just to clear up the concerns about "Year" being independent data, I'd like to filter as much of the data as possible and an example would be the various drops in "Makes" recently (Oldsmobile, Pontiac, Saturn, etc.). For example, if I didn't use Year in the cascade and I were to choose 2009, the Make combo would still show Pontiac in the drop down, even though they do not exist anymore.

It will take me a few days to dissect the information above, especially the example .mdb file, but I think the solutions provided will definitely get the job done. I'll upload what I have so far - but I haven't applied the solutions yet. Suggestions are always welcome. Thanks, everyone here has been very helpful!
Vehicle-Management-System.accdb
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question