Change a Criteria in a Query with VBA?

[gheyman]

Can you change the Criteria in a Query using VBA? I have a query [qry_OnTimeDelivery_MetricData]. In that Query I have a Field [MONTH0_Active].

What I want to do is from a form, if the User Clicks a Button, the Criteria for MONTH0_Active changes to Like "1"

Right now I have an unbound textbox, on a form that the query criteria is looking at. I change that with VBA and then refresh. But I know that's inefficient

 

[Micron]

Re: Access: Change a Criteria in a Query with VBA?

Not sure which it is you're after.
If a query references a form control then "changing" the control is just a matter of moving the focus off of it before running the query. No refresh required.

If you want to actually alter the query itself, then you need to set the .SQL property of the query in code before you run it. This is altering the sql of a stored query. I for one don't like to repeatedly modify objects in this manner as I believe it contributes to bloat and corruption.

Then there is building a sql statement in vba to assign to the recordsource property of a form or report, then opening that object.

 

[Joe4]

Re: Access: Change a Criteria in a Query with VBA?

If you want to actually alter the query itself, then you need to set the .SQL property of the query in code before you run it. This is altering the sql of a stored query. I for one don't like to repeatedly modify objects in this manner as I believe it contributes to bloat and corruption.

As Micron states, there are multiple ways to accomplish this. I actually use the method he mentioned above quite a bit (make a selection form to build the SQL of the query), and have never really encountered corruption issues in these databases. I guess it might contribute to "bloat" (not sure about that), but no more so than deleting data. I run a Compact and Repair on my databases regularly (which is a good maintenance idea anyway, regardless if you use this method or not), which gets rid of the bloat, so never really run into issues with that either.