insert into with table values as well as form field values 1

[vburrows]

OK I am sending mail messages to a customer list I am trying to send the list to be mailed to a table in ACCESS I need to use the information on the form as well as the query I am calling to I am trying to use an insert into sql statement what am I doing wrong heres is the code.

Public Sub SendMailSQL()
Dim strsql As String
Dim k As String
Dim j As String
Dim i As String
Dim h As String
Dim b As String
Dim a As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim attache As String
Dim vendor As String
Dim strSubject As String
Dim strEmailAddress As String
Dim strEMailMsg As String
Dim ingCounter As Integer
Dim intCount As Integer
strEMailMsg = Me.emailbody
DoCmd.SetWarnings False
strSubject = "testing messages"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrySendMail")
rst.MoveFirst
a = CurDir 'USE THIS TO INSTALL

Do Until rst.EOF
k = "D:\Development\Cartwright\calendar\blank.pdf" 'location of the blank.pdf
j = "D:\Development\Cartwright\calendar\outgoing2\blank.pdf" 'location of the template
h = "D:\Development\Cartwright\calendar\outgoing2\" 'location of the out going files for
        vendor = rst![vendor]
        strEmailAddress = rst![strEMailAddess]
FileCopy k, j 'Simple File copy
i = Format(Now, "yyyymmdd")
b = h & i & vendor & ".pdf" 'rename files
Name j As b
    attache = b
strsql = "INSERT INTO Outgoingserver (from, Subject, message) VALUES " & Chr(34) & strEmailAddress & Chr(34) & ", " & Chr(34) & b & Chr(34) & "," & Chr(34) & strEMailMsg & Chr(34) & " ;"
Debug.Print strsql
    DoCmd.RunSQL (strsql)
    
rst.MoveNext
Loop

rst.Close
DoCmd.SetWarnings True
Set rst = Nothing
dbs.Close
Set dbs = Nothing
DoCmd.SetWarnings True
End Sub

 

[PHV]

Replace this:
strsql = "INSERT INTO Outgoingserver (from, Subject, message) VALUES " & Chr(34) & strEmailAddress & Chr(34) & ", " & Chr(34) & b & Chr(34) & "," & Chr(34) & strEMailMsg & Chr(34) & " ;"
with this:

strsql = "INSERT INTO Outgoingserver (from, Subject, message) VALUES ('" _
  & strEmailAddress & "','" & b & "','" & Replace(strEMailMsg, "'", "''") & "');"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vburrows]

ok I am getting 3134 error "syntax"
the debug.print

INSERT INTO Outgoingserver (from, Subject, message) VALUES ('rgparker@transposvc.com','D:\Development\Cartwright\calendar\outgoing2\20080606123456.pdf','Change this text Before you send any Emails testing');

 

[PHV]

You may try to replace this:
(from,
with this:
([from],

Bottom line; avoid to use reserved words as field name ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vburrows]

OOh yeah overlooked that one.
I really appreciate it that fixed it