Selecting Records With Specific Criteria

[parry]

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.

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

 

[Norie]

Couldn't you use an SQL query for the recordset?

 

[SydneyGeek]

Hi Parry,

Picking up on Norie's comment -- here's one approach for generating n random records from a table, and selecting them. Maybe you could adapt it: http://www.fontstuff.com/vba/vbatut02.htm

Also, check out these links:
http://support.microsoft.com/?kbid=210468
http://support.microsoft.com/default.aspx?scid=kb;EN-US;208855
http://www.4guysfromrolla.com/webtech/102999-1.shtml

Denis

 

[parry]

Thanks for the links. Im happy enough with doing RND its just getting the recordset beforehand which the first link seems to have. It seems you have to use a connected recordset rather than a RecordsetClone. My understanding is that the RecordsetClone is for forms while Recordset is the actual table/s behind all my queries so not quite the same but I'll see what happens after I adjust some things.