Changing a Pre-existing Action qry in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an action query that I'd like to change on the fly in VBA

strGroupNo is a working parameter

It seems like I have everything in place, but it doesn't run:
'=========================================
Dim qdfYTDDOIDOL As DAO.QueryDef
Dim db As DAO.Database
Dim strGroupNo as String
Set db = CurrentDb

strGroupNo = "[Group #] = 10107"

strQry = "SELECT YTDDOIDOL01DGroupDecision.[Type of category],
Sum(YTDDOIDOL01DGroupDecision.Overturned) AS Overturned,
Sum(YTDDOIDOL01DGroupDecision.Upheld) AS Upheld,
Sum(YTDDOIDOL01DGroupDecision.Unresolved) AS Unresolved INTO YTDDOIDOL_Graph
FROM YTDDOIDOL01DGroupDecision GROUP BY YTDDOIDOL01DGroupDecision.[Type of
category], YTDDOIDOL01DGroupDecision.[Group #] HAVING (((" & strGroupNo & "))"

Set qdfYTDDOIDOL = db.CreateQueryDef("YTDDOIDOL01ERptGraph", strQry)
'DoCmd.OpenQuery "YTDDOIDOL01ERptGraph"

=============================================
 
Hi,

1)You need an extra bracket on the end of your SQL statement. (If you've
used DoCmd.SetWarnings False, then turn them back on so you can see errors!)

To answer your question
2) If you're editing an existing query 'YTDDOIDOL01ERptGraph' then

Dim qdfYTDDOIDOL As DAO.QueryDef
Dim db As DAO.Database
Dim strGroupNo As String

Set db = CurrentDb

strGroupNo = "[Group #] = 10107"
Dim strQry As String
strQry = "SELECT YTDDOIDOL01DGroupDecision.[Type of category],
Sum(YTDDOIDOL01DGroupDecision.Overturned) AS Overturned,
Sum(YTDDOIDOL01DGroupDecision.Upheld) AS Upheld,
Sum(YTDDOIDOL01DGroupDecision.Unresolved) AS Unresolved INTO YTDDOIDOL_Graph
FROM YTDDOIDOL01DGroupDecision GROUP BY YTDDOIDOL01DGroupDecision.[Type of
category], YTDDOIDOL01DGroupDecision.[Group #] HAVING (((" & strGroupNo &
")))"

Set qdfYTDDOIDOL = db.QueryDefs("YTDDOIDOL01ERptGraph") ' Editing an
existing query
qdfYTDDOIDOL.SQL= strQry ' Replace SQL in query

db.QueryDefs.Refresh ' For completeness
'DoCmd.OpenQuery "YTDDOIDOL01ERptGraph" ' will prompt to append data
qdfYTDDOIDOL.Execute ' Won't prompt to append


Might be simpler:

Dim db As DAO.Database
Dim strGroupNo As String

Set db = CurrentDb

strGroupNo = "[Group #] = 10107"
Dim strQry As String
strQry = "SELECT YTDDOIDOL01DGroupDecision.[Type of category],
Sum(YTDDOIDOL01DGroupDecision.Overturned) AS Overturned,
Sum(YTDDOIDOL01DGroupDecision.Upheld) AS Upheld,
Sum(YTDDOIDOL01DGroupDecision.Unresolved) AS Unresolved INTO YTDDOIDOL_Graph
FROM YTDDOIDOL01DGroupDecision GROUP BY YTDDOIDOL01DGroupDecision.[Type of
category], YTDDOIDOL01DGroupDecision.[Group #] HAVING (((" & strGroupNo &
")))"

db.Execute strQry
 
That is probably the most direct answer I have ever received for this
question which keeps arising.

Thank you.

Would you have a recommendation of additional reading for this topic?
 
Sorry, there's more to your post...
Would you have a recommendation of additional reading for this topic?
Click to expand...

Access 2000 Developer's Handbook is a good bedtime read (I haven't seen a
Developer's Handbook for XP or 2003). MS Access help files are OK for
reference.

Graeme.
 
Excited to try what you just showed me, here are the results...

=======the following code works====================
strGroupNo = "[Group #] = 10107"

DoCmd.RunSQL "SELECT YTD.[Type of category], Sum(YTD.Overturned) AS
Overturned, " & _
"Sum(YTD.Upheld) AS Upheld, Sum(YTD.Unresolved) AS Unresolved INTO
YTDDOIDOL_Graph " & _
"FROM YTDDOIDOL01DGroupDecision as YTD " & _
"GROUP BY YTD.[Type of category], " & _
"YTD.[Group #] HAVING " & strGroupNo & "; "

- note, if I run db.execute --> nothing happens


====================================================

==========the following code does not work===================
Dim db as DAO.Database
Dim strGroupNo as String

Set db = CurrentDb

strQry = "SELECT YTD.[Type of category], Sum(YTD.Overturned) AS Overturned,
" & _
"Sum(YTD.Upheld) AS Upheld, Sum(YTD.Unresolved) AS Unresolved " & _
"FROM YTDDOIDOL01DGroupDecision as YTD " & _
"GROUP BY YTD.[Type of category], " & _
"YTD.[Group #] HAVING " & strGroupNo & "; "

Set qdfYTDDOIDOL = db.QueryDefs("YTDDOIDOL01ERptSourceTPA")
'Change the underlying SQL of the report query
qdfYTDDOIDOL.SQL = strQry

db.QueryDefs.Refresh

=======================================
Graham, if I can get the 2nd code to work, as you suggest... it would help
in so many applications.

However, it doesn't yet work. Can you see what I need to tweak?



Graeme Richardson said:
Hi,

1)You need an extra bracket on the end of your SQL statement. (If you've
used DoCmd.SetWarnings False, then turn them back on so you can see errors!)

To answer your question
2) If you're editing an existing query 'YTDDOIDOL01ERptGraph' then

