Is there a way to turn off query parameter prompts?

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?
Who is Participating?
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
>> "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

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

T1080Author Commented:
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,
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.

Why don't you add a query without the param for the form and just use the parameter query for when you want it?
Jeffrey CoachmanMIS LiasonCommented:

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)

Jeffrey CoachmanMIS LiasonCommented:
...If you modify the query, just click: File-->Save As
... and overwrite the old existing query.
Why ask for more than you need in the first place?
Leigh PurvisDatabase DeveloperCommented:
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.
T1080Author Commented:
Works Great.  Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.