The query runs fine as a saved query. The SQL is below:
INSERT INTO dbo_3MSA_tblScheduleMonitor ( StockNo,
Product_Name, PL_3M, Status, FamCode )
SELECT qryFlyFamily.StockNo, qryFlyFamily.[Product Name],
IIf([Product Name] Like "3M*","3M","PL") AS PL, "H" AS
Stat, qryFlyFamily.FamCode
FROM qryFlyFamily LEFT JOIN dbo_3MSA_tblScheduleMonitor ON
qryFlyFamily.StockNo = dbo_3MSA_tblScheduleMonitor.StockNo
WHERE (((qryFlyFamily.FamCode)="crystalsfamilycode") AND
((dbo_3MSA_tblScheduleMonitor.StockNo) Is Null));
This is the VBA code:
Dim str3M, strLike3M, strPL, strStatus, strSubSQL As String
str3M = "3M"
strLike3M = "3M*"
strPL = "PL"
strStatus = "H"
strSubSQL = "IIF([Product Name] Like """ & strLike3M
& """" & ",""" & str3M & """" & ",""" & _
strPL & """" & ") AS Type"
'Add the line(s) to dbo_3MSA_tblScheduleMonitor
strSQL = "INSERT INTO dbo_3MSA_tblScheduleMonitor" & _
" ( StockNo, Product_Name, PL_3M, Status, FamCode )" & _
" SELECT qryFlyFamily.StockNo," & _
" qryFlyFamily.[Product Name], " & strSubSQL & ",""" &
strStatus & """" & _
" AS Stat, qryFlyFamily.FamCode" & _
" FROM qryFlyFamily LEFT JOIN dbo_3MSA_tblScheduleMonitor"
& _
" ON qryFlyFamily.StockNo =
dbo_3MSA_tblScheduleMonitor.StockNo" & _
" WHERE (((qryFlyFamily.FamCode)=""" & strFamCode & """"
& ") AND" & _
" ((dbo_3MSA_tblScheduleMonitor.StockNo) Is Null));"
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected
Crystal