----- Original Message -----
Sent: Monday, October 03, 2005 12:24
PM
Subject: RE: [AccessDevelopers] Appending
Records
Toby -
I am having a little trouble with your code.
While Not results.EOF
db.Execute("INSERT INTO
tblContactDetails VALUES(" & results!IngClientID &
", <<txtContactType here>>")
results.MoveNext
Wend
In
particular the part where I give the value of txtContactType. I need to
feed the string "2005EL" so I have tried:
db.Execute
("INSERT INTO tblContactDetails VALUES(" & results!lngClientID &",
"2005EL") which gives me a syntax error. When I change it to "2005EL"
") I also receive a syntax error.
db.Execute ("INSERT
INTO tblContactDetails VALUES(" & results!lngClientID & ", 2005EL ")
gives me a missing operator error.
I would really like to be able to run this in code
since I believe that this will provide me the best flexibility for updating
information for future seminars.
Thanks in advance for your
help.
This message was
posted to a newsgroup. Please post replies and questions to the group so
that others can learn as well.
' Update Contact Details
Dim db As DAO.Database
Dim results As DAO.Recordset
Set db = CurrentDb
Set results = db.OpenRecordset("your query from
your email here")
While Not results.EOF
db.Execute("INSERT INTO
tblContactDetails VALUES(" & results!IngClientID &
", <<txtContactType here>>")
results.MoveNext
Wend
***Query to find reverse of query in your
email***
SELECT tblClientInfo.lngClientID
FROM [ExportToMasterClient - 2005 EL
Seminar], tblClientInfo
WHERE ([ExportToMasterClient - 2005 EL Seminar].[Last
Name] <> tblClientInfo.txtLastName)
OR ([ExportToMasterClient - 2005 EL Seminar].[First
Name] = tblClientInfo.txtFirstName)
OR ...
The above query will find all records where there is any
field that DOESN'T match.
HTH,
Toby
----- Original Message -----
Sent: Friday, September 30, 2005 1:58
PM
Subject: [AccessDevelopers] Appending
Records
I have a master client
contacts database. There are three tables: tblClientInfo,
tblContactDetails, tblContactTypes
tblClientInfo has a pk
of lngClientID (autoNumber)
tblContactDetails has
the foreign key of lngClientID & foreign key of txtContactType (joining
to tblContactTypes)
I also have many
Seminar databases used for registering people to attend the various seminars
that our firm provides.
Now, the Marketing
Manager wants the attendee's of the seminars to be migrated into the Master
Client Database. On the surface, not really a problem.
However,
1. I
don't want to duplicate people in the Client db. (Clients can attend
more than one seminar)
2. I
do want to locate the client and update their Contact Details to reflect
that they have attended a certain seminar.
I can identify the
clients that are already in the database by using the following
query:
SELECT
tblClientInfo.lngClientID
FROM [ExportToMasterClient - 2005 EL Seminar]
INNER JOIN tblClientInfo ON ([ExportToMasterClient - 2005 EL Seminar].[Last
Name] = tblClientInfo.txtLastName) AND ([ExportToMasterClient - 2005 EL
Seminar].[First Name] = tblClientInfo.txtFirstName) AND
([ExportToMasterClient - 2005 EL Seminar].Title = tblClientInfo.txtTitle)
AND ([ExportToMasterClient - 2005 EL Seminar].Organization =
tblClientInfo.txtCompanyName) AND ([ExportToMasterClient - 2005 EL
Seminar].Address = tblClientInfo.txtAddress1) AND ([ExportToMasterClient -
2005 EL Seminar].City = tblClientInfo.txtCity) AND ([ExportToMasterClient -
2005 EL Seminar].State = tblClientInfo.txtState) AND ([ExportToMasterClient
- 2005 EL Seminar].ZipCode = tblClientInfo.txtZip);
What I don't know how
to do is using this information add a record for the
tblContactDetails.
I also do not know how
to get the reverse of this query so I can append new clients into the
database.
Suggestions?
Please zip all files prior to uploading to Files section.
SPONSORED LINKS
YAHOO! GROUPS LINKS
