Create report based on an sql statement

[martinot]

I've created a multicriteria search form for a contact management database. Now I would like to have a button on my form that would create a report based on the results of the search (lstresults).
How can I do that ?

Thanks in advance for your help

Here is the code I'm using
Dim SQL As String
Dim SQLWhere As String
SQL = &quot;SELECT OrganisationID, ContactID, FirstName, LastName, EmailName, Title, Board, Communications, Executive, Expert FROM Contacts Where Contacts!ContactID <> 0 &quot;
If Not Me.chkExp Then
SQL = SQL & &quot;And Contacts!Expert like '*&quot; & Me.txtSrchExp & &quot;*' &quot;
End If
If Not Me.chkexec Then
SQL = SQL & &quot;And Contacts!Executive like '*&quot; & Me.txtSrchExec & &quot;*' &quot;
End If
If Not Me.ChkTech Then
SQL = SQL & &quot;And Contacts!Technical like '*&quot; & Me.txtSrchTech & &quot;*' &quot;
End If
If Not Me.chkCom Then
SQL = SQL & &quot;And Contacts!Communications like '*&quot; & Me.TxtSrchCom & &quot;*' &quot;
End If
If Not Me.chkRegul Then
SQL = SQL & &quot;And Contacts!RegulAff like '*&quot; & Me.txtSrchRegul & &quot;*' &quot;
End If
If Not Me.chkPetc Then
SQL = SQL & &quot;And Contacts!Petcore like '*&quot; & Me.txtSrchPetc & &quot;*' &quot;
End If
If Not Me.chkBoard Then
SQL = SQL & &quot;And Contacts!Board like '*&quot; & Me.TxtSrchBoard & &quot;*' &quot;
End If

SQLWhere = Trim(Right(SQL, Len(SQL) - InStr(SQL, &quot;Where &quot - Len(&quot;Where &quot + 1))

SQL = SQL & &quot;;&quot;

Me.lblStats.Caption = DCount(&quot;*&quot;, &quot;Contacts&quot;, SQLWhere) & &quot; / &quot; & DCount(&quot;*&quot;, &quot;Contacts&quot
Me.lstResults.RowSource = SQL
Me.lstResults.Requery

End Sub

 

[Mike555]

Try looking at jfgambit's database in this thread: thread702-563930

His database uses a query to perform the search. Search words entered onto the Search form are actually passed directly to the query and stay in the query until the next time the search is performed.

You could setup your search form like this one, and then add a button that runs a report based off of the query.

Hope that helps.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?