Results 1 to 3 of 3

Thread: Change a Criteria in a Query with VBA?

  1. #1
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,665
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Change a Criteria in a Query with VBA?

    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
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  2. #2
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,727
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,065
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default 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 by Joe4; Sep 25th, 2019 at 11:56 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •