Thanks a lot for your input, John. Let me briefly describe the issue that
necessitates the storage of data in the second table. We have a clients table
that lists all the pertinent info about our clients. We have an assignments
table that lists each case we have received. We use a form to enter each new
assignment that is then stored in the assignments table.

I work for a forensic accident reconstruction firm and oftentimes our files
must be produced in legal proceedings. Let's assume we didn't "store" the
data in the assignments table but just accessed client info from the clients
table. If info changed about one of our clients, e.g., they transfer to a
different company, change their name, or get a new address, that new info
would update in the assignments table. But we need an unchanging record about
the case to establish the facts at the time.

So I am back to figuring out not only how to autopopulate the assignment
input form but have that data stored in the assignments table. I have a
control box called CUST ID in the assignment input form. I set the row source
type to table/query. I set the row source to the following:

SELECT Clients.CustomerID, Clients.Contact FROM Clients ORDER BY CustomerID;

I then set the control source of the textbox to:

=[CUST ID].Column(1)

This results in the name of the client (contact) autopopulating when I
select the customer id. However, it apparently does not get stored in the
assignments table, as I removed the required property for that field from the
assignments table and the client's name that autopopulated in the form does
not appear in the table. This is where I am stuck.

(Of course, I have other data that needs to autopopulate too. I just need to
make sure the method works before I add the additional code in the row source
of the CUST ID control.)
--
rszebras


"John Vinson" wrote:

I think you're mistaken. It is almost NEVER necessary to copy data
redundantly from one table into another table. If you're assuming that
you must have all the information in one table in order to use it,
that assumption is simply wrong.

You're using a relational database. USE IT RELATIONALLY! Store the
name, address, etc. *once*, and only once, in the Client table where
it belongs. If you need that information in conjunction with data in
some other table, say for a Report, store the ClientID and use a Query
joining the Client table to this other table; base your Report *on
that query*, choosing your table's fields from your table, and the
client information fields from the client table.

John W. Vinson[MVP]