Cascading Lists

[SJW]

I am trying to create a database to sort a few hundred pubications. I have
five categories, and multiple sub-categories under each category. The user
needs to enter values in each before listing the relevant publications under
that category/sub-category.
As a beginner using cascading lists, I have been using the example from
http://www.fontstuff.com/access/acctut10.htm#top and used the simplest
example ie. multiple row source tables. I created one main table ie.
tblCategoryPublications; and five other tables corresponding to each category
(as per code below).
My search includes a main cboCategory feeding the row source for
cboSubcategory. I have used the following code on AfterUpdate:

Private Sub ComboCategory_AfterUpdate()
On Error Resume Next
Select Case cboCategory.Value
Case "Market Information"
cboSubcategory.RowSource = "tblSubcategoryMktInfo"
Case "Technical Information"
cboSubcategory.RowSource = "tblSubcategoryTechInfo"
Case "Chain Integration"
cboSubcategory.RowSource = "tblSubcategoryChainInteg"
Case "Management Issues"
cboSubcategory.RowSource = "tblSubcategoryMgtIssues"
Case "Projects Information"
cboSubcategory.RowSource = "tblSubcategoryProjectsInfo"
End Select
End Sub

The problem is I cannot link the subcategory combo to the main category cbo.
I have searched through other 'cascading combos/lists' threads on this
discussion group but I cannot find how to link the cboSubcategory to the main
cboCategory. I presume it is through the rowsource, but what should the
rowsource be for cboSubcategory

I know there is probably some basic explanation, however, I cannot find the
answer on other threads.
thanks for assistance
SJW

 

[Jeff Boyce]

Reconsider.

If you have 5 tables, each dedicated to a different category, won't you have
to revise your table structure, queries, forms, reports, macros, code, ...
each time the number of categories change?! That's a lot of work (and ends
up looking a lot like a spreadsheet, not a relational database)!

Do a bit of searching on "lookup tables" ... I suspect (but I'm not clear)
that you could get much better use of Access' relationally-oriented
features/functions if your table structure was more-normalized.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Ken Sheridan]

You might like to take a look at the file obtainable from:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


It shows various ways of using combo boxes for such hierarchical data while
maintaining a normalized structure to the tables. The demo file uses
geographical data from my own area, the local administrative areas of county,
district and parish, but the principles are the same for any type data of a
hierarchical nature

Ken Sheridan
Stafford, England

 

[SJW]

Dear Ken
Thanks for your reply which proved useful. One difference I note from your
file. You bound your third cbo ie. cboParish to ParishID. When I did this
(comboPublications to PublicationID) I received a Run-time Error 2448 in the
first cbo (comboCategory) stating "You can't assign a value to this object".
thanks for your advice.
SJW

 

[Ken Sheridan]

I take it it’s the single form view you are referring to. The cboParish
combo box is, as you say, bound to the ParishID column. You'll see that
that's the only column in the table which records the administrative area in
which each location is situated as, once you know the parish, you know the
district and county from this, i.e. there is no redundancy in the table.

The error you are getting sounds like one which would arise when a value is
assigned to the control in code. There are a number of instances in the
form's module where this is the case; in the AfterUpdate event procedure of
the cboCounty control; in the AfterUpdate event procedure of the cboDistrict
control; and in the Click event procedure of the cmdUndo control. From what
you say it sounds like the error is occurring in your case when the
AfterUpdate procedure of the comboCategory control executes and attempts to
set the value of the comboPublications control to Null. The thinking behind
this is that if the user selects a different category in a record already
containing data, then any existing PublicationID will be invalid for the new
category, so the control is cleared ready for a new publication to be
selected (as also would be the sub-category combo box). Quite why this would
raise an error is difficult to say at this remote distance. Your scenario
seems directly analogous to my demo with categories instead of counties,
sub-categories instead of districts and publications instead of parishes, so
it should adapt to suit your requirements very easily.

Ken Sheridan
Stafford, England