Where do I add a Combo Box - Table or Form?

PMFJI:

Most experienced developers refuse to put lookup fields (i.e. "combo boxes") in tables. The reasons include the kind of problems you are encountering and others.

If you want to limit the available values for this field, the more appropriate way to do that is with a Lookup table, not a value list in a lookup field in that same table.

Value lists are kind of quick and dirty, but tend to be harder to maintain over time because they have to be modified IN THE DESIGN OF THE FORM itself, and that's a thankless task.

You don't identify the kind of values used here, but typically things like Products -- to use the example Hans offered -- are stored in a Product table and that would be the related table for the values in a "ProductSold" field in an Order table, for example.

Other Lookup tables are things like shipping methods (Ground, Air, Train or Ship) for example, or tests performed during quality control or so on. All of these tend to be smaller lists and they tend to be Closed Domains. I.e. There are four and only four shipping methods and that's unlikely to change very often, if at all. Again, you didn't identify the values you are concerned with, but my guess is that, if you put them in Value list, they are probably a small, closed, domain.

In a lookup table, you can add new values, if appropriate, or mark old values "InActive" if they go out of use. The key here is that you are managing records in a table, not modifying object designs to update row sources, etc.

In short, forget there is even such a thing as a lookup field in a table and concentrate on the more standard designs used in relational database applications

Perhaps we can step back and review the overall design of a relational database application as a way to think about the reason for Default values in forms as opposed to, or in addition to, tables.

All relational database applications consist of three tiers, or layers, or components. 

1. Data tier. This is the part of the relational database application in which data is stored. In other words, the tables. Tables are structured to support the most efficient and reliable storage for your data. There are principles on which all good table structures are built. This is called Normalization. It describes the proper way to set up tables and define the relationships between them. E.g. You have a table for Plots and a table for Persons. They are related in some way. I'm going to take a guess and say that that relationship may be that Persons purchase Plots. Persons and Plots are two entities of interest to you and the way they relate is "Purchase". (Of course I may be guessing wrong.) All well designed relational database applications start with a fully developed set of related tables.
2. Interface tier. This is the part of the relational database application through which users interact with that data. The only function of forms and reports, in other words, is to provide a graphical interface for users so that they can add, modify, delete or report on their data.
3. Logic tier. This is the part of the relational database application in which you automate tasks for users. For example, you might implement code to open a form and retrieve a specific set of records for the user to work on. More complex tasks require more complex code, but essentially, this part of the database is there to move the burden of logical operations on data from the user to the application.

Now, the important thing here is that Access, unlike many other development environments, provides all three components. It has a database engine, called ACE, to manage the tables. It has forms and reports from which you create an interface. It has VBA (and for really simple tasks, macros) with which you design and implement automation logic. That makes Access uniquely well-suited to rapid development tasks. SQL Server is a database engine with some very powerful logic tools (stored procedures, for example) but no interface. Web pages provide highly interactive user interfaces and logic, but require an additional database engine (e.g. SQL Server or MySQL, etc.) to store data.

One problem most of us have encountered at some point in our career is that, because Access is so user-friendly and so forgiving of ill-founded designs, a new developer can get quite a way into a project with less than appropriate approaches and designs before things become too unwieldy to continue. I am thinking here of the Lookup fields in tables we've been talking about. Initially, they seemed really sweet because they put the combo box right there in the table for you. Easy-peezy, Right? Only later do you find they complicate things; in other words, trying to successfully merge interface elements (combo boxes) into data elements (fields in tables) is seldom productive for either kind of element.

I'm coming around now to talking about the importance of having a fully developed Data tier in place long before you start whacking out interface elements. Why? Because the interface (i.e. forms and reports) has to be designed to work with the data. If you end up changing tables during the design phase, your forms are also going to have to be modified, or even scrapped altogether. Extra work for no reason.

With regard to the Default values for controls on forms, I think the best answer I can think of is that you are working one step removed from the table. The interface is intended specifically FOR that sort of thing; it's the tier in which user input is mediated before being stored in the table. I'm sure there's a better technical explanation, but to me the common sense logic of thinking in terms of why you are even bothering with an interface makes it more reasonable to have defaults in the interface take precedence. Of course, they can't override validation rules that may exist on fields or on tables, but the defaults themselves are part of the input and update tasks pertaining to the interface.

Hi GroverParkGeorge, thanks for your reply.

I am using about 15 Combo Boxes over 3 tables, all value lists. 3 are Yes/No, the rest are simple like Title (Mr, Mrs etc), Risk (Wheelchair, Mental, Respiratory etc), Position (Internal, Perimeter, Landlocked), the latter 2 use the first letter in column 1 and the full description in column 2.

As you comment, these are things that do not change much over time.

However, I will be soon venturing into related tables. I have a Plots table (allotments) and every Plot has a Tenant which is held in a Personal Details table. I will do a lookup on the Personal table when allocating a Tenant to a particular Plot via a Combo Box.

So, I agree, I will remove all Combo Box properties from the Tables and only rely upon the Form based Combo Box.

But I do find it curious that the Default for the Combo Box on the Form is taken from the Form but the values for the RowSource are taken from the Table!

Thanks

Hi HansV

I agree that the Combo Box is best to set up on the Form. I am only using Value Lists. I have added Combo Boxes to both the Table and Form in my lack of understanding.

So, should I only put the Combo Box on the Form, not the Table? I ask this because the Combo Box on the Form seems to display the values from the Table even though I have the Value List on the Form. I noticed this when I misspelled a value on the Table, the misspelled value was displayed, not the value from the Form.

So, should I remove all details of Combo Box use from my Tables and rely entirely upon Forms and revert the Lookup on the Table to be Text Box?

I notice that the Default from the Form is used instead of the Default on the Table - I shudder to think that the developers of Access haven't used normalisation in placing the same property on two objects - in the case of Default it takes the value from the Form, not the value on the Table - reverse of the contents of the Combo Box!  Or is there a good reason for this?

Thanks for your speedy reply.

Although you can set the Display Property of a field in a table to ComboBox, it is often confusing.

Let's say you have the following tables:

Categories with fields CategoryID (AutoNumber, Primary Key) and Category (Short Text), plus possibly more.

Products with fields ProductID (AutoNumber, Primary Key), Product (Short Text) and CategoryID (Number, Foreign Key linking to Categories), plus possibly more.

If you set the Display Property of CategoryID in the Products table to Combo Box, you'd usually set the lookup properties as follows:

Row Source Type: Table/Query

Row Source: Categories

Bound Column: 1, so that CategoryID in Products corresponds to CategoryID in Categories.

Column Count: 2

Column Widths: 0";1" so that the field displays the Category field while being linked to CategoryID.

This appears to be convenient and user-friendly, but while you are manipulating the database as designer, it is confusing because you see a text where the actual field value is a number.

So it's better not to do this. After all, end users should never interact with the tables directly, only through forms and reports.

On a form bound to the Products table, you can create a combo box bound to the CategoryID field, and set the properties of the combo box as described above. The end user will only see the description of the category, not its numeric ID.