Query from a list

  • Thread starter Thread starter DavPet
  • Start date Start date
D

DavPet

I have a lot of queires.
I want to put the query names in a table, one field of the table will have a
group_id. Then I want to execute the queries for a certain group_id.

Please jump start me on how to do it.
 
I am assuming that the table with all of hte query names and group_ids in it
is complete. I am going to name this table query_list with fields group_id
and queryname in my example

Sub RunQueries(GroupID As Long)
Dim strQuery As String
Dim qry As QueryDef
Dim rec As Recordset

'get a list of all queries for the given groupid
strQuery = "Select * from [query_list] where group_id=" & GroupID
Set qry = CurrentDb.CreateProperty("", strQuery)
Set rec = qry.OpenRecordset()
'record list of all of the queries
With rec
'go through the list until it is complete
If Not (.EOF) Then
'grab the appropriate query with name of in the QueryName field
Set qry = CurrentDb.QueryDefs(.Fields("QueryName"))
'run the query
qry.Execute
'move to the next query
.MoveNext
End If
End With

'clean up by setting objects to nothing
Set qry = Nothing
Set rec = Nothing
End Sub

Please let me know if this is helpful and if I can provide more assistance.
 
I made a typo, not CreateProperty, but CreateQueryDef.

hmadyson said:
I am assuming that the table with all of hte query names and group_ids in it
is complete. I am going to name this table query_list with fields group_id
and queryname in my example

Sub RunQueries(GroupID As Long)
Dim strQuery As String
Dim qry As QueryDef
Dim rec As Recordset

'get a list of all queries for the given groupid
strQuery = "Select * from [query_list] where group_id=" & GroupID
Set qry = CurrentDb.CreateProperty("", strQuery)
Set rec = qry.OpenRecordset()
'record list of all of the queries
With rec
'go through the list until it is complete
If Not (.EOF) Then
'grab the appropriate query with name of in the QueryName field
Set qry = CurrentDb.QueryDefs(.Fields("QueryName"))
'run the query
qry.Execute
'move to the next query
.MoveNext
End If
End With

'clean up by setting objects to nothing
Set qry = Nothing
Set rec = Nothing
End Sub

Please let me know if this is helpful and if I can provide more assistance.

DavPet said:
I have a lot of queires.
I want to put the query names in a table, one field of the table will have a
group_id. Then I want to execute the queries for a certain group_id.

Please jump start me on how to do it.
Click to expand...
Click to expand...
 
I am so embarassed I made another mistake. Here is the new code

Sub RunQueries(GroupID As Long)
Dim strQuery As String
Dim qry As QueryDef
Dim rec As Recordset

'get a list of all queries for the given groupid
strQuery = "Select * from [query_list] where group_id=" & GroupID
Set qry = CurrentDb.CreateQueryDef("", strQuery)
Set rec = qry.OpenRecordset()
'record list of all of the queries
With rec
'go through the list until it is complete
Do Until (.EOF)
'grab the appropriate query with name of in the QueryName field
Set qry = CurrentDb.QueryDefs(.Fields("QueryName"))
'run the query
qry.Execute
'move to the next query
.MoveNext
Loop
End With

'clean up by setting objects to nothing
Set qry = Nothing
Set rec = Nothing
End Sub
 
Thanks for the self-checking and updates.

When I execute "RunQueries (1)"
I am getting this error:
Too few parameters. Expected 1 (Error 3061) at the line that says Set rec =
qry.OpenRecordset()

Dunno' what to do.


hmadyson said:
I am so embarassed I made another mistake. Here is the new code

Sub RunQueries(GroupID As Long)
Dim strQuery As String
Dim qry As QueryDef
Dim rec As Recordset

'get a list of all queries for the given groupid
strQuery = "Select * from [query_list] where group_id=" & GroupID
Set qry = CurrentDb.CreateQueryDef("", strQuery)
Set rec = qry.OpenRecordset()
'record list of all of the queries
With rec
'go through the list until it is complete
Do Until (.EOF)
'grab the appropriate query with name of in the QueryName field
Set qry = CurrentDb.QueryDefs(.Fields("QueryName"))
'run the query
qry.Execute
'move to the next query
.MoveNext
Loop
End With

'clean up by setting objects to nothing
Set qry = Nothing
Set rec = Nothing
End Sub

