Running Append Query in VBA (1 Viewer)

[jmaty23]

I am trying to Run an Append Query using VBA. I cannot seem to get it to work. I am getting syntax error when I try to run it. Can someone take a look?

Private Sub cmdloaddefault_Click()

DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO tblschedule_lineitem ( labor_name, shift, location, title, Active, Saturday, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, weekof )"
SELECT tbllabor_info.labor_name, tbllabor_info.shift, tbllabor_info.location, tbllabor_info.title, tbllabor_info.Active, tbllabor_info.Saturday, tbllabor_info.Sunday, tbllabor_info.Monday, tbllabor_info.Tuesday, tbllabor_info.Wednesday, tbllabor_info.Thursday, tbllabor_info.Friday, [Me].[forms1].[txtweekof].[value] AS Expr1
FROM tbllabor_info
WHERE (((tbllabor_info.location)=[forms]![Form1]![cbolocation]) AND ((tbllabor_info.Active) Like "Active"));

DoCmd.SetWarnings True

End Sub

 

[tranchemontaigne]

You have doublequotes inside of your string. You also did not terminate your SQL statement with a double quote.

You can replace quotes inside of the SQL with single quote marks, or build your SQL statement as a string variable and concatenate CHR(34) double quotes or CHR939) single quotes into the string whenever you need them.

 

[jmaty23]

I added the double quotes and it is still shiowing the same error. I am confused on where to added and change the quotes.

 

[tranchemontaigne]

dim strSQL as string
 
strSQL = "INSERT INTO
strSQL = strSQL &  " "
strSQL = strSQL &  "tblschedule_lineitem
strSQL = strSQL &  " "
strSQL = strSQL &  "("
strSQL = strSQL &  " "
strSQL = strSQL &  "labor_name"
strSQL = strSQL &  ", "
strSQL = strSQL &  "shift
strSQL = strSQL &  ", "
strSQL = strSQL &  "location"
strSQL = strSQL &  ", "
strSQL = strSQL &  "title"
strSQL = strSQL &  ", "
strSQL = strSQL &  "Active"
strSQL = strSQL &  ", "
strSQL = strSQL &  "Saturday"
strSQL = strSQL &  ", "
strSQL = strSQL &  "Sunday"
strSQL = strSQL &  ", "
strSQL = strSQL &  "Monday"
strSQL = strSQL &  ", "
strSQL = strSQL &  "Tuesday"
strSQL = strSQL &  ", "
strSQL = strSQL &  "Wednesday"
strSQL = strSQL &  ", "
strSQL = strSQL &  "Thursday"
strSQL = strSQL &  ", "
strSQL = strSQL &  "Friday"
strSQL = strSQL &  ", "
strSQL = strSQL &  "weekof"
strSQL = strSQL &  " "
strSQL = strSQL &  ")"
strSQL = strSQL &  " "
strSQL = strSQL &  "SELECT"
strSQL = strSQL &  " "
strSQL = strSQL &  "tbllabor_info.labor_name"
strSQL = strSQL &  ", "
strSQL = strSQL &  "tbllabor_info.shift"
strSQL = strSQL &  ", "
strSQL = strSQL &  "tbllabor_info.location"
strSQL = strSQL &  ", "
strSQL = strSQL &  "tbllabor_info.title"
strSQL = strSQL &  ", "
strSQL = strSQL &  "tbllabor_info.Active"
strSQL = strSQL &  ", "
strSQL = strSQL &  "tbllabor_info.Saturday"
strSQL = strSQL &  ", "
strSQL = strSQL &  "tbllabor_info.Sunday"
strSQL = strSQL &  ", "
strSQL = strSQL &  "tbllabor_info.Monday"
strSQL = strSQL &  ", "
strSQL = strSQL &  "tbllabor_info.Tuesday"
strSQL = strSQL &  ", "
strSQL = strSQL &  "tbllabor_info.Wednesday"
strSQL = strSQL &  ", "
strSQL = strSQL &  "tbllabor_info.Thursday"
strSQL = strSQL &  ", "
strSQL = strSQL &  "tbllabor_info.Friday"
strSQL = strSQL &  ", "
strSQL = strSQL &  [Me].[forms1].[txtweekof].[value]
strSQL = strSQL &  " "
strSQL = strSQL &  "FROM"
strSQL = strSQL &  " "
strSQL = strSQL &  "tbllabor_info"
strSQL = strSQL &  " "
strSQL = strSQL &  "WHERE"
strSQL = strSQL &  " "
strSQL = strSQL &  "("
strSQL = strSQL &  "("
strSQL = strSQL &  "(tbllabor_info.location)="
strSQL = strSQL &  [forms]![Form1]![cbolocation]
strSQL = strSQL &  ")"
strSQL = strSQL &  " "
strSQL = strSQL &  "AND"
strSQL = strSQL &  " "
strSQL = strSQL &  "("
strSQL = strSQL &  "(tbllabor_info.Active) Like "
strSQL = strSQL &  chr(39) & "Active" & chr(39) 
strSQL = strSQL &  ")"
strSQL = strSQL &  ")"
strSQL = strSQL &  ";"
 
with DoCmd
  .SetWarnings False
  .RunSQL strsql
  .SetWarnings true
end with