DoCmd.RunSQL "INSERT..." is causing problems with text data.

[Guest]

Hi all,

Table1 has column col1 as memo.

I'm trying to insert text from a text box on the Form into my col1. But the
textbox on the form has for example:

3" and 4" wide panels. 3"2' more space.

Here's my code that gives Runtime error 3075 for wrong syntax:

sql = Forms!frm1!txt1.text
DoCmd.RunSQL "INSERT into Table1 VALUES (' " & sql & " ')"

I know those quotes and double quotes are the ones causing the syntax error,
but how can I correct this problem. I tried searching for a similar question,
but couldn't find one.

Thanks for you help.

 

[John Vinson]

Hi all,

Table1 has column col1 as memo.

I'm trying to insert text from a text box on the Form into my col1. But the
textbox on the form has for example:

3" and 4" wide panels. 3"2' more space.

Here's my code that gives Runtime error 3075 for wrong syntax:

sql = Forms!frm1!txt1.text
DoCmd.RunSQL "INSERT into Table1 VALUES (' " & sql & " ')"

I know those quotes and double quotes are the ones causing the syntax error,
but how can I correct this problem. I tried searching for a similar question,
but couldn't find one.

Thanks for you help.

Might the form textbox *contain* a ' character, perhaps as an
apostrophe? If so, you can use " (ASCII value 34) to delimit. Try:

DoCmd.RunSQL "INSERT into Table1 VALUES (" & Chr(34) & sql & Chr(34) &
")"

Note that you should probably also use txt1's Value property, which is
always available, rather than its Text property (meaninful only when
the control has the focus). The Value property is the default so you
could simply use

DoCmd.RunSQL "INSERT into Table1 VALUES (" & Chr(34) & Forms!frm1!txt1
& Chr(34) & ")"

Normally, of course, no code is needed to do this at all - you'ld just
use a bound form with the textbox bound to the memo field. I presume
you're "doing it the hard way" for some good reason!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Ken Snell [MVP]]

John -

His textbox appears to contain a string that has both a ' and a " in it!

He gave this example in his post:

3"2' more space
--

Ken Snell
<MS ACCESS MVP>

 

[John Vinson]

John -

His textbox appears to contain a string that has both a ' and a " in it!

He gave this example in his post:

3"2' more space

OOPS! He did indeed.

Can you get around this by doubling up? e.g.

INSERT INTO tablename
FIELDS memofield
VALUES ("'" & REPLACE([Forms]![formname]![controlname], "'", "''") &
"'");

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Tim Ferguson]

DoCmd.RunSQL "INSERT into Table1 VALUES (' " & sql & " ')"

Public Function SQLString(SomeText As Variant) As String

' change these to single quote chars if you use SQL server
' or prefer single-q delimiters
Const c_strOneQuote = """" ' one double-quote char
Const c_strTwoQuote = """""" ' two d-q chars

If IsNull(SomeText) Then
' translate into a SQL NULL value -- this is not always
' correct but at least it's legal!
SQLString = "NULL"

Else
' double up the quotes
SQLString = Replace(SomeText, _
c_strOneQuote, c_strTwoQuote, 1, -1, _
vbTextCompare)

End If

End Function


' #####################################

db.Execute "INSERT INTO Table1 " & _
"VALUES( " & SQLString(Forms!frm1!txt1.Value) & " )", _
dbFailOnError

Hope that helps


Tim F

 

[Guest]

THANK YOU SO MUCH EVERYONE!

Al