Create a form command button that moves the form to a random record

[John S. Ford, MD]

I have a form bound to an underlying table. Can someone suggest some code
for a command button that would move the form to a random record? I assume
the button's event handler will have some DAO code in it.

Thanks in advance!

John

 

[Allen Browne]

I have a form bound to an underlying table. Can someone suggest some
code for a command button that would move the form to a random record?
I assume the button's event handler will have some DAO code in it.

Try something like this:

Private Sub cmdRandom_Click()
Dim rs As DAO.Recordset
Dim lngHowMany As Long

Randomize
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveLast
Me.Bookmark = rs.Bookmark
lngHowMany = Int(rs.RecordCount * Rnd()) + 1
DoCmd.GoToRecord , Me.Name, acPrevious, lngHowMany
End If
Set rs = Nothing
End Sub

 

[John S. Ford, MD]

Thanks Allen. That looks good.

John

Try something like this:

Private Sub cmdRandom_Click()
Dim rs As DAO.Recordset
Dim lngHowMany As Long
Randomize
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveLast
Me.Bookmark = rs.Bookmark
lngHowMany = Int(rs.RecordCount * Rnd()) + 1
DoCmd.GoToRecord , Me.Name, acPrevious, lngHowMany
End If
Set rs = Nothing
End Sub