Modify your last line to include the second closing parenthesis (it goes
the with one after the word "execute").
Also you need to enclose your actual text string in single quotes.
db.Execute ("INSERT INTO tblContactDetails VALUES(" &
results!lngClientID & ",'2005EL')")
-----Original Message-----
From: [email protected]
[mailto:[EMAIL PROTECTED] On Behalf Of Crosier, Dawn
Sent: Monday, October 03, 2005 14:25
To: [email protected]
Subject: RE: [AccessDevelopers] Appending Records
Importance: Low
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.
Dawn Crosier
Application Specialist
"Education Lasts a Lifetime"
This message was posted to a newsgroup. Please post replies and
questions to the group so that others can learn as well.
From: [email protected]
[mailto:[EMAIL PROTECTED] On Behalf Of Toby Bierly
Sent: Friday, September 30, 2005 4:40 PM
To: [email protected]
Subject: Re: [AccessDevelopers] Appending Records
' 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 -----
From: Crosier, Dawn
To: [email protected]
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?
Dawn Crosier
Application Specialist
"Education Lasts a Lifetime"
Please zip all files prior to uploading to Files section.
SPONSORED LINKS Microsoft access developer Microsoft access help
Microsoft access database
Microsoft access training Microsoft access training course Microsoft
access programming
YAHOO! GROUPS LINKS
Visit your group "AccessDevelopers" on the web.
To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
------------------------ Yahoo! Groups Sponsor --------------------~-->
Fair play? Video games influencing politics. Click and talk back!
http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/q7folB/TM
--------------------------------------------------------------------~->
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
