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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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,
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.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Why don't you add a query without the param for the form and just use the parameter query for when you want it?
Leigh PurvisDatabase 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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.