Re: Filtering Via Form, and then Export

Tech-Archive recommends: Fix windows errors by optimizing your registry

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).




.

Quantcast