locked
Using the same "parameter" query with multiple forms in Access RRS feed

  • Question

  • Hi! I have a working Form that has two text boxes for user input. Also on the form are 15 check-boxes for the user to choose which of 20 queries (some check-boxes cause two queries to be run) to run.  The values in the two text boxes are input parameters for each of the queries.  When the "Run Queries" button is pushed on the form, a cascading set of Query output windows appears.

    I run each of the queries using DoCmd.OpenQuery.  Inside the queries I have the WHERE clause parameters coded like "[Forms]![ShowDataForForm]![txtBox1]".

    This has been working fine.

    Now comes the problem: I want to call these same queries from a different Form.  However, the name of the Form is built into the Query, so when I run the query from a different Form it no longer has a value (because the original Form is closed).

    Is there some way I can have the query use parameters that do not contain the name of the Form?  Can I still use "DoCmd.OpenQuery" to get the query results window to appear?  (I have tried some things with dB.QueryDefs, and can get to "OpenRecordset" but I have no clue for how to get the Recordset from a SELECT query to appear in a window, such as occurs with DoCmd.OpenQuery.)

    I hope I don't have to use the "brute force" solution of simply copying each of the queries, renaming the copies, and replacing in each WHERE clause the old Form name with the new Form name so that the new copies of the queries work with the new Form.

    Any help is appreciated.  Thanks in advance.

    Dan


    Dan

    Wednesday, April 1, 2015 3:30 PM

Answers

  • Another way of dealing with this issue (for Access 2007 and later) is to use TempVars Collection. Before execution of DoCmd.OpenQuery you assign this value to a corresponding TempVar. You also use this TempVar as a query criteria.

    For more information check: https://msdn.microsoft.com/en-us/library/bb243659(v=office.12).aspx


    Andrey V Artemyev | Saint-Petersburg, Russia

    • Marked as answer by DanR1 Monday, April 13, 2015 2:47 PM
    Thursday, April 2, 2015 11:47 AM
  • Are you sure you're not getting

    [TempVars]![tmp1]

    mixed up with

    [TempVars].[tmp1]?  

    Note the difference between "." and "!". Getting these mixed up will result in the behavior you describe.  You'll need to use [TempVars]![tmp1] (use the "!" operator) for your query to work correctly.

    -Bruce


    • Edited by Bruce Hulsey Monday, April 6, 2015 9:17 PM
    • Marked as answer by DanR1 Monday, April 13, 2015 2:47 PM
    Monday, April 6, 2015 9:16 PM
  • The only syntax that will work as a parameter for a query is TempVars!ItemName, using the "!".

    There are alternative ways you can express that in VBA as noted in the article you cited but not in the SQL of a query (and TempVars.ItemName using "." is never correct in either case).  

    As stated before, you'll need to use  the "!" delimiter for your query to work correctly.

    -Bruce



    • Edited by Bruce Hulsey Monday, April 6, 2015 11:07 PM
    • Proposed as answer by L.Hl Monday, April 13, 2015 10:33 AM
    • Marked as answer by DanR1 Monday, April 13, 2015 2:48 PM
    Monday, April 6, 2015 11:05 PM

