Expert: Scottgem - 2/22/2005

Question
Is it possible to program Access so that I can enter a code into my table and get information to prefil into another table cell based on the code entered?
Thanks for your assistance.

Answer
Yes its possible, but generally it violates normalization rules. The purpose of a relational database is to reduce redundant data. Therefore, you don't want data in one table that already exists in another. For example: you might have an Orders application and you want to select a customer from your customer table. In such a case the ONLY thing you need in the Orders table is the Primary key value for that customer. This is known as the foreign key. You can pull the rest of the customer info (name, address etc) via a join on the CustomerID.

The exception to this is time sensitive data. When you have data that might change over time and you need to capture the data at that point in time. Lets look at the same Orders application for an example. You select a product for the order. You want to carry over the current price of the product because prices can change and you don't want to review the order a few months from now and have it reflect the current price. So you would copy the current price from the Products table and store it with the order data.

How you do this depends on how you have your database setup. If you look at the Northwinds sample (especially the order forms) you can see examples of how this is done. If you need any more help with it, please let me know.

HTH
Scott<>