Random Record Selection

[soundmangav]

I’m trying to modify a simple Database application in order to retrieve Records in a Random order. Basically the application consists of a Table of Accounts and a simple Front End Form used to retrieve and update each account.

At present when the user clicks the “Next Account” command button the first available account is retrieved and displayed. The selection criteria states that the “Date_Actioned” field must be null, using the following code:

Private Sub Next_Account()

Dim db As Database
Dim rst1 As Recordset

Dim sSearch As String

Set db = CurrentDB

sSearch = "SELECT TOP 1 * FROM tblAccounts "
sSearch = sSearch & "WHERE tblAccounts.Date_Actioned Is Null "

Set rst1 = db.OpenRecordset(sSearch, dbOpenDynaset, dbPessimistic)

If rst1.RecordCount < 1 Then
MsgBox &quot;Sorry, there are no more records to work.&quot;, vbInformation, &quot;Account Viewer&quot;
Exit Sub
Else
Display record details, edit & update etc…


Question: How can I modify the SQL Statement to Select the next available account in Random order. For example, say I have 10 Accounts in my Table the first 5 of which have been previously updated (Date Field populated). When I click “Next Account” instead of being presented with Account 6, I wish to select a Random Account from the available records (i.e. 6-10). Is it possible to use the RecordCount property of the recordset as the upper limit in a random function?

 

[gol4]

this should be close will want to ensure it is not eof or bof but have at it

If rst1.RecordCount < 1 Then
MsgBox &quot;Sorry, there are no more records to work.&quot;, vbInformation, &quot;Account Viewer&quot;
Exit Sub
Else 'loop thru a random amount of times
for x = to Int((rst1.recordcount * Rnd) + 1)
rst1.movenext
next x
Display record details, edit & update etc…