combo box query

[iain]

whilst i'm on a roll - my last question (for now)

i often use combo boxes as a short cut to entering data
in a field, using Value List as the Row Source Type, and
then typing the possible values into the Row Source.

my question here is, if i have two combo boxes, where the
Row source values for the second automatically alter,
depending on what is entered in the first?

basically i have a database of developments, split into
categories, and again into sub categories

so can i have one combi box for the category - eg
education, health etc, and then a second which will list
the relevant sub categories for each category - eg
primary school, secondary school etc for education, or
hospital, health centre etc for health etc...

hope that makes some sense...

cheers,
Iain

 

[Van T. Dinh]

Much easier to have 2 Tables: tblCategory and tblSubCategory with a
ForeignKey Field frg_CategoryID in the tblSubCategory pointing to the
tblCategory.

You can then simply use the AfterUpdate Event of the cboCategory to re-query
the cboSubCategory.

 

[iain]

i think i get you - instead of having the sub category in
the same table as the category - have the sub categories
in their own table - just with their names and an ID?

and then in the form, after the category box is filled,
use AfterUpdate to requery for the subcategory?

actually, i'm not sure that is it. i'll have to have a
play i think.

my problem is that i'm completely self taught with
access, been using it for 6-7 yrs and have over time
gotten it to do pretty much most stuff i've needed to -
however I do lack proper training in database design and
don't really have that firm a grasp of relationships
(even tho' my databases are relational)

Iain

 

[Van T. Dinh]

I find formal training for software like Access is a waste of time unless
you know nothing about Access since most courses are really beginner's
courses, even for those "Advanced" courses. It is much better to learn from
a few good books.

I have not attended any formal training for Access either but having
attended a training course on SQL Server (about AUS$3,000 for a week), I
think it would have been MUCH MORE useful to buy about 20 books (for about
the same costs) on SQL server and learn/experiment the SQL server by myself.
The training centre is MS authorised training centre and the instructor is
an MCT (Microsoft-Certified Trainer). Clearly, the trainer's knowledge is
through training rather than experience and anything not in the training
manual, the trainer got stuck.

 

[iain]

hi -

i kind of agree with you - i probably could have gone on
a course at some point, but by then i pretty much knew my
way around how access worked. i just sometimes feel i
could do with having a better understanding and down pat
(rather than looking back at stuff i've down to see how
to do it again) knowledge of the principles stuff.

Iain

 

[tina]

hi iain.
you're close on the subcategories table design. you need
one additional field, the foreign key.
Example tables:

tblCategories
CatID (primary key)
CatName
any other fields that are needed to describe each category.

tblSubCategories
SubID (primary key)
Sub_CatID (foreign key, links back to CatID in
tblCategories)
SubName
any other fields that are needed to describe each
subcategory.

in the form, the category combo's rowsource is a query or
select statement built on tblCategories. the subcategory
combo's rowsource is built on tblSubCategories, with a
criteria as Sub_CatID = Forms!FormName!CategoryComboName.
then just requery the subcategories combo box in the
category combo's AfterUpdate event.

btw, i agree with Van - most Access classes are too basic
if you've already been building applications for years
(although when i finally took some classes after
developing for a couple years, i did learn a few things -
"you don't know what you don't know").
but you might consider taking a class in relational data
modeling, or getting a good book or two on the subject -
you'll be amazed how much easier it will be to lay out a
good solid table/relationship design and how much better
that foundation will support the rest of your development.
also, relational data modeling rules apply to any
relational database, not just those built in Access - so
your knowledge is portable.

hth

 

[rkc]

I find formal training for software like Access is a waste of time unless
you know nothing about Access since most courses are really beginner's
courses, even for those "Advanced" courses. It is much better to learn from
a few good books.

I've often wondered if those "Developers Conventions" I get brochures for
on a regular basis live up to all the hype they lavish on themselves.