Email loop not recognizing apostrophe

[TallMan]

Hello,


I am having trouble getting my DB to recognize and accept an apostrophe in the ("FA_Email_Address"). Once I click the "send Email" button on my form it gives me an error because the "client_Name" or ("FA_Email_Address") has an apostrphe. If there is not an apostrophe present the code will run fine and the email will be sent.

Could someone please take a look at my code and let me know what I am going wrong or what I can add?


While Not rs.EOF
sqlinsert = "INSERT INTO Pending_Tbl (Account_Number, Client_Name, Branch, Primary_FA_num, New_Subcategory_ID, DT_Code_Changed, Email,Dt_contract_Received,Pending_Notes) VALUES ('" & rs.Fields("Account_Number") & "','" & rs.Fields("Client_Name") & "', '" & rs.Fields("Branch") & "', '" & rs.Fields("Primary_FA_num") & "', '" & rs.Fields("New_Subcategory_ID") & "', '" & rs.Fields("DT_Code_Changed") & "', '" & rs.Fields("FA_Email_Address") & "', '" & rs.Fields("Dt_Contract_Received") & "', '" & rs.Fields("Pending_Notes") & "');"
db.Execute (sqlinsert)
rs.MoveNext
Wend

 

[boblarson]

instead of using the single appostrophe to encapsulate the items, use

& Chr(34) &

instead.

So like:

" & Chr(34) & rs.Fields("FA_Email_Address") & Chr(34) & "

And I would do that for any of the other fields which are using an appostrophe. I've started using that method because it is easy to remember (over trying to get the right number of double-quotes) and it works every time.

 

[TallMan]

I will try this! Thank you Bob! If I have any issues I will post back.

 

[TallMan]

Bob,

What do I end the statement with?
While Not rs.EOF
sqlinsert = "INSERT INTO Pending_Tbl (Account_Number, Client_Name, Branch, Primary_FA_num, New_Subcategory_ID, DT_Code_Changed, Email) VALUES " & Chr(34) & rs.Fields("Account_Number") & Chr(34) & "," & Chr(34) & Replace(rs.Fields("Client_Name") & Chr(34) & ", " &Chr(34)& rs.Fields("Branch")& Chr(34) & ", " & Chr(34)& rs.Fields("Primary_FA_num") & Chr(34) & ", " & Chr(34) & rs.Fields("New_Subcategory_ID") & Chr(34) & ", " & Chr(34) & rs.Fields("DT_Code_Changed") & Chr(34) & ", " & Chr(34) & rs.Fields("FA_Email_Address") & Chr(34) & "
db.Execute (sqlinsert)
rs.MoveNext
Wend

 

[boblarson]

Either

rs.Fields("FA_Email_Address") & Chr(34) & ";"

or

rs.Fields("FA_Email_Address") & Chr(34)

 

[TallMan]

Bob-

I could not get the Chr(34) to work.

Do you see anything wrong with this code?


sqlinsert = "INSERT INTO Pending_Tbl (Account_Number, Client_Name, Branch, Primary_FA_num, New_Subcategory_ID, DT_Code_Changed, Email, Dt_Contract_Received, Pending_Notes) VALUES ('" & rs.Fields("Account_Number") & "','" & Replace(rs.Fields("Client_Name"), "'", "''") & "', '" & rs.Fields("Branch") & "', '" & rs.Fields("Primary_FA_num") & "', '" & rs.Fields("New_Subcategory_ID") & "', '" & rs.Fields("DT_Code_Changed") & "', '" & Replace(rs.Fields("FA_Email_Address"), "'", "''") & rs.Fields("Dt_Contract_Received") & "', '" & rs.Fields("Pending_Notes") & "');"

 

[TallMan]

I am getting an error "run-time error 3346"