Array's <Subscript out of Range>

[crosmill]

Hello all, it's been a while since I was last here, I've been doing alot of ASP recently.

Anyway my problem...

I'm creating a program that the will randomly select multiple records, I'm at a stage where I want to randomly create x number of winners.

Here's the code:

Private Sub Command7_Click()
Dim dbs As Database, rst As Recordset, RanValue, NoOfRecords, noLoop, nolooped, AccNoOfRecords, RanNum, ln As Integer
Dim sqlStr As String
Dim RecordArray As Variant

Set dbs = CurrentDb

'Creates Record ID array
sqlStr = "SELECT * FROM WinnerSelector WHERE [WinnerType] = Null"
Set rst = dbs.OpenRecordset(sqlStr)
While Not rst.EOF
NoOfRecords = NoOfRecords & rst("ID") & ","
rst.MoveNext
Wend
'Removes Trailing Comma
ln = Len(NoOfRecords)
NoOfRecords = Left(NoOfRecords, (ln - 1))
RecordArray = Array(NoOfRecords)
AccNoOfRecords = rst.RecordCount
Set dbs = Nothing

'Selects a Random record excluding those already selected
'and updates Winner Type to that specified
Set dbs = CurrentDb
'Loop no of times requested
noLoop = Me.NoOfPrizes
nolooped = 0
While nolooped <> noLoop

Randomize
RanNum = (Int(AccNoOfRecords * Rnd) + 1)
RanValue = RecordArray(RanNum)
sqlStr = "SELECT * FROM WinnerSelector WHERE [ID] = " & RanValue & " AND [WinnerType] = Null"
Set rst = dbs.OpenRecordset(sqlStr)

rst.Edit
rst("WinnerType") = Me.PrizeType
rst.Update
nolooped = nolooped + 1
Wend
Set dbs = Nothing
End Sub


OK, I haven't worked with Array's before but I'm muddleing through.

The error I get is from this line:
RanValue = RecordArray(RanNum)
Where the record Array is telling me <Subcript out of Range>

I've checked the values and RanNum is between 0 and total number of values in the array.

I don't really know whats wrong.

Cheers everyone.

c

 

[Travis]

I believe your problem is that the Array is 0 to your recordcount-1

and your random statement is 1 to your recordcount.

change your random statement to include the range 0 to recordcount-1

or add an additional -1 to the value of your randomized number to keep it in the range.

 

[crosmill]

Thanks Travis

I read abit more about Rnd and have changed the code to


Randomize
RanNum = (Int((AccNoOfRecords - 0 + 1) * Rnd) + 0)
RanValue = RecordArray(RanNum)



When you debugg it will tell you the value for each variable.

RanNum is always a valid number???


but I'm not sure that was what you were getting at???


still the same problem.

 

[Travis]

If you are getting a <Subcript out of Range> error then the RANNUM is not a valid number.

Add a BreakPoint in your Code and walk through it. Look at the values of RANNUM. Also look at the RecordArray in the Locals Window. Look to see how many values that it has. and see if it is what you expect.

 

[crosmill]

I've checked the RanNum several times and it's always a whole number between 1 and the total number in the array.

I tried taking the RanNum out and replacing it with a number, ie not a variable, and I still get the same error, which makes me think I'm doing something wrong with the array???


Ah!!!

in the locals window I discovered that the Value RecordArray has a plus, when clicked it has RecordArray(0) with a value of "1,2,3,4,5,6,7,8,9,10".

Should this value be "Array(1,2,3,4,5,6,7,8,9,10)"

RanNum has a vlaue of "4".

Thanks for help

 

[crosmill]

OK I've worked out the problem but I don't know how to solve it.

The code is putting all the values into the the array at point '0'

So, if RanNum = 0 then it will call the array, but position 0 is all the values 1, 2, 3, 4...... etc

When I've put the values into the array manually, in the debugger the array has values of:
RecordArray(1) = 1
RecordArray(2) = 2
RecordArray(3) = 3
etc.

See last post.

The Value of NoOfRecords has "s around the vlaue in the dubugger - could this be the problem??

Any Thoughts????????

 

[David R]

Here's the code:

Private Sub Command7_Click()
Dim dbs As Database, rst As Recordset, RanValue, NoOfRecords, noLoop, nolooped, AccNoOfRecords, RanNum, ln As Integer
Dim sqlStr As String
Dim RecordArray As Variant

Try defining each of those variables in the first line indepedently as Integer type. Access doesn't follow the convention where everything in a comma-list is defined as the last type given. Your variables are getting defined as Variant and that may be part of the problem.

Dim dbs As Database, rst As Recordset, RanValue As Integer,
 NoOfRecords As Integer, noLoop As Integer, nolooped As Integer,
 AccNoOfRecords As Integer, RanNum As Integer, ln As Integer

 

[crosmill]

Thanks Dave,

I retyped them as

Dim dbs As Database
Dim rst As Recordset
Dim RanValue, noLoop, nolooped, AccNoOfRecords, RanNum, ln As Integer
Dim sqlStr, NoOfRecords As String
Dim RecordArray As Variant

But with no joy.

I think the problem is that it's reading NoOfRecords as just one string and ignoring the commas inside the variable.

Is there a way to force the Array to look for an series of integers rather than a string type??

 

[David R]

Sorry, I thought NoOfRecords was a numeric variable.

Look again at what I said. Variables Dimmed in series with commas separating them get defined as Variant unless you specify what they are. The line that ends "ln As Integer" defines everything before that as Variant still.

 

[crosmill]

Dim dbs As Database
Dim rst As Recordset
Dim RanValue As Integer
Dim noLoop As Integer
Dim nolooped As Integer
Dim AccNoOfRecords As Integer
Dim RanNum As Integer
Dim ln As Integer
Dim sqlStr As String
Dim RecordArray, NoOfRecords As Variant

Still no joy

 

[crosmill]

Closed

http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=97010#post97010