Coding Core, Webmaster Forum, Coding Forum

Coding Core, Webmaster Forum, Coding Forum (
-   Databases (
-   -   How to Find a Random Record? (

Voluntaryist 06-24-2014 10:06 PM

find a random record
I am trying to find a random record in an access table using VBA and the click of a button called "RandomWOD".

Currently, this is my code:

Option Compare Database

Private Sub RandomWOD_Click()
Dim RecordNumber As Integer
Dim RecSor As String
RecSor = Me.RecordSource
RecordNumber = CInt(Rnd() * DCount("*", RecSor))
DoCmd.GoToRecord , , acFirst
Me.Recordset.Move RecordNumber
End Sub

It appears to return random records for a while, but at some point I get the error:

Run-time error '3021':

No current record.

I am not sure how to solve this problem.

I also am not sure it is returning a truly random number. It may be giving me pseudo-random answers in the same order each time I open the database up and push the button....

Any help would be greatly appreciated.

Blackened 06-24-2014 10:06 PM

find a random record
If you move to an invalid record without having set the bookmark property (or moving to a valid record) then you'll get the 'no current record' error. It's kind of an annoying error because it'll seem like you're on a valid record, but it'll keep throwing the error until you close/reopen the form, or manually move to another record. Also, you need to use the randomize statement to keep from getting the same sequence over and over. (Technically, I don't think this code should be able to move to an invalid record, unless the recordset is empty, so the bookmark might not be necessary).

Private Sub RandomWOD_Click()
Dim RecordNumber As Long
Dim bMark As Variant 'to store bookmark
RecordNumber = CLng(Rnd() * Me.Recordset.RecordCount)
With Me.Recordset
bMark = .Bookmark
DoCmd.GoToRecord , , acFirst
.Move RecordNumber
If .EOF Or .BOF Then
.Bookmark = bMark
End If
End With
End Sub

All times are GMT. The time now is 04:13 PM.

All Rights Reserved.