multiple related comboboxes in datasheet view

[Lappalainen Tomi]

Hi,

Im building a very simple database for asset register purposes. I dont want
to make any fromt end (forms) to the database, just use the datasheet view.

I would like to add 2 comboboxes that use lookup tables, so that the other
combobox acts as the filter to the second combobox.

Example:

In combobox "group" there are values:

- Computer
- Network equipment
- Telephone


In combobox "subgroup" there are values:

- Laptop
- Desktop
- Hub
- Switch
- Deskphone
- Mobilephone

Now I need to link these two comboboxes so, that when ever i select for
example value "computer" in combobox "group", then only available option for
the next combobox are values "laptop" and "desktop"

Any idea how to accomplish this? - I got the both comboboxes already done,
but I can't figure out how to reference from second combobox to first in
datasheet view...

Thanks in advance!

 

[Vincent Johns]

Hi,

Im building a very simple database for asset register purposes. I dont want
to make any fromt end (forms) to the database, just use the datasheet view.

I would like to add 2 comboboxes that use lookup tables, so that the other
combobox acts as the filter to the second combobox.

Example:

In combobox "group" there are values:

- Computer
- Network equipment
- Telephone


In combobox "subgroup" there are values:

- Laptop
- Desktop
- Hub
- Switch
- Deskphone
- Mobilephone

The Table underlying this one needs to include an additional field
linking to the first, as in ...

Group Subgroup
---------- ------------
Computer Laptop
Computer Desktop
...
Telephone Mobilephone

Now I need to link these two comboboxes so, that when ever i select for
example value "computer" in combobox "group", then only available option for
the next combobox are values "laptop" and "desktop"

Use the linking field, [Group], to link them.

Any idea how to accomplish this? - I got the both comboboxes already done,
but I can't figure out how to reference from second combobox to first in
datasheet view...

Thanks in advance!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Lappalainen Tomi]

Hi again,

sorry, my mistake on the poor explanation ;-)

What I did, was to create a table, and 2 lookup tables. I linked these 2
lookup tables to master table, an to each other, so that in the lookup table
where the "subgroup" entries are taken, there is also another column which
is linked to the "group" contents.

A better look at the tables:

Table "hardware"

Running number Group Subgroup
------------------ --------- ----------
xxxxx xxxxx xxxxxxx
xxxxx xxxxx xxxxxxx
xxxxx xxxxx xxxxxxx


table lookup_group:

ID Definition
--- ----------
1 Computer
2 Network Equipment
3 Telephone

table lookup_subgroup

ID Definition Link (by a lookup query to
"lookup_group)
--- ---------- ---------------
1 Laptop Computer
2 Desktop Computer
3 Hub Network Equipment
4 Switch Network Equipment
5 Deskphone Telephone
6 Mobilephone Telephone



So the link is there. The problem is, that when I set the lookup in table
"hardware", column "group", to get it's data from table "lookup_group", and
column "subgroup" to take data from table "lookup_subgroup", I dont seem to
be able to modify the guery so, that I can read the selected value of column
"group" and use it as ?

When making SQL query (to rowsource property), I cannot find how to
reference to "previous.cell.in.same.row.in.same.table.value" (dont take this
literally).

I think that I must first make a rowsource query which reads all entries
from lookup_subgroup, and then find a way to read the value from column
"group" and use it as a filter. But how? What should I write to the filter
property of column "subgroup"? Or can it be referenced with SQL? what would
the right reference format be?

Hope this clarifies a bit ;-)

Hi,

Im building a very simple database for asset register purposes. I dont
want to make any fromt end (forms) to the database, just use the
datasheet view.

I would like to add 2 comboboxes that use lookup tables, so that the
other combobox acts as the filter to the second combobox.

Example:

In combobox "group" there are values:

- Computer
- Network equipment
- Telephone


In combobox "subgroup" there are values:

- Laptop
- Desktop
- Hub
- Switch
- Deskphone
- Mobilephone

The Table underlying this one needs to include an additional field linking
to the first, as in ...

Group Subgroup
---------- ------------
Computer Laptop
Computer Desktop
...
Telephone Mobilephone

Now I need to link these two comboboxes so, that when ever i select for
example value "computer" in combobox "group", then only available option
for the next combobox are values "laptop" and "desktop"

