This will take some VBA coding. the After Update is an event. It "fires"
(runs) after you have completed entry in the text box. Be aware there is
also an After Update event for a form, but don't worry about that now.
1. Open your form in Design Mode.
2. Click on the text box you need to work with. This should be the text box
where you put in the Client's Id.
3. Click on Properties (either on the tool bar or by right clicking and
selecting properties)
4. Click on the Events tab and select After Update.
5. Select code builder. The VB editor will open and you will be in
procedure for the event
6. Copy the code I posted earlier.
7. Change the names to protect the innocent
a. The name of the field in your table you want to put the client's id in
b. The name of the client table
c. The name of the control (text box) on your form where you put the
client's id.
a b a
c
If IsNull(DLookup("[Client]","ClientTable","[Client] = '" & Me.Client & "'")
then
b a
c
strSQL = "INSERT INTO ClientTable (Client) VALUES '" & Me.Client & "';"
CurrentDB.Execute strSQL
End If
One other thing. For this method, you need to go into design mode for your
client and consultant tables and be sure all fields have "Required" set to No
and "Allow Zero Length" set to Yes. If you don't do this, you will get
errors trying to add a new row to the table. The INSERT INTO statement will
attempt to add a new row to the table, and if any validation rules are
broken, you will get an error.
For the consultants, just change the names where necessary.
Don't be discouraged if it doesn't work the first time. It may take some
twiking, because this is untested "air code". Post back if you have more
questions.
Michelle said:
How do I enter this information in the AfterUpdate box? Do I use an
expressions builder, code builder, or just type what you wrote directly in
the box? Since I'm fairly new with Access, I'm not quite clear on where I
substitute my table name and field name, for the code you gave me. Thanks
for the prompt reply!
:
Michelle,
You did not say so, but I will have to assume you are using a form to enter
data in the main table. If you are entering data directly in the table, you
can't do it.
So, for your form:
In the After Update event of the control (hopefully a text box), Check to
see if the client already exists. If it does, no action is required. If it
does not exits, add the client to the client table.
If IsNull(DLookup("[Client]","ClientTable","[Client] = '" & Me.Client & "'")
then
strSQL = "INSERT INTO ClientTable (Client) VALUES '" & Me.Client & "';"
CurrentDB.Execute strSQL
End If
Now, here are the gotchas:
1. If you have any required fields in the client (or consultant table) then
you will get an error.
2. How will you know to later come back and complete this client's data?
A better approach is (but users may not like it) would be to use the DLookup
in the After Update event as above, but rather than just adding the
incomplete row, would be to present a message box telling the user the client
does not exist, ask them if they want to add the client. If the do, then
open the form that accepts entry for client data and let them add it. If
they do not, then you will have to cancel entry in the main table, because
you don't have the info you need.
:
I have created a database in Acess 2003 with all information related to our
company's projects. In the main table, I have a column for "Clients" and a
column for "Consultants." I have created a table for "Clients" and a table
for "Consultants" each table including the contact information for clients
and consultants.
I would like to enter a client name in the main table and have it
automatically enter the name in the client table so that I can just add the
client's contact information later in the client table. I would like to do
the same for consultants.
This should be a simple task, and I've tried to set up relationships, but it
still doesn't work. Any suggestions? Thanks in advance.
