Can I set up cascading combo boxes in a TABLE?

I am setting up a database for a research project. I would like to enter the
data within the table as it is easier to see everything together.

I have a combo box for General Injury Region, which is upper extremity,
lower extremity, spine. I would like to the second combo box populate based
on the first with shoulder, hand, wrist, etc.

Thanks in advance!

Debi wrote:

I am setting up a database for a research project. I would like to enter
the data within the table as it is easier to see everything together.

I have a combo box for General Injury Region, which is upper extremity,
lower extremity, spine. I would like to the second combo box populate
based on the first with shoulder, hand, wrist, etc.

Thanks in advance!

No, Use a form.

The answer is no, but in any case entering data directly into a table in
datasheet view is not a good idea. Data should only be entered or edited via
forms. This is not a problem, however as you simply need to set the form's
DefaultView property to datasheet if you want to see it that way. Continuous
forms view is a better option, however as it enables you to include unbound
controls on the form. It also allows a more professional standard of
presentation. You can create a form in continuous forms view very quickly
using the form wizard, then amend its design to set up your correlated combo
boxes.

Before looking at how to set up the combo boxes, however, there is one
important consideration which affects this when using a form in datasheet or
continuous forms view. If the table from which the combo box gets its list
uses a 'surrogate' primary key rather than a 'natural' primary key correlated
combo boxes don't work as you'd wish in continuous forms or datasheet view.
This is because the value of the column in the table underlying the form is a
number which points to the numeric primary key value of a row in the
'referenced' table, not the text value you see in the control.

So if you have tables GeneralInjuryRegions and SpecificInjuryRegions say, and
they have numeric primary keys GeneralInjuryRegionID and
SpecificInjuryRegionID the columns in the table underlying your form (The
'referencing' table) will have corresponding numeric foreign keys, although
you'll see the text values in them, particularly if you've used the 'lookup
wizard' when designing the table (more about this below). If you set up
correlated combo boxes in a datasheet view or continuous forms view form what
will happen is that if you select injuries in the upper extremity region in
some rows in some rows, but then select an injury in the lower extremity
region in another row say, the injuries in the rows for the upper extremity,
or any region other than lower extremity will go blank. Their values will be
unchanged, and no data is lost, but you won't see those injuries, only ones
in other rows for the lower extremity region.

There are ways around this by using a hybrid controls made up of a text box
superimposed on a combo box so that they look like a single control. I've
posted a demo of this at the following link, but its not a trivial task to
implement this sort of thing:

http://community.netscape.com/n/pfx/...g=ws-msdevapps


I have made a simpler version involving only two levels of hierarchy, which
is more directly analogous to your situation. Its not in the demo at the
above link, however, but I'd be happy to send it to you if you mail me at:

kenwsheridanatyahoodotcodotuk

If on the other hand your tables use 'natural' keys, i.e.
GeneralInjuryRegions and SpecificInjuryRegions have text columns with the
names of the regions as their keys, and the table underlying your form has
similar text foreign key columns then setting up correlated combo boxes in a
datasheet view or continuous forms view form is very much simpler. The
GeneralInjuryRegion combo box would have a RowSource such as:

SELECT [GeneralInjuryRegion] FROM [GeneralInjuryRegions] ORDER BY
[GeneralInjuryRegion];

And the SpecificInjuryRegion combo box wouild have a RowSource such as:

SELECT [SpecificInjuryRegion] FROM [SpecificInjuryRegions] WHERE
[GeneralInjuryRegion] = Form![GeneralInjuryRegion] ORDER BY
[SpecificInjuryRegion];

Note that you can use the Form property here to reference the control; you
don't need to use a full reference to the form by name as both controls are
in the same form.

In the AfterUpdate event procedure of the GeneralInjuryRegion combo box
control put:

Me.[SpecificInjuryRegion] = Null
Me.[SpecificInjuryRegion].Requery


Returning to the subject of the 'lookup wizard', most experienced developers
strongly advise against the use of this feature. For reasons why see:

http://www.mvps.org/access/lookupfields.htm

If you have used it don't be unduly alarmed, however; your database is not
fatally injured. But for future reference it would be wise to avoid it.
Setting up a combo box in a form for data entry achieves the same thing but
without the problems associated with this feature.

Finally, another issue which my demos address is that of normalization of the
tables in these circumstances. You have probably included columns for both
GeneralInjuryRegion and SpecificInjuryRegion in the 'referencing' table. If
so the table is not in fact correctly normalized as it contains redundancy.
You'd be told multiple times that a shoulder injury is in the upper extremity
region (I assume). The real point here is that this allows scope for
inconsistent data as there is nothing to stop an ankle injury being assigned
incorrectly to the upper extremity region in some rows. This is what the use
of correlated combo boxes is designed to avoid of course, but that in itself
is not a solution to the underlying design flaw as it remains possible for
the data to be updated in other ways.

The correct design would be to have only a SpecificInjuryRegion column in the
referencing table. This references the foreign key of the
SpecificInjuryRegions table, and this in turn includes a GeneralInjuryRegion
column which references the primary key of the GeneralInjuryRegions table.
Dta integrity is thus ensured as each 'fact' is stored only once in the
database. My demo files are essentially to show how such a normalized
structure can be achieved while still allowing 'top-down' selection, i.e. in
your case selecting a general injury region, then a specific injury region
from a restricted list.

With a correctly normalized design, its then just a question of joining the
tables in queries for reporting purposes etc.

Ken Sheridan
Stafford, England

Debi wrote:
I am setting up a database for a research project. I would like to enter the
data within the table as it is easier to see everything together.

I have a combo box for General Injury Region, which is upper extremity,
lower extremity, spine. I would like to the second combo box populate based
on the first with shoulder, hand, wrist, etc.

