Filtering Via Form, and then Export

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PatK - 07 Nov 2007 00:02 GMT

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

Reply to this Message

Allen Browne - 07 Nov 2007 12:10 GMT

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

Signature

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.

> Hi:
>
[quoted text clipped - 33 lines]
> will
> never be able to train them all).

Reply to this Message

PatK - 07 Nov 2007 14:08 GMT

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

> Okay, there's 2 parts to this.
>
[quoted text clipped - 62 lines]
> > will
> > never be able to train them all).

Reply to this Message

PatK - 07 Nov 2007 17:41 GMT

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.

> 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
[quoted text clipped - 70 lines]
> > > will
> > > never be able to train them all).

Reply to this Message

John Spencer - 07 Nov 2007 19:48 GMT

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]

Signature

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

> quick noob quiestion: In your sample search code, you have code like:
>
[quoted text clipped - 101 lines]
>> > > will
>> > > never be able to train them all).

Reply to this Message

PatK - 07 Nov 2007 21:13 GMT

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

> Me refers to the form or report in which the code is executing.
>
[quoted text clipped - 107 lines]
> >> > > will
> >> > > never be able to train them all).

Reply to this Message

PatK - 07 Nov 2007 23:39 GMT

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

> Now I "am" embarrassed. LOL..thanks John
>
[quoted text clipped - 109 lines]
> > >> > > will
> > >> > > never be able to train them all).

Reply to this Message

Allen Browne - 08 Nov 2007 00:46 GMT

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

Signature

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.

>I think I have the filter form working ok (THANKS!). Question: Once I
>bound
[quoted text clipped - 157 lines]
>> > >> > > will
>> > >> > > never be able to train them all).

Reply to this Message

PatK - 08 Nov 2007 16:31 GMT

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

> This will cause the form to load without any records:
>
[quoted text clipped - 173 lines]
> >> > >> > > will
> >> > >> > > never be able to train them all).

Reply to this Message

PatK - 08 Nov 2007 16:33 GMT

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?

> 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
[quoted text clipped - 198 lines]
> > >> > >> > > will
> > >> > >> > > never be able to train them all).

Reply to this Message

PatK - 08 Nov 2007 22:32 GMT

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

> 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
[quoted text clipped - 203 lines]
> > > >> > >> > > will
> > > >> > >> > > never be able to train them all).

Reply to this Message

Allen Browne - 08 Nov 2007 23:57 GMT

Great. Sounds like you have a solution.

The filter code goes into the Open event of the form if you ever need to do
it that way.

Signature

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.

> well...I may have solved my problem. Basically, I just converted they
> query
[quoted text clipped - 283 lines]
>> > > >> > >> > > will
>> > > >> > >> > > never be able to train them all).

Reply to this Message

PatK - 09 Nov 2007 22:29 GMT

I got both the excel and access exports working. Thanks a million for your
help. One final question: After I have "exported" a tbl to a database, I
have, ocassionally, gotten an Access error telling me I do not have exclusive
access to the database. I am not sure if it is referring to my source DB, or
my target db. Is there some hygienic cleanup I should be doing after an
Access export (or even excel, for that matter).

> Great. Sounds like you have a solution.
>
[quoted text clipped - 257 lines]
> >> > > >> > >> > > option of
> >> > > >> > >> > > exporting to an excel file, or two another access table

Reply to this Message

PatK - 09 Nov 2007 22:40 GMT

Oh! Here was the code for the Access export. Still need to do some cleanup,
but you get the drift. Note that the value strCurrentFilter is a Public
variable that is created in the form processing. Once I have created you
strWhere string, I initialize strCurrentFilter with that value, and then when
the user hits the export to access button, they end up flowing thru the codee
below. Anyway..thanks!

Patk

Sub ExportFilteredDataAccess()
'---------------------------------------------------------------------------
' Author: Pat Klocke
' Version: 1.0
' Datecode: 2007-11-09
'---------------------------------------------------------------------------

Dim sFname As String 'String to store selected file
Dim sWSheet As String 'String to store worksheet to export data in to
Dim sTable As String 'String to store name of table to we are export from
Dim lngFlags As Long 'flag variable
Dim sFilter As String 'Filter for file open routine to limit to xls files
Dim sTableName As String 'Store name of user input table name

sWSheet = "Filtered Tickets" ' Name of tab in excel
spreadsheet
sTable = "tbl All Tickets All Types Transformed" ' Table containing
potential filtered tickets for export

Const strcStub = "SELECT * FROM [tbl All Tickets All Types Transformed]
WHERE (" ' Build front part of query
Const strcTail = ") ORDER BY [Service Call ID];"
' Build tail of query

' --------------------------------------------------------------------------
' Select the output MS Access target file
' --------------------------------------------------------------------------

MsgBox "In following dialog box, identify the target MS Access DB file
in which to place exported table."

sFilter = AddFilterItem(sFilter, "Excel Files (*.XLS)", "*.XLS")
sFilter = AddFilterItem(sFilter, "Access Files (*.mda, *.mdb)",
"*.MDA;*.MDB")
sFilter = AddFilterItem(sFilter, "dBASE Files (*.dbf)", "*.DBF")
sFilter = AddFilterItem(sFilter, "Text Files (*.txt)", "*.TXT")
sFilter = AddFilterItem(sFilter, "All Files (*.*)", "*.*")

sFname = CommonFileOpenSave( _
Filter:=sFilter, OpenFile:=True, _
DialogTitle:="Identify output MS Access DB file...", _
Flags:=ahtOFN_HIDEREADONLY)

sTableName = InputBox("Enter a name for the export Ticket Table: ",
"Enter Table Name", "Custom Export")

If IsNull(strCurrentFilter) Then
GoTo StringError
Else
If IsNull(sFname) Then
GoTo StringError
End If
End If
CurrentDb.QueryDefs("qry 9999 Export").SQL = strcStub & strCurrentFilter
& strcTail 'Build up strWhere as shown in the example above:
DoCmd.TransferDatabase acExport, "Microsoft Access", sFname, acTable,
"qry 9999 Export", sTableName

MsgBox "Access Table Export Complete. Table " & sTableName & " was
stored in " & sFname & ". Please make a note of this information."
Exit Sub
StringError:
MsgBox "No Table exported as the Save File is invalid, or Filter does
not exist. Please Try Again. [EX002]"
End Sub

Reply to this Message