Call value from a textbox during SQL execution

[David Ball]

Hi,

I have this segment of code that adds data to fields FName and FPath in the table “Files”. It works fine but the syntax is a nightmare with all the double quotes and “and” symbols. I don’t really understand it.

[FONT=Calibri][SIZE=3]strSQL = "INSERT INTO Files " _[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]          & " (FName, FPath) " _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          & " SELECT """ & strTemp & """" _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          & ", """ & strFolder & """;"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]         CurrentDb.Execute strSQL[/FONT][/SIZE]

I have another field in the table Files called CompletedBy that shows who updated the table (they type their name into a textbox “Text1” before clicking a command button to run the code). I would like to add this data to the table during the execution of strSQL. All the records added to the table Files will have that value in the CompletedBy field (say, Bob Jones).
I tried the below changes but the code doesn’t work. Not sure if it is the confusing syntax causing me problems or I’m just going about it the wrong way?

[FONT=Calibri][SIZE=3]strSQL = "INSERT INTO Files " _[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]          & " (FName, FPath, [B]CompletedBy[/B]) " _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          & " SELECT """ & strTemp & """" _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          & ", """ & strFolder & """ [B]& Me.Text1 &[/B] """;"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]         CurrentDb.Execute strSQL[/FONT][/SIZE]

How would I add a value in field CompletedBy, from the textbox Text1, to all records added to table Files when the code is run?
Thanks very much
Dave

 

[CJ_London]

you can use single quotes instead which I find more readable. You also don't need the semi colon at the end and you probably are breaking the line up a lot more than you need to for a simple query

Your error is here where you are missing a comma

strFolder & """ & Me.Text1 & """;"


strSQL = "INSERT INTO Files (FName, FPath, CompletedBy) SELECT '" & strTemp & "', '" & strFolder & "', '" & Me.Text1 & "'"

if you do want to break it up, suggest do so in a more readable way e.g.

strSQL = "INSERT INTO Files (FName, FPath, CompletedBy) SELECT '" & _
             strTemp & "', '" & _
             strFolder & "', '" & _
             Me.Text1 & "'"

 

[David Ball]

That works, and is a lot easier to look at.

Thanks very much