Array data types

[crosmill]

I've reposted this from another post because I've worked out the problem but still don't know how to solve it.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=30033

First things first, here's the code

Private Sub Command7_Click()
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 As Variant
Dim NoOfRecords 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
If NoOfRecords = Null Then
NoOfRecords = rst("ID") & ", "
Else
NoOfRecords = NoOfRecords & rst("ID") & ", "
End If
rst.MoveNext
Wend
'Removes Trailing Comma and space
ln = Len(NoOfRecords)
AccNoOfRecords = rst.RecordCount
NoOfRecords = Left(NoOfRecords, (ln - 2))
RecordArray = Array(NoOfRecords)
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 - 0 + 1) * Rnd) + 0)
RanValue = RecordArray(8) '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

Now, the problem is, RecordArray = Array(NoOfRecords), NoOfRecords contains data similar to "1, 2, 3, 4, 5, 6, 7, 8, 9, 10" the trouble is that the Array is looking at it like Array("1, 2, 3, 4, 5, 6, 7, 8, 9, 10", "more stuff here if you want it") so it's seeing the inserted numbers as a string instead of a series of integers.

Can anyone help?

 

[cogent1]

You need to declare an array with more than one element, which is not what you're doing at present. Declare an empty dynamic array , then redimension it to the Recordcount as shown.

rs.MoveLast
rs.MoveFirst
ReDim MyArray(rs.RecordCount)

For x = 1 To rs.RecordCount
MyArray(x) = rs("ID")
Debug.Print MyArray(x)

rs.MoveNext
Next

That seemed to work for me. You don't need to add NoOfRecords to the array...

 

[crosmill]

Great one cheers mate

 

[crosmill]

This is the code I finished up with.

Private Sub Command7_Click()
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 As Variant
Dim NoOfRecords As Variant
Dim x As Integer

Set dbs = CurrentDb

noLoop = Me.NoOfPrizes
nolooped = 0

While nolooped < noLoop
'Creates Record ID array
sqlStr = "SELECT * FROM WinnerSelector WHERE [WinnerType] = Null"
Set rst = dbs.OpenRecordset(sqlStr)
rst.MoveLast
rst.MoveFirst
ReDim RecordArray(rst.RecordCount)
For x = 1 To rst.RecordCount
RecordArray(x) = rst("ID")
Debug.Print RecordArray(x)
rst.MoveNext
Next
AccNoOfRecords = rst.RecordCount

Set rst = Nothing

'Selects a Random record excluding those already selected
Randomize
RanNum = (Int((AccNoOfRecords - 1 + 1) * Rnd) + 1)
RanValue = RecordArray(RanNum)
sqlStr = "SELECT * FROM WinnerSelector WHERE [ID] = " & RanValue & " AND [WinnerType] = Null"
Set rst = dbs.OpenRecordset(sqlStr)
'and updates Winner Type to that specified
rst.Edit
rst("WinnerType") = Me.PrizeType
rst.Update
nolooped = nolooped + 1
Wend
Set dbs = Nothing
End Sub




Thanks again for everyones help