Solved: Checking for BOF or EOF

[JustJerry]

This in relation to a question I asked prior.

I am getting the following error:

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

I am selecting a random record from a table and I am unsure how to correctly check for the above condition. The table DOES contain records, so how do I code this so that if the condition is 'True', then look for a valid record.

Dim jk As String
Dim i As Integer
Dim RndNmbr As Single
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst.Open "SELECT * FROM tblJokeName", CurrentProject.Connection, adOpenStatic
'select the Jokes names

RndNmbr = Int((9 * Rnd + 1))
'randomize it

For i = 0 To RndNmbr
rst.MoveNext
Next i
'get to the random Record

jk = rst(1)
'assign it

rst.Close
Set rst = Nothing

DoCmd.Close
'close the main form
DoCmd.OpenForm stDocName
Form_frmStart.FrownImage.Visible = False
Form_frmStart.HelloImage.Visible = True
Form_frmStart.lblWelcome.Value = "WELCOME " & jk & " (aka " & [Forms]![frmTemp].Form!Name.Value & ")"
'display on joke_form......!

[OBP]

Is it the rst.movenext that creates the error?
If it is, you need to check for EOF before tryig to move.
I would do this using using a form, where you can see what is happening. THe first thing to do is to move the last record and record it in a variable, then check that you are not at tha record beforetrying to movenext.

[geekgirlau]

Instead of using .MoveNext in a loop, you can also move a specific number of records:

rst.Move RndNmbr
However you still need a check for the number of records in the recordset, as this will generate an error if the random number is greater than the number of records.


Randomize
RndNmbr = Int((9 * Rnd + 1))

With rst
If .RecordCount >= RndNmbr Then
.Move RndNmbr
End If
End With

[JustJerry]

Thank you both for the suggestions.

I added geekgirlau's code and seems to be working fine now.