One report to multiple file exports.

Hi, Mark! I'd LOVE to send this to you but I'm sure I'd be breaking company
confidentiality mumbo jumbo.

Yes, all tables and queries that feed each report (main and subs) contain
the field CUSTOMER_CODE and the report works just fine until I enter the code
then it acts like none of the subreports can find the CUSTOMER_CODE links to
the main report because none of the subs have any data in them when clicking
"OK" through all of the parameters windows.

Thank you again for continuing to help me on this! I REALLY appreciate it.


"Mark Andrews" wrote:

Does the sql that drives the main report have the CUSTOMER_CODE field?

Do all of the tables that drive the 7 sub-reports have the CUSTOMER_CODE
field?

Does the report work if you run it manually with no code and just doesn't
work when calling it via code?

that's three things to look at to get closer to an answer.

You could email it to me and I'll take a look. Or you could upload to my
ftp site if needed.
Send me an email if you want to go that route.

I could probably look at it and in a few minutes tell you what the problem
is.

Mark
RPT Software
http://www.rptsoftware.com



"Rachel" wrote in message
...
All of the subreports are running off of tables - I'm guessing that
somehow,
the code entered affects the master and child link fields - which are all
linked to the main report by CUSTOMER_CODE. I have about 7 subreports,
that's how many times I get asked for parameters and none of the
subreports
come up with data.

Thanks for your help!

"Mark Andrews" wrote:

Do any of the reports or subreports have any references to forms?

You need to figure out what the report or subreports are referencing that
they can't find.
Example: CUSTOMER_CODE pops ups becuase a query or reference somewhere
can't find the field.

Try running the queries that drive each report and sub report.

You could send it to me and I'll take a look.

Mark
RPT Software
http://www.rptsoftware.com


"Rachel" wrote in message
...
Thanks, Mark! I put the code in - exactly like you had it, opened up
the
report and it asked me parameters on CUSTOMER_CODE. I think it has
something
to do with all of the subreports - I hit "ok" thru all of the parameter
windows and got to view the report but none of the subreport data was
there.

Any suggestions? ...and creating each file manually isn't an option.


"Mark Andrews" wrote:

You can use our Email module. It has two functions to help with
exactly
this type of thing:
http://www.rptsoftware.com/products/email/

You basically have to either pass a where clause to the report on
every
file
creation or recreate the queries that drive the
reports on every file creation.

If you want to keep your code you need to:
- use strEmp in a where clause on OPENING the report and pass that in
as
a
parameter, then use OutputTo to create the file
- use StrEmp to change how the filename string is put together, so it
changes for every customer

If a simple where clause won't change how your report and subreports
work
you would need more code to
create the underlying queries differently.

I would also probably use PDF format instead of SNP (if it's going to
customers).

Also, I tend to loop thru recordsets as:
Dim db As Database
Dim RS As DAO.Recordset
Dim Result As Variant
Dim sql As String

sql = "SELECT * From Tbl_Dealers_for_Scorecard"

Set db = CurrentDb()
Set RS = db.OpenRecordset(sql, dbOpenDynaset)
If Not (RS.BOF And RS.EOF) Then
RS.MoveFirst
Do While Not RS.EOF
'Do stuff### such as:
' Result = RPT_CreateSingleFile("Rpt_Dealer_Scorecards",
"C:\Reports\Report" & strEmp & ".SNP","SNP","WHERE CUSTOMER_CODE = " &
strEmp)
RS.MoveNext
Loop
End If
RS.Close
Set RS = Nothing
Set db = Nothing

Hope some of that helps,
If you go with the email module and can't get it to work drop me a
line
and
I'll help,
Mark

PS: there are other ways to do batch file creation with our product as
well
such as just running an append query to get the
500 records added to our tblReportQueue table and calling
RPT_CreateFiles(),
gives you history of each report, timestamps and any possible
errors if anything happened during the run.


"Rachel" wrote in message
...
I have tried every code I can find on the internet to no avail. I
have
one
giant report grouped by customer_code that I want to automate to
break
up
into individual reports based on customer_code, export to .snp files
using
their individual customer_code as the file name. I'm using Access
2003.
My
report is based off of a table and has several subreports in it.

Here's the last code I've tried:

Dim rs As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String
Dim strEmp As String

Set cnn = CurrentProject.Connection
strSQL = "SELECT * From Tbl_Dealers_for_Scorecard"
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText

If rs.RecordCount 0 Then
rs.MoveFirst
Do While Not rs.EOF
strEmp = rs.Fields("CUSTOMER_CODE").Value
DoCmd.OutputTo acReport, "Rpt_Dealer_Scorecards",
"SnapshotFormat(*.snp)", "D:\Documents and Settings\fm038\My
Documents\PROJECTS - DEPT\Sales - Freight\Dealer Sales
Comparison\Rpt_Dealer_Scorecards.snp", True

rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing

It gives me a compile error on "Set cnn = CurrentProject.Connection"
as
an
invalid outiside procedure. Then, if I just close the module and
open
up
my
report - it gives me compile errors in my iif statements on the
queries
used
for the subreports! ALSO - I know this won't name the files like I
want -
was just trying to start somewhere!

I'm not totally Access retarded...but this code is sure making me
feel
that
way!

Thank you in advance for any help!

-Rachel

Rachel,

I just noticed you posted another thread to this topic (sorry I missed it).

If you still haven't got this to work drop me an email and we can figure out
a way to get it solved.

Possibly you could create a new database, and import in the report, sub
reports, queries and tablesused.

