Button on form to launch query filtered by text box on same form

[CynthiaMaine]

I am using Access 365 on Windows 11 and the database was created in the *.mdb days.

I have a query that pulls data, including the date the information was last updated. I want to be able to launch the query using a form where a user can type in the date and the query will pull only records updated after that date.

I built a form with a text box formatted as a general date, and a button that launches the query.



I know how to write the query itself so that it will pull the contents of that text box when it launches, but I want the filter to be applied by the code or macro in the button properties so that the query can also be launched by other forms for other purposes.

I'm more familiar (though very novice) with VBA than macros but Office 365 buttons use macros by default so I'm trying that.

Currently the "Where Condition" of the macro is [Query]![Field]>[TextBox] (using of course the actual names of the controls - I used the little wizard on the right end of the "Where Condition" field to generate the phrase). It launches the query, but it pops up a little box asking for input for TextBox. If I put the date in there, it does what it should. How do I get it to pull the date that I already typed into TextBox on the form?

And do I need to put something in the "Control Name" field? (which control would that refer to?)

Thank you in advance for suggestions.

[June7]

I never use macros, only VBA.

You could use a parameterized query that references textbox or use code (macro or VBA) to open filtered form or report (review http://allenbrowne.com/ser-62.html)

or VBA and QueryDefs to modify query object.

[Welshgasman]

You refer to the form control in the query.
You get asked for the textbox as Access cannot find that unless you give the correct syntax.

If the query would be used from more than one form, I tended to use a TempVar.

[Micron]

I want the filter to be applied by the code

If I interpret that correctly, you'd never be able to use one parameterized query for several forms because the form names would be different. One way could be to have no query criteria but then apply a filter to it. Not highly efficient if you end up first loading hundreds of thousands of records into a query only to filter it after that. Most other efficient methods would require code, I think. One might be to have the same unfiltered query but grab the sql from it, add the WHERE clause based on your particular form and control(s) and run that from code. Lots of ways I imagine, most of which I'd say are better than having 24 versions of the same query for basically the same thing as so many seem to be doing.

[CynthiaMaine]

Thank you June 7. Macros always seem like they should be easier but every time I try them I go back to VBA. I will study your example (very nicely annotated, by the way) and see if I can work up VBA for that button instead of the macro it set up by default. Since it seemed like I was almost there I was hoping to be able to just get the where clause right and be done but perhaps it's not to be!

[CynthiaMaine]

[QUOTE=Micron;510981]... One way could be to have no query criteria but then apply a filter to it.

That is exactly what I am trying to do, and for just the reason you mention (other forms that I might launch it for other reasons would have other names).

I think all I'm missing is the syntax to get the macro to filter the query using the value in the textbox in the form - it's the same form that the button is on. Everything but that "where condition" syntax seems to be in place and working (unless I've formatted the textbox wrong - it's currently formatted as a general date because a similar situation that I read somewhere search for a solution today suggested that as a consideration).

[Micron]

I for one wouldn't even think of attempting that with an Access macro. I don't even think it's possible to achieve what I described (or certainly not what I meant). You could probably achieve that for one form, but then you'd need a macro per form so you wouldn't accomplish the efficiency it seems you're looking for.

N.B - Excel macros are the same thing as Access procedures. Access macros are a different thing altogether. I mention that just to be certain we're on the same page.

[Welshgasman]

[QUOTE=CynthiaMaine;510983]

... One way could be to have no query criteria but then apply a filter to it.

That is exactly what I am trying to do, and for just the reason you mention (other forms that I might launch it for other reasons would have other names).

I think all I'm missing is the syntax to get the macro to filter the query using the value in the textbox in the form - it's the same form that the button is on. Everything but that "where condition" syntax seems to be in place and working (unless I've formatted the textbox wrong - it's currently formatted as a general date because a similar situation that I read somewhere search for a solution today suggested that as a consideration).

That is why I used a tempVar?
You could still use a macro if you insisted, just set the tempvar first, before opening the query.