[AccessD] Random numbers lottery-style

Heenan, Lambert Lambert.Heenan at AIG.com
Thu Mar 3 16:53:52 CST 2005


Could you post the code you are using to refer to an item in the collection?
Both from your procedure and whatever you use in the immediate window.

In fact would you mind posting the whole procedure that calls the two
routines to populate the collections and then makes use of them?

Lambert

> -----Original Message-----
> From:	accessd-bounces at databaseadvisors.com
> [SMTP:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Erbach
> Sent:	Thursday, March 03, 2005 5:27 PM
> To:	Access Developers discussion and problem solving
> Subject:	Re: [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.
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



More information about the AccessD mailing list