[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