Filtering Via Form, and then Export

[Guest]

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

 

[Allen Browne]

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

 

[Guest]

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

 

[Guest]

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.

 

[John Spencer]

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

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
works out!

Thanks, Allen!

Patk

 

[Guest]

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

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

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
works out!

Thanks, Allen!

Patk

:

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Guest]

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

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

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
works out!

Thanks, Allen!

Patk

:

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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
works out!

Thanks, Allen!

Patk

:

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Guest]

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:

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

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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

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
works out!

Thanks, Allen!

Patk

:

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Guest]

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
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:

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

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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

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
works out!

Thanks, Allen!

Patk

:

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Guest]

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
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
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:

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

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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

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
works out!

Thanks, Allen!

Patk

:

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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
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
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:

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

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

:

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

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
works out!

Thanks, Allen!

Patk

:

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Guest]

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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
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
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:

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

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

:

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

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
works out!

Thanks, Allen!

Patk

:

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Guest]

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