[AccessD] Random numbers lottery-style

Steve,

Sorry I was out sick on Friday.  Is this problem not solved yet?  You could try changing your line of code that adds a value to your colInt to this:

   colI.Add rstI("ActivityID").Value, CStr(lng1)

	or

   'create a variable to hold the field value and add that to the collection instead
   intActivityID = rstI("ActivityID") 
   colI.Add intActivityID, CStr(lng1)

It sounds like you may be adding the field from the recordset to your collection as opposed to the field's value (hence the reference in your error to no current record and why the object is not valid).

HTH,

Jim D.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto: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


 



***********************************************************************************
"This electronic message is intended to be for the use only of the named recipient, and may contain information from Hudson Health Plan (HHP) that is confidential or privileged.  If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited.  If you have received this message in error or are not the named recipient, please notify us immediately, either by contacting the sender at the electronic mail address noted above or calling HHP at (914) 631-1611. If you are not the intended recipient, please do not forward this email to anyone, and delete and destroy all copies of this message.  Thank You".
***********************************************************************************