Dim qdfYTDDOIDOL As DAO.QueryDef
Dim db As DAO.Database
Dim strGroupNo As String

Set db = CurrentDb

strGroupNo = "[Group #] = 10107"
Dim strQry As String
strQry = "SELECT YTDDOIDOL01DGroupDecision.[Type of category],
Sum(YTDDOIDOL01DGroupDecision.Overturned) AS Overturned,
Sum(YTDDOIDOL01DGroupDecision.Upheld) AS Upheld,
Sum(YTDDOIDOL01DGroupDecision.Unresolved) AS Unresolved INTO YTDDOIDOL_Graph
FROM YTDDOIDOL01DGroupDecision GROUP BY YTDDOIDOL01DGroupDecision.[Type of
category], YTDDOIDOL01DGroupDecision.[Group #] HAVING (((" & strGroupNo &
")))"

Set qdfYTDDOIDOL = db.QueryDefs("YTDDOIDOL01ERptGraph") ' Editing an
existing query
qdfYTDDOIDOL.SQL= strQry ' Replace SQL in query

db.QueryDefs.Refresh ' For completeness
'DoCmd.OpenQuery "YTDDOIDOL01ERptGraph" ' will prompt to append data
qdfYTDDOIDOL.Execute ' Won't prompt to append


Might be simpler:

Dim db As DAO.Database
Dim strGroupNo As String

Set db = CurrentDb

strGroupNo = "[Group #] = 10107"
Dim strQry As String
strQry = "SELECT YTDDOIDOL01DGroupDecision.[Type of category],
Sum(YTDDOIDOL01DGroupDecision.Overturned) AS Overturned,
Sum(YTDDOIDOL01DGroupDecision.Upheld) AS Upheld,
Sum(YTDDOIDOL01DGroupDecision.Unresolved) AS Unresolved INTO YTDDOIDOL_Graph
FROM YTDDOIDOL01DGroupDecision GROUP BY YTDDOIDOL01DGroupDecision.[Type of
category], YTDDOIDOL01DGroupDecision.[Group #] HAVING (((" & strGroupNo &
")))"

db.Execute strQry
Click to expand...
 
HI, the second query is not an action query so the db.Execute command
appears to do nothing.

What does Docmd.RunSQL do? When the SQL argument is not an acrion query.

Graeme.
 
Back
Top