G
Guest
Can anyone point me in the direction of cascading combo
boxes pref with examples.
Thanks
Clive
boxes pref with examples.
Thanks
Clive
D
Douglas J. Steele
http://www.mvps.org/access/forms/frm0028.htm at "The Access Web" is one
example.
example.
G
gavin
Douglas J. Steele said:
I have been trying to find out how to do this for months (literally) with
little success. I have found examples of it working but haven't been able to
work out how to implement it in my real life scenario. I'm afraid the
explanation at http://www.mvps.org/access/forms/frm0028.htm is a little over
my head (this is the "gettingstarted" group!!!). Does anyone have the
time/inclination to talk me (and possibly Clive?) through it step by step? I
would be very, very grateful!
Regards,
Gavin
K
Ken Snell [MVP]
Gavin -
Sure. Where do you want to start?
Sure. Where do you want to start?
D
Douglas J. Steele
The "trick" is that you redefine the recordsource for the second combobox in
the first combobox's AfterUpdate event. In other words, once you know what
value has been selected in the first combobox, you use that value to
determine what should appear in the second combobox.
Let's assume your comboboxes are named cboState and cboCity
If the SQL for cboCity would have been SELECT CityId, CityNm FROM Cities,
but you want to limit it to those rows where StateId equals whatever was
selected in cboState,
Private Sub cboState_AfterUpdate()
Dim strSQL As String
strSQL = "Select CityId, CityNm from Cities " & _
"WHERE StateId = " & Me.cboState
Me!cboCity.RowSourceType = "Table/Query"
Me!cboCity.RowSource = strSQL
End Sub
This assumes that cboState has whatever column contains the State ID as its
bound column.
the first combobox's AfterUpdate event. In other words, once you know what
value has been selected in the first combobox, you use that value to
determine what should appear in the second combobox.
Let's assume your comboboxes are named cboState and cboCity
If the SQL for cboCity would have been SELECT CityId, CityNm FROM Cities,
but you want to limit it to those rows where StateId equals whatever was
selected in cboState,
Private Sub cboState_AfterUpdate()
Dim strSQL As String
strSQL = "Select CityId, CityNm from Cities " & _
"WHERE StateId = " & Me.cboState
Me!cboCity.RowSourceType = "Table/Query"
Me!cboCity.RowSource = strSQL
End Sub
This assumes that cboState has whatever column contains the State ID as its
bound column.
G
gavin
Hi Ken,
Sorry for the delay in replying - I can't access this forum from my work PC
and the database I was working on was at work.
My scenario is that I have an Access database to track the requests for
information we get at our tourist information centre. The information I need
to record is contact details for the person needing information (i.e. name,
address, email, phone etc) and the information they require, when they
requested it and how (in person, by letter, email, phone), how we actioned
their request and when.
In order to make data entry simpler for users I thought it would be good to
put the "information requested" in a drop down box. However, the list of
topics could get quite long so I thought it would be neat to have two drop
down boxes. The first would have a list of possible topics (accommodation,
events, information, tickets) and the second box would display
sub-categories depending on the selection in the first box, i.e if
"Accommodation" was selected in the first box the second box would have the
choices "hotels", "B&B" "hostels" "campsites".
First of all I need to get the structure of the database right! I am
assuming that I will need more than one table (drat!!!)? I reckon 3? The
main table for storing information about the request (who made it, when, the
action taken and when), one for the top level category for the nature of the
request and one for the second level? If this is wrong please correct me!
Then I'll need to work out relationships - primary keys and foreign keys. I
would appreciate help here!
Lastly the process of getting this feature to work. I have seen a few
examples on various websites but the explanations haven't made much sense to
me. From what I can remember there was an AfterUpdate event attached to
either the first combo box or both combo plus a query?
I would really appreciate any time you can give to this.
Regards,
Gavin
Sorry for the delay in replying - I can't access this forum from my work PC
and the database I was working on was at work.
My scenario is that I have an Access database to track the requests for
information we get at our tourist information centre. The information I need
to record is contact details for the person needing information (i.e. name,
address, email, phone etc) and the information they require, when they
requested it and how (in person, by letter, email, phone), how we actioned
their request and when.
In order to make data entry simpler for users I thought it would be good to
put the "information requested" in a drop down box. However, the list of
topics could get quite long so I thought it would be neat to have two drop
down boxes. The first would have a list of possible topics (accommodation,
events, information, tickets) and the second box would display
sub-categories depending on the selection in the first box, i.e if
"Accommodation" was selected in the first box the second box would have the
choices "hotels", "B&B" "hostels" "campsites".
First of all I need to get the structure of the database right! I am
assuming that I will need more than one table (drat!!!)? I reckon 3? The
main table for storing information about the request (who made it, when, the
action taken and when), one for the top level category for the nature of the
request and one for the second level? If this is wrong please correct me!
Then I'll need to work out relationships - primary keys and foreign keys. I
would appreciate help here!
Lastly the process of getting this feature to work. I have seen a few
examples on various websites but the explanations haven't made much sense to
me. From what I can remember there was an AfterUpdate event attached to
either the first combo box or both combo plus a query?
I would really appreciate any time you can give to this.
Regards,
Gavin
K
Ken Snell [MVP]
Gavin -
I'm tied up on a project at the moment, but will get back to you. Have you
obtained a book on ACCESS to help you get started on how to design the
database tables and such? That may be very helpful as a start. I will post
back shortly.
I'm tied up on a project at the moment, but will get back to you. Have you
obtained a book on ACCESS to help you get started on how to design the
database tables and such? That may be very helpful as a start. I will post
back shortly.
G
gavin
Hi Ken,
I have been using Access for a while but I am never very confident when it
comes to having more than one table. If I can keep my data all in one table
then I can happily build a few queries and pat myself on the back for being
rather clever
In this case I ma not sure about the structure of the database since I am
sure it will affect how the combo boxes work.
If you get time to get back to me I would be very pleased
Regards,
Gavin
I have been using Access for a while but I am never very confident when it
comes to having more than one table. If I can keep my data all in one table
then I can happily build a few queries and pat myself on the back for being
rather clever
In this case I ma not sure about the structure of the database since I am
sure it will affect how the combo boxes work.
If you get time to get back to me I would be very pleased
Regards,
Gavin
K
Ken Snell [MVP]
Comments inline...
--
Ken Snell
<MS ACCESS MVP>
Good thought.
Another good thought.
For a start, I see five tables for the simple database that you've
described:
tblCallers
CallerID (IDs the caller's record - primary key)
CallerName
(etc. - info related to the caller himself/herself)
tblCategories
CategoryID (IDs the category - primary key)
CategoryName
tblSubCategories
SubcategoryID (IDs the subcategory - primary key)
SubcategoryName
tblCatSubcat
CategoryID (composite primary key with SubcategoryID -- foreign key
to tblCategories table)
SubcategoryID (composite primary key with CategoryID -- foreign key
to tblSubcategories table)
tblCallerDetails
CallerID (composite primary key with CallerDateOfCall, CategoryID, and
SubcategoryID -- foreign key to tblCallers table)
CallerDateOfCall (composite primary key with CallerID, CategoryID, and
SubcategoryID)
CategoryID (composite primary key with CallerID, CallerDateOfCall, and
SubcategoryID -- foreign key to tblCategories table)
SubcategoryID (composite primary key with CallerID, CallerDateOfCall,
and CategoryID -- foreign key to tblSubCategories table)
InfoMailed
The tblCatSubcat table relates the subcategories to each category. This
table structure allows a subcategory to be assigned to more than one
category, and of course a category to have more than one subcategory. This
table also is the one that will be the source of records for the second
combo box (filtered, of course, by the value of CategoryID that should be in
the first combo box).
The last table is used to store info related to each call.
Yes. What do you need to know? How to set up the code?
--
Ken Snell
<MS ACCESS MVP>
gavin said:
Good thought.
Another good thought.
For a start, I see five tables for the simple database that you've
described:
tblCallers
CallerID (IDs the caller's record - primary key)
CallerName
(etc. - info related to the caller himself/herself)
tblCategories
CategoryID (IDs the category - primary key)
CategoryName
tblSubCategories
SubcategoryID (IDs the subcategory - primary key)
SubcategoryName
tblCatSubcat
CategoryID (composite primary key with SubcategoryID -- foreign key
to tblCategories table)
SubcategoryID (composite primary key with CategoryID -- foreign key
to tblSubcategories table)
tblCallerDetails
CallerID (composite primary key with CallerDateOfCall, CategoryID, and
SubcategoryID -- foreign key to tblCallers table)
CallerDateOfCall (composite primary key with CallerID, CategoryID, and
SubcategoryID)
CategoryID (composite primary key with CallerID, CallerDateOfCall, and
SubcategoryID -- foreign key to tblCategories table)
SubcategoryID (composite primary key with CallerID, CallerDateOfCall,
and CategoryID -- foreign key to tblSubCategories table)
InfoMailed
The tblCatSubcat table relates the subcategories to each category. This
table structure allows a subcategory to be assigned to more than one
category, and of course a category to have more than one subcategory. This
table also is the one that will be the source of records for the second
combo box (filtered, of course, by the value of CategoryID that should be in
the first combo box).
The last table is used to store info related to each call.
Yes. What do you need to know? How to set up the code?
G
gavin
Hi Ken,
Many thanks for your help, I really do appreciate it. However, if you have
time I'm going to need a bit of hand holding here :-(
First three tables - no problem.
tblCatSubcat - 2 fields, both primary keys and both a foreign key to a field
in another table.
tblCallerDetails - 5 fields, loads of primary and foreign keys!!!!!!! I can
create this table but I am not really understanding why it should be like
this - it seems very complicated (I am sure you are 100% correct but I am
just having trouble getting my head around the theory - in fact, I don't
understand why 5 tables are required. I know you've explained why we need
the tblCatSubcat table but I still don't get it).
OK. Assuming I've set up my tables like this, what's next? At some point
I'll need to create a form for data inputting - I know I'm going to need
help here too :-(
If you can help me some more, Ken, I'd be very grateful.
Regards,
Gavin
Many thanks for your help, I really do appreciate it. However, if you have
time I'm going to need a bit of hand holding here :-(
First three tables - no problem.
tblCatSubcat - 2 fields, both primary keys and both a foreign key to a field
in another table.
tblCallerDetails - 5 fields, loads of primary and foreign keys!!!!!!! I can
create this table but I am not really understanding why it should be like
this - it seems very complicated (I am sure you are 100% correct but I am
just having trouble getting my head around the theory - in fact, I don't
understand why 5 tables are required. I know you've explained why we need
the tblCatSubcat table but I still don't get it).
OK. Assuming I've set up my tables like this, what's next? At some point
I'll need to create a form for data inputting - I know I'm going to need
help here too :-(
If you can help me some more, Ken, I'd be very grateful.
Regards,
Gavin
K
Ken Snell [MVP]
Are you asking for more explanation about the suggested five-table
structure? Or what to do next?
The tables are where the data are stored. Forms are where the "power" of the
database is made possible through the display and manipulation of the data
via queries, etc.
The reason for the tblCatSubcat table is so that you can have multiple
subcategories for each category. In a spreadsheet, one would just have
multiple "columns" for a specified maximum number of subcategories (e.g.,
Subcategory1, Subcategory2, etc.), which means that you have to add more
columns when you find out that you need more subcategories than you'd
anticipated, or you have many empty columns because you're not using the
maximum number of subcategories for each category.
With these tables, you can create a query that finds the specific
subcategories based on a specific category. This is the "heart" of being
able to use the filtering/linked combo boxes that you desire to set up. From
this query you can create the RowSource of the subcategory combo box on the
form. The AfterUpdate event stuff is used on the form, but you need to set
up a form with combo boxes first. The form's RecordSource would be a query
that allows you to save data into the tblCallerDetails table -- namely, the
caller's ID number from the database, the caller's category is identified by
the ID of the category from the database, and the caller's subcategory is
identified by the ID of the subcategory from the database. For the latter
two fields, your combo boxes are the way you select and save those values
into this table.
I recommend that you obtain a book on ACCESS that will help you get through
the "basics" of setting up tables, queries, forms, etc. A newsgroup is not
an effective forum to walk through the "tediously" mundane details of
setting up tables, etc., but is better suited for addressing specific
questions related to those actions. (To describe this in a newsgroup post
would mean reproducing chapters of such books. )
structure? Or what to do next?
The tables are where the data are stored. Forms are where the "power" of the
database is made possible through the display and manipulation of the data
via queries, etc.
The reason for the tblCatSubcat table is so that you can have multiple
subcategories for each category. In a spreadsheet, one would just have
multiple "columns" for a specified maximum number of subcategories (e.g.,
Subcategory1, Subcategory2, etc.), which means that you have to add more
columns when you find out that you need more subcategories than you'd
anticipated, or you have many empty columns because you're not using the
maximum number of subcategories for each category.
With these tables, you can create a query that finds the specific
subcategories based on a specific category. This is the "heart" of being
able to use the filtering/linked combo boxes that you desire to set up. From
this query you can create the RowSource of the subcategory combo box on the
form. The AfterUpdate event stuff is used on the form, but you need to set
up a form with combo boxes first. The form's RecordSource would be a query
that allows you to save data into the tblCallerDetails table -- namely, the
caller's ID number from the database, the caller's category is identified by
the ID of the category from the database, and the caller's subcategory is
identified by the ID of the subcategory from the database. For the latter
two fields, your combo boxes are the way you select and save those values
into this table.
I recommend that you obtain a book on ACCESS that will help you get through
the "basics" of setting up tables, queries, forms, etc. A newsgroup is not
an effective forum to walk through the "tediously" mundane details of
setting up tables, etc., but is better suited for addressing specific
questions related to those actions. (To describe this in a newsgroup post
would mean reproducing chapters of such books.
)
G
gavin
Ken Snell said:
Both really
When I create a database I usually use a form to enter data into the
table(s). That is what I was hoping this would achieve - a way of making it
easier for users to select categories and sub-categories to enter into a
table.
So what's the function of the tblSubCategories table? I know this will sem
totally obvious to you but I really don't get it!
I recommend that you obtain a book on ACCESS that will help you get through
the "basics" of setting up tables, queries, forms, etc. A newsgroup is not
an effective forum to walk through the "tediously" mundane details of
setting up tables, etc., but is better suited for addressing specific
questions related to those actions. (To describe this in a newsgroup post
would mean reproducing chapters of such books.
I have got an Access book and I think I do know the basics of tables, form
and queries but I find there is a huge leap from understanding the basics to
getting Access to do "real" stuff.
Thanks again, Ken! Please persist with me
Regards,
Gavin
K
Ken Snell [MVP]
The purpose of the tblSubCategories table is to list all the possible
subcategories -- irrespective of which category or categories that may be
the "parent" of the subcategory.
For example:
tblCategories contains these values for CategoryName:
Accommodations
Transportation
Dining
And then let's assume that tblSubCategories has these values for
SubcategoryName:
Hotel
Motel
Airplane
Train
Car
Taxi
Bus
Restaurant
Then it's possible to have Hotel be a subcategory of more than one category.
Let's assume that it's a subcategory for Accombodations and Dining. So then
the tblCatSubcat table would have two records with the SubcategoryID that
equates to Hotel, and one of those records would have a CategoryID value
that equates to Accommodations and the other would have a CategoryID value
that equates to Dining.
And so on.
As I've posted previously, your thought for using two combo boxes is the way
to go for this. The first selects a CategoryID value from tblCategory, and
the second selects a SubcategoryID value from tblCatSubcat table based on
the value of CategoryID.
subcategories -- irrespective of which category or categories that may be
the "parent" of the subcategory.
For example:
tblCategories contains these values for CategoryName:
Accommodations
Transportation
Dining
And then let's assume that tblSubCategories has these values for
SubcategoryName:
Hotel
Motel
Airplane
Train
Car
Taxi
Bus
Restaurant
Then it's possible to have Hotel be a subcategory of more than one category.
Let's assume that it's a subcategory for Accombodations and Dining. So then
the tblCatSubcat table would have two records with the SubcategoryID that
equates to Hotel, and one of those records would have a CategoryID value
that equates to Accommodations and the other would have a CategoryID value
that equates to Dining.
And so on.
As I've posted previously, your thought for using two combo boxes is the way
to go for this. The first selects a CategoryID value from tblCategory, and
the second selects a SubcategoryID value from tblCatSubcat table based on
the value of CategoryID.
G
gavin
Thanks for persisting, Ken - however, I think I'm biting off more than I can
chew :-( I still don't understand the different purposes of tblCatSubcat
tblSubCategories.
Regards,
Gavin
chew :-( I still don't understand the different purposes of tblCatSubcat
tblSubCategories.
Regards,
Gavin
K
Ken Snell [MVP]
I don't know what more I can say at this point. Keep reading and trying....
G
gavin
Will do, Ken. I'm not giving up on Access, just this particular feature. My
users will have to wait for this development
Gavin
users will have to wait for this development
Gavin