Report-Macro-Apply Filter

[TDC21]

I have a report that I am creating, I have added a text box and a Command button. I am trying to use the text box as a search tool and the command button to execute the search.

For the Command, I have created a Event - On Click - Macro - Apply Filter

[TRACE_ID] Like "*" & [Reports]![BALANCE_QUERY]![Text36] & "*"

Trace ID is the field I am searching, its in the current report, built from the balance query, and its being compared to the text box "Text36"

I am piecing this together from some U-Tube videos so I may be way off but dose anything stand out as wrong here? I am trying to have any "Like" text entered into the text box return all "like" results in the report, text "3W" would return records for 3W548, 3W597, 3WX1R, and so on.


Thanks everyone

 

[Micron]

You have the textbox and button on a report??I have never tried (nor would I) to do that. If that's the case, I know that in vba, when you create a filter, you have to apply it with the directive .FilterOn = True. I don't use macros, so I have to guess that the same is true for macros and you'd want to check if there is a command to do that.

 

[TDC21]

Thanks Micron,

I think I see what you are talking about, I scrapped the previous plan, created a lookup form, and then added criteria to my query to reference the form. This is working quite well but there is one issue I still have and I have not found anything online to guide my efforts.

In my search form I have a command to open query, the query opens just fine and applies the filter criteria entered however if I try to perform more than one search, I have to either close the query prior to using the run query command on my form, or I have to refresh the query once the command "opens" the query.

Is there a way to add multiple functionality to a command, Run Query Or if Query is Open, refresh Query?

 

[Micron]

Are we talking macros here or not? Your post title suggest yes, but when I said I don't do macros, you didn't say which you're using, vba or macros.
If a macro, I'd try closing the query at the first step. AFAIK, that won't create an error if it's not open anyway.
If vba, I'd test if the query is open, and if so, close it, then run it. To test if open:

If CurrentData.AllQueries("qryNameOfYourQuery").IsLoaded Then
   DoCmd.Close acQuery, "qryNameOfYourQuery", acSaveNo  'I believe the default if not specified, is to prompt to save changes
End If