Change a Criteria in a Query with VBA?

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top