[RESOLVED] **Help Needed** Randomly picking a record out of a recordset

[intercepter]

Hi Everyone,

Wondering if someone could guide me in the right direction regarding an error I am getting with a random function.

This sub opens a recordset that has a total that will change from time to time. Its total right now is 18 records. But could get as big as 200 or so. What I am trying to do, is get all the records from a table, and then just use a random function to pick one and move the ado rs curser to that record and then do some other processing.

But every now and again, the sub throws a bof / eof error like the recordset is empty. All fields are checked and valid, and they all have data in each row.

Here is the sub

SQLStr = "Select * From tblFav"

Set db1 = New Connection
db1.CursorLocation = adUseClient
sPath = App.Path & "\kjvbible.mdb"
db1.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
Set adoPrimaryRS1 = New Recordset
Set adoPrimaryRS1 = db1.Execute(SQLStr, , adCmdText)

Randomize
Dim i
i = Int((adoPrimaryRS1.RecordCount - 1 + 1) * Rnd + 1)
adoPrimaryRS1.Move i, 1

'I usually get an error somewhere in this section.

secID = adoPrimaryRS1!Section_ID
Book_ID = adoPrimaryRS1!Book_ID
Chapter = adoPrimaryRS1!Chapter
Verse = adoPrimaryRS1!Verse
User = adoPrimaryRS1!User

' Close RecordSet and Database
adoPrimaryRS1.Close
db1.Close

I am wondering if maybe it is going to EOF with the +1 I am using but I am not sure.

This table will have records added to it, and records removed from it. So the index with not be consistant. it may be 1,2,3,12,33,39,144 etc.

So I just use the recordcount to tell me how many records are in there and randomly move to a record.

If someone can give some ideas, that would be great.

Intercepter

[pranay]

ur prob is not where u r looking ur prob is :


Randomize
Dim i
i = Int((adoPrimaryRS1.RecordCount - 1 + 1) * Rnd + 1)
adoPrimaryRS1.Move i, 1


just run ur project in debug mode and check it out.

Hope it helps!!
Take Care!!

[intercepter]

Thanks for your reply.

I did as you suggested. And with what I saw, I came up with the following:

Randomize
Dim i
i = Int((adoPrimaryRS1.RecordCount - 1) * Rnd)
adoPrimaryRS1.Move i, 1

Is this what you were suggestiong?

Intercepter

[JeffB]

Try this little function:

Public Function GenerateRand(min As Integer, max As Integer) As Integer
    GenerateRand = Int(Rnd * (max - min + 1)) + min
End Function

Just pass it 0 as minimum and your recordcount - 1 as maximum

Just remember that the RND function return a number in the range [0,1[, so for example, if you would do INT(RND * 10), you would get a number from 0 to 9.

I think in the code you just write that you could remove the -1, for example, if your record count is 10, you want to move from the beginning to a max of 9 positions.

JeffB

[intercepter]

Thank you very much. That works great. No errors at all now. Very cool. Thanks again.

I would say this thread is resolved!!

intercepter