All replies

  • I run each of the queries using DoCmd.OpenQuery.  Inside the queries I have the WHERE clause parameters coded like "[Forms]![ShowDataForForm]![txtBox1]".

    Hi Dan,

    You can extract the form dependent information out of the query, and replace it by a different name, e.g. [txtBox1].

    Just prior you run the query you substitute the parameter [txtBox1] with the appropriate data on the moment, so in case of the ShowDataForForm form with Forms!ShowDataForForm!txtBox1, or with the control of the other form.

    Imb.

    Wednesday, April 1, 2015 3:46 PM
  • I have no idea what the Visual Basic changes are to accomplish what you are saying.  Would you mind providing some example, that ends with DoCmd.OpenQuery "QueryName", acViewNormal, acEdit?

    Everything I have read about "DoCmd.OpenQuery" indicates there is no way to pass Query parameters to the Query, so I am quite pleased to learn that you have a solution!


    Dan

    Wednesday, April 1, 2015 4:22 PM
  • If you set the query parameters to the style   Screen.ActiveForm.textboxname  then the query will refer to the currently active form's text boxes.  Just make sure all the forms use the same names for the parameter text boxes.
    Wednesday, April 1, 2015 5:48 PM
  • I have no idea what the Visual Basic changes are to accomplish what you are saying.  Would you mind providing some example, that ends with DoCmd.OpenQuery "QueryName", acViewNormal, acEdit?

    Hi Dan,

    I am not too familiar with the use of Parameters in a QueryDef. But in this forum there are quite a few people that are really specialist on this topic. I hope they jump in to give you a clear answer.

    Imb.

    Wednesday, April 1, 2015 5:54 PM
  • Marshall,

    That works fine for the first query that runs -- but subsequent queries that are run "because their check-box is checked" seem to return all rows from the associated table, instead of using the WHERE clause.

    Might the "DoCmd.OpenQuery" change the definition of "Screen.ActiveForm"?

    Dan


    Dan

    Wednesday, April 1, 2015 7:03 PM
  • No. The Active form is the form with the focus, which is where your check boxes and the button are located.

    What is the WHERE clause and how is it applied?

    The only thing I can think of is the OpenQuery doesn't open a new query window if it's already open.  Have you tried closing the query window before clicking the button?


    Maybe you should try using a datasheet form with the query as its record source instead of just opening the query.  AFAIK, the only issue here is that the datasheet form would need to be opened if it's not already open and requeried if it was opened earlier.  You can use code like this to do that:

    If CurrentProject.AllForms!nameofdatasheetform.IsLoaded Then
       Forms!nameofdatasheetform.Requery
    Else 
       DoCmd.OpenForm "nameofdatasheetform"
    End If 


    Wednesday, April 1, 2015 9:50 PM
  • Another way of dealing with this issue (for Access 2007 and later) is to use TempVars Collection. Before execution of DoCmd.OpenQuery you assign this value to a corresponding TempVar. You also use this TempVar as a query criteria.

    For more information check: https://msdn.microsoft.com/en-us/library/bb243659(v=office.12).aspx


    Andrey V Artemyev | Saint-Petersburg, Russia

    • Marked as answer by DanR1 Monday, April 13, 2015 2:47 PM
    Thursday, April 2, 2015 11:47 AM
  • Marshall,

    Thanks for your suggestions.  Working with you has me thinking, "Why not simply put the values into a single-row table, and use the table within the queries to provide the user input to the queries?"  This would have an added advantage of "persistence" if the user of the form might want to re-run with the values in place when the form was last used.

    As for your questions, the WHERE clause is in the Criteria portion of each Query, in the following manner

    Like [Screen].[ActiveForm].[txtBox1] & '*'

    By checking/unchecking the various check boxes, I see that this "[Screen].[ActiveForm].[txtBox1]" method works within each query, if I run a single query (have a single check-box checked).  Whenever I have multiple boxes checked, only the first query uses the "Like" condition; all subsequent queries return the entire table (can be a few hundred thousand rows, instead of maybe 10 rows).


    Dan

    Thursday, April 2, 2015 2:02 PM
  • Andrey,

    Thanks for your suggestion.  I will read more about the TempVar Collection.  I think I will also consider placing the user input into a single-row table, and then use that table in the numerous Queries.  These Forum posts have helped me to think of various solutions, ones that I had not considered before.


    Dan

    Thursday, April 2, 2015 2:05 PM
  • Andrey,

    I am trying to use TempVar (because when I tried to use the "single row table" solutions my queries were very slow to run).  (Most of the queries are using "LIKE" in the WHERE clause, and are linked to tables with 100,000 rows, or so, on SQL Server 2008.)

    I can use the immediate window to see that "TempVars!strBox" has the value entered in the text box on the Form.

    When the Query attempts to run, I get, "Run-time error '3085': Undefined function 'TempVars.Item' in expression."  I have also tried other ways of referencing the TempVars!strBox value, such as, Like [TempVars].[strBox] & '*' .  This is the text in my "Criteria" section in Query Design View.  With this syntax, when the Query runs I get an "Enter Parameter Value" window, asking for TempVars.strBox.

    Why does the Query ask for this value if it is defined in TempVars via a TempVars.Add statement and the value can be viewed in the Immediate Window?  I seem to be missing something.  Your help is appreciated.


    Dan

    Thursday, April 2, 2015 5:37 PM
  • That's likely(?) a problem in how the query's WHERE clause was constructed.  Post back with a Copy/Paste of your query's WHERE clause in SQL view.  Also explain what you want the query to return when multiple check boxes are checked (records that march any one of the check boxes or only records that match all of the check boxes).
    Thursday, April 2, 2015 11:19 PM
  • Marshall,

    Each check box controls whether or not an additional query (or sometimes two additional queries) are run.  So when I push the "run queries" button on the form, I may get as many as 20 cascading OpenQuery output windows, each with a different type of information related to the user input.  (The whole purpose of the Form is to save the user the trouble of having to enter the same information in 20 queries to see a wide variety of data relevant to the organization of interest.)

    Here is a SQL View of a sample Query:

    SELECT Table1.Org_ID, Table1.FY, Table1.As_Of_Date, Table1.Address, Table1.City, Table1.State, Table1.Zip, Table1.Org_Name
    FROM Table1
    WHERE (((Table1.Org_ID) Like [Forms]![ShowDataForOrg]![txtBox1] & '*') AND ((Table1.FY) Like '*' & [Forms]![ShowDataForOrg]![txtFY]))
    ORDER BY Table1.Org_ID, Table1.FY;

    The txtBox1 contains an alphanumeric string "ID" for the organization, e.g., "ABCD".  The txtFY contains the last digit of a 4-character Fiscal Year stored as a string (no reason to have the user enter more than a single digit, since the data never include more than a few consecutive years).

    So one check box could be to see information concerning where the organization is located; another check box could be used to see whether it is a non-profit or a for-profit organization; another check box could be to see what its annual income has been for the past 20 years; another check box could be to see the names of all the people employed by the organization; one check box could be to see what items were ordered from what suppliers by the organization during the FY of interest; etc.  The user can check/uncheck the boxes to see only the types of information of interest at the moment.  Generally each set of information is coming from different tables, needing different queries and different output windows.  (Please note that these sample types of data are fictional and purely for the sake of discussion; they do not represent the type of data available to me.)

    Press the "Run Queries" button and a set of cascading windows shows the various types of output requested according to the check boxes.

    I hope that helps.

    When I used your "Screen.ActiveForm" approach, only the first query (of the set of 20) used the WHERE clause (similar to the one shown above).  All other queries functioned as if there was no WHERE clause -- so each returned the entire data set, sometimes a few million rows, even though I only wanted a few hundred rows (where the WHERE clause, if functioning, would have limited the data returned).

    When I run only a single query (because only a single box is checked) the "Screen.ActiveForm" approach works fine for the individual query.  Only when I run multiple queries (because multiple boxes are checked, which is the usual operation of the form -- with 10-12 boxes typically checked, and even set as checked by default) do I run into problems, where only the first query of the set (whichever it happens to be) uses the WHERE condition.


    Dan

    Friday, April 3, 2015 3:01 PM
  • Oh boy!  That is not what I thought you were doing.  Like I said earlier, your problem is that the form is no longer active after the first query is opened.

    That means you can not use Screen.ActiveForm.  Instead you need to use either a specific hidden form with a control for each parameter.  Then, add a line of code to the AfterUpdate event of each parameter control in the user's form to copy the the control's value to the corresponding control on the hidden form.  The parameters would then look like:
       Forms("name of hidden form").txtbox1

    Alternatively, you could create and a tempvar for each parameter to the TempVars collection as Andrey suggested.  You still need to copy each value from the user's form to its corresponding tempvar and change the parameters to something like:
       TempVars!tempvarname

    Maybe I'm just stuck in my ways, but I think it would be easier to create a simple (users would never see it) form for this than it would be to manage a collection.

    Friday, April 3, 2015 7:28 PM
  • Thanks for your help.  I tried using TempVars but found that they don't work in a "Like" clause the same way that [Forms]![ShowDataForForm]![txtBox1] works.  I found some discussion about this on the Internet.

    So I've gone to the "brute force" approach, and simply made a new copy of each query that needs to run with the new form, and changed the Form name in the references in all relevant "Like" clauses.  I'm done with that -- it took a total of 15-20 minutes of effort, far less than I've spent trying to research an alternative.

    I appreciate your suggestions and those of others.

    Thanks again.


    Dan

    Friday, April 3, 2015 7:41 PM
  • So I've gone to the "brute force" approach, and simply made a new copy of each query that needs to run with the new form, and changed the Form name in the references in all relevant "Like" clauses.  I'm done with that -- it took a total of 15-20 minutes of effort, far less than I've spent trying to research an alternative.

    Hi Dan,

    I am very sorry to hear that you had to use the brute force.

    Your problem though is still puzzling me. I can simply not understand why you need so many QueryDefs, to do about the same thing! I have now running more than 70 applications (from simple to complex), and I never use a QueryDef. I think there must be a mismatch between the concept that you have in your mind and the concept that we have in our minds. Personally I do not understand why you need as many as 20 cascading OpenQuery windows. What is the function of them in your application?

    Special for you I created a QueryDef "Test" ("SELECT * FROM Test_tbl"), and ran DoCmd.OpenQuery "Test". It was not too difficult modify it so that only one record was selected instead of all records, or to see a completely different selection.

    If you work with web apps, forget all the above.

    Imb.

    Friday, April 3, 2015 10:03 PM
  • As you are finding out, putting forms! expression in queries can get really messy real fast.

    Even worse, is now that the query is now "married" and attached to that ONE form. Often, I have a nice query that I could use MANY times for different reports, and often even that same query could be used for reports...but then someone comes along and puts in a expression that means the query is ONLY good when that form is opened.

    Worse, is very hard to control things like having 5 combo boxes, but the user only selects restrictions in 3 of the combo boxes...and wants the other 2 to be ignore.

    I could probably write another 10 or pages as to why putting forms expressions in queries is bad (besides...it makes the queries real ugly, and hard to read. and, the sql then is not standard anymore (it will not work with server based systems either).

    So, the solution use now is simply to take the values from the form, and build your own where clause in code. That way, you simply design the reports (or forms), and attached them to the query, BUT NO FORMS! conditions are placed in the query.

    To "send" the conditions to the report (or form), you simply use the "where" clause. This is exactly why ms-access has this feature…and it solves a zillion problems…and will reduce your development costs by a substantial amount.

    Take a look at the following screen shots to see what I mean:

    http://www.kallal.ca/ridesrpt/ridesrpt.html

    The code to make those above screens work and launch the report with the selected restrictions when you hit the "print" button is easy:


    dim   strWhere       as string
    
     select sales rep combo
    
    if isnull(cboSalesRep) = false then
    
       strWhere = "SalesRep = " & cboSalesRep & ""
    
    end if
    
     select what City for the report
    
    if isnull(cboCity) = false then
       if strWhere <> "" then
          strWhere = strWhere " and "
       endif
       strWhere = strWhere & "City = " & cobCity & ""
    end if
    

    Note how the 2nd combo test is setup. You can add as "many" more conditions you want. Lets say we have a check box to only include Special Customers. We can add to our very nice prompt screen a check box to

    [x] Show Only Special customers

    The code we add would be:

    if chkSpeicalOnly = True then
       if strWhere <> "" then
          strWhere = strWhere " and "
       endif
       strWhere = strWhere & "SpecialCust  =  true"
    endif
    

    For sure, each combo and control we add to the nice report screen takes a bit of code, but no more messy then the query builder..and this way, each query is nice and clean, and free of a bunch of HIGHLY un-maintainable forms! expressions.

    Further, it means you can re-use the same query for different reports, and have no worries about some form that is supposed to be open. So, a tiny bit more code eliminates the messy query problem.. For me, this is very worth while trade.

    You also don’t mention what the run quires button does. However, you simply NEVER EVER want to open a query or table directly in your application. Build a set of reports or continues forms to display that data. This also allows printing or emailing of such reports.

    So on your neat-o filter form, you could have the user enter/set the values you want to filter on. You then have that code create a “where” clause. Once you have that, then the SAME where clause can open any report or form based on this where clause.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Saturday, April 4, 2015 7:43 PM
  • Hi Dan,

    sorry for delay, I've lost in the real life for the weekend.

    Your words about TempVars and a query criteria sound strange.

    I've just created an Access file, linked 1 table (it's a standard UserInfo table from a SharePoint site), then opened Immediate window and ran:

    TempVars.Add "tmp1", "Artemyev"

    Pushed Enter and went to the query editor. The query I added:

    SELECT UserInfo.*
    FROM UserInfo
    WHERE (((UserInfo.Name) Like [TempVars]![tmp1] & '*'));
    It returned 4 users, including myself, with the same surname as mine.


    Andrey V Artemyev | Saint-Petersburg, Russia


    Monday, April 6, 2015 12:23 PM
  • Andrey,

    I'm confused as to why you get such operation and I do not.  For me the Query seems not to know where to get the value for "[TempVars]![tmp1]" since when the query runs (from the Form, and when I can pause VBA with Debug to see that "[TempVars]![tmp1]" has the correct content) I get a window to supply the value for "[TempVars]![tmp1]".

    I will have to investigate this unexpected behavior at some future time.

    Might there be some security settings that must be made so that TempVars values are available to queries?


    Dan

    Monday, April 6, 2015 2:47 PM
  • I'm sorry -- maybe I wrote something that was misunderstood.  I do not use QueryDef.  I generally create my Access queries using the Query Design tool.  Occasionally the queries are quite complex.

    My 20 cascading OpenQuery windows provide as much information as possible about an organization in order to support the people staffing a "Help Desk".  Once they know the identifier of the organization that is the subject of the user-support call, they can enter that identifier into the Form and receive information from 20 different data tables, information that can aid their response to the user-support call.


    Dan

    Monday, April 6, 2015 2:52 PM
  • Mr. Kallal,

    I appreciate your concerns.  In the environment where this Form is being used, the queries in which I have "hard-coded" the Form name are unlikely to be needed for additional reuse in the next 10-20 years, so I don't feel I have created a maintenance nightmare by using such "hard-coded" Form names.

    The whole purpose of the Form is to allow the user to enter the Organization ID and Fiscal Year only once, and get many windows of various types of information about the Organization -- as needed to support the staff of a "help desk."  Prior to my creating the Form, a Help Desk user (which sometimes meant "me" as a problem was escalated to my attention) would have to hunt among many different queries to find the ones of interest, and then run them -- when each Query would require the input of the same two parameters (Organization ID and Fiscal Year).

    With the Form, the user needs only to enter the Organization ID and Fiscal Year on the form, choose the check-boxes for all the types of information desired, and press the "Run Queries" button.  Then the user is presented with a wide variety of information concerning the organization of interest.  A generally adequate set of check-boxes is pre-selected "by default."


    Dan

    Monday, April 6, 2015 3:02 PM
  • No one is suggesting to remove or dump the use of some prompt form. The prompt form is a great approach. The REAL issue however is those hard coded forms! expression in the quires.


    As you stated:

    >> I hope I don't have to use the "brute force" solution of simply copying each of the queries, renaming the copies, and replacing in each WHERE clause the old Form name with the new Form name so that the new copies of the queries work with the new Form.

    And the answer is you don’t. Now you have two sets of quires. So quite much the reason.

    >> Is there some way I can have the query use parameters that do not contain the name of the Form? 


    That is exactly what I am suggesting. If hard coding forms! references was not an issue then your question here would not exist!

    The simple issue is at the end of the day you now have two sets of quires to maintain, and two forms to maintain.

    The issue is thus how to setup the quires and remove the forms references.

    As my post notes, this will require some additional code, but the payoff is greater then that extra effort. The only “difficult” part is if any of the existing quires are based on other quires that require the same parameters.

    So using a prompt form is still recommend, but the longer term goal should be to remove the marriage of that form + queries to thus allow use of such quires without a particular form open.

    And  as noted, you likely want to use some kind of report for the results. Allowing users to see/use the tables (or query results) directly is generally considered a bad practice, and will often allow users to edit data (by accident or on purpose).

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Monday, April 6, 2015 5:26 PM
  • Mr. Kallal,

    Thank you for your concerns. I rely on Windows authentication, and the read-only access rights to linked SQL Server tables to prevent users from editing data returned by the multiple Access queries.

    I may try to get the TempVars solution to work.  For some reason Andrey can get this to work on his PC, but I cannot seem to get it to work in my environment.  I can see the value for my TempVars variables in my Immediate Window of the VBA debugger, but the Query doesn't seem to know what they are when the OpenQuery action is performed -- so that OpenQuery action gives me a window in which I must enter the value of each parameter, for each query -- which is what I wished to avoid by creating the Form!

    C'est la vie.


    Dan

    Monday, April 6, 2015 8:50 PM
  • Are you sure you're not getting

    [TempVars]![tmp1]

    mixed up with

    [TempVars].[tmp1]?  

    Note the difference between "." and "!". Getting these mixed up will result in the behavior you describe.  You'll need to use [TempVars]![tmp1] (use the "!" operator) for your query to work correctly.

    -Bruce


    • Edited by Bruce Hulsey Monday, April 6, 2015 9:17 PM
    • Marked as answer by DanR1 Monday, April 13, 2015 2:47 PM
    Monday, April 6, 2015 9:16 PM
  • Bruce,

    I'll have to check, but maybe you've identified the problem.  I was under the impression that lots of syntax choices are possible -- and I fail to appreciate the difference between the exclamation point "divider" and the period "divider."

    This article led me to believe that many syntax choices were possible, and I tried a few before deciding that TempVars was not going to work for me.

    http://www.utteraccess.com/wiki/index.php/TempVars

    Maybe tomorrow I'll give TempVars another try!

    Thanks.


    Dan

    Monday, April 6, 2015 10:11 PM
  • The only syntax that will work as a parameter for a query is TempVars!ItemName, using the "!".

    There are alternative ways you can express that in VBA as noted in the article you cited but not in the SQL of a query (and TempVars.ItemName using "." is never correct in either case).  

    As stated before, you'll need to use  the "!" delimiter for your query to work correctly.

    -Bruce



    • Edited by Bruce Hulsey Monday, April 6, 2015 11:07 PM
    • Proposed as answer by L.Hl Monday, April 13, 2015 10:33 AM
    • Marked as answer by DanR1 Monday, April 13, 2015 2:48 PM
    Monday, April 6, 2015 11:05 PM
  • Bruce,

    With this revised syntax it is working perfectly!  Thank you for realizing that I may have been using the wrong syntax in my Query expressions.


    Dan

    Tuesday, April 7, 2015 3:06 PM