[AccessD] Random numbers lottery-style

Jim,

Thanks. I've created a collection that holds the dates for each day of
the year for a one-year period. I also have a collection for the list
of volunteer interests for one volunteer. But something funny is
happening.

I'm using Access 2003. In one procedure I DIM the two New Collections,
colDays and colInts. I fill the colDays collection with a procedure
that looks like this (I pass the colDays collection as a parameter
right after DIMing it):

Private Sub FillDaysCol(colD As Collection)
   Dim i As Long
   Dim dat As Date
   
   On Error GoTo PROC_ERR
   
   ' Set the starting day
   dat = CDate("8/1/2003")
   ' 366 days because of the leap year
   For i = 1 To 366
      colD.Add dat, CStr(i)
      dat = DateAdd("d", 1, dat)
   Next i

PROC_EXIT:
   Exit Sub
PROC_ERR:
   MsgBox "Error " & Err.Number & " : " & Err.Description & _
      " : Line " & Erl, vbCritical, "Error in FillDaysCol"
   Resume PROC_EXIT
End Sub

This works champion. I can Remove items from the collection at will
and the collection persists as I pass it from procedure to procedure
as a parameter.

On the other hand, the colInts doesn't work so well. I DIM the colInts
and pass it as a parameter to another procedure to fill it with the
values from a recordset:

Private Sub FillIntsCol(rstI As DAO.Recordset, colI As Collection)
   Dim lng1 As Long

   On Error GoTo PROC_ERR

   lng1 = 1
   Do While Not rstI.EOF
      colI.Add rstI("ActivityID"), CStr(lng1)
      lng1 = lng1 + 1
      rstI.MoveNext
   Loop
   
PROC_EXIT:
   Exit Sub
PROC_ERR:
   MsgBox "Error " & Err.Number & " : " & Err.Description & _
      " : Line " & Erl, vbCritical, "Error in FillIntsCol"
   Resume PROC_EXIT
End Sub

The procedure builds the collection all right, but when I try to refer
to one of its items back in the calling procedure, I get the error:

Error 3420 : Object invalid or no longer set

Now, if I put a break in the code at the start of the Do While loop, I
can check the item's value in the Immediate window right after it's
been added to the collection. But when the loop finishes up, I check
the Count of the collection and I get the right number of items...but
then I try to check the value of one of those items in the Immediate
window, I get the error:

No current record (runtime error 3021)

What's this about a "record"? I am stumped here. Why does one
collection "building" procedure work, but the other one doesn't?

Steve Erbach

On Thu, 3 Mar 2005 15:22:04 -0500, Jim DeMarco
<Jdemarco at hudsonhealthplan.org> wrote:
> The collection will reindex itself.  There will be one less item for each iteration so you'll have to use the Count property of the Collection to determine the upper bound of the index in your random selection process.
> 
> Jim D.