DavPet said:
I have a lot of queires.
I want to put the query names in a table, one field of the table will
have a
group_id. Then I want to execute the queries for a certain group_id.

Please jump start me on how to do it.
Click to expand...
Click to expand...
 
It depends on what you put in the line strQuery = "Select * from [query_list]
where group_id=" & GroupID

query_list should be the name of the table that you are looking for.
group_id must be the name of the field that you want to limit on, and GroupID
must be an appropriate value. If group_id is a text field, you will need to
change it like

strQuery = "Select * from [query_list] where group_id='" & GroupID &"'"

the previous code assumes that it is a number.

I have proved on my PC that this code works (reason for all of the
rewrites), but it all depends on your database specifics.


DavPet said:
Thanks for the self-checking and updates.

When I execute "RunQueries (1)"
I am getting this error:
Too few parameters. Expected 1 (Error 3061) at the line that says Set rec =
qry.OpenRecordset()

Dunno' what to do.


hmadyson said:
I am so embarassed I made another mistake. Here is the new code

Sub RunQueries(GroupID As Long)
Dim strQuery As String
Dim qry As QueryDef
Dim rec As Recordset

'get a list of all queries for the given groupid
strQuery = "Select * from [query_list] where group_id=" & GroupID
Set qry = CurrentDb.CreateQueryDef("", strQuery)
Set rec = qry.OpenRecordset()
'record list of all of the queries
With rec
'go through the list until it is complete
Do Until (.EOF)
'grab the appropriate query with name of in the QueryName field
Set qry = CurrentDb.QueryDefs(.Fields("QueryName"))
'run the query
qry.Execute
'move to the next query
.MoveNext
Loop
End With

'clean up by setting objects to nothing
Set qry = Nothing
Set rec = Nothing
End Sub

DavPet said:
I have a lot of queires.
I want to put the query names in a table, one field of the table will
have a
group_id. Then I want to execute the queries for a certain group_id.

Please jump start me on how to do it.
Click to expand...
Click to expand...
Click to expand...
 
Now I get it. It works great for action queries but won't execute a "Select
query".
Cannot execute a select query. (Error 3065)
You tried to use the Execute method with a select query. The Execute method
is valid only with action queries. Select queries contain a SELECT statement
and can return records; action queries do not.

Any ideas??


hmadyson said:
It depends on what you put in the line strQuery = "Select * from
[query_list]
where group_id=" & GroupID

query_list should be the name of the table that you are looking for.
group_id must be the name of the field that you want to limit on, and
GroupID
must be an appropriate value. If group_id is a text field, you will need
to
change it like

strQuery = "Select * from [query_list] where group_id='" & GroupID &"'"

the previous code assumes that it is a number.

I have proved on my PC that this code works (reason for all of the
rewrites), but it all depends on your database specifics.


DavPet said:
Thanks for the self-checking and updates.

When I execute "RunQueries (1)"
I am getting this error:
Too few parameters. Expected 1 (Error 3061) at the line that says Set
rec =
qry.OpenRecordset()

Dunno' what to do.


hmadyson said:
I am so embarassed I made another mistake. Here is the new code

Sub RunQueries(GroupID As Long)
Dim strQuery As String
Dim qry As QueryDef
Dim rec As Recordset

'get a list of all queries for the given groupid
strQuery = "Select * from [query_list] where group_id=" & GroupID
Set qry = CurrentDb.CreateQueryDef("", strQuery)
Set rec = qry.OpenRecordset()
'record list of all of the queries
With rec
'go through the list until it is complete
Do Until (.EOF)
'grab the appropriate query with name of in the QueryName
field
Set qry = CurrentDb.QueryDefs(.Fields("QueryName"))
'run the query
qry.Execute
'move to the next query
.MoveNext
Loop
End With

'clean up by setting objects to nothing
Set qry = Nothing
Set rec = Nothing
End Sub

:

I have a lot of queires.
I want to put the query names in a table, one field of the table will
have a
group_id. Then I want to execute the queries for a certain group_id.

Please jump start me on how to do it.
Click to expand...
Click to expand...
Click to expand...
 
For a select query use

DoCmd.OpenQuery "queryname", acViewNormal

You can tell if a query is select by checking for

qry.Type = dbQSelect

or just that the first word in the sql is select

Let me know if I can provide more assistance.

