workaround for acCmdSaveAsQuery

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?

Answer 1

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

Answer 2

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

Answer 3

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.

Imb.

Edit:

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

    sql_string + sql_string & " WHERE " & filtercondtion

Imb.