Use the linking field, [Group], to link them.

Any idea how to accomplish this? - I got the both comboboxes already
done, but I can't figure out how to reference from second combobox to
first in datasheet view...

Thanks in advance!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Sorry for the slow answer; real life intruded.

Anyway, I've interspersed some comments with your description. They're
kind of hasty (I have to leave soon), but I hope they help some.

Hi again,

sorry, my mistake on the poor explanation ;-)

What I did, was to create a table, and 2 lookup tables. I linked these 2
lookup tables to master table, an to each other, so that in the lookup table
where the "subgroup" entries are taken, there is also another column which
is linked to the "group" contents.

I usually define a Query, instead of using an existing Table, to do my
lookups for foreign keys, but if your Tables have the primary key as
their 1st field and the verbal explanation as the 2nd field, my system
will work with a Table, too. (But a Query would be more flexible, allow
you to abbreviate, to combine multiple fields, &c.)

A better look at the tables:

Table "hardware"

Running number Group Subgroup
------------------ --------- ----------
xxxxx xxxxx xxxxxxx
xxxxx xxxxx xxxxxxx
xxxxx xxxxx xxxxxxx


table lookup_group:

ID Definition
--- ----------
1 Computer
2 Network Equipment
3 Telephone

Is this the best possible name for your Table? I think you want the
names to be suggestive of what they contain (such as [Equipment
Group])... but for my example, I'm using your names.

table lookup_subgroup

ID Definition Link (by a lookup query to
"lookup_group)
--- ---------- ---------------
1 Laptop Computer
2 Desktop Computer
3 Hub Network Equipment
4 Switch Network Equipment
5 Deskphone Telephone
6 Mobilephone Telephone


So the link is there.

Actually, as jahoobob pointed out, you have EXTRA links that allow
inconsistency. In [hardware], you could specify in record 1 a
[Lookup_groupID] of "Telephone" and a [Lookup_subgroupID] of "Hub". I
assume that that isn't what you want. I recommend zapping the
[Hardware].[Lookup_groupID] field, and I have done that in my examples
below.

The problem is, that when I set the lookup in table
"hardware", column "group", to get it's data from table "lookup_group", and
column "subgroup" to take data from table "lookup_subgroup", I dont seem to
be able to modify the guery so, that I can read the selected value of column
"group" and use it as ?

When making SQL query (to rowsource property), I cannot find how to
reference to "previous.cell.in.same.row.in.same.table.value" (dont take this
literally).

I think that I must first make a rowsource query which reads all entries
from lookup_subgroup, and then find a way to read the value from column
"group" and use it as a filter. But how? What should I write to the filter
property of column "subgroup"? Or can it be referenced with SQL? what would
the right reference format be?

Hope this clarifies a bit ;-)

OK, here's what I did. For each primary key (1st field in each record,
unique value used only for lookup), I set the type to Autonumber, random
values (to make it obvious that they have no other use).

How I set up each foreign key (a field containing some other Table's
primary key value, used to link the records), for example for the
[Lookup_groupID] foreign key in the [Lookup_subgroup] Table, is to open
the looking-up Table ([Lookup_subgroup] in this case) in Table Design
View, select the [Lookup_groupID] foreign key field, and select the
Lookup tab. I change its default properties to be these:

Display Control List Box
Row Source Lookup_group
Column Count 2
Column Widths 0;1

Notice that the Row Source is the name of the Table, not of its
primary-key field.

Primary keys you leave alone (Access won't let you define lookups on
them anyway); I usually hide them in Table Datasheet View because they
aren't normally meaningful to a human being. I intentionally set my
Autonumber type to "random" for these to emphasize that. (But it is
possible to use some meaningful unique number, such as an equipment
serial number, as your Primary Key, in which case you would want to keep
it visible. One disadvantage is, that if you later discover a typo in
it, you have to reconstruct the entire record, not just that field, and
redo all the links from other Tables to that record. It's kind of a
pain to do that, so I prefer to use Autonumbers for my primary keys.)

Here's how I would set up your Tables, and they will look like this in
Table Datasheet View if you set up your lookups as I did:

[Lookup_Group]

Lookup_groupID Definition
-------------- ---------------
-1021991833 Telephone
-251255343 Computer
55471038 Network Equipment

