workaround for acCmdSaveAsQuery RRS feed

  • Question

  • I am willing to copy by code a query having a filter at the header to a query having filtered fields, so I use the Advanced filter capability

    DoCmd.OpenQuery MyQuery, acViewNormal
    RunCommand acCmdAdvancedFilterSort
    RunCommand acCmdSaveAsQuery

    the problem is that the last code displays a popup for the user to enter the name of the target query, but I would like to do this by code

    it appears that acCmdSaveAsQuerydoes not accept a parameter

    I tried to use [Sendkeys] but it doesn't work on a popup widows

    Any workaround?

    Saturday, October 28, 2017 9:53 AM

All replies

  • I don't think there is an easy solution. The intention of this line of code is to have the user select the name. As a long shot, maybe you can try running the query (not saving it), then inspecting the Filter property.

    A completely different approach would be where you have your own form asking for criteria, and save the corresponding SQL statement.

    -Tom. Microsoft Access MVP

    Saturday, October 28, 2017 3:06 PM
  • Thanks Tom for your feedback

    yes it seems there is no direct solution. your approach is perfect when you don't have criteria issued from a lookup field (because lookup field implies lookup table that is not present in the initial query)

    The Advanced query filter/sort capability of access manages perfectly this difficulty while translating lookup filter as SQL statement while generating the lookup table in the target query... but if I cannot make this automatic, this is worthless for me

    Saturday, October 28, 2017 4:19 PM
  • Any workaround?

    Hi Gil365,

    I do not work with QueryDefs, but generate all sql-strings dynamically.

    In your case, you could pick the SQL-property of the QueryDef, manipulate the string, and write it back. Adding a new table to the sql-string should not be a problem, as you can substitute the current table "Table1" by something like:

      Table1   -->  (Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field2)

    This feature is standard build in in all my applications.



    I now realize it is not an additional table, but an additional condition:

        sql_string + sql_string & " WHERE " & filtercondtion


    • Edited by Imb-hb Saturday, October 28, 2017 5:25 PM edit
    Saturday, October 28, 2017 4:41 PM