Export reports to separate files

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

D - 22 Oct 2004 17:46 GMT

Hi:

Due to Rick I found out how to print a list of many stores from the same
report into a separate pages; now, how I can export/ or e-mail every
page/store separately/ or to a separate fiel?

Thanks a lot,

D

Reply to this Message

Steve Schapel - 23 Oct 2004 06:30 GMT

D,

Well, it may be applicable to open a recordset listing all the stores
that have data in the report, looping through these records and
exporting each to a separate file, by manipulating the criteria of the
query. For example, let's say your report is based on a query called
StoresData. So your code might look something like this...
Dim rst As DAO.Recordset
Dim qdf As DAO.Querydef
Dim BaseSQL As String
Dim strSQL As String
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT StoreID FROM
StoresData")
Set qdf = CurrentDb.QueryDefs("StoresData")
BaseSQL = Left(qdf.SQL, Len(qdf.SQL)-3)
With rst
Do Until .EOF
strSQL = BaseSQL & " WHERE StoreID=" & ![StoreID]
qdf.SQL = strSQL
DoCmd.OutputTo acOutputReport, "YourReport", "SnapshotFormat",
![StoreID] & ".snp"
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing

Signature

Steve Schapel, Microsoft Access MVP

> Hi:
>
[quoted text clipped - 5 lines]
>
> D

Reply to this Message

D - 25 Oct 2004 01:45 GMT

Hi Steve, Thanks!

Were should I incorporate this code? in the report open?

Thanks,

Dan

> D,
>
[quoted text clipped - 34 lines]
> >
> > D

Reply to this Message

Steve Schapel - 25 Oct 2004 07:11 GMT

Dan,

No, not on the Open event of the report. The report isn't even getting
opened! This code goes on whatever event you use to trigger the
exporting... maybe it's the Click event of a command button somewhere?

Signature

Steve Schapel, Microsoft Access MVP

> Hi Steve, Thanks!
>
[quoted text clipped - 3 lines]
>
> Dan

Reply to this Message

D - 25 Oct 2004 13:27 GMT

Hi Steve:

Fine tahnks, now I have the code in the click event of the exporting button,
but I get : user defined type not defined in the Dim rst As DAO.Recordset?

Thanks,

Dan

> Dan,
>
[quoted text clipped - 9 lines]
> >
> > Dan

Reply to this Message

Steve Schapel - 25 Oct 2004 18:19 GMT

Dan

In your code module, select References from the Tools menu, and tick the
box next to:
Microsoft DAO 3.6 Object Library

Signature

Steve Schapel, Microsoft Access MVP

> Hi Steve:
>
[quoted text clipped - 4 lines]
>
> Dan

Reply to this Message

D - 25 Oct 2004 18:59 GMT

Hi Steve:

Here is the code, adjusted, but does not do anything?? what is the location
for output files?

Private Sub Command26_DblClick(Cancel As Integer)

Dim rst As DAO.Recordset
Dim qdf As DAO.Querydef
Dim BaseSQL As String
Dim strSQL As String
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT natl_str_nbr FROM union")

Set qdf = CurrentDb.QueryDefs("union")
BaseSQL = Left(qdf.SQL, Len(qdf.SQL) - 3)
With rst
Do Until .EOF
strSQL = BaseSQL & " WHERE natl_str_nbr=" & ![natl_str_nbr]
qdf.SQL = strSQL
DoCmd.OutputTo acOutputReport, "mcdeal all stores all days",
"SnapshotFormat", ![natl_str_nbr] & ".snp"

.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub

Thanks,

DAn

> Dan
>
[quoted text clipped - 10 lines]
> >
> > Dan

Reply to this Message

D - 25 Oct 2004 19:11 GMT

Steve, now I get error 3450, incomplete query clause...

Thanks,

Dan

> Hi Steve:
>
[quoted text clipped - 46 lines]
> > >
> > > Dan

Reply to this Message

Steve Schapel - 25 Oct 2004 19:21 GMT

Dan,

You will have to specify the location of the output files in your code.
Something like...
DoCmd.OutputTo acOutputReport, "mcdeal all stores all days",
"SnapshotFormat", "C:\YourFolder\" & ![natl_str_nbr] & ".snp"

Signature

Steve Schapel, Microsoft Access MVP

> Hi Steve:
>
[quoted text clipped - 46 lines]
>>>
>>>Dan

Reply to this Message

D - 25 Oct 2004 19:34 GMT

Hi Steve:

I get error 3450: "incomplete query clause" in this line:

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT * FROM union",
dbOpenDynaset, dbReadOnly)

> Dan,
>
[quoted text clipped - 53 lines]
> >>>
> >>>Dan

Reply to this Message

Steve Schapel - 25 Oct 2004 19:44 GMT

Dan,

First of all, this should not have a * in it, you need to use your id
field whatever it is. Secondly, "union" is a special word in SQL, so
maybe you should try calling your query something else.

Signature

Steve Schapel, Microsoft Access MVP

> Hi Steve:
>
> I get error 3450: "incomplete query clause" in this line:
>
> Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT * FROM union",
> dbOpenDynaset, dbReadOnly)

Reply to this Message

D - 25 Oct 2004 20:01 GMT

THANK YOU STEVE!

Now is working GREAT! I have added: ...dbOpenDynaset, dbReadOnly)

Now I will try to output to rtf format, because the client does not have snp

THANKS AGAIN,

Dan

> Dan,
>
[quoted text clipped - 8 lines]
> > Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT * FROM union",
> > dbOpenDynaset, dbReadOnly)

Reply to this Message