Cascading combo box - like fileds in a table...possible?

[wiklendt]

You should always use forms for data entry - never work directly in tables. Use combos on your forms to do the lookups. It is not that combos are bad - combos are good. What is bad is defining them at the table level. There are bugs in the way Access uses criteria against fields with table level lookups and other bugs in the way the fields are referenced in VBA. They are a crutch and since you are capable of creating a query with a join, you don't need them at all.

so, let me make sure i understand - if i want a combo for one of my fields, which won't cause probelms, but also for ease of data entry i must do the following:

in my table design, create the field as a text or number or date - anything that isn't a lookup. then, when i make the form, for the control bound to the field, i convert it to a combo box, and make the source... what... a table with the values i want in them? doesn't that kind of make an unrelated table situation? in a relational database?

see, it's just that people are quick to say "don't use lookups" but they don't actually say what you SHOULD use, or HOW...

 

[boblarson]

so, let me make sure i understand - if i want a combo for one of my fields, which won't cause probelms, but also for ease of data entry i must do the following:

in my table design, create the field as a text or number or date - anything that isn't a lookup. then, when i make the form, for the control bound to the field, i convert it to a combo box, and make the source... what... a table with the values i want in them?

Yes, that is true but it is a fact that the same thing is used for a lookup in a field at table level.

doesn't that kind of make an unrelated table situation? in a relational database?

No, that isn't true.
What you CAN do, is if you set up the tables with lookups at first and then create your forms, it will automatically create the combo for you. THEN, and this is the big important piece of info - REMOVE the lookup at table level after your form is created. You can also set up the combo manually if you want (and I do that all of the time) but it is really okay to use them initially to set up things. The thing is that you don't want them after the form is in use because it will only cause problems. In fact there are several MVP's who do it that way as well.

Does that help?

 

[wiklendt]

Does that help?

yes, it does, thanks boB... it's a nuisance that it has to be done in such a round about way, though - darned micro$oft!

 

[wiklendt]

(i tried giving you rep for that answer, boB, but the system won't let me until i "spread rep around before giving to boblarson again".. .... ...

 

[dkinley]

What you CAN do, is if you set up the tables with lookups at first and then create your forms, it will automatically create the combo for you. THEN, and this is the big important piece of info - REMOVE the lookup at table level after your form is created.

Haha! In my own little world, I thought I was the only one doing that. Thanks for validating me as being normal, Bob.

-dK

PS. I repped him for us both Wiklendt. =]

 

[Pat Hartman]

I prefer to not use the text description as the key for my lookups so my lookup tables are generally an autonumber primary key with a long text description and a short text description. That gives me some flexibility as to which to use in which situation. If you have only a few lookups, you can create separate tables for each. I have a whole mini-app that I add to each new application to manage lookup tables and all my simple lookup tables are stored in a single table (a table of tables as it were) so that i can use common forms and reports and not have to reinvent the wheel for each application.

What all the nay-sayers don't tell you is that if your lookups are straight text or numbers and you use only a single field in the lookup, you actually can get away with defining them at the table level. So using "Male";"Female";"Unknown" as the lookup for sex works just fine and won't cause problems of any kind. However, using 1;"Male";2;"Female";3;"Unkown" - which is the prefered method - is what leads to grief. Access will always show the text when you open a table or query - however, in some cases, it expects you to enter the numeric value to search for a record which is what causes the issue.

Single column value lists are what the table level lookup was invisioned to handle. If you have anything more complex, go with a table. I go with tables in ALL cases because it is consistant, plus I already have the infrastructure built to support the table of tables.