Producing report from Keyword search

[michael.young]

I wonder if someone can help....

I've created a keyword search form to enable users to filter my DB. I have also created a number of parameter queries for users and created reports which display the results of their parameter query. Is there any way I can create a report using the results of the keyword query?

My keyword search is basically a form, containing a text box with an 'OK' button and the following code assigned to 'On click':

Private Sub OK_Click()

DoCmd.OpenForm "frmDistributionList", acNormal, "", "[Category] Like '*' & [Forms]![frmKeyword Search]![Keyword] & '*' Or [SubCategory] Like '*' & [Forms]![frmKeyword Search]![Keyword] ", acReadOnly, acNormal

DoCmd.Close acForm, "frmKeyword Search"

End Sub

When I created reports for my parameter queries, the actual query was used as the control source but I'm struggling to create a fully working report for my keyword search as there is no query to use as the control source.

One method I tried to get round this was to put the relevant code under the relevant categories. E.g. for the Category field:

Like '*' & [Forms]![frmKeyword Search]![Keyword] & '*'

This enabled me to run the report and yielded the results expected but I had to input the keyword twice, once for the parameter form and again for the query. Does anyone know of a way round this?

The code in my report is as follows:

Private Sub Report_Open(Cancel As Integer)
bInReportOpenEvent = True

DoCmd.OpenForm "frmKeyword Search(dialog)", , , , , acDialog

bInReportOpenEvent = False
End Sub

Sorry about the long winded post but any advice would be gratefully received

 

[liddlem]

Hi Michael
It sound like you're trying to do everything in a single step.
I do this quite often in a 4 step process.

Step 1 Create a form which displays all the fields that you want to filter by.

Step 2 Create a simple select query (using the query wizard) that displays ALL (unfiltered) the relevant data that is required in the report.

Step 3 Now edit the query so that the CONDITION meets your requirement.
You can right-click in the condition block and choose "build" which takes you through another wizard to build the condition or you could type in your condition as
"[Category] Like '*' & [Forms]![frmKeyword Search]![Keyword] & '*'
and if you want to include the OR condition for the SubCategory, then in the Subcategory column, BELOW the condition block type in
Like '*' & [Forms]![frmKeyword Search]![Keyword]

To test your query - the "frmKeyword Search" form must be open with criteria selected.

Step 4 - Add a button to the form and follow the prompts which will write the code that opens the report for you.