Re: Filtering Via Form, and then Export

well...I may have solved my problem. Basically, I just converted they query
the detail rows were bound to, to a make table action query, and have indexes
that, using the filter set up you have provide, eliminates the whole query
process. So, it brings up the 200K rows "instantly" on the initial form
load, thus eliminating any concern I had about the slowness of loading the
form.

I just need to write the code to purge, and then reload the table each month
(or add only new records...still ponder which. But the filter form works
GREAT (instantly) every time I change the filter. Working on the export
now...thanks Allen!

Patk

"PatK" wrote:

Sorry...one possibly important clarification: My "detail" data (that which I
am not asking to be presented initially, "IS" bound to a query, but my
presumption was since we set the filter parameter to false, that it would not
execute the query. Maybe that is the problem?

"PatK" wrote:

Allen: Could you tell me where I would add this code? I added it to the form
itself (where I have the cmdClick routines, etc), as well as in the routine
where the filter where string is created. But now, it seems to be doing two
queries at open, instead of one, taking twice as long.

Maybe I am not understanding the filter process at all. When I open my form,
I see a "running query" status bar pop up in the low left corner of Access.
I presume this is not running a query and I have none bound to the form.
Rather, that the "filter" itself is the query it is executing, and if so, and
I am not asking for any data returned, why is it now executing twice, instead
of once, since I added the code you mentioned below (thus, my thinking I
stuck it in the wrong place). It is true that since I added the code, I do
not, in fact, get any detail rows (great), but then, what the heck are the
queries doing. My intent was to pop open the form, allow the user to set
their filter parameters, and then execute the filter and present the data.
Also interestingly, somehow, my filter only seems to work once in a row, now.

Sorry...and again, thanks for any help you may provide.

for the time being, I will revert my code back, until I figure out the issue.

Cheers!

patk

"Allen Browne" wrote:

This will cause the form to load without any records:

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

A WHERE clause (or filter) is ultimately an expression that evaluates to
True or False for each record. The expression above evaluates to False for
all records (since False is always False), so no records load.

If your source query is read-only, or you don't allow new records, you will
find that the Detail section of your form goes completely blank. If that's a
problem, see:
http://allenbrowne.com/casu-20.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"PatK" <PatK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:20AAACBF-3138-4EAA-A338-325777BB5C4D@xxxxxxxxxxxxxxxx
I think I have the filter form working ok (THANKS!). Question: Once I
bound
my query to the form, it wants to populate the detail section of the form
immediately, at first opening of the form, before I have chosen any
filters.
Any way to stop this behavior? I want it to wait to always require filter
choices, before populating.

As it is working now, it does, in fact, return the filtered record set as
I
expect, but since I have 200K (and growing rows), the presenation of the
initial filter form take a bit longer than I am comfortable with. Note:
All
my filter fields are actually bound to very small tables created that
contain
all possible filtered values, or short lists I have provide, so they won't
slow down the setup of the combo boxes, initially).

Thanks again, should you have any ideas.....

Patk

"PatK" wrote:

Now I "am" embarrassed. LOL..thanks John

"John Spencer" wrote:

Me refers to the form or report in which the code is executing.

Me.txtFilterCity is short hand for referring to the control
txtFilterCity
that exists on the current form.
[Forms]![NameOfCurrentForm]![txtFilterCity]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"PatK" <PatK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9905558E-E192-4EFA-BA94-EEB7585488BB@xxxxxxxxxxxxxxxx
quick noob quiestion: In your sample search code, you have code
like:

If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """)
AND
"
End If

Can you tell me what the "Me. prefix on search items like
txtFilterCity
means? As I look at the code, I am trying to to determine what
values
might
be in Me.txtFilterCity, and I am unable to determine the data source.
I
am
guessing the Me. is the key, but not sure. I know...really DUMB
question,
but I am new to coding in Access.

"PatK" wrote:

You have captured what I am trying to do, very well. Thank you for
the
pointers and the sample code. I shall give 'er a go and let you
know how
it
works out!

Thanks, Allen!

Patk

"Allen Browne" wrote:

Okay, there's 2 parts to this.

Firstly, you are asking how to create a simple interface where
users
can
enter various criteria. Here's an example of how to do that:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Once you have worked out how to build the Filter string, you can
apply
it as
the WHERE clause of a query, and then export the query. Typically
a
query
contains the same field list and ORDER BY clause, so it's just the
WHERE
clause that changes. You can do that like this:
Dim strWhere As String
Dim strFile As String
Const strcStub = "SELECT Field1, Field2 FROM Table1 WHERE ("
Const strcTail = ") ORDER BY Field1;"
strFile = "C:\MyFolder\MyFile.xls"
'Build up strWhere as shown in the example above:
CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere &
strcTail
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,
"Query1",
strFile, True

Since you want to choose different formats, you code will need to
use a
Select Case to choose TransferText, TransferDatabase, or whatever.

If you want to give a File SaveAs dialog to choose the file name,
use
the
code in this link to do that:
http://www.mvps.org/access/api/api0001.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"PatK" <PatK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4DE3BA67-94C2-42DD-AB2B-E13772FE4AE6@xxxxxxxxxxxxxxxx
Hi:

I have a database 'query' that has about 200K rows of data. My
user
population, and any given time, is interested in working with
(charting,
excel'ing, powerpointing, etc) just a small subset of the data
(for
example,
all rows of data for a given month, to work on month end reports
(the
file
contains ~2 years of data).

Each of these users sends me requests for excel files (sometimes
access
table data creation, as well) for their specific needs, based
upon a
fairly
limited set of what I call requirements and/or filters. I
envision a
"form"
that they could use that wherein they would select specific
filters
(like,
MonthYY of a set of transactions) based upon data that actually
exists
(like
a dropdown). There would be, maybe, 3-4 of these, with the
option of
exporting to an excel file, or two another access table (like a
make-table
query that they can change the data within, however often they
want).

Something like this in a form (and I cannot show the dropdowns,
but
you
get
the picture, maybe):

Select MonthYear of Data to Export: <dropdown choices>
Select Company: <dropdown choices>
Select Order Status: <dropdown choices>

Output Format (check one): Excel X Access

(GO BUTTON/Cancel)

Is there an "easy" way to do this (note: I do not want them
using
filters
on the forms, etc...it needs to be check boxes and dropdown
choices
or I
will
never be able to train them all).







.