One report to multiple file exports.

[Rachel]

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

 

[Jeff Boyce]

Rachel

I'm not quite following...

Are you saying that you want to be able to design a report, then run
something like "one page per customer"? Would it be enough to just add
"start on a new page for a new customer" to your report design?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rachel]

I want the report to export into individual files based on the customer
number grouping - and save the file name with the customer number. The goal
is put these files online so our customers can look at them - but we only
want them to see their information.

 

[Jeff Boyce]

Rachel

So it's not just the report, but exporting it...

Are you aware that you can "Export" a report to a PDF format, which you
could then store where-ever you wished?

Generically, yes, you should be able to create a procedure that incorporates
the customer number as part of the file name ... perhaps one of the other
newsgroup readers has set up something like this?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rachel]

Is anyone else reading?

Rachel

So it's not just the report, but exporting it...

Are you aware that you can "Export" a report to a PDF format, which you
could then store where-ever you wished?

Generically, yes, you should be able to create a procedure that incorporates
the customer number as part of the file name ... perhaps one of the other
newsgroup readers has set up something like this?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mark Andrews]

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]

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]

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]

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]

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]

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]

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