parry
MrExcel MVP
- Joined
- Aug 20, 2002
- Messages
- 3,355
Hi all, Im not familiar enough with the recordset object to know how to achieve this properly. The purpose is to locate a random record in a form that has the Watched field = true. The following code works but is very unelegant as Im filtering the form, finding a random number and selecting the record, taking a note of the TitleIndex (a unique key field) then turning off filter and locating the record with the found TitleIndex. Because Im turning filter on and off I see the form being flashed a few times.
What I would like is preferably a way to determine the TitleIndex in the recordset clone without filtering the form or alternatively an equivalent of ScreenUpdating=False for Access.
What I would like is preferably a way to determine the TitleIndex in the recordset clone without filtering the form or alternatively an equivalent of ScreenUpdating=False for Access.
Code:
Private Sub RandomRecord_Click()
Dim Rs As DAO.Recordset, RandRec As Long, RecID As Long
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
'^ Purpose: Select a random record in the form. ^
'^ Note: The record must have the watched column = true ^
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
' Filter form so that only Watched records are displayed
Me.Filter = "Watched = True"
' Turn on filter
Me.FilterOn = True
' Create a clone of the filtered form recordset
Set Rs = Me.RecordsetClone
' Initialize random-number generator.
Randomize
' Generate random value between 1 and total records in the filtered form.
RandRec = Int((Rs.RecordCount * Rnd) + 1)
' select the record
DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, RandRec
'Get TitleIndex to identify record when filter is removed
RecID = Me.TitleIndex
'Turn off filter
Me.FilterOn = False
'Create a recordset of the unfiltered form
Set Rs = Me.RecordsetClone
'Find the record in question
Rs.FindFirst "[TitleIndex]=" & RecID
'Display record
Me.Bookmark = Rs.Bookmark
'Clean up
Set Rs = Nothing
End Sub