SQL Error message

[Tony]

I am receiving the following error:

Syntax error in query expression
reports_in_progress_current_xl[GLOBALDCCDATA].[GLOBAL_DCC_CODE]

when I am executing the following code:

For Each varItem In lst.ItemsSelected
'All information is present; write a record to the link table
lngID = lst.Column(0, varItem)
servID = Forms![ServiceOrderRegister]![ServiceOrderNumber]
MsgBox servID
strSQL = "INSERT INTO TblOrderSubSysLnk (ServiceNumLnk,SubsystemLnk)" _
& "SELECT [Service Order Register].ServiceOrderNumber, SubSystemID
FROM [Service Order Register], Subsystems WHERE [Service Order
Register].ServiceOrderNumber = " & servID & " AND SubSystemID = " & lngID &
";"
DoCmd.RunSQL strSQL
Next varItem

But the query itself is executed properly. What is wrong, how I can fix it
to stop this error from appearing.

Thanks for help.

 

[Sylvain Lafontaine]

Try removing the ";" at the end and print each strSQL to make sure that
everything is OK. Learn to use the Profiler on the SQL-Server and you will
probably see what's happening.

If not, then try to add the prefix: dbo. to the names of your tables in
your query statement. It is possible that the DoCmd.RunSQL may add another
prefix.

Finally, replace this call to the DoCmd.RunSQL by ADO objects. This command
is very, very limited in its features and don't give you the possibility of
using queries with parameters.

S. L.