Is there a way to turn off query parameter prompts?

Posted on 2008-11-05
Last Modified: 2013-11-29
I would like to turn off query parameter prompts.  I use a form to filter my query, but in many cases I open the query and run it without the form, and I do not want the query to ask for the prompts.  What are my options?
Question by:T1080
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    None really.  Anything Access can't resolve will prompt you for a parameter.

    You can supply parameters in code even if a form is not open, but not when you run it manually.


    Author Comment

    Is there a way to code the parameter so it will only prompt when the form is loaded? or is there a way to code the parameter when it can't find the form value it uses null as opposed to the form value?
    Thank you,
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<Is there a way to code the parameter so it will only prompt when the form is loaded? >>

      If the form is open, it won't prompt for the parameter.

    <<or is there a way to code the parameter when it can't find the form value it uses null as opposed to the form value?>>

      In code, you can loop through the parameters collection and do anything you want to supply a value for the parameter.  When executing a query via code, Access does not try and resolve parameter values on its own.  Any you don't supply a value for will toss an error.

      But when opening a query manually, Access will automatically prompt for any unresolved reference and you can't turn that off.

    LVL 18

    Expert Comment

    Why don't you add a query without the param for the form and just use the parameter query for when you want it?
    LVL 44

    Accepted Solution

    >> "is there a way to code the parameter when it can't find the form value it uses null as opposed to the form value?"
    You'd need to switch to using a function as your parameter value in this instance.
    The function could accept a Form expression passed as parameters or a string formated expression (but *not* as an actual form expression of you'll encounter exactly the same behaviour as the expression service attempts to resolve it for you!)
    For example - a simplistic example originally posted elsewhere.
    Your query can reference the function passing the form name and control as arguments.
    SELECT * FROM TableName WHERE FieldName = fOptionalParam("MyForm", "MyControl")
    I'll voice the caveat this time though lol.
    If Null is returned - that will see no results returned (as your field value can never equal Null).
    That's how I'd want to see it work anyway personally - but just so you know.

    Function fOptionalParam(strForm As String, strControl As String) As Variant 
    On Error GoTo errHere 
        Const cDefault = Null 'Default to return if form value not valid 
        fOptionalParam = cDefault 
        If CurrentProject.AllForms(strForm).IsLoaded Then 
            fOptionalParam = Forms(strForm).Controls(strControl).Value 
        End If 
        Exit Function 
        If Err = 2467 Then 
            'Invalid form reference - ignore ?
            MsgBox "Uh oh - Error " & Err.Description 
        End If 
    End Function 

    Open in new window

    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Just curious...

    If the query does not change often, then why not just have two queries?
    (One for the form and one to run alone)

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    ...If you modify the query, just click: File-->Save As
    ... and overwrite the old existing query.
    LVL 18

    Expert Comment

    Why ask for more than you need in the first place?
    LVL 44

    Expert Comment

    by:Leigh Purvis
    There can be value in using a single data object (e.g. a query here) for more than one purpose.

    Not everyone holds to that - some prefer to fill their DB window with objects for individual purposes.
    Which can make application maintenance more effort.
    The flip side of that is if you try to make a single object do too many things then your maintainance can also become more difficult without an on-the-ball developer or good documentation (wassat?? lol).
    I'm certainly a fan of "core" queries.  e.g. a query that is used as a core source for other objects, queries, forms SQL statements etc - where you might have performed a calculation in the core query or joined data.  
    It's helping to isolate the logic of your application.
    I wouldn't necessarily advocate a core query to have a form expression parameter though.
    As for asking for more than needed - does that refer to the question here, or from a data perpective?
    (If the question then ignore the rest - that doesn't affect anything I have to say).
    From a data perspective asking for more is a bad idea.  So IMO any criteria which refers to a form expression but returns Null otherwise (a good default) should mean the query returns no data if the form isn't open.
    To have it behave otherwise and return all is indeed asking for more than necessary.
    The default behaviour of the method I offered above is (as mentioned) to return none - as a operator comparison (=) against Null will never be successful.

    Author Closing Comment

    Works Great.  Thanks.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Aggregate Function error 7 25
    calculated field in a table? 3 20
    message box formulas 3 17
    SQL syntax error in VBA 11 21
    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now