Create Report from Search Criteria

[cbrsix]

Hi,

I have a search form and I want to have the results go into a report (whatever they may be). The search form has a subform in it where it shows the criteria that matches the search. I want that information to go into my report that I am creating. I have included the code for my search form below. There will be a button that will have on the form that when clicked should show the results just as the subform does, but as a separate report. I can't figure out how to incorporate the BuildFilter into the report click similar to how it is done for the Search_Click().



Any suggestions?

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

[June7]

Possibly store the value of strCriteria into a textbox (I do that) or a global variable. Then:
DoCmd.OpenReport "report name", , , Me.textboxname [or the variable]

[cbrsix]

The strCriteria is from a mulit-select extended listbox. If I store the values into a textbox, and I have multiple items selected, how will that work? I understand the concept if it didn't allow multi-select.

[June7]

You have a procedure that builds a string that is used in the WHERE argument of an SQL statement. Save that string to a textbox on the form and the string is available for use in other procedures, such as button to open report.

You are using the string to modify a query definition - why? What is that query used for? I don't modify query definition just to get a filtered form or report. I use the WHERE CONDITION argument of OpenForm/OpenReport method to pass filter criteria.

[cbrsix]

You are using the string to modify a query definition - why? What is that query used for? I don't modify query definition just to get a filtered form or report. I use the WHERE CONDITION argument of OpenForm/OpenReport method to pass filter criteria.

Please see this thread..https://www.accessforums.net/forms/s...orm-28370.html

[June7]

Not sure how that thread relates to the question posed in this one.

You want to know how to use the constructed criteria string to filter a report? I think I have addressed that requirement.

I still don't know why you are modifying query definition.

[cbrsix]

I included that thread because that is where I received help/guidance on how to use a multi-select listbox to filter my search form. In one of the links provided, it used the format for the query. That is why I have that in there. That query has no other function (as of now).

[June7]

Okay, so the procedure to build criteria string works? Want to use the string to filter report? Has my suggestion helped?

Either use the string in the OpenReport method or use the query that you are modifying as the RecordSource for report. As I said, I don't do the latter.

[cbrsix]

I haven't had a chance to give it a try yet, but will do tomorrow. Is one way more efficient than the other?

[June7]

Probably just a matter of preference. I prefer not to alter saved Access queries. And in fact, most of my forms and reports don't have an Access query as RecordSource - I often build the SQL statement directly in the RecordSource property.