QueryDefs

[LB79]

Hello,

I'm having some problems using QueryDefs and hoped someone could tell me whats wrong with my code.
I'm starting off simple!

[SIZE=3][COLOR=#000080][FONT=Arial]Private Sub Command145_Click()[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Dim db As DAO.Database[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Dim qdf As DAO.QueryDef[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Dim strSQL As String[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set db = CurrentDb[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set qdf = db.QueryDefs("qryMyQuery")[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]strSQL = ""[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]strSQL = "SELECT C20_tblTargetDetails.[Item ID], C20_tblTargetDetails.FY"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]strSQL = strSQL & " FROM C20_tblTargetDetails"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]strSQL = strSQL & " GROUP BY C20_tblTargetDetails.[Item ID], C20_tblTargetDetails.FY;"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]qdf.sql = strSQL[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]DoCmd.OpenQuery "qryMyQuery"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set qdf = Nothing[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set db = Nothing[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]End Sub[/FONT][/COLOR][/SIZE]

Thanks for any help!

 

[namliam]

Appart from a slight formatting problem
strSQL = "SELECT C20_tblTargetDetails.[Item ID], C20_tblTargetDetails.FY"

should be
strSQL = strSQL & "SELECT C20_tblTargetDetails.[Item ID], C20_tblTargetDetails.FY"

And a logical problem
DoCmd.OpenQuery "qryMyQuery"
Set qdf = Nothing
Set db = Nothing

Should be
Set qdf = Nothing
Set db = Nothing
DoCmd.OpenQuery "qryMyQuery"

I dont see any issues, what is your problem/error?

 

[LB79]

Thanks for that - I have amended my code...

Im still getting the error "Run Time 3265 - Item not found in this collection".
It highlights Set qdf = db.QueryDefs("qryMyQuery")

Thanks again

 

[Banana]

The error means exactly what it says. The query isn't in querydefs collection because you haven't appended it to the QueryDefs collection.

However, it looks like you're doing a temporary querydef... If so, I'd just not append it and just use the QueryDef directly without any references to QueryDefs collection.

 

[smig]

what are you trying to do with the query ?

 

[LB79]

Thanks for the advice. Im not too sure with querydefs... I created that based on a tutorial but tbh it wasnt as plain to read as it could have been. Ive no clue about appending to querydefs collection...

This was just my first try at it. Really what I want to do is run a query in VBA. But the query is a crosstab with a dynamic field.

Ive tried the usual ways (making the query and calling it) but I was told this was the way to do it.

Thansk again

 

[namliam]

Set qdf = db.QueryDefs("qryMyQuery")

This requires qryMyQuery to exist as a query object/defenition... can be any old query, basicaly access requires a place to store the code.

 

[smig]

if all you want to do is run it there is no need to add it to the QueryDefs.
you can use the docmd. to run it

 

[LB79]

Thanks namliaM... Thats interesting and worked on a quick test... just have to put it to practice now!