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.
Code:
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