If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
combo/list boxes from other tables
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! |
#2
|
|||
|
|||
combo/list boxes from other tables
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. -- Good luck Jeff Boyce Access MVP |
#3
|
|||
|
|||
combo/list boxes from other tables
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 -----Original Message----- 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. -- Good luck Jeff Boyce Access MVP . |
#4
|
|||
|
|||
combo/list boxes from other tables
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 -----Original Message----- 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. -- Good luck Jeff Boyce Access MVP . |
#5
|
|||
|
|||
combo/list boxes from other tables
"Pamela Fong" wrote in
: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mutliple Tables lookup? | Westley | Database Design | 4 | June 15th, 2004 01:07 AM |
searching for "join tables" and "join word tables" | Uncle Bill | Tables | 1 | June 11th, 2004 09:33 PM |
Linked text boxes containing tables | Gary McGill | Tables | 3 | June 9th, 2004 11:57 PM |
Linked text boxes containing tables | Gary McGill | Page Layout | 3 | June 9th, 2004 11:57 PM |
More alternatives to text boxes. | David Young | Page Layout | 2 | April 26th, 2004 04:47 PM |