tr******@comcast.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.QueryDefs
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(fld 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.QueryDefs
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 acLBGetColumnCount ' Get number of columns.
ReturnVal = 1
Case acLBGetColumnWidth ' 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).QueryName
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