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...yMessages&tsn=1&tid=23626&webtag=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:
kenwsheridan<at>yahoo<dot>co<dot>uk
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
