Subform combo boxes

[Mo]

Hello,

I need to design a subform (in datasheet view), on which will be two
drop-down boxes and maybe a text box. The first combo is called
'categories' of which there are four types. The second combo is called
'symptoms'. A category can consist of many symptoms. This relation is
reflected at table-level.

Is it possible to have the first combo designed so that when the user
selects the category, the second combo shows only those symptoms
relevant to that category?

Sorry if this question has been asked before, but this is not something
I have experience with.

TIA for any help.

 

[Mo]

This is generally referred to as a cascading combo box. Here is a way to
implement this:

http://www.fontstuff.com/access/acctut10.htm

The second example is the one you need, I expect. The first one should not
be considered unless it uses named queries rather than tables. Considering
that there are just four categories this would not be a bad choice, but I
really don't think it is much easier. The second one is more versatile if
you add new categories.

Presumably you have tables something like this:

tblCategory
CatID (primary key, or PK)
Description
etc.

tblSymptom
SymptomID (PK)
CatID
Symptom

In the category combo box (cboCat), the stored value is CatID. In the After
Update event, something like:

Dim strSQL as String

strSQL = "SELECT [SymptomID], [Symptom] "& _
"FROM tblSymptom "& _
"WHERE [CatID] = "& Me.cboCat& _
" ORDER BY [Symptom]"

Me.cboSymptom.RowSource = strSQL

The above assumes the ID fields are Number fields. In both combo boxes, the
Row Count is 2, the Bound Column is 1 (that is, you are storing the ID, not
the text description of Category and Symptom), and the Column Widths are
something like 0";1.5". The cboCategory Row Source would be similar, except
without the WHERE, since you want to see all Category records.

Thanks very for the response Bruce. I'll give it a try out and see how
it goes.

 

[George]

Assuming that you have two tables (one to many) Categories and Symptoms then
on the lookout of sympltoms put something like the following as a criterion
under category
[Forms]![YourMainFormName]![YourSubformName].[Form]![YourCategoryField]

Also, have this textbox (lookup) to requery it self (i.e. after update of
the category field)

Hope this helps,

GeorgeCY

Ο χÏήστης "Mo" έγγÏαψε: