Run-time error '3075': Syntax error in (comma)

[RickRo]

Hello All,

I am attempting to insert a record with selected data into a temp table and I am getting "Run-time error '3075': Syntax error in (comma)...".
Here is the code:

Private Sub XferDataToTempTable()
Dim db As Database
Dim strSQL As String

Set db = CurrentDb

strSQL = "INSERT INTO tblTemp " _
    & "(ChangeType, ChangeDate, ImpactTicket, Status, IBMTag, " _
    & "SerialNumber, Class, Manufacturer, SubCatagory, Make, AssetComments, " _
    & "FromBuilding, FromFloor, FromRoom, FromHostname, ToBuilding, ToFloor, " _
    & "ToRoom, ToHostname) " _
    & "Select (tblAsset.ChangeType, tblAsset.ChangeDate, tblAsset.ImpactTicket, " _
    & "tblAsset.Status, tblAsset.IBMTag, tblAsset.SerialNumber, tblAsset.Class, " _
    & "tblAsset.Manufacturer, tblAsset.SubCatagory, tblAsset.Make, " _
    & "tblAsset.AssetComments, tblAsset.FromBuilding, tblAsset.FromFloor, " _
    & "tblAsset.FromRoom, tblAsset.FromHostname, tblAsset.ToBuilding, " _
    & "tblAsset.ToFloor, tblAsset.ToRoom, tblAsset.ToHostname) " _
    & "From [tblAsset] " _
    & "WHERE [tblAsset.SerialNumber]=[Forms]![frmChange2]![SerialNumber]"
 
Debug.Print strSQL
DoCmd.RunSQL strSQL
End Sub

The debug output is:
INSERT INTO tblTemp (ChangeType, ChangeDate, ImpactTicket, Status, IBMTag, SerialNumber, Class, Manufacturer, SubCatagory, Make, AssetComments, FromBuilding, FromFloor, FromRoom, FromHostname, ToBuilding, ToFloor, ToRoom, ToHostname) Select (tblAsset.ChangeType, tblAsset.ChangeDate, tblAsset.ImpactTicket, tblAsset.Status, tblAsset.IBMTag, tblAsset.SerialNumber, tblAsset.Class, tblAsset.Manufacturer, tblAsset.SubCatagory, tblAsset.Make, tblAsset.AssetComments, tblAsset.FromBuilding, tblAsset.FromFloor, tblAsset.FromRoom, tblAsset.FromHostname, tblAsset.ToBuilding, tblAsset.ToFloor, tblAsset.ToRoom, tblAsset.ToHostname) From [tblAsset] WHERE [tblAsset.SerialNumber]=[Forms]![frmChange2]![SerialNumber]

I tried this with just one data element, and it wrote to the table just fine:

Private Sub XferDataToTempTable()
Dim db As Database
Dim strSQL As String

Set db = CurrentDb

strSQL = "INSERT INTO [tblTemp]([ChangeType]) " _
    & "Select (ChangeType) " _
    & "From [tblAsset] " _
    & "WHERE [tblAsset.SerialNumber]=[Forms]![frmChange2]![SerialNumber]"
 
Debug.Print strSQL
DoCmd.RunSQL strSQL
End Sub

What am I missing?

Rick

 

[JHB]

Try the below:

strSQL = "INSERT INTO tblTemp " _
& "(ChangeType, ChangeDate, ImpactTicket, Status, IBMTag, " _
& "SerialNumber, Class, Manufacturer, SubCatagory, Make, AssetComments, " _
& "FromBuilding, FromFloor, FromRoom, FromHostname, ToBuilding, ToFloor, " _
& "ToRoom, ToHostname) " _ & "Select (tblAsset.ChangeType, tblAsset.ChangeDate, tblAsset.ImpactTicket, " _
& "tblAsset.Status, tblAsset.IBMTag, tblAsset.SerialNumber, tblAsset.Class, " _
& "tblAsset.Manufacturer, tblAsset.SubCatagory, tblAsset.Make, " _
& "tblAsset.AssetComments, tblAsset.FromBuilding, tblAsset.FromFloor, " _ & "tblAsset.FromRoom, tblAsset.FromHostname, tblAsset.ToBuilding, " _
& "tblAsset.ToFloor, tblAsset.ToRoom, tblAsset.ToHostname) " _
& "From [tblAsset] " _
& "WHERE [tblAsset.SerialNumber]=" & "" & [Forms]![frmChange2]![SerialNumber] & ""

 

[RickRo]

I tried that, and I have the same error. I looked at the changed code and it appears that the last line was changed

 & "WHERE [tblAsset.SerialNumber]=" & "" & [Forms]![frmChange2]![SerialNumber] & ""

When I look at this in the immediate window I don't have anything after the equals sign.
Again, this works with just one field in the SQL, so I think the WHERE statement is correct.

 

[spikepl]

so add 5 fields back to your sql. If you get the error then it's there! If not, then add another 5. Etc. This is in fact a standard method of debugging i-can't-see-it type of errors.

The other method, of posting it here, works if someone with too much time on their hands can be bothered

 

[RickRo]

Well thanks Spikepl, I have done that already; I got the error with all of the fields, so I backed it down to just one, and it worked. I add just one more, and I get the error.
Which is why I posted here - not to get someone with to much time on their hands, but perhaps find someone to point me in the right direction to troubleshoot.

 

[JHB]

Well thanks Spikepl, I have done that already; I got the error with all of the fields, so I backed it down to just one, and it worked. I add just one more, and I get the error.

Is the field type equal in both table? And the value you try to add, does it be the right type for the "ChangeDate" field in the "tblTemp" table?
Put the query in the QBE - window, it is easier to debug from there.

 

[jdraw]

INSERT INTO tblTemp (ChangeType, ChangeDate, ImpactTicket, Status, IBMTag, SerialNumber, Class, Manufacturer, SubCatagory, Make, AssetComments, FromBuilding, FromFloor, FromRoom, FromHostname, ToBuilding, ToFloor, ToRoom, ToHostname) Select (tblAsset.ChangeType, tblAsset.ChangeDate, tblAsset.ImpactTicket, tblAsset.Status, tblAsset.IBMTag, tblAsset.SerialNumber, tblAsset.Class, tblAsset.Manufacturer, tblAsset.SubCatagory, tblAsset.Make, tblAsset.AssetComments, tblAsset.FromBuilding, tblAsset.FromFloor, tblAsset.FromRoom, tblAsset.FromHostname, tblAsset.ToBuilding, tblAsset.ToFloor, tblAsset.ToRoom, tblAsset.ToHostname) From [tblAsset] WHERE [tblAsset.SerialNumber]=[Forms]![frmChange2]![SerialNumber]

You don't need this brackets in red.

When you do a debug.print of your SQL, any form based data should be rendered to a value. If you are seeing the literal

[Forms]![frmChange2]![SerialNumber]

, then your form based data has not been rendered. (Where rendered means evaluated and resolved as some number/string etc output)

The implication is you have a syntax error in how you have referenced the form based data.

 

[RickRo]

That was it - no parenthesis needed on the select portion. Thank you very much!