Return Random Records

[cbrsix]

Hi,



I spent some time looking around online for an answer but couldn't find anything specific to how I need to integrate it into my current form. I want to be able to genereate a list of "n" results (which I want to select the number on a case by case basis) that is based on specific criteria selected in a form. I have a table of clients with their reps and occasionally have to do randome spot checks on certain reps on a random account basis. I already have a search form (code is below) that I would like to have this random function run off of.

Private Sub Clear_Click()
Dim intIndex As Integer
'clear all search items
Me.LastName = ""
Me.FirstName = ""
Me.AccountNumber = ""
Me.SocialSecurityNumber = ""
Me.EntityName = ""
Me.EIN = ""
Me.Company = ""
Me.Status = ""
End Sub
Private Sub Form_Load()
'clear the search form
Clear_Click
End Sub
Private Sub PreviewReport_Click()
'Open Search Report
DoCmd.OpenReport "SearchReport", acViewPreview
'Close Search Form
DoCmd.Close acForm, "Search"
End Sub
Private Sub Search_Click()
'Update the record source
Me.SearchSubform.Form.RecordSource = "Select * From Search " & BuildFilter
'Requery the subform
Me.Form!SearchSubform.Form.Requery
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
varWhere = Null 'Main Filter
'Check for LIKE Status
If Me.Status > "" Then
    varWhere = varWhere & "[Status] LIKE '*" & Me.Status & "*' And "
End If
'Check for LIKE Last Name
If Me.LastName > "" Then
    varWhere = varWhere & "[Last Name] Like '*" & Me.LastName & "*' And "
End If
'Check for LIKE First Name
If Me.FirstName > "" Then
    varWhere = varWhere & "[First Name] LIKE '*" & Me.FirstName & "*' And "
End If
'Check for LIKE Account Number
If Me.AccountNumber > "" Then
    varWhere = varWhere & "[Account Number] LIKE '*" & Me.AccountNumber & "*' And "
End If
'Check for LIKE Social Security Number
If Me.SocialSecurityNumber > "" Then
    varWhere = varWhere & "[Social Security Number] LIKE '*" & Me.SocialSecurityNumber & "*' And "
End If
'Check for LIKE Entity Name
If Me.EntityName > "" Then
    varWhere = varWhere & "[EntityName] LIKE '*" & Me.EntityName & "*' And "
End If
'Check for LIKE EIN
If Me.EIN > "" Then
    varWhere = varWhere & "[EIN] LIKE '*" & Me.EIN & "*' And "
End If
'Check if there is a filter to return...
If IsNull(varWhere) Then
    varWhere = ""
Else
    varWhere = "WHERE" & varWhere
    
' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
    varWhere = Left(varWhere, Len(varWhere) - 5)
    End If
End If
BuildFilter = varWhere
Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim varItem As Variant
   Dim strCriteria As String
   Dim strSQL As String
   Set db = CurrentDb()
   Set qdf = db.QueryDefs("Search")
   If Me!Company.ItemsSelected.Count > 0 Then
      For Each varItem In Me!Company.ItemsSelected
         strCriteria = strCriteria & "[Account List].[Company Name] = " & Chr(34) & Me!Company.ItemData(varItem) & Chr(34) & "OR "
      Next varItem
      strCriteria = Left(strCriteria, Len(strCriteria) - 3)
   Else
      strCriteria = "[Account List].[Company Name] Like '*'"
   End If
   strSQL = "SELECT * FROM [Account List] " & "WHERE " & strCriteria & ";"
   qdf.SQL = strSQL
   Set db = Nothing
   Set qdf = Nothing
End Function

[ssanfu]

There is an example at "Rogers Access Library" on how to return Random Records. Maybe it will help you.

http://www.rogersaccesslibrary.com/forum/topic293.html