query in code

  • Thread starter Thread starter Crystal
  • Start date Start date
C

Crystal

I'm running this code:

strSQL = "INSERT INTO tblScheduleMonitor ( StockNo," & _
"ProductName, FamCode )" & _
"SELECT qryFlyFamily.Stock," & _
" qryFlyFamily.[Product Name]," & _
" qryFlyFamily.FamCode" & _
" FROM qryFlyFamily LEFT JOIN tblScheduleMonitor ON " & _
" qryFlyFamily.Stock = tblScheduleMonitor.StockNo" & _
" WHERE (((qryFlyFamily.FamCode)=""" & strFamCode &_
"""" & ") AND ((tblScheduleMonitor.StockNo) Is Null));"

db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected

If I run this code, the query does not append any records
to the table. If I run this as a query, it works. Any
ideas as to why it refuses to work in code?

Crystal
 
I suggest you add the following line just before the Execute line

debug.print strSQL

Then you can copy if from the immediate window into a blank query and see
how the string is being interpreted.
 
I'm running this code:

strSQL = "INSERT INTO tblScheduleMonitor ( StockNo," & _
"ProductName, FamCode )" & _
"SELECT qryFlyFamily.Stock," & _
" qryFlyFamily.[Product Name]," & _
" qryFlyFamily.FamCode" & _
" FROM qryFlyFamily LEFT JOIN tblScheduleMonitor ON " & _
" qryFlyFamily.Stock = tblScheduleMonitor.StockNo" & _
" WHERE (((qryFlyFamily.FamCode)=""" & strFamCode &_
"""" & ") AND ((tblScheduleMonitor.StockNo) Is Null));"

db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected

If I run this code, the query does not append any records
to the table. If I run this as a query, it works. Any
ideas as to why it refuses to work in code?

Crystal
Click to expand...

Step through the code in the Debugger and type

?strSQL

in the Immediate window right after the strSQL= line. Does the SQL
look like it's correct? Try copying and pasting it into the SQL view
of a new query; does it return the records you expect? It may be that
strFamCode doesn't contain what you think it does...
 
Back
Top