Change a Criteria in a Query with VBA?

gheyman

Well-known Member
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

Well-known Member
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

MrExcel MVP, Junior Admin
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:

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top