Linking Fields

[Brian Hauf]

I am trying to set up something in a database that seems
like it should be simple but I cannot get it to work. We
have created a table for our clients with the clients name
and client number. The client number is set by the auto
number and the client number is set as the primary key.

I have created another table that is an archive of all
the projects we have done for our clients. Each project
is auto numbered and this table is related to the client
table by the client number.

What I want to do is create a form so that when I enter
the clients name into the name field the client number
for that client automatically appears in the client
number field and when the record is entered both fields
are saved in the archive table

I am new to this so i hope this makes sense. Any help you
can give me is appreciated. Thanks

 

[Andrew Smith]

First, you don't save the client name in the archive table - you only need
the id number to identify the client and the name should be stored in the
client table and nowhere else.

Do you have a table for projects other than the archive table? If so the
answer that I'm about to give may not be quite right!

Assuming there is only one projects table then all you do is create a form
based on this table and use a combo box that is bound to the clientID field
in the project table. Set up the combo box so that it will display the
client name (sorted alphabetically), but will store the ID number. The combo
box wizard should do this for you.

Then you just need to select the clent name from the list, or type in the
first few characters to find the right client, but the value stored in the
project table will be the client id.