Creating a function that returns a collection...

[Piet Linden]

actually, then I'm assigning the collections it can return to
something else... but one problem at a time...

Here's my function... don't laugh too hard... I'm brand new at this...

The basic idea is to collect the names of all the select and crosstab
queries in the database and put them in a collection, so that I can
pass the resulting collections to a pair of listboxes in the MultiPik
form (from ADH).

This works fine. I can declare a collection and stuff the return
value of this function into it, and it works fine....

Public Function colChosenQueries(ByVal strGroup As String) As
Collection
Dim myQueries As New Collection
Dim qdf As DAO.QueryDef
Dim intKey As Integer
Dim intCounter As Integer

If strGroup = "Available" Then
For Each qdf In CurrentDb.QueryDefs
If qdf.Type = dbQSelect Or qdf.Type = dbQCrosstab Then
If Left$(qdf.Name, 1) <> "~" And qdf.Name <>
"zqryObjectList" Then
intKey = intKey + 1
myQueries.Add Item:=qdf.Name, Key:=CStr(intKey)
End If
End If
Next qdf
ElseIf strGroup = "Selected" Then
Set colChosenQueries = myQueries 'an empty collection
End If


Set colChosenQueries = myQueries
Set myQueries = Nothing
End Function

e.g.:
?colchosenqueries("Available").Count
7
?colchosenqueries("Selected").Count
0


but when I try to assign the collections to the two listboxes, it
fails... no error - it just doesn't populate any listboxes...

Private Sub Form_Open(Cancel As Integer)
' Four steps to using Multipik:

' 1. Instantiate the object --- OK
' 2. Set up the RowSourceType for the list boxes.
' If you did this at design time, you needn't
' do it now.
' 3. Register the 8 controls with the object --- OK
' 4. Tell it where your data comes from -- OK, the collection
function...

Set mmp = New MultiPik

mmp.RegisterControls _
lstAvailable, lstSelected, _
cmdAddOne, cmdAddAll, _
cmdDeleteOne, cmdDeleteAll, _
cmdUp, cmdDown
' Modify the following line to
' match your own needs.

' Or two collections, using the SetCollections method.
mmp.SetCollections colChosenQueries("Available"), colChosenQueries
("Selected")

End Sub

the code executes without error, but I get nothing in either
listbox...

So I'm baffled... any idea what I could be doing wrong?

Thanks!

Pieter

 

[Scott McDaniel]

is there a reason you're using a Collection for this? You can refer to the MSysObjects table directly, and set your
RowSource to show only your queries:

Me.MyCombo.RowSource = "SELECT MSysObjects.Name, MSysObjects.Type, Left([Name],1) AS FirstChar FROM MSysObjects WHERE

actually, then I'm assigning the collections it can return to
something else... but one problem at a time...

Here's my function... don't laugh too hard... I'm brand new at this...

The basic idea is to collect the names of all the select and crosstab
queries in the database and put them in a collection, so that I can
pass the resulting collections to a pair of listboxes in the MultiPik
form (from ADH).

This works fine. I can declare a collection and stuff the return
value of this function into it, and it works fine....

Public Function colChosenQueries(ByVal strGroup As String) As
Collection
Dim myQueries As New Collection
Dim qdf As DAO.QueryDef
Dim intKey As Integer
Dim intCounter As Integer

If strGroup = "Available" Then
For Each qdf In CurrentDb.QueryDefs
If qdf.Type = dbQSelect Or qdf.Type = dbQCrosstab Then
If Left$(qdf.Name, 1) <> "~" And qdf.Name <>
"zqryObjectList" Then
intKey = intKey + 1
myQueries.Add Item:=qdf.Name, Key:=CStr(intKey)
End If
End If
Next qdf
ElseIf strGroup = "Selected" Then
Set colChosenQueries = myQueries 'an empty collection
End If


Set colChosenQueries = myQueries
Set myQueries = Nothing
End Function

e.g.:
?colchosenqueries("Available").Count
7
?colchosenqueries("Selected").Count
0


but when I try to assign the collections to the two listboxes, it
fails... no error - it just doesn't populate any listboxes...

Private Sub Form_Open(Cancel As Integer)
' Four steps to using Multipik:

' 1. Instantiate the object --- OK
' 2. Set up the RowSourceType for the list boxes.
' If you did this at design time, you needn't
' do it now.
' 3. Register the 8 controls with the object --- OK
' 4. Tell it where your data comes from -- OK, the collection
function...

Set mmp = New MultiPik

mmp.RegisterControls _
lstAvailable, lstSelected, _
cmdAddOne, cmdAddAll, _
cmdDeleteOne, cmdDeleteAll, _
cmdUp, cmdDown
' Modify the following line to
' match your own needs.

' Or two collections, using the SetCollections method.
mmp.SetCollections colChosenQueries("Available"), colChosenQueries
("Selected")

End Sub

the code executes without error, but I get nothing in either
listbox...

So I'm baffled... any idea what I could be doing wrong?

Thanks!

Pieter

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com