DavPet said:
Now I get it. It works great for action queries but won't execute a "Select
query".
Cannot execute a select query. (Error 3065)
You tried to use the Execute method with a select query. The Execute method
is valid only with action queries. Select queries contain a SELECT statement
and can return records; action queries do not.

Any ideas??


hmadyson said:
It depends on what you put in the line strQuery = "Select * from
[query_list]
where group_id=" & GroupID

query_list should be the name of the table that you are looking for.
group_id must be the name of the field that you want to limit on, and
GroupID
must be an appropriate value. If group_id is a text field, you will need
to
change it like

strQuery = "Select * from [query_list] where group_id='" & GroupID &"'"

the previous code assumes that it is a number.

I have proved on my PC that this code works (reason for all of the
rewrites), but it all depends on your database specifics.


DavPet said:
Thanks for the self-checking and updates.

When I execute "RunQueries (1)"
I am getting this error:
Too few parameters. Expected 1 (Error 3061) at the line that says Set
rec =
qry.OpenRecordset()

Dunno' what to do.


I am so embarassed I made another mistake. Here is the new code

Sub RunQueries(GroupID As Long)
Dim strQuery As String
Dim qry As QueryDef
Dim rec As Recordset

'get a list of all queries for the given groupid
strQuery = "Select * from [query_list] where group_id=" & GroupID
Set qry = CurrentDb.CreateQueryDef("", strQuery)
Set rec = qry.OpenRecordset()
'record list of all of the queries
With rec
'go through the list until it is complete
Do Until (.EOF)
'grab the appropriate query with name of in the QueryName
field
Set qry = CurrentDb.QueryDefs(.Fields("QueryName"))
'run the query
qry.Execute
'move to the next query
.MoveNext
Loop
End With

'clean up by setting objects to nothing
Set qry = Nothing
Set rec = Nothing
End Sub

:

I have a lot of queires.
I want to put the query names in a table, one field of the table will
have a
group_id. Then I want to execute the queries for a certain group_id.

Please jump start me on how to do it.
Click to expand...
Click to expand...
Click to expand...
 
Great job. Thank you very much.


hmadyson said:
For a select query use

DoCmd.OpenQuery "queryname", acViewNormal

You can tell if a query is select by checking for

qry.Type = dbQSelect

or just that the first word in the sql is select

Let me know if I can provide more assistance.

DavPet said:
Now I get it. It works great for action queries but won't execute a
"Select
query".
Cannot execute a select query. (Error 3065)
You tried to use the Execute method with a select query. The Execute
method
is valid only with action queries. Select queries contain a SELECT
statement
and can return records; action queries do not.

Any ideas??


hmadyson said:
It depends on what you put in the line strQuery = "Select * from
[query_list]
where group_id=" & GroupID

query_list should be the name of the table that you are looking for.
group_id must be the name of the field that you want to limit on, and
GroupID
must be an appropriate value. If group_id is a text field, you will
need
to
change it like

strQuery = "Select * from [query_list] where group_id='" & GroupID &"'"

the previous code assumes that it is a number.

I have proved on my PC that this code works (reason for all of the
rewrites), but it all depends on your database specifics.


:

Thanks for the self-checking and updates.

When I execute "RunQueries (1)"
I am getting this error:
Too few parameters. Expected 1 (Error 3061) at the line that says Set
rec =
qry.OpenRecordset()

Dunno' what to do.


I am so embarassed I made another mistake. Here is the new code

Sub RunQueries(GroupID As Long)
Dim strQuery As String
Dim qry As QueryDef
Dim rec As Recordset

'get a list of all queries for the given groupid
strQuery = "Select * from [query_list] where group_id=" & GroupID
Set qry = CurrentDb.CreateQueryDef("", strQuery)
Set rec = qry.OpenRecordset()
'record list of all of the queries
With rec
'go through the list until it is complete
Do Until (.EOF)
'grab the appropriate query with name of in the QueryName
field
Set qry = CurrentDb.QueryDefs(.Fields("QueryName"))
'run the query
qry.Execute
'move to the next query
.MoveNext
Loop
End With

'clean up by setting objects to nothing
Set qry = Nothing
Set rec = Nothing
End Sub

:

I have a lot of queires.
I want to put the query names in a table, one field of the table
will
have a
group_id. Then I want to execute the queries for a certain
group_id.

Please jump start me on how to do it.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top