Combo box in table?

[Matty D]

I am still new to designing databases and have learned alot from this years database I built, in large part to reading these forums

On another Access site it stated, on a list of standard rules, that:

"Thou shalt never allow thy users to see or edit tables directly, but only through forms and thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One."

I'm concerned with the "lookup fields" part of this rule. Does this include using a combo box to choose the correct value for a field?

My current data base tracks work orders. Part of this is inputing the location of the work. The locations are all known sites that don't change. Currently I have a "Locations" table that I link to in my work order table via a combo box. On the form this combo box displays all the locations and I simply pick the correct one. I'm designing next years database and don't want to intorduce errors and bad design if it can be avoided now. I did some quick queries using the location combo box in a few different ways and had no errors or problems. Any thoughts or direction on this?

 

[Rabbie]

Basically Table should be used for storing data. Forms and reports should be used for displaying data. And Queries for selecting data.

Your forms/reports should be based on queries. Lookup fields should never be used in tables. A Combo box is fine on a form but that is the only place they should be used.

 

[boblarson]

using a combo on a form is not bad, but doing so in a table (lookup field) is.

 

[Matty D]

Thanks to both of you for the replies. I will edit the table and form as needed to be proper. Much appreciated.

 

[David Eagar]

Basically Table should be used for storing data. Forms and reports should be used for displaying data. And Queries for selecting data.

Your forms/reports should be based on queries. Lookup fields should never be used in tables. A Combo box is fine on a form but that is the only place they should be used.

Is creating the combo box in the query also a no no?

 

[boblarson]

Is creating the combo box in the query also a no no?

Queries don't have combo boxes. Only if they are tied to a lookup field, which we know you don't want to do. You want your users to use FORMS and REPORTS for their interaction and keep away from letting them use tables and queries directly as you lose major control there.

 

[The_Doc_Man]

The simpler and less complicated your tables, the better off you are. Putting lookups and fancy-shmancy features in tables KILLS performance and complicates your life.

Queries cannot have any features not found in a table - except formulas. Which can include DLookup but not controls such as list-box or combo-box.

I won't say with absolute finality that there is no time to implement complex controls in a table just because Access allows it. But I WILL say I've never yet seen the need for such a complexity.

 

[David Eagar]

Queries don't have combo boxes. Only if they are tied to a lookup field, which we know you don't want to do. You want your users to use FORMS and REPORTS for their interaction and keep away from letting them use tables and queries directly as you lose major control there.

What I have been 'cleverly' doing is creating a query of a table and in the properties of that query, creating lookup combo boxes in the fields appropriate

The use these quries to generate forms for user access

If I check my full table relationships, it does not appear as though I have created any lookup fields at table level

Am I fooling myself and yet again return to gaol & not collect $200?

 

[The_Doc_Man]

Never tried that, David, because I'm so comfortable using forms for data population. But maybe it is a new thing. As long as it isn't in the table, as long as what you see in the table is a very plain reality; then I don't THINK you are hurting yourself. But hey, that's just one man's opinion no matter how you slice it.

 

[David Eagar]

Never tried that, David, because I'm so comfortable using forms for data population. But maybe it is a new thing. As long as it isn't in the table, as long as what you see in the table is a very plain reality; then I don't THINK you are hurting yourself. But hey, that's just one man's opinion no matter how you slice it.

Hey, that's why I ask the question - to get valuable answers like these. I will keep proceding with my project as is and see how things perform and post any difficulties that arise

 

[The_Doc_Man]

Feedback to the forum is ALWAYS a good thing, David. Thanks in advance.

 

[David Eagar]

Here is an example of lookups created in queries, rather than tables or forms. Can the sceptical give this the once over to check whether this is a valid technique

ps any contributions to the menu welcome

 

[boblarson]

Here is an example of lookups created in queries, rather than tables or forms. Can the sceptical give this the once over to check whether this is a valid technique

ps any contributions to the menu welcome

The queries all look like just normal queries to me. I don't see anything special about them. That's the way it should normally be. So, there really isn't any "lookups" in queries, but rather a query is the basis for a lookup.