Thanks in advance!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200912/1

Warning: your first paragraph makes it sound as if you're thinking in
spreadsheet idiom. Databases are different, and in many ways much more
powerful. The key is understanding how to structure your data, and it's
vital to get that right first. Here are a few links to get you started:

Free samples from Lynda.com on table design:
http://www.lynda.com/home/Player.aspx?lpk4=31001 (A2007)
http://www.lynda.com/home/Player.aspx?lpk4=13150 (A2003)

Microsoft tutorial on table design:
http://bit.ly/ms-access-tables-tutorial

You can also find video tutorials on many Access topics (e.g. Combo Box) by
Googling.

Back to the question!. Don't be tempted to use "Lookup Fields" in tables -
there are lots of reasons why experienced Access users never use these. The
typical scenario is to divide your data between a number of tables which
represent distinct "entities" in the world you are modelling. These
entities will be related (e.g. one-to-many, many-to-many) and a record in
one table may include a reference (a "foreign key") to a record in another
to represent these relationships. When data needs to be extracted
selectively or in a particular sort order, queries are used to draw together
related records. Forms (or reports) can be based on these queries. It's
common to have several forms based on the same data tables to support
different uses of the data.

Once you have a suitable query on which to base a form, try using the Form
Wizard. In Access 2007 it's particularly easy - highlight the query which
will form the "Record Source" of your new form, and, in the Create tab,
simply click the Form button (left-most in its group). Bingo - a basic form
for your data. To change one of the text boxes (default) into a combo box,
simply right-click it and pick "Change to..." and pick combo box. You'll
then adjust the combo box's properties appropriately (always Row Source, and
often Number of Columns and Column Widths). It's surprisingly quick and
easy after the first time.

Finally, this article deals with the particular issue you ask about:

http://office.microsoft.com/en-gb/ac...CL100570041033

HTH

Phil, London

"Debi" wrote in message
...
I am setting up a database for a research project. I would like to enter
the
data within the table as it is easier to see everything together.

I have a combo box for General Injury Region, which is upper extremity,
lower extremity, spine. I would like to the second combo box populate
based
on the first with shoulder, hand, wrist, etc.

Thanks in advance!

Here's a neat video I found on this!

http://www.youtube.com/watch?v=pMiJHhjzC8Y

Phil

"Philip Herlihy" wrote in message
...
Warning: your first paragraph makes it sound as if you're thinking in
spreadsheet idiom. Databases are different, and in many ways much more
powerful. The key is understanding how to structure your data, and it's
vital to get that right first. Here are a few links to get you started:

Free samples from Lynda.com on table design:
http://www.lynda.com/home/Player.aspx?lpk4=31001 (A2007)
http://www.lynda.com/home/Player.aspx?lpk4=13150 (A2003)

Microsoft tutorial on table design:
http://bit.ly/ms-access-tables-tutorial

You can also find video tutorials on many Access topics (e.g. Combo Box)
by Googling.

Back to the question!. Don't be tempted to use "Lookup Fields" in
tables - there are lots of reasons why experienced Access users never use
these. The typical scenario is to divide your data between a number of
tables which represent distinct "entities" in the world you are modelling.
These entities will be related (e.g. one-to-many, many-to-many) and a
record in one table may include a reference (a "foreign key") to a record
in another to represent these relationships. When data needs to be
extracted selectively or in a particular sort order, queries are used to
draw together related records. Forms (or reports) can be based on these
queries. It's common to have several forms based on the same data tables
to support different uses of the data.

Once you have a suitable query on which to base a form, try using the Form
Wizard. In Access 2007 it's particularly easy - highlight the query which
will form the "Record Source" of your new form, and, in the Create tab,
simply click the Form button (left-most in its group). Bingo - a basic
form for your data. To change one of the text boxes (default) into a
combo box, simply right-click it and pick "Change to..." and pick combo
box. You'll then adjust the combo box's properties appropriately (always
Row Source, and often Number of Columns and Column Widths). It's
surprisingly quick and easy after the first time.

Finally, this article deals with the particular issue you ask about:

http://office.microsoft.com/en-gb/ac...CL100570041033

HTH

Phil, London

"Debi" wrote in message
...
I am setting up a database for a research project. I would like to enter
the
data within the table as it is easier to see everything together.

I have a combo box for General Injury Region, which is upper extremity,
lower extremity, spine. I would like to the second combo box populate
based
on the first with shoulder, hand, wrist, etc.

Thanks in advance!

Apart from the fact that his table is not normalized to Third Normal Form!

Ken Sheridan
Stafford, England

Philip Herlihy wrote:
Here's a neat video I found on this!

http://www.youtube.com/watch?v=pMiJHhjzC8Y

Phil

Warning: your first paragraph makes it sound as if you're thinking in
spreadsheet idiom. Databases are different, and in many ways much more
[quoted text clipped - 51 lines]

Thanks in advance!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200912/1

I've learned not to ignore anything you say, but I don't get that. To fail
3NF, one table would have to have a field whose value was not tied to the
key - can't see it! In the State table, the key is the (only) value - but I
don't think that makes a difference?

One of the benefits of offering answers here is that you get corrected from
time to time!

Phil

"KenSheridan via AccessMonster.com" u51882@uwe wrote in message
news:a088e3d5c4288@uwe...
Apart from the fact that his table is not normalized to Third Normal Form!

Ken Sheridan
Stafford, England

Philip Herlihy wrote:
Here's a neat video I found on this!

http://www.youtube.com/watch?v=pMiJHhjzC8Y

Phil

Warning: your first paragraph makes it sound as if you're thinking in
spreadsheet idiom. Databases are different, and in many ways much more
[quoted text clipped - 51 lines]

Thanks in advance!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200912/1