Using a variable with INSERT INTO

[Jackie]

How do you make the DoCmd.RUNSQL "INSERT INTO ...." to recognize a variable that is set in VB code just prior to exectuting the "INSERT INTO" statement?

I use the following code to append a record to a table. I get the "Enter Parameter Value" window for the keyword_temp
field. I know that the keyword_temp value is being set as it is displayed in the MsgBox.


Dim keyword_temp As String
Dim intCurrentRow As Integer
Dim ctlSource As Control
Set ctlSource = frm!lst_applications
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
keyword_temp = ctlSource.Column(0,IntCurrentRow)
MsgBox (keyword_temp)
'category_temp = "Application"
DoCmd.RunSQL "(INSERT INTO KeyWords(spcr_number, category, keyword) VALUES (Forms![Enter Key Words]![lst_spcr_number], 'Application ', keyword_temp))"
End If

 

[BobLoblaws]

Dim keyword_temp As String
Dim intCurrentRow As Integer
Dim ctlSource As Control
Set ctlSource = frm!lst_applications
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
keyword_temp = ctlSource.Column(0,IntCurrentRow)
MsgBox (keyword_temp)
'category_temp = "Application"
DoCmd.RunSQL "(INSERT INTO KeyWords(spcr_number, category, keyword) VALUES (Forms![Enter Key Words]![lst_spcr_number], 'Application ', " & keyword_temp & &quot)"
End If

 

[Jackie]

Thank you for responding.

I got an error message when I used double-quotes for the "&keyword_temp&" syntax, so I used single quotes. The error was due to the INSERT INTO statement must be surrounded by double-quotes.

This resulted in "&keyword_temp&" being added to the table instead of the value stored in the keyword_temp field.

Is there something else that you might suggest?

Jackie

 

[BobLoblaws]

Let's try this again...

Dim strSQL as String

strSQL = "INSERT INTO Keywords (spcr_number, category, keyword) VALUES (Forms![Enter Key Words]![lst_spcr_number], 'Application ','" & keyword_temp & "');"

MsgBox strSQL

DoCmd.RunSQL strSQL

What does the strSQL print in the messagebox?

 

[Jackie]

Thank you again for your help. Our responses must have crossed in cyber-space.

I fiddled with the sequence of single-quotes, double-quotes and ampersands and came up with the following solution.

DoCmd.RunSQL "(INSERT INTO KeyWords(spcr_number, category, keyword) VALUES (Forms![Enter Key Words]![lst_spcr_number], 'Application ', ' " & keyword_temp & "' ))"

Jackie