How to change a column's "Row Source" in Access table at run-time?

Question

I would like to change the "Row Source" values in an Access table through code. This way I can filter what a user can choose in a column's combo box.

Answer 1

I don't know whether or not this is an option for you, but I would recommend separating the table and filtering options, setting up a form to do the data entry and modification and using code to set the Row Source for the combo box on the form. As you've already seen, while Access does provide the opportunity to treat tables as more than just tables, that is primarily for GUI purposes. Behind the scenes, Access prefers that you work with forms for data entry.

If you need to pursue the table method, you might try looking at the MS knowledge base articles referenced here. (The kb articles are here and here.) Keep in mind that these refer to Access 97 and may no longer be relevant. I wasn't able to find any more recent references to those properties in an Access tabledef.

Answer 2

In line with Dave DuPlantis' answer, you may wish to read "The Evils of Lookup Fields in Tables" From the Access Web by various Microsoft Access MVPs.

Answer 3

Your question is quite confusing. "RowSource" is a property of a list box or combo box. A table does not have a RowSource, but, as Remou points out, Access allows you to define fields in your tables so that they display a combo box that looks up values from a related table. This combo box has a rowsource, so it's perhaps what you mean, but it's simply a terrible idea.

That combo box that you define in the table design is a user interface object, and UI doesn't belong in your table definitions. Not only does it lead to all sorts of problems, but it means your tables themselves can be broken by changes to other tables that invalidate the definition of the lookup fields.

User interface controls belong in user interface objects, which means that you should be putting them only in forms (or, at the lowest level, in saved queries).

Filtering for a user is, again, a user interface issue, and not one that has anything to do with table design.

--
David W. Fenton
David Fenton Associates