Export reports to separate files

D

D

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
 
S

Steve Schapel

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
 
D

D

Hi Steve, Thanks!

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

Thanks,

Dan

Steve Schapel said:
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

--
Steve Schapel, Microsoft Access MVP
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
Click to expand...
Click to expand...
 
S

Steve Schapel

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?
 
D

D

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
 
S

Steve Schapel

Dan

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

D

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
 
D

D

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

Thanks,

Dan
D said:
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

Steve Schapel said:
Dan

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

Steve Schapel

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"

--
Steve Schapel, Microsoft Access MVP

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

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

D

Hi Steve:

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

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

Steve Schapel said:
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"

--
Steve Schapel, Microsoft Access MVP

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

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

--
Steve Schapel, Microsoft Access MVP


D wrote:

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
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Schapel

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

D

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
 
Top