combo/list boxes from other tables

[amelia]

hi

i have three tables: products, categories and
subcategories. any product may have many categories and
many subcategories.

in my products table, for the category and subcategory
fields, i want a list/combo dropdown that populates from
the corresponding tables and i'll need to be able to
selece more than one option from each dropdown.

how can i accomplish this?

thanks!

 

[Jeff Boyce]

Amelia

Don't! A review of the responses in this newsgroup (tablesdbdesign) will
reveal a strong consensus against using the "lookup" data type in defining
your tables.

There are two main reasons why you won't see much support for this "feature"
in Access. First, what a lookup field displays and what it actually holds
are NOT the same -- this is confusing, especially when it comes time to
build queries and search for values you THINK are in the field. Second,
tables are NOT meant for display -- they are 'buckets-o-data'. Forms are
for display, and you can use a combo box in a form to do what you're
describing.

 

[Pamela Fong]

I'm very glad to see this suggestion. I'm in the process
of learning by doing. At first, I was going to stick
with plain XML in Excel spreadsheet, but then I stumbled
across pretty looking InfoPath forms, then moved into
trying to lock the user into entering data in the proper
format, which then moved me into Access. When I tried
creating an InfoPath form to let the users enter a new
record into an Access database, the form designer
complained that I couldn't use submit new Data because of
too many duplications of entry values. So then, I
launched into trying to split every column into a
separate table to use lookup columns, but stumbed across
what the advice seems to suggest. For example, if the
user wants to insert a new value which doesn't appear in
the Combo box, there is no way except via VBScript or
Macro magic to add this new value into the table which
supplies the Combo box values. Oh, what a nightmare for
newbie!

I wonder what approach to take? Stick with Notepad and
XML, and write in-house data validation? And then just
use InfoPath for saving data into .xml files by
programmatic inport? I'll be looking here and at the
main Microsoft site for some direction.

---Pam

 

[Pamela Fong]

Oh, also, I don't know if it is a glitch with my
database, or with Access. But sometimes when I try to
insert a lookup column, I get an error, "You can't change
the data type or field size of this field; it is part of
one or more relationships. If you want to change the
data type of this field, first delete its relationships
in the Relationships window."

I tried showing all the relationships and there are none
for the nearby columns. I try closing the database, and
repairing it but get the same error. The only way to
work around the error seems to be to insert a bunch of
new columns, and then try inserting a lookup column in
the middle of all the new ones, deleting these column
padding after the lookup is in place.

---Pam

 

[Tim Ferguson]

But sometimes when I try to
insert a lookup column, I get an error, "You can't change
the data type or field size of this field; it is part of
one or more relationships.

Well, that is the problem with "look up columns" -- the wizard that does
them is actually a vicious old crone intent on destroying databases and the
users that depend on them. Just make a proper table and establish the
relationships normally and you'll be fine.

B Wishes


Tim F