Syntax error in query expression

[mdavid]

Hi I have the following code:

Private Sub CopySlctd2FP_Click()
Dim ctl As Control
Dim strList As String
Dim varSelected As Variant
Dim strSQL As String


  Set ctl = Me!lstbMempComplaints
  If ctl.ItemsSelected.Count = 0 Then
    MsgBox "You haven't selected anything"
  Else
    For Each varSelected In ctl.ItemsSelected
      strList = strList & ctl.ItemData(varSelected) & ", "
      strSQL = "INSERT INTO FPSubComplaint(ComplaintID, Complaint, Preparation, Administration, PartsUsed, Healer) " & _
               "VALUES ('" & ctl.ItemData(varSelected) & "', SELECT Complaint, Preparation, Administration, PartUsed, " & _
               "Healer From MempComplaintTom Where ComplaintID = """ & ctl.ItemData(varSelected) & """)"
      Debug.Print strSQL
      DoCmd.RunSQL strSQL
    Next varSelected
    strList = Left$(strList, Len(strList) - 2)
    MsgBox "You selected the following items:" & vbCrLf & strList
  End If
End Sub

The Debug.print outputs:

INSERT INTO FPSubComplaint(ComplaintID, Complaint, Preparation, Administration, PartsUsed, Healer) VALUES ('199', SELECT Complaint, Preparation, Administration, PartUsed, Healer From MempComplaintTom Where ComplaintID = "199")

On DoCmd.RunSQL strSQL I get the error: "Syntax error in Query expression 'SELECT Complaint'."

Can someone please tell me what's wrong with query

Thanks
David

[June7]

strSQL = "INSERT INTO FPSubComplaint(ComplaintID, Complaint, Preparation, Administration, PartsUsed, Healer) " & _
"SELECT " & ctl.ItemData(varSelected) & " AS CID, Complaint, Preparation, Administration, PartUsed, Healer " & _
"FROM MempComplaintTom WHERE ComplaintID = " & ctl.ItemData(varSelected)

Is ComplaintID a number type? If not then use apostrophe delimiters.


Review https://www.w3schools.com/SQL/sql_in...nto_select.asp

[mdavid]

Thanks June,
Drives me crazy trying to sort the quote from the quotes.
Is there a way of building this type of a statement with a wizard?

Thanks
David

[June7]

Can use query builder to help with syntax but then transferring to VBA you will have to figure out the concatenation and use of quotes/apostrophes.