[Lookup_subgroup]

Lookup_subgr Definition Lookup_groupID
oupID
------------ ---------- ----------------
-1905861395 Desktop Computer
-1893064477 Switch Network Equipment
-858668118 Hub Network Equipment
-744612407 Mobilephone Telephone
229882344 Deskphone Telephone
323121180 Laptop Computer

[Hardware]

HardwareID Running Lookup_subgroupID
number
----------- ------- --------------
-2033331626 20 Hub
-1523481662 4 Mobilephone
-1313471387 10 Desktop
-15755148 7 Desktop
78069023 3 Switch
747926811 0 Laptop


As I said, I'd normally hide the first field.

Having done this, you can easily add Subdatasheets to the first 2
Tables, and not bother with Combo Boxes at all.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Here are a couple of addenda to my previous message...

Vincent Johns wrote:

[...]

Here's how I would set up your Tables, and they will look like this in
Table Datasheet View if you set up your lookups as I did:

[Lookup_Group]

Lookup_groupID Definition
-------------- ---------------
-1021991833 Telephone
-251255343 Computer
55471038 Network Equipment

[Lookup_subgroup]

Lookup_subgr Definition Lookup_groupID
oupID
------------ ---------- ----------------
-1905861395 Desktop Computer
-1893064477 Switch Network Equipment
-858668118 Hub Network Equipment
-744612407 Mobilephone Telephone
229882344 Deskphone Telephone
323121180 Laptop Computer

[Hardware]

HardwareID Running Lookup_subgroupID
number
----------- ------- --------------
-2033331626 20 Hub
-1523481662 4 Mobilephone
-1313471387 10 Desktop
-15755148 7 Desktop
78069023 3 Switch
747926811 0 Laptop

Note that the above is how [Hardware] would LOOK if you define the
lookup properties as I did. Without them, it would look like this:

[Hardware]

HardwareID Running Lookup_subgroupID
number
----------- ------- -----------------
-2033331626 20 -858668118
-1523481662 4 -744612407
-1313471387 10 -1905861395
-15755148 7 -1905861395
78069023 3 -1893064477
747926811 0 323121180

These numbers (foreign keys in this Table) are the values actually
stored there, but they are meaningless to a human being, hence the
lookup field.

As I said, I'd normally hide the first field.

.... which would make the Table look much more civilized, like this:

[Hardware]

Running Lookup_subgroupID
number
------- --------------
20 Hub
4 Mobilephone
10 Desktop
7 Desktop
3 Switch
0 Laptop

Even though you can't see the Primary Key now at all, and you see only
the text equivalent to the foreign key in [Lookup_subgroupID], the Table
is still completely functional and still contains those weird numeric
values. It is just much easier to interpret in this form.

Having done this, you can easily add Subdatasheets to the first 2
Tables, and not bother with Combo Boxes at all.

To do this, open [Lookup_group], the topmost Table, in Table Datasheet
View, and select Insert --> Subdatasheet. Specify the [Lookup_subgroup]
Table, and tell Access to create the suggested relationship. Then open
[Lookup_subgroup] and do the same thing, specifying [Hardware] as the
subdatasheet Table.


If you are longing to see the [Lookup_groupID] values in the [Hardware]
Table along with the [Lookup_subgroupID] values, what you need is a
Query, such as the following one:

SELECT Hardware.[Running number],
Lookup_group.Definition AS [Group],
Lookup_subgroup.Definition AS Subgroup
FROM (Lookup_group INNER JOIN Lookup_subgroup
ON Lookup_group.Lookup_groupID
= Lookup_subgroup.Lookup_groupID)
INNER JOIN Hardware
ON Lookup_subgroup.Lookup_subgroupID
= Hardware.Lookup_subgroupID
ORDER BY Hardware.[Running number],
Lookup_group.Definition, Lookup_subgroup.Definition;

This Query will suck the suitable data out of your Tables without
disturbing any of them, and present those data in whatever way you want
to specify. If the Query is defined as I show here, the results will
look like this:

Running number Group Subgroup
-------------- ----------------- -----------
0 Computer Laptop
3 Network Equipment Switch
4 Telephone Mobilephone
7 Computer Desktop
10 Computer Desktop
20 Network Equipment Hub


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.