[AccessD] Random numbers lottery-style

Steve Erbach erbachs at gmail.com
Mon Mar 7 12:45:25 CST 2005


Jim,

You may have something there. For now it's an academic point because I
worked around it to make over 7000 test data records. I will, however,
remember the .value bit for future reference. It makes sense. Thank
you.

Steve Erbach


On Mon, 7 Mar 2005 11:48:23 -0500, Jim DeMarco
<Jdemarco at hudsonhealthplan.org> wrote:
> 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".
> ***********************************************************************************
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


-- 
Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com
Security Page: www.swerbach.com/security



More information about the AccessD mailing list