Running query from Access Form commmand using VBA code

B

Berny

I'm trying to run an append query using VBA code but can seem to get it to
work.

Can someone tell me if this is even possible?

Below is the query I'm trying to run:

INSERT INTO tblPastDueUpload ( PdWoNo, PdWoDesc1, PdWoDesc2, PdWoDesc3,
PdDateChk, PdPty, PdWoCls )
SELECT tblReviewWorkOrders.WoNo, Left(tblReviewWorkOrders!WoDesc,40) AS
WoDesc1,
IIf(Len(RTrim(tblReviewWorkOrders!WoDesc))>40,Mid(tblReviewWorkOrders!WoDesc,41,60),"
") AS WoDesc2,
IIf(Len(RTrim(tblReviewWorkOrders!WoDesc))>100,Mid(tblReviewWorkOrders!WoDesc,101,60),"
") AS WoDesc3,
IIf(tblReviewWorkOrders!EstComp<(Date()+30),(Date()+45),tblReviewWorkOrders!EstComp)
AS DateChk, tblReviewWorkOrders.Pty, IIf([ActHrs]<20,"B",[SrvCat]) AS WoCls
FROM tblReviewWorkOrders
WHERE (((tblReviewWorkOrders.Bklg)="ad3") AND
((tblReviewWorkOrders.Status)<"07") AND
((tblReviewWorkOrders.EstComp)<Date()) AND
((tblReviewWorkOrders.TotalCost)<[tblReviewWorkOrders]![Authorized]-50) AND
((tblReviewWorkOrders.blnExclude)=0) AND
((Left([tblReviewWorkOrders]![WoNo],1)) Like "l" Or
(Left([tblReviewWorkOrders]![WoNo],1))="m" Or
(Left([tblReviewWorkOrders]![WoNo],1))="v") AND
((tblReviewWorkOrders.CatCode) Not Like "*pwo*") AND
((tblReviewWorkOrders.UserField2) Not Like "wp*"))
ORDER BY tblReviewWorkOrders.WoNo;

I was trying to use code similar to below but can get it to work

strSql = ***I can't get any code in this area to work, any suggestions? ***
DoCmd.SetWarnings False
DoCmd.RunSQL strSql

Any help would be greatly appreciated.
 
A

Allen Browne

What result are you getting? Error message? Nothing at all?

One of the problems with RunSQL is that, if you turn off SetWarnings, you
get no indication if something goes wrong. Instead of:
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
try:
dbEngine(0)(0).Execute strSql, dbFailOnError
That will not bother you with a confirmation dialog, but will generate a
trappable error if the insert fails.

The values you are inserting into PdWoDesc2 and PdWoDesc3 involve IIf()
expressions. Because of the way the lines broke, I cannot tell if you are
trying to insert a space of a zero-length string for the final option, but
this could be the cause of the failure. Try inserting a Null instead:
IIf(Len(RTrim(tblReviewWorkOrders!WoDesc))>40,
Trim(Mid(tblReviewWorkOrders!WoDesc,41,60)), Null) AS WoDesc2,

You could also make it more efficient by removing the Left() from the WHERE
clause for the WoNo field, i.e. change:
((Left([tblReviewWorkOrders]![WoNo],1)) Like "l" Or
to:
([tblReviewWorkOrders]![WoNo],1 Like "l*" Or
and the same for "m*" and "v*". If the WoNo field happens to be indexed, JET
will be able to use the index if the function call is not there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Berny said:
I'm trying to run an append query using VBA code but can seem to get it to
work.

Can someone tell me if this is even possible?

Below is the query I'm trying to run:

INSERT INTO tblPastDueUpload ( PdWoNo, PdWoDesc1, PdWoDesc2, PdWoDesc3,
PdDateChk, PdPty, PdWoCls )
SELECT tblReviewWorkOrders.WoNo, Left(tblReviewWorkOrders!WoDesc,40) AS
WoDesc1,
IIf(Len(RTrim(tblReviewWorkOrders!WoDesc))>40,Mid(tblReviewWorkOrders!WoDesc,41,60),"
") AS WoDesc2,
IIf(Len(RTrim(tblReviewWorkOrders!WoDesc))>100,Mid(tblReviewWorkOrders!WoDesc,101,60),"
") AS WoDesc3,
IIf(tblReviewWorkOrders!EstComp<(Date()+30),(Date()+45),tblReviewWorkOrders!EstComp)
AS DateChk, tblReviewWorkOrders.Pty, IIf([ActHrs]<20,"B",[SrvCat]) AS
WoCls
FROM tblReviewWorkOrders
WHERE (((tblReviewWorkOrders.Bklg)="ad3") AND
((tblReviewWorkOrders.Status)<"07") AND
((tblReviewWorkOrders.EstComp)<Date()) AND
((tblReviewWorkOrders.TotalCost)<[tblReviewWorkOrders]![Authorized]-50)
AND ((tblReviewWorkOrders.blnExclude)=0) AND
((Left([tblReviewWorkOrders]![WoNo],1)) Like "l" Or
(Left([tblReviewWorkOrders]![WoNo],1))="m" Or
(Left([tblReviewWorkOrders]![WoNo],1))="v") AND
((tblReviewWorkOrders.CatCode) Not Like "*pwo*") AND
((tblReviewWorkOrders.UserField2) Not Like "wp*"))
ORDER BY tblReviewWorkOrders.WoNo;

I was trying to use code similar to below but can get it to work

strSql = ***I can't get any code in this area to work, any suggestions?
***
DoCmd.SetWarnings False
DoCmd.RunSQL strSql

Any help would be greatly appreciated.
Click to expand...
 
B

Berny

I was trying to create an append query on the fly instead of using a regular
query


Allen Browne said:
What result are you getting? Error message? Nothing at all?

One of the problems with RunSQL is that, if you turn off SetWarnings, you
get no indication if something goes wrong. Instead of:
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
try:
dbEngine(0)(0).Execute strSql, dbFailOnError
That will not bother you with a confirmation dialog, but will generate a
trappable error if the insert fails.

The values you are inserting into PdWoDesc2 and PdWoDesc3 involve IIf()
expressions. Because of the way the lines broke, I cannot tell if you are
trying to insert a space of a zero-length string for the final option, but
this could be the cause of the failure. Try inserting a Null instead:
IIf(Len(RTrim(tblReviewWorkOrders!WoDesc))>40,
Trim(Mid(tblReviewWorkOrders!WoDesc,41,60)), Null) AS WoDesc2,

You could also make it more efficient by removing the Left() from the
WHERE clause for the WoNo field, i.e. change:
((Left([tblReviewWorkOrders]![WoNo],1)) Like "l" Or
to:
([tblReviewWorkOrders]![WoNo],1 Like "l*" Or
and the same for "m*" and "v*". If the WoNo field happens to be indexed,
JET will be able to use the index if the function call is not there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Berny said:
I'm trying to run an append query using VBA code but can seem to get it
to work.

Can someone tell me if this is even possible?

Below is the query I'm trying to run:

INSERT INTO tblPastDueUpload ( PdWoNo, PdWoDesc1, PdWoDesc2, PdWoDesc3,
PdDateChk, PdPty, PdWoCls )
SELECT tblReviewWorkOrders.WoNo, Left(tblReviewWorkOrders!WoDesc,40) AS
WoDesc1,
IIf(Len(RTrim(tblReviewWorkOrders!WoDesc))>40,Mid(tblReviewWorkOrders!WoDesc,41,60),"
") AS WoDesc2,
IIf(Len(RTrim(tblReviewWorkOrders!WoDesc))>100,Mid(tblReviewWorkOrders!WoDesc,101,60),"
") AS WoDesc3,
IIf(tblReviewWorkOrders!EstComp<(Date()+30),(Date()+45),tblReviewWorkOrders!EstComp)
AS DateChk, tblReviewWorkOrders.Pty, IIf([ActHrs]<20,"B",[SrvCat]) AS
WoCls
FROM tblReviewWorkOrders
WHERE (((tblReviewWorkOrders.Bklg)="ad3") AND
((tblReviewWorkOrders.Status)<"07") AND
((tblReviewWorkOrders.EstComp)<Date()) AND
((tblReviewWorkOrders.TotalCost)<[tblReviewWorkOrders]![Authorized]-50)
AND ((tblReviewWorkOrders.blnExclude)=0) AND
((Left([tblReviewWorkOrders]![WoNo],1)) Like "l" Or
(Left([tblReviewWorkOrders]![WoNo],1))="m" Or
(Left([tblReviewWorkOrders]![WoNo],1))="v") AND
((tblReviewWorkOrders.CatCode) Not Like "*pwo*") AND
((tblReviewWorkOrders.UserField2) Not Like "wp*"))
ORDER BY tblReviewWorkOrders.WoNo;

I was trying to use code similar to below but can get it to work

strSql = ***I can't get any code in this area to work, any suggestions?
***
DoCmd.SetWarnings False
DoCmd.RunSQL strSql

Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
T

TC

Berny, Allen is saying that: "can't seem to get it to work" is not
enough for us to go on. Are you getting an error message? If so, what
is that message? Have you checked SetWarnings, as he suggested?

HTH,
TC [MVP Access]
 
B

Berny

I changed my approach to this problem and posted my new problem under the
Queries Group (I thought it was a more appropriate group)

By the way Allen is also helping me with my new problem

MANY Thanks!

Your Help is Greatly Appreciated
 
Top