INSERT INTO problems

[Guest]

Hallo

I am having problems with the follwoing INSER INTO statment it comes up with
a (Enter Parameter Value) and i dont know why

This is the code

Dim strT As String
Dim strRA As String

strT = "XAAA" ' the strT value will change when opening the form but for
testing i left it as a fixed value

strRA = (strT & "A")
DoCmd.RunSQL "INSERT INTO RACF_Table ( [RACF-ID] ) Values (" & strRA & ");"

This code runs when clicking on the add button

I hope some one can help

 

[Duncan Bachen]

Hallo

I am having problems with the follwoing INSER INTO statment it comes up with
a (Enter Parameter Value) and i dont know why

This is the code

Dim strT As String
Dim strRA As String

strT = "XAAA" ' the strT value will change when opening the form but for
testing i left it as a fixed value

strRA = (strT & "A")
DoCmd.RunSQL "INSERT INTO RACF_Table ( [RACF-ID] ) Values (" & strRA & ");"

This code runs when clicking on the add button

I hope some one can help

Since the value you are inserting is a string, you need to enclose it in
single quotes.

DoCmd.RunSQL "INSERT INTO RACF_Table ( [RACF-ID] ) Values ('" & strRA &
"');"

 

[TC]

Change this:

DoCmd.RunSQL "INSERT INTO RACF_Table ( [RACF-ID] ) Values (" &
strRA & ");"

to this:

dim sql as string
sql = "INSERT INTO RACF_Table ( [RACF-ID] ) Values (""" & strRA &
""")"
msgbox sql
DoCmd.RunSQL sql

I've fixed the quote-marks in your sql statement. I suggest you copy &
paste that line. Do not type it manually, or you might mess it up!

You should normally build sql statements into a string variable, as I
have shown above, so you can print the content using msgbox or
debug.print, when necessary to help with debugging. I've shown you that
above. You'll want to comment-out the msgbox statement when you're sure
the sql is correct. But keep using the string variable. The "wasted"
overhead in using a variable, when it is not really required, is
infinitesimal.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Thank you TC and Duncan

 

[Brian Wilson]

Thank you TC and Duncan

You need to be careful if the value itself might contain speech marks - if
so they need to be douled up. As this sort of operation is so common, you
could create a bit of code to do any doubling up and surround the string in
quotes. So you just need to write:

strValue=AddQuotes(strValue,DoubleQuote)

This function is my own - but I think it makes the code look more readable
than any other I have seen.




' ****** Code Starts ********
Option Compare Database
Option Explicit

Public Enum QuoteTypeEnum
NoQuote
SingleQuote
DoubleQuote
End Enum

Public Function AddQuotes(strValue, Q As QuoteTypeEnum) As String

Dim strReturn As String

Select Case Q

Case QuoteTypeEnum.SingleQuote
strReturn = Replace(strValue, "'", "''")
strReturn = "'" & strReturn & "'"

Case QuoteTypeEnum.DoubleQuote
strReturn = Replace(strValue, """", """""")
strReturn = """" & strReturn & """"

Case Else
strReturn = strValue

End Select

AddQuotes = strReturn

End Function