Runtime 7874

[LB79]

Hi all,

I'm having a problem with Runtime 7874 (Cant find the object INSERT INTO...).
I run all my queries from VBA but haven’t come across this yet. The query runs perfectly from Query Design, but debugs when running from VBA.

Can anyone see whats wrong?

Thanks for any help.

--- SQL from Query Design ---

[COLOR=#1f497d][FONT=Arial]INSERT INTO tbl_NCT_TransportMissingCost ( REF, RespAgent, [Item CD], Item, [From], [From UN], [To UN], [To], CUR, FAF, 20DF, 40DF, 40HC, 45DF, 20RF, 40RF, 40HCRF, 20FR, 40FR, 20OT, 40OT, 20OTH, 40OTH, BL, Added )[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]SELECT tbl_NCT_AgentResponseTemp.REF, tbl_NCT_AgentResponseTemp.UN, tbl_NCT_AgentResponseTemp.Item, tbl_NCT_AgentResponseTemp.[Cost Item], tbl_NCT_AgentResponseTemp.From, tbl_NCT_AgentResponseTemp.[From UN], tbl_NCT_AgentResponseTemp.[To UN], tbl_NCT_AgentResponseTemp.To, tbl_NCT_AgentResponseTemp.CUR, tbl_NCT_AgentResponseTemp.FAF, tbl_NCT_AgentResponseTemp.[20DF], tbl_NCT_AgentResponseTemp.[40DF], tbl_NCT_AgentResponseTemp.[40HC], tbl_NCT_AgentResponseTemp.[45DF], tbl_NCT_AgentResponseTemp.[20RF], tbl_NCT_AgentResponseTemp.[40RF], tbl_NCT_AgentResponseTemp.[40HCRF], tbl_NCT_AgentResponseTemp.[20FR], tbl_NCT_AgentResponseTemp.[40FR], tbl_NCT_AgentResponseTemp.[20OT], tbl_NCT_AgentResponseTemp.[40OT], tbl_NCT_AgentResponseTemp.[20OTH], tbl_NCT_AgentResponseTemp.[40OTH], tbl_NCT_AgentResponseTemp.BL, Now() AS ATMT_Added[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]FROM tbl_NCT_AgentResponseTemp[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]GROUP BY tbl_NCT_AgentResponseTemp.REF, tbl_NCT_AgentResponseTemp.UN, tbl_NCT_AgentResponseTemp.Item, tbl_NCT_AgentResponseTemp.[Cost Item], tbl_NCT_AgentResponseTemp.From, tbl_NCT_AgentResponseTemp.[From UN], tbl_NCT_AgentResponseTemp.[To UN], tbl_NCT_AgentResponseTemp.To, tbl_NCT_AgentResponseTemp.CUR, tbl_NCT_AgentResponseTemp.FAF, tbl_NCT_AgentResponseTemp.[20DF], tbl_NCT_AgentResponseTemp.[40DF], tbl_NCT_AgentResponseTemp.[40HC], tbl_NCT_AgentResponseTemp.[45DF], tbl_NCT_AgentResponseTemp.[20RF], tbl_NCT_AgentResponseTemp.[40RF], tbl_NCT_AgentResponseTemp.[40HCRF], tbl_NCT_AgentResponseTemp.[20FR], tbl_NCT_AgentResponseTemp.[40FR], tbl_NCT_AgentResponseTemp.[20OT], tbl_NCT_AgentResponseTemp.[40OT], tbl_NCT_AgentResponseTemp.[20OTH], tbl_NCT_AgentResponseTemp.[40OTH], tbl_NCT_AgentResponseTemp.BL, Now()[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]HAVING (((tbl_NCT_AgentResponseTemp.REF) Is Not Null));[/FONT][/COLOR]

--- VBA with SQL ---

[COLOR=#1f497d][FONT=Arial]ATMTSQL = "INSERT INTO tbl_NCT_TransportMissingCost ( REF, RespAgent, [Item CD], Item, [From], [From UN], [To UN], [To], CUR, FAF, 20DF, 40DF, 40HC, 45DF, 20RF, 40RF, 40HCRF, 20FR, 40FR, 20OT, 40OT, 20OTH, 40OTH, BL, Added )"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]ATMTSQL = ATMTSQL & " SELECT tbl_NCT_AgentResponseTemp.REF, tbl_NCT_AgentResponseTemp.UN, tbl_NCT_AgentResponseTemp.Item, tbl_NCT_AgentResponseTemp.[Cost Item], tbl_NCT_AgentResponseTemp.From, tbl_NCT_AgentResponseTemp.[From UN], tbl_NCT_AgentResponseTemp.[To UN], tbl_NCT_AgentResponseTemp.To, tbl_NCT_AgentResponseTemp.CUR, tbl_NCT_AgentResponseTemp.FAF, tbl_NCT_AgentResponseTemp.[20DF], tbl_NCT_AgentResponseTemp.[40DF], tbl_NCT_AgentResponseTemp.[40HC], tbl_NCT_AgentResponseTemp.[45DF], tbl_NCT_AgentResponseTemp.[20RF], tbl_NCT_AgentResponseTemp.[40RF], tbl_NCT_AgentResponseTemp.[40HCRF], tbl_NCT_AgentResponseTemp.[20FR], tbl_NCT_AgentResponseTemp.[40FR], tbl_NCT_AgentResponseTemp.[20OT], tbl_NCT_AgentResponseTemp.[40OT], tbl_NCT_AgentResponseTemp.[20OTH], tbl_NCT_AgentResponseTemp.[40OTH], tbl_NCT_AgentResponseTemp.BL, Now() AS ATMT_Added"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]ATMTSQL = ATMTSQL & " FROM tbl_NCT_AgentResponseTemp"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]ATMTSQL = ATMTSQL & " GROUP BY tbl_NCT_AgentResponseTemp.REF, tbl_NCT_AgentResponseTemp.UN, tbl_NCT_AgentResponseTemp.Item, tbl_NCT_AgentResponseTemp.[Cost Item], tbl_NCT_AgentResponseTemp.From, tbl_NCT_AgentResponseTemp.[From UN], tbl_NCT_AgentResponseTemp.[To UN], tbl_NCT_AgentResponseTemp.To, tbl_NCT_AgentResponseTemp.CUR, tbl_NCT_AgentResponseTemp.FAF, tbl_NCT_AgentResponseTemp.[20DF], tbl_NCT_AgentResponseTemp.[40DF], tbl_NCT_AgentResponseTemp.[40HC], tbl_NCT_AgentResponseTemp.[45DF], tbl_NCT_AgentResponseTemp.[20RF], tbl_NCT_AgentResponseTemp.[40RF], tbl_NCT_AgentResponseTemp.[40HCRF], tbl_NCT_AgentResponseTemp.[20FR], tbl_NCT_AgentResponseTemp.[40FR], tbl_NCT_AgentResponseTemp.[20OT], tbl_NCT_AgentResponseTemp.[40OT], tbl_NCT_AgentResponseTemp.[20OTH], tbl_NCT_AgentResponseTemp.[40OTH], tbl_NCT_AgentResponseTemp.BL, Now()"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]ATMTSQL = ATMTSQL & " HAVING (((tbl_NCT_AgentResponseTemp.REF) Is Not Null));"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]DoCmd.RunSQL ATMTSQL[/FONT][/COLOR]

 

[pbaldy]

Personally I'd use a saved query, since I don't see anything dynamic in that. Saved queries get compiled and run more efficiently. That said, this should help you debug the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

I don't see a problem right off, but it's hard for me to read with the scrolling.

 

[LB79]

I keep my SQL in VBA to stop other users changing the queries (They take copies then fuss when they break it).
Anyway... I fixed the problem - I was being a plum with my variable.