Learn how to a build a cloud-first strategyRegister Now


Is there a way to turn off query parameter prompts?

Posted on 2008-11-05
Medium Priority
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
  • 2
  • 2
  • 2
  • +2
LVL 58
ID: 22892136
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

ID: 22892221
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 58
ID: 22892291
<<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.

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

LVL 18

Expert Comment

ID: 22894351
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

Leigh Purvis earned 2000 total points
ID: 22896707
>> "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
ID: 22901680

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
ID: 22901684
...If you modify the query, just click: File-->Save As
... and overwrite the old existing query.
LVL 18

Expert Comment

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

Expert Comment

by:Leigh Purvis
ID: 22903655
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

ID: 31513766
Works Great.  Thanks.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

810 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