Question:

I have created a bunch of tables and I want to validate data being keyed.  I want to use forms to key the data and have subforms so that data enterred into fields will be automatically passed to related data fields in subform tables.  I currently have fields in my tables setup as lookup fields pulling from other tables.  I’m wondering if it is best to use the lookup fields or to “update” fields using subforms?

Solution:

First: Are you 100% certain that you should “pass” data to your subforms? Generally, you’d relate those records in the subforms to the “parent” record in the mainform. The ONLY reason to store data in the subform would be if you need some sort of history. In the overwhelming majority of cases (and I’m almost certain in this one) you would NOT pass data to the subform.

Lookups and Subforms perform drastically different purposes. Lookups are generally used to limit choices a user can make for a specific column .. for example, if I want to only allow users to choose from Red, Yellow or Green for a txtColor field in my table, I’d use a combo or listbox on the Form. However, if I want users to be able to add multiple “child” records to a single Parent record, I’d use a subform (which may or may not have combos on it as well).

You really, really should NOT use lookups defined on your tables. They serve no purpose, and can cause a lot of issues down the road. Lookups defined on the form are perfectly fine.