none
form with parameter query as recordsource/recordset RRS feed

  • Question

  • I have a form that uses a parameter query for its RecordSource. I have to define it as the RecordSource so that design time and code compile time knows the fields we are working with.

    In the form's Load (or Open; I've tried both) event, I use code something like

    Set qdf=CurrentDB.OpenQueryDefs("myParamQuery")
    qdf.Parameters("MyParam") = lngUseAsParam
    Set Me.Recordset = qdf.OpenRecordset

    to get the parameterized query results associated with the form.

    The problem I'm having is that, when I open the form from the Access UI, Access tries to query myParamQuery to satisfy the RecordSource before I get a chance, in the code, to set the parameterized Recordset. So I get unnecessary and unwanted Enter Parameter… prompts

    Is there a workaround? Is there some form event that fires BEFORE Access tries to query the RecordSource?

    Thanks in advance for sharing your expertise.


    • Edited by Dick Watson Monday, May 14, 2018 2:17 AM fix a typo in the air code
    Saturday, May 12, 2018 4:59 AM

Answers

  • I'd previously tried just removing the RecordSource once the form was all designed. This created object/member not found problems with the some of the code that made the form Caption data driven, and that passes the correct value to the code that creates the chart RowSource on-the-fly.

    Something you wrote, not quite sure what, turned on the light for me about how to fix that code problem:

    Typical WAS, needs the RowSource:

        ' fix the caption
        Me.Auto_Header0.Caption = FormCaptionLead & Me.Vehicle
       
        ' fix the chart's row source
        MkTmpTblForChart Me.VehicleID

    The NOW code, using the Recordset not the RowSource, and allows me to remove the form's RecordSource in the saved form:

        ' fix the caption
        Me.Auto_Header0.Caption = FormCaptionLead & Me.Recordset.Fields!Vehicle
       
        ' fix the chart's row source
        MkTmpTblForChart Me.Recordset.Fields!VehicleID

    Always ten ways to get there from here.

    Thanks for your help!

    Just to brag, here's what the populated form looks like. The circled data values are the only ones that are stored not produced by query and calculation within the row or across multiple rows.

    Saturday, May 12, 2018 10:56 PM

