Re: Filtering Via Form, and then Export

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

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





.

Quantcast