ADH MultiPik weirdness...

[Piet Linden]

Well, maybe it's just me, but no matter what, I can't get it to
work...

I have a table of queries...
ztblQueries(
QueryName Text(50) NOT NULL,
Sequence Integer)

What I am trying to do is populate one listbox (selected) with the
queries where the Sequence is not null and put the rest in the other
listbox. (available).

No matter what I do, I get hung up on converting the recordset to an
array.

Okay, I *know* this is screwy... I was just trying to figure out how
to get the stupid thing to work... that's why all the weird code...

Private Sub cmdReloadDefaultList_Click()

Dim mmp As MultiPik

Dim varSelectedRows As Variant
Dim varAvailableRows As Variant
Dim strList As String
Dim intIndex As Integer

' process the selected set.
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("SELECT QueryName FROM
ztblQueries WHERE Sequence IS NOT NULL ORDER BY Sequence ASC;",
dbOpenSnapshot)
Do Until rs.EOF
'varSelectedRows(intCounter) = rs.Fields("QueryName")
strList = strList + "," & rs.Fields("QueryName")
rs.MoveNext
Loop
rs.Close

Debug.Print "Selected: " & strList
strList = Right$(strList, Len(strList) - 1)
varSelectedRows = Split(strList, ",")
strList = ""


Set rs = DBEngine(0)(0).OpenRecordset("SELECT QueryName FROM
ztblQueries WHERE Sequence IS NULL ORDER BY Sequence ASC;",
dbOpenSnapshot)
Do Until rs.EOF
strList = strList + "," & rs.Fields("QueryName")
rs.MoveNext
Loop
rs.Close

strList = Right$(strList, Len(strList) - 1)
Debug.Print "Available: " & strList
varAvailableRows = Split(strList, ",")

Set rs = Nothing

'Debug.Print: Debug.Print "Available Rows"
'For intIndex = LBound(varAvailableRows) To UBound
(varAvailableRows)
' Debug.Print intIndex, varAvailableRows(intIndex)
'Next intIndex

'Debug.Print
' Debug.Print: Debug.Print "Selected Rows"
'For intIndex = LBound(varSelectedRows) To UBound(varSelectedRows)
' Debug.Print intIndex, varSelectedRows(intIndex)
'Next intIndex

If UBound(varAvailableRows) = 0 And UBound(varSelectedRows) = 0
Then
'do nothing
Else
Debug.Print "Available: " & UBound(varAvailableRows),
"Selected: " & UBound(varSelectedRows)
Set mmp = New MultiPik
Debug.Print "TestFail: ", IsArray(varSelectedRows), IsArray
(varAvailableRows)
If IsArray(varAvailableRows) And IsArray(varSelectedRows) Then
mmp.SetArrays varAvailableRows, varSelectedRows
End If
Set mmp = Nothing
End If

If all I really want is an array of AvailableRows and SelectedRows,
how do I build them?
I tried using GetRows and no matter what I did, it would fail - the
arrays would be empty. Read the "help", and as usual, convoluted as
all get out.

Weirdness... IsArray(varSelectedRows) returns True. don't think UBound
(var...Rows) works... (getting really late...)

Any idea what stupid mistake I'm making? This SHOULD be about as
simple as
varRows=rst.GetRows

but it ain't so, Joe!!!

Any idea what I"m doing wrong?

Thanks!
Pieter

 

[Piet Linden]

Okay, how about an easier one...

Private Sub cmdReloadDefaultList_Click()

Dim mmp As MultiPik
Dim colAvailable As Collection
Dim colSelected As Collection
Dim rs As DAO.Recordset

Set mmp = New MultiPik
Set colAvailable = New Collection
Set colSelected = New Collection

' process the selected set.
Set rs = DBEngine(0)(0).OpenRecordset("SELECT QueryName FROM
ztblQueries WHERE Sequence IS NOT NULL ORDER BY Sequence ASC;",
dbOpenSnapshot)
Do Until rs.EOF
colSelected.Add rs!QueryName
rs.MoveNext
Loop
rs.Close


' process the available set.
Set rs = DBEngine(0)(0).OpenRecordset("SELECT QueryName FROM
ztblQueries WHERE Sequence IS NULL ORDER BY Sequence ASC;",
dbOpenSnapshot)
Do Until rs.EOF
colAvailable.Add Item:=rs!QueryName
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

' the collections are filled,
Debug.Print "Available:" & colAvailable.Count, "Selected: " &
colSelected.Count
' now assign the collections to the listboxes
'FAILS!!!

'Error: 3420: Object Invalid or no longer set
mmp.SetCollections colAvailable, colSelected

Set colAvailable = Nothing
Set colSelected = Nothing
Set mmp = Nothing

End Sub

What's weird is that the debug line works...
Available:3 Selected: 4

.... so the collections exist and are populated...

I guess I'm missing the voodoo to make this work... any idea what I'm
doing wrong?

 

[Graham Mandeno]

Hi Piet

I'm sorry, I know nothing about the MultiPik class you are using here, but
I'm assuming it is a way of subclassing two listboxes so you can move items
from one to the other.

If so, then I would expect that you'd need to tell the MultiPik what the
listboxes are before attempting to load them.