Referencing Filtered Data in Access 2007

[tewald]

In Access XP, I have a form with several comboboxes in the header; this
allows the form (in the data portion, of course) to imitate Excel's
AutoFilter capabilities. Now that Access 2007 has those abilities built-in,
I'd like to use them.

In my original form, as the user made choices from the comboboxes, I added
code to the Where clause of the underlying SQL code for the data portion of
the form. When the user had the dataset wanted, he/she would click a button
to export the data for use elsewhere. To do the export, I used the SQL code
that had been built. If I use Access 2007's native filtering capability, this
SQL code will not exist. How do I reference the filtered data from the form?

If this would be easier using a form and subform, please feel free to
suggest that. In either case, I'm unclear as to how to reference the filtered
data.

Thank you.

Tom Ewald, Detroit Area

 

[Allen Browne]

See if the form's filter is found in the Filter property of the form. I
would expect that to be equivalent to the WHERE clause of a SQL statement.
Test if FilterOn is true (i.e. if the filter is applied.)

You may have some difficulties with that if the filter includes values from
combos where the display column is not the bound column. The filter may
include some "Lookup_" prefixes. If you have to cope with that, try aliasing
any lookup tables in your query statement so they match.

In the end, you should be able to patch this WHERE clause into a string that
contains the other parts of your SQL statement (SELECT ... FROM ... ORDER BY
....), and assign the result to the SQL property of a QueryDef that you want
to use for TransferSpreadsheet.