Loop through the queries collection

[troy_lee@comcast.net]

I want to loop through the queries collection and then use these names
to define a value list for a combo box. What is the best way to do
this?

Thanks in advance.

[Salad]

Re: Loop through the queries collection

troy_lee@comcas t.net wrote:

I want to loop through the queries collection and then use these names
to define a value list for a combo box. What is the best way to do
this?
>
Thanks in advance.

Here's a method to list the queries. As far as a value list...do that
only if you have a teeny tiny database with very few queries.

Sub QueryList()
Dim qdf As QueryDef

For Each qdf In CurrentDb.Query Defs
If Left(qdf.Name, 1) <"~" Then
Debug.Print qdf.Name
End If
Next qdf
MsgBox "Done"
End Sub


Here's a method I might use instead for a list/combobox.
Private Type QueryList
QueryName As Variant
End Type
Private Function FillQueryList(f ld As Control, ID As Variant, row As
Variant, col As Variant, Code As Variant) As Variant
On Error Resume Next

Static strRows() As QueryList
Static Entries As Integer
Dim qdf As QueryDef
Dim ReturnVal As Variant

ReturnVal = Null

Select Case Code
Case acLBInitialize ' Initialize.

Entries = 0
ReDim Preserve strRows(Entries )

'creates header/column row. Useful if colheads is True
strRows(Entries ).QueryName = "Query Name"

For Each qdf In CurrentDb.Query Defs
If Left(qdf.Name, 1) <"~" Then
Entries = Entries + 1
ReDim Preserve strRows(Entries )
strRows(Entries ).QueryName = qdf.Name
End If
Next qdf

ReturnVal = True
Case acLBOpen ' Open.
ReturnVal = Timer ' Generate unique ID for control.
Case acLBGetRowCount ' Get number of rows.
ReturnVal = Entries + 1
Case acLBGetColumnCo unt ' Get number of columns.
ReturnVal = 1
Case acLBGetColumnWi dth ' Column width.
ReturnVal = -1 ' -1 forces use of default width.
Case acLBGetValue ' Get data.
Select Case col
Case 0
'this example has a single column.
ReturnVal = strRows(row).Qu eryName
End Select
Case acLBEnd ' End.
Erase strRows
End Select
FillQueryList = ReturnVal
End Function

Now create a listbox, 1 column to display query name.

In the Listbox's RowSourceType row (under Data tab) enter
FillQueryList
and then blank out the RowSource row.

Drop this code into the Form's code module.

Save and run.

BTW, I would spend a few minutes and study this code. If you have a
combo/list box that is slow due to the number of records this method
will speed it up. You now have the template to do this for any other
times/types.

Moby

http://www.youtube.com/watch?v=7vxYaEAyTd4

[troy_lee@comcast.net]

Re: Loop through the queries collection

Very nice Salad. BTW, the value list for this combo box is limited and
is relatively static. I could have hand-typed the value list, but I
wanted to know how to do this out of curiosity. With your code, you
actually solved another problem I have had, so I got a two-for-one
today.

Thanks as always.

[Salad]

Re: Loop through the queries collection

troy_lee@comcas t.net wrote:

Very nice Salad. BTW, the value list for this combo box is limited and
is relatively static. I could have hand-typed the value list, but I
wanted to know how to do this out of curiosity. With your code, you
actually solved another problem I have had, so I got a two-for-one
today.
>
Thanks as always.
>

Not a prob.

I had a complicated query in the past that I needed to filter and used
that in a combo box's rowsource. It started getting slow. I moved to
the method I supplied and it was lightening fast.

The problem with value lists is the number of rows it can display. It's
easy to exceed the length of the max value list.