RE: [AccessDevelopers] Appending Records

Mon, 03 Oct 2005 12:25:40 -0700

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.
 
 

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 -----
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



Reply via email to