Old 06-24-2014, 10:06 PM   #1 (permalink)
Status: Junior Member
Posts: 3

Default 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.
Voluntaryist is offline   Reply With Quote
Old 06-24-2014, 10:06 PM   #2 (permalink)
Status: Senior Member
Posts: 101

Default 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
Blackened is offline   Reply With Quote



Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
Random ringing noise from laptop after getting a random phonecall from a random guy that talks a random language.? LarsAndre Programming Languages 2 06-12-2014 02:06 PM
how do i make a random sentence appear with 1 random noun, 1 random and 1 random adjective in sakura Coding 0 05-01-2013 02:06 PM
php mysql multiple record random? JohnCristopher MySQL Databases 0 02-09-2013 03:05 AM
Write a window program to display random string 20 times with random colors at random positions? Unknown_User Programming 2 08-24-2012 10:38 AM
Where to find a random online colour generator? Unknown_User Coding 1 03-18-2012 07:08 AM