Access 2007 - I have a table (Job Numbers) that gives me autonumbers for the jobs we work on.  It has 3 fields, Job#/LastName/FirstName.  My second table (Applicant List) contains all the client information.  I want to be able to type a Job# in my second table and have it automatically pull the LastName and FirstName field info into the second table. Not every client on the Applicant List will have a Job# assigned so I still need to be able to fill in the LastName and FirstName manually for those that aren't assigned a Job#. How can I do this? Thank you.  Joanne

---

Richard Pontificates:

Normally, you don't want to have duplicate data in your database. Copying names between two tables like that is generally considered bad database organization. What you should have is a table with your customers and then a table with your jobs. The jobs should be linked to the customers by the customer ID. So your database structure SHOULD look like this:

JobTable
--------
JobID
JobName
Description
CustomerID
etc.

CustomerTable
-------------
FirstName
LastName
etc.

So now if you want to see what Jobs belong to a customer, you have the customer's ID stored in the job table. This is basic RELATIONAL database design, and here is a free tutorial that explains it in more detail.

Relational Table Design in Microsoft Access

HOWEVER, if you really want to type in a job number and have the customer's name copied into the other table, you could do it, but it's going to involve a little VBA programming. You would need a text box to type in the job number and then an AfterUpdate with a DLOOKUP statement to pull in the data and save the duplicate copy in the second table. Here's a tutorial that talks about that:

DLOOKUP Function in Access

But I strongly recommend you consider option 1.