All replies

  • Is there a workaround? Is there some form event that fires BEFORE Access tries to query the RecordSource?

    Hi Dick,

    Another way could be to make a complete sql_string. including the formatting for textfields and datefields, and assign that to the RecordSource of the form.

    Imb.

    Saturday, May 12, 2018 9:25 AM
  • I don't quite follow your post.  So this suggestion is a maybe....

    Make a query that results always in a single record containing the correct criteria/parameter. (Q1)

    Then in the record source query - in it's criteria/parameter use a DLookUp("fieldname", "Q1")

    .....complete air code idea.... may not work at all......

    Saturday, May 12, 2018 1:37 PM
  • A far better approach would be to set the form to the query or table WITHOUT any paramaters.

    That way you don't need any code.

    And then in your application that needs to launch the form

    Just go:

    docmd.OpenFrom "frmMyForm",,,"InvoiceNumber = " & lngUseInvoiceNumber

    You find it is FAR better to remove the param(s) from the query, and open such forms using the "where" clause. This also means you can open + test the query and use it in "many" places.

    And you can also thus on the fly OVER time add more conditions etc., but NOT have to modfy the query.

    So you can go:

    dim strWhere   as string

    strWhere = "City = 'Edmonton' and InvoiceNumber = "  & lngUseINvoiceNumber

    docmd.OpenForm "frmMyForm",,,strWhere

    So you find that if you bind the form to the table, or the query, then you don't have to worry about some parameter issue or prompt, and you also eliminate the need to build + setup a reocrdset in the forms on-load.

    You also find this not only supports ease of development, but having the query without params means it has far more re-use in your application. Simply adopt use of the "where" clause when you open a form/report, and thus  you not only worry about parameters, but also can change or add the criteria over time without having to modify the query.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Saturday, May 12, 2018 2:52 PM
  • Thanks for all these replies.

    I was trying not to overload the OP with all the gory details of why I'm doing what I'm doing. The form in question is a dashboard-like summary of statistics over a data set (vehicle and fuel purchases (quantity, price, miles, date)) that displays these statistics by vehicle for each row (vehicle) of the RecordSource. The statistics in the RecordSource (53 columns) are the product of a whole slew of nested queries implemented as SQL subqueries or named queries. It's 13 or 14--I lost count--and probably a maximum nesting depth of 5 or 6. They are displayed on the form in several tables for last fill, X most recent fills, and life of vehicle fills, as well as in an embedded Graph that shows the 10 most recent trend of miles per gallon and miles per day moving averages. There's a *lot* of query activity under this form given the stored table data we started with.

    This all used to be done in Excel--and most of the graphic data products are still done in Excel because it is so much better at graphing than Access. It didn't scale at all well in Excel.

    The parameters are used in the most deeply nested queries to a) determine which fill-ups are the most recent [paramX] fill-ups, per vehicle, and to determine which fill-ups, per vehicle, are the [paramY] more recent fill-ups and [paramZ] less recent fill-ups than every fill-up in order to compute moving averages about each fill-up. These are then used in aggregate queries, and, in some cases, aggregate queries of the aggregate queries. The short of it is a) if I want to be able to readily change the size of the "last X fills window" or the number of fills to compute the moving averages over, parameter queries or, maybe, the VBA function() call to get the window size values are probably the only ways--short of something like modifying/saving hardcoded values in the SQL from QueryDef, b) WHERE at the top/form level doesn't work, c) generating the pseudo- RecordSource, complete with data typing and formatting, is a daunting problem and would have great maintenance complication.

    I think I'll go try the VBA function() where the PARAMETERS are today. I'm afraid of the performance impacts--and this form already takes a while to open/change as it is. There will also be impacts on the Excel side since I use DAO from Excel to extract the statistical data sets that ultimately depend on these parameter queries. (Of course, I already have the QueryDef definition of parameter values going on over there so one set of VBA or another may not matter.)

    I was really hoping there was some event I could hang the

    Set Me.Recordset = qdf.OpenRecordset

    off of that would fire before Access tried to resolve the RecordSource...

    Saturday, May 12, 2018 5:06 PM
  • Hum, if the form is un bound at load time, then setting the parms should work in the on-load event.

    I don't believe that the form will load any reocrdset if the form is un-bound. So if the form has the query set as the data source, you have to remove that in the properties sheet.

    R

    Albert

    Saturday, May 12, 2018 5:17 PM
  • I was really hoping there was some event I could hang the

    Set Me.Recordset = qdf.OpenRecordset

    off of that would fire before Access tried to resolve the RecordSource...

    Hi Dick,

    What happens if you use something like:

        my_sql = qdf.SQL
        manipulate my_sql to include the formatted Where-part
        Me.Recordsource = my_sql

    I do not use QueryDefs, but generate all kind of sql-strings dynamically depending on momentary context.

    Imb.

    Saturday, May 12, 2018 5:54 PM
  • Hum, if the form is un bound at load time, then setting the parms should work in the on-load event.

    The form is bound at design time. I suppose I could unbind it and just data-populate it from code. It's just a view-only thing. It would be a lot of effort and less maintainable.
    Saturday, May 12, 2018 9:21 PM
  • The issue with generating the SQL on-the-fly is that the RecordSource query is deeply nested with subqueries and heavily dependent on reuse--several places--of several key underlying stored queries. Flattening all this into one piece of SQL would be unmaintainable and difficult at best. Given the number of places a few key queries are used in the family of queries this evaluates to would make a huge SQL string.

    And, since Access seems to think SQL is best edited by using Windows 3.1 Notepad--, just the nesting would make it all but indecipherable. Thanks, Access Team.

    A prototype of using a me-defined VBA function GetParam(strParamName) in the query is looking like the least bad solution, overall.

    Saturday, May 12, 2018 9:28 PM
  • Not suggesting that you un bind the controls.

    What I am saying that you ONLY have to remove the "data" source of the table or query from the form in design mode - every thing else can stay as you have now. As I stated, your code should work.

    so if the forms data setting is "already" set to the query, then it make sense that the forms attempts to work with the query BEFORE you on-load code can run.

    So as long as you leave the forms property sheet blank and NOT set to that query, then no attempt to use the query should occur until your code you have actually sets the recordset.

    So this should not be any extra code at all – but just ensuring that the forms property sheet does not have a datasource setting before your code runs.

    What you have/had should work. Something else is going on here.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Saturday, May 12, 2018 9:57 PM
  • I'd previously tried just removing the RecordSource once the form was all designed. This created object/member not found problems with the some of the code that made the form Caption data driven, and that passes the correct value to the code that creates the chart RowSource on-the-fly.

    Something you wrote, not quite sure what, turned on the light for me about how to fix that code problem:

    Typical WAS, needs the RowSource:

        ' fix the caption
        Me.Auto_Header0.Caption = FormCaptionLead & Me.Vehicle
       
        ' fix the chart's row source
        MkTmpTblForChart Me.VehicleID

    The NOW code, using the Recordset not the RowSource, and allows me to remove the form's RecordSource in the saved form:

        ' fix the caption
        Me.Auto_Header0.Caption = FormCaptionLead & Me.Recordset.Fields!Vehicle
       
        ' fix the chart's row source
        MkTmpTblForChart Me.Recordset.Fields!VehicleID

    Always ten ways to get there from here.

    Thanks for your help!

    Just to brag, here's what the populated form looks like. The circled data values are the only ones that are stored not produced by query and calculation within the row or across multiple rows.

    Saturday, May 12, 2018 10:56 PM
  • Hello Dick Watson,

    I'm glad to hear that your issue has been resolved. I would suggest you mark any helpful reply or your solution to close the thread. If you have any other issue, please feel free to post thread to let us know.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 14, 2018 2:02 AM