Strip out most of the data in the tables and then send me the file.

You can get email info at my website:
http://www.rptsoftware.com

Mark
"Rachel" wrote in message
...
Hi, Mark! I'd LOVE to send this to you but I'm sure I'd be breaking
company
confidentiality mumbo jumbo.

Yes, all tables and queries that feed each report (main and subs) contain
the field CUSTOMER_CODE and the report works just fine until I enter the
code
then it acts like none of the subreports can find the CUSTOMER_CODE links
to
the main report because none of the subs have any data in them when
clicking
"OK" through all of the parameters windows.

Thank you again for continuing to help me on this! I REALLY appreciate
it.


"Mark Andrews" wrote:

Does the sql that drives the main report have the CUSTOMER_CODE field?

Do all of the tables that drive the 7 sub-reports have the CUSTOMER_CODE
field?

Does the report work if you run it manually with no code and just doesn't
work when calling it via code?

that's three things to look at to get closer to an answer.

You could email it to me and I'll take a look. Or you could upload to my
ftp site if needed.
Send me an email if you want to go that route.

I could probably look at it and in a few minutes tell you what the
problem
is.

Mark
RPT Software
http://www.rptsoftware.com



"Rachel" wrote in message
...
All of the subreports are running off of tables - I'm guessing that
somehow,
the code entered affects the master and child link fields - which are
all
linked to the main report by CUSTOMER_CODE. I have about 7 subreports,
that's how many times I get asked for parameters and none of the
subreports
come up with data.

Thanks for your help!

"Mark Andrews" wrote:

Do any of the reports or subreports have any references to forms?

You need to figure out what the report or subreports are referencing
that
they can't find.
Example: CUSTOMER_CODE pops ups becuase a query or reference
somewhere
can't find the field.

Try running the queries that drive each report and sub report.

You could send it to me and I'll take a look.

Mark
RPT Software
http://www.rptsoftware.com


"Rachel" wrote in message
...
Thanks, Mark! I put the code in - exactly like you had it, opened
up
the
report and it asked me parameters on CUSTOMER_CODE. I think it has
something
to do with all of the subreports - I hit "ok" thru all of the
parameter
windows and got to view the report but none of the subreport data
was
there.

Any suggestions? ...and creating each file manually isn't an
option.


"Mark Andrews" wrote:

You can use our Email module. It has two functions to help with
exactly
this type of thing:
http://www.rptsoftware.com/products/email/

You basically have to either pass a where clause to the report on
every
file
creation or recreate the queries that drive the
reports on every file creation.

If you want to keep your code you need to:
- use strEmp in a where clause on OPENING the report and pass that
in
as
a
parameter, then use OutputTo to create the file
- use StrEmp to change how the filename string is put together, so
it
changes for every customer

If a simple where clause won't change how your report and
subreports
work
you would need more code to
create the underlying queries differently.

I would also probably use PDF format instead of SNP (if it's going
to
customers).

Also, I tend to loop thru recordsets as:
Dim db As Database
Dim RS As DAO.Recordset
Dim Result As Variant
Dim sql As String

sql = "SELECT * From Tbl_Dealers_for_Scorecard"

Set db = CurrentDb()
Set RS = db.OpenRecordset(sql, dbOpenDynaset)
If Not (RS.BOF And RS.EOF) Then
RS.MoveFirst
Do While Not RS.EOF
'Do stuff### such as:
' Result =
RPT_CreateSingleFile("Rpt_Dealer_Scorecards",
"C:\Reports\Report" & strEmp & ".SNP","SNP","WHERE CUSTOMER_CODE =
" &
strEmp)
RS.MoveNext
Loop
End If
RS.Close
Set RS = Nothing
Set db = Nothing

Hope some of that helps,
If you go with the email module and can't get it to work drop me a
line
and
I'll help,
Mark

PS: there are other ways to do batch file creation with our product
as
well
such as just running an append query to get the
500 records added to our tblReportQueue table and calling
RPT_CreateFiles(),
gives you history of each report, timestamps and any possible
errors if anything happened during the run.


"Rachel" wrote in message
...
I have tried every code I can find on the internet to no avail. I
have
one
giant report grouped by customer_code that I want to automate to
break
up
into individual reports based on customer_code, export to .snp
files
using
their individual customer_code as the file name. I'm using
Access
2003.
My
report is based off of a table and has several subreports in it.

Here's the last code I've tried:

Dim rs As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String
Dim strEmp As String

Set cnn = CurrentProject.Connection
strSQL = "SELECT * From Tbl_Dealers_for_Scorecard"
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText

If rs.RecordCount 0 Then
rs.MoveFirst
Do While Not rs.EOF
strEmp = rs.Fields("CUSTOMER_CODE").Value
DoCmd.OutputTo acReport, "Rpt_Dealer_Scorecards",
"SnapshotFormat(*.snp)", "D:\Documents and Settings\fm038\My
Documents\PROJECTS - DEPT\Sales - Freight\Dealer Sales
Comparison\Rpt_Dealer_Scorecards.snp", True

rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing

It gives me a compile error on "Set cnn =
CurrentProject.Connection"
as
an
invalid outiside procedure. Then, if I just close the module and
open
up
my
report - it gives me compile errors in my iif statements on the
queries
used
for the subreports! ALSO - I know this won't name the files like
I
want -
was just trying to start somewhere!

I'm not totally Access retarded...but this code is sure making me
feel
that
way!

Thank you in advance for any help!

-Rachel