Hi, It worked perfectly Thank you so much. Could you please tell me
the
code to create the folders? It will save me some work including it.
I have a related question of this process and since you are
familiarized
with what I am trying to do, but not sure if for benefit of the
discussion
group I should post it in a different post.
If I have a report that is based on the same table/query for each group
and
want to filter it and export it for each group in the specific folder,
how
can it be done?
Hopefully it follows a similar logic of the one you just helped me
with.
:
You cannot run code from a table in ACCESS. Are you using a form that
shows
a table's data? Is the code that you posted the entire code in that
module
that you created? If yes, it will not work at all because the code is
not
part of a subroutine or function in that module.
What you should do is create a form that contains a command button.
Create
an event procedure for the button's Click event, and paste all the
code
from
your regular module (except for the Option Compare Database line)
between
the Private Sub and End Sub lines that you'll find in the Visual Basic
Editor after you create the event procedure for the button's Click
event.
If you do this, then you would open the form, click the command
button,
and
the code will run.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
I am trying to run the code in a table. I saved the code as a module
and
ran
it. I would like to export a table or query to Excel and create a
file
for
each group. I also would like to be able to filter a report by
group
and
export it to a specific location as PDF for each group separately.
:
In what type of object are you trying to run this code? And in what
event
procedure? I just noticed that your original code does not show a
specific
function or subroutine as the procedure.
This code is not designed for exporting a report directly to EXCEL.
This
code is designed to export filtered versions of a single query.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
What it does is that it keeps with the error several times and
after
a
couple
of times clicking on the Run button, it takes the value of the
virst
group
name and exports the report. I founf the report in the location,
but
it
does
not do it when running the first time.
Taking out the breakpoint, does not generate the report.
:
Put a breakpoint on this line of code:
Set qdf = dbs.QueryDefs(strTemp)
Run the code. When the code breaks on that line, hover the
curosr
over
the
strTemp text in the line. What value do you see in the popup
window?
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Hi Ken
I did it but still gives me the same error...
:
Create a new query as a "dummy". Put any table in the query
and
select
one
field from it. Save the query and name it qry_z_DUMMY. The
code
that
you're
using assumes that there is at least one query already in the
database,
and
it sounds as if your database file doesn't have any queries
yet.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Now I get the error 3265- Item not found in this
collection,
and
highlights
the line
Set qdf = dbs.QueryDefs(strTemp)"
:
Probably what's happened is that you created that query
and
then
your
code
didn't finish so it wasn't deleted.
Go to your query window and delete that query. Keep the
code
the
same
as
I
provided. Try it then.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
message
I still receive the same error...
:
Change the code to this:
====================
Option Compare Database
Const strQName As String = "zExportQuery"
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As
String
Sub AMP_1()
Set dbs = CurrentDb
' Create temporary query that will be used for
exporting
data;
' we give it a dummy SQL statement initially (this name
will
' be changed by the code to conform to each manager's
identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform
to
your
' *** database design -- ManagerID and EmployeesTable
need
to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my
generic
table
and
field
names
' with the real names of the EmployeesTable table and
the
ManagerID
field
strSQL = "SELECT DISTINCT AMP_ID FROM ADS_Revenue_PRV;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset,
dbReadOnly)
' Now loop through list of ManagerID values and create
a
query
for
each
ManagerID
' so that the data can be exported -- the code assumes
that
the
actual
names
' of the managers are in a lookup table -- again,
replace
generic
names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform
to
your
' *** database design -- ManagerNameField,
ManagersTable,
and
' *** ManagerID need to be changed to your table and
field
names
' *** be changed to your table and field names
strMgr = DLookup("AMP_NAME",
"ADS_Revenue_PRV", _
"AMP_ID = " & rstMgr!AMP_ID.Value)
' *** code to set strSQL needs to be changed to conform
to
your
' *** database design -- ManagerID and EmployeesTable
need
to
' *** be changed to your table and field names
strSQL = "SELECT * FROM ADS_Revenue_PRV
WHERE
" &
_
"AMP_ID = " & rstMgr!AMP_ID.Value &
";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9,
_
strTemp, "C:\Elaine\" & strMgr &
Format(Now(),
