Save each page of report as a file

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have the following task to automate:

I have a report that has a few hundred pages in it. Currently each page of
the report is printed on paper then scanned into an imaging system. Each
page has fields on it that are indexes in the imaging system. For example,
policy number and last name. When the page is imaged the indexes are keyed
into the image system and attached to the image.

The imaging system has a utility that will do this automatically but only if
EACH PAGE of the Access report is saved as a file and each file be named with
the policy number and last name indexes in the filename. So the files would
look like:

POLICYNUMBER1_LASTNAME1.rtf
POLICYNUMBER2_LASTNAME2.rtf
POLICYNUMBER3_LASTNAME3.rtf etc...

I'm wondering if anyone can help me with this? How can I cycle through all
the pages of the report and save only one page at a time as a file? If
possible I would prefer to only run the report one time - not have to
regenerate it for each record because it takes a long time to run. Can
anyone give me something to start with? Thanks.
 
You can do it. It will require running the report once for each policy, but
never fear, you can automate that.
Since you are using the OutputTo method to create rtf files, you have to get
a bit inventive to filter the report to only one policy. It doesn't have the
filtering capability of the OpenReport method.

So here is what you do.
First, you need a control on your form where you will put the policy number
to print. It can be hidden by making its Visible property No. We will call
it txtPolicyNbr
Now, if you are using a table as the report's record source, you need to
change it to a query and and a Where Clause so it filters the report based on
the value in the form control. It would be something like:

WHERE [Policy_Number] = Forms!MyFormName!txtPolicyNbr

If you are using the query builder, put this part in the Criteria row for
the policy number field:

Forms!MyFormName!txtPolicyNbr

Use real names, of course.

Now we need to set up a recordset loop to print all the policies. If you
apply any filtering the the report's record source, you need to apply the
same filtering to this recordset. You only need two fields in the query, the
policy number and the last name.

SELECT Policy_Number, Last_Name FROM PolicyTable WHERE ....(same as you
do for report)

Now, put a command button on the form to run the report. This would be an
example of what you would do:

Dim dbf As DAO.Database
Dim rst As DAO.Recordset
Dim strFileName As String
Dim strPathName As String

strPathName = "Q:\PolicyReport\" 'Use the real folder
Set dbf = Currentdb
Set rst = dbf.OpenRecordset("MyQuery")

With rst
If .RecordCount = 0 Then
MsgBox "No Policies To Process"
Else
.MoveLast
.MoveFirst
Do While Not .EOF
strFileName = strPathName & ![Policy_Number] & "_" *
![Last_Name] & ".rtf"
Docmd.OutputTo acOutputReport, acFormatRTF, strFileName
.MoveNext
Loop
.Close
End With
End If

Post back if you have questions on this.

Set rst = Nothing
 
Thanks Dave.

I don't fully follow what you are doing here - but that is entirely do to my
lack of programming skill - I'll admit that.

Anyway, I think I might need to clarify some things. From your solution
method I'm sensing that you think that I have a list of policies in a table
prior to the report being generated. I don't.

Let me try to give you a better idea what what is going on prior to the
report being generated:

On a daily basis we cut checks to policy holders through our accounts
payable system. I have an Access database set up that ODBC connects to the
AP database and pulls data that corresponds to the checks that were cut -
i.e., check number, policy number and last name, amoung many other pieces of
info. The query that is attached to my report is made up of several other
queries that have various parameters set on them to filter out just today's
checks and only checks of a certain type.

So as things are right now - there is no predefined list of policy numbers -
my policy numbers result from which checks were cut that day.

I could make a list as a final step if I need to. Is that what you think I
will need to do? Or does having a bit more detail change the way you would
do this?

If you still think what you originally came up with is the way to go - can
you tell me where in the code I tell it what report to open? Thanks for
everything.

Jeff

Klatuu said:
You can do it. It will require running the report once for each policy, but
never fear, you can automate that.
Since you are using the OutputTo method to create rtf files, you have to get
a bit inventive to filter the report to only one policy. It doesn't have the
filtering capability of the OpenReport method.

So here is what you do.
First, you need a control on your form where you will put the policy number
to print. It can be hidden by making its Visible property No. We will call
it txtPolicyNbr
Now, if you are using a table as the report's record source, you need to
change it to a query and and a Where Clause so it filters the report based on
the value in the form control. It would be something like:

WHERE [Policy_Number] = Forms!MyFormName!txtPolicyNbr

If you are using the query builder, put this part in the Criteria row for
the policy number field:

Forms!MyFormName!txtPolicyNbr

Use real names, of course.

Now we need to set up a recordset loop to print all the policies. If you
apply any filtering the the report's record source, you need to apply the
same filtering to this recordset. You only need two fields in the query, the
policy number and the last name.

SELECT Policy_Number, Last_Name FROM PolicyTable WHERE ....(same as you
do for report)

Now, put a command button on the form to run the report. This would be an
example of what you would do:

Dim dbf As DAO.Database
Dim rst As DAO.Recordset
Dim strFileName As String
Dim strPathName As String

strPathName = "Q:\PolicyReport\" 'Use the real folder
Set dbf = Currentdb
Set rst = dbf.OpenRecordset("MyQuery")

With rst
If .RecordCount = 0 Then
MsgBox "No Policies To Process"
Else
.MoveLast
.MoveFirst
Do While Not .EOF
strFileName = strPathName & ![Policy_Number] & "_" *
![Last_Name] & ".rtf"
Docmd.OutputTo acOutputReport, acFormatRTF, strFileName
.MoveNext
Loop
.Close
End With
End If

Post back if you have questions on this.

Set rst = Nothing

--
Dave Hargis, Microsoft Access MVP


Jeff said:
I have the following task to automate:

I have a report that has a few hundred pages in it. Currently each page of
the report is printed on paper then scanned into an imaging system. Each
page has fields on it that are indexes in the imaging system. For example,
policy number and last name. When the page is imaged the indexes are keyed
into the image system and attached to the image.

The imaging system has a utility that will do this automatically but only if
EACH PAGE of the Access report is saved as a file and each file be named with
the policy number and last name indexes in the filename. So the files would
look like:

POLICYNUMBER1_LASTNAME1.rtf
POLICYNUMBER2_LASTNAME2.rtf
POLICYNUMBER3_LASTNAME3.rtf etc...

I'm wondering if anyone can help me with this? How can I cycle through all
the pages of the report and save only one page at a time as a file? If
possible I would prefer to only run the report one time - not have to
regenerate it for each record because it takes a long time to run. Can
anyone give me something to start with? Thanks.
Click to expand...
Click to expand...
 
Dave,

I think I have a better handle on what you are telling me to do here. I've
also figured out how to tell it what report to open.

My main problem now is the code doesn't seem to be moving to the next
record. The form just sits on the first record and the reports run but since
the form doesn't change to the next record all the files that are getting
created all look the same (yet they are named differently).

The naming of the files with the policy number is working, so it is cycling
through the recordset correctly, but the form doesn't advance to the next
record - leaving my report to have the same policy over and over. How do I
get the form to advance to the next record so that the
=Form!frmPolicyForm.txtPolicyNum can work? Thanks.

Jeff

Klatuu said:
You can do it. It will require running the report once for each policy, but
never fear, you can automate that.
Since you are using the OutputTo method to create rtf files, you have to get
a bit inventive to filter the report to only one policy. It doesn't have the
filtering capability of the OpenReport method.

So here is what you do.
First, you need a control on your form where you will put the policy number
to print. It can be hidden by making its Visible property No. We will call
it txtPolicyNbr
Now, if you are using a table as the report's record source, you need to
change it to a query and and a Where Clause so it filters the report based on
the value in the form control. It would be something like:

WHERE [Policy_Number] = Forms!MyFormName!txtPolicyNbr

If you are using the query builder, put this part in the Criteria row for
the policy number field:

Forms!MyFormName!txtPolicyNbr

Use real names, of course.

Now we need to set up a recordset loop to print all the policies. If you
apply any filtering the the report's record source, you need to apply the
same filtering to this recordset. You only need two fields in the query, the
policy number and the last name.

SELECT Policy_Number, Last_Name FROM PolicyTable WHERE ....(same as you
do for report)

Now, put a command button on the form to run the report. This would be an
example of what you would do:

Dim dbf As DAO.Database
Dim rst As DAO.Recordset
Dim strFileName As String
Dim strPathName As String

strPathName = "Q:\PolicyReport\" 'Use the real folder
Set dbf = Currentdb
Set rst = dbf.OpenRecordset("MyQuery")

With rst
If .RecordCount = 0 Then
MsgBox "No Policies To Process"
Else
.MoveLast
.MoveFirst
Do While Not .EOF
strFileName = strPathName & ![Policy_Number] & "_" *
![Last_Name] & ".rtf"
Docmd.OutputTo acOutputReport, acFormatRTF, strFileName
.MoveNext
Loop
.Close
End With
End If

Post back if you have questions on this.

Set rst = Nothing

--
Dave Hargis, Microsoft Access MVP


Jeff said:
I have the following task to automate:

I have a report that has a few hundred pages in it. Currently each page of
the report is printed on paper then scanned into an imaging system. Each
page has fields on it that are indexes in the imaging system. For example,
policy number and last name. When the page is imaged the indexes are keyed
into the image system and attached to the image.

The imaging system has a utility that will do this automatically but only if
EACH PAGE of the Access report is saved as a file and each file be named with
the policy number and last name indexes in the filename. So the files would
look like:

POLICYNUMBER1_LASTNAME1.rtf
POLICYNUMBER2_LASTNAME2.rtf
POLICYNUMBER3_LASTNAME3.rtf etc...

I'm wondering if anyone can help me with this? How can I cycle through all
the pages of the report and save only one page at a time as a file? If
possible I would prefer to only run the report one time - not have to
regenerate it for each record because it takes a long time to run. Can
anyone give me something to start with? Thanks.
Click to expand...
Click to expand...
 
Sorry for the delayed post. I took a couple of days off.
I don't know why it would not be advancing. I checked the code, and there
is a MoveNext that should advance the record.

Yes, since you have the ODCB connection, you do have a list of policies to
process.
A query against the AP table that has the policies the checks were cut for
would be what you would use to create the loop.

Let me know how it is going.
--
Dave Hargis, Microsoft Access MVP


Jeff said:
Dave,

I think I have a better handle on what you are telling me to do here. I've
also figured out how to tell it what report to open.

My main problem now is the code doesn't seem to be moving to the next
record. The form just sits on the first record and the reports run but since
the form doesn't change to the next record all the files that are getting
created all look the same (yet they are named differently).

The naming of the files with the policy number is working, so it is cycling
through the recordset correctly, but the form doesn't advance to the next
record - leaving my report to have the same policy over and over. How do I
get the form to advance to the next record so that the
=Form!frmPolicyForm.txtPolicyNum can work? Thanks.

Jeff

Klatuu said:
You can do it. It will require running the report once for each policy, but
never fear, you can automate that.
Since you are using the OutputTo method to create rtf files, you have to get
a bit inventive to filter the report to only one policy. It doesn't have the
filtering capability of the OpenReport method.

So here is what you do.
First, you need a control on your form where you will put the policy number
to print. It can be hidden by making its Visible property No. We will call
it txtPolicyNbr
Now, if you are using a table as the report's record source, you need to
change it to a query and and a Where Clause so it filters the report based on
the value in the form control. It would be something like:

WHERE [Policy_Number] = Forms!MyFormName!txtPolicyNbr

If you are using the query builder, put this part in the Criteria row for
the policy number field:

Forms!MyFormName!txtPolicyNbr

Use real names, of course.

Now we need to set up a recordset loop to print all the policies. If you
apply any filtering the the report's record source, you need to apply the
same filtering to this recordset. You only need two fields in the query, the
policy number and the last name.

SELECT Policy_Number, Last_Name FROM PolicyTable WHERE ....(same as you
do for report)

Now, put a command button on the form to run the report. This would be an
example of what you would do:

Dim dbf As DAO.Database
Dim rst As DAO.Recordset
Dim strFileName As String
Dim strPathName As String

strPathName = "Q:\PolicyReport\" 'Use the real folder
Set dbf = Currentdb
Set rst = dbf.OpenRecordset("MyQuery")

With rst
If .RecordCount = 0 Then
MsgBox "No Policies To Process"
Else
.MoveLast
.MoveFirst
Do While Not .EOF
strFileName = strPathName & ![Policy_Number] & "_" *
![Last_Name] & ".rtf"
Docmd.OutputTo acOutputReport, acFormatRTF, strFileName
.MoveNext
Loop
.Close
End With
End If

Post back if you have questions on this.

Set rst = Nothing

--
Dave Hargis, Microsoft Access MVP


Jeff said:
I have the following task to automate:

I have a report that has a few hundred pages in it. Currently each page of
the report is printed on paper then scanned into an imaging system. Each
page has fields on it that are indexes in the imaging system. For example,
policy number and last name. When the page is imaged the indexes are keyed
into the image system and attached to the image.

The imaging system has a utility that will do this automatically but only if
EACH PAGE of the Access report is saved as a file and each file be named with
the policy number and last name indexes in the filename. So the files would
look like:

POLICYNUMBER1_LASTNAME1.rtf
POLICYNUMBER2_LASTNAME2.rtf
POLICYNUMBER3_LASTNAME3.rtf etc...

I'm wondering if anyone can help me with this? How can I cycle through all
the pages of the report and save only one page at a time as a file? If
possible I would prefer to only run the report one time - not have to
regenerate it for each record because it takes a long time to run. Can
anyone give me something to start with? Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Dave,

Thanks for responding. I got it to work, by adding the following into your
code
.MoveLast DoCmd.GoToRecord acDataForm, "frmOutputTest", acLast
.MoveFirst DoCmd.GoToRecord acDataForm, "frmOutputTest", acFirst
Do While Not .EOF
strFileName = strPathName & ![Policy_Number] & "_" *
![Last_Name] & ".rtf"
Docmd.OutputTo acOutputReport, acFormatRTF, strFileName
.MoveNext
Click to expand...
Click to expand...
Click to expand...
DoCmd.GoToRecord acDataForm, "frmOutputTest", acNext

What I added doesn't have the > > > across from it (the DoCmd.GoToRecord
lines). It appears to work. Does that make sense that I had to add that or
did I have to add that because I did something wrong? Thanks.

Jeff


Klatuu said:
Sorry for the delayed post. I took a couple of days off.
I don't know why it would not be advancing. I checked the code, and there
is a MoveNext that should advance the record.

Yes, since you have the ODCB connection, you do have a list of policies to
process.
A query against the AP table that has the policies the checks were cut for
would be what you would use to create the loop.

Let me know how it is going.
--
Dave Hargis, Microsoft Access MVP


Jeff said:
Dave,

I think I have a better handle on what you are telling me to do here. I've
also figured out how to tell it what report to open.

My main problem now is the code doesn't seem to be moving to the next
record. The form just sits on the first record and the reports run but since
the form doesn't change to the next record all the files that are getting
created all look the same (yet they are named differently).

The naming of the files with the policy number is working, so it is cycling
through the recordset correctly, but the form doesn't advance to the next
record - leaving my report to have the same policy over and over. How do I
get the form to advance to the next record so that the
=Form!frmPolicyForm.txtPolicyNum can work? Thanks.

Jeff

Klatuu said:
You can do it. It will require running the report once for each policy, but
never fear, you can automate that.
Since you are using the OutputTo method to create rtf files, you have to get
a bit inventive to filter the report to only one policy. It doesn't have the
filtering capability of the OpenReport method.

So here is what you do.
First, you need a control on your form where you will put the policy number
to print. It can be hidden by making its Visible property No. We will call
it txtPolicyNbr
Now, if you are using a table as the report's record source, you need to
change it to a query and and a Where Clause so it filters the report based on
the value in the form control. It would be something like:

WHERE [Policy_Number] = Forms!MyFormName!txtPolicyNbr

If you are using the query builder, put this part in the Criteria row for
the policy number field:

Forms!MyFormName!txtPolicyNbr

Use real names, of course.

Now we need to set up a recordset loop to print all the policies. If you
apply any filtering the the report's record source, you need to apply the
same filtering to this recordset. You only need two fields in the query, the
policy number and the last name.

SELECT Policy_Number, Last_Name FROM PolicyTable WHERE ....(same as you
do for report)

Now, put a command button on the form to run the report. This would be an
example of what you would do:

Dim dbf As DAO.Database
Dim rst As DAO.Recordset
Dim strFileName As String
Dim strPathName As String

strPathName = "Q:\PolicyReport\" 'Use the real folder
Set dbf = Currentdb
Set rst = dbf.OpenRecordset("MyQuery")

With rst
If .RecordCount = 0 Then
MsgBox "No Policies To Process"
Else
.MoveLast
.MoveFirst
Do While Not .EOF
strFileName = strPathName & ![Policy_Number] & "_" *
![Last_Name] & ".rtf"
Docmd.OutputTo acOutputReport, acFormatRTF, strFileName
.MoveNext
Loop
.Close
End With
End If

Post back if you have questions on this.

Set rst = Nothing

--
Dave Hargis, Microsoft Access MVP


:

I have the following task to automate:

I have a report that has a few hundred pages in it. Currently each page of
the report is printed on paper then scanned into an imaging system. Each
page has fields on it that are indexes in the imaging system. For example,
policy number and last name. When the page is imaged the indexes are keyed
into the image system and attached to the image.

The imaging system has a utility that will do this automatically but only if
EACH PAGE of the Access report is saved as a file and each file be named with
the policy number and last name indexes in the filename. So the files would
look like:

POLICYNUMBER1_LASTNAME1.rtf
POLICYNUMBER2_LASTNAME2.rtf
POLICYNUMBER3_LASTNAME3.rtf etc...

I'm wondering if anyone can help me with this? How can I cycle through all
the pages of the report and save only one page at a time as a file? If
possible I would prefer to only run the report one time - not have to
regenerate it for each record because it takes a long time to run. Can
anyone give me something to start with? Thanks.
Click to expand...
Click to expand...
Click to expand...
 
If your logic is depending on the value of a control on your form, that seems
reasonable.
--
Dave Hargis, Microsoft Access MVP


Jeff said:
Dave,

Thanks for responding. I got it to work, by adding the following into your
code
.MoveLast DoCmd.GoToRecord acDataForm, "frmOutputTest", acLast
.MoveFirst DoCmd.GoToRecord acDataForm, "frmOutputTest", acFirst
Do While Not .EOF
strFileName = strPathName & ![Policy_Number] & "_" *
![Last_Name] & ".rtf"
Docmd.OutputTo acOutputReport, acFormatRTF, strFileName
.MoveNext
Click to expand...
Click to expand...
DoCmd.GoToRecord acDataForm, "frmOutputTest", acNext

What I added doesn't have the > > > across from it (the DoCmd.GoToRecord
lines). It appears to work. Does that make sense that I had to add that or
did I have to add that because I did something wrong? Thanks.

Jeff


Klatuu said:
Sorry for the delayed post. I took a couple of days off.
I don't know why it would not be advancing. I checked the code, and there
is a MoveNext that should advance the record.

Yes, since you have the ODCB connection, you do have a list of policies to
process.
A query against the AP table that has the policies the checks were cut for
would be what you would use to create the loop.

Let me know how it is going.
--
Dave Hargis, Microsoft Access MVP


Jeff said:
Dave,

I think I have a better handle on what you are telling me to do here. I've
also figured out how to tell it what report to open.

My main problem now is the code doesn't seem to be moving to the next
record. The form just sits on the first record and the reports run but since
the form doesn't change to the next record all the files that are getting
created all look the same (yet they are named differently).

The naming of the files with the policy number is working, so it is cycling
through the recordset correctly, but the form doesn't advance to the next
record - leaving my report to have the same policy over and over. How do I
get the form to advance to the next record so that the
=Form!frmPolicyForm.txtPolicyNum can work? Thanks.

Jeff

:

You can do it. It will require running the report once for each policy, but
never fear, you can automate that.
Since you are using the OutputTo method to create rtf files, you have to get
a bit inventive to filter the report to only one policy. It doesn't have the
filtering capability of the OpenReport method.

So here is what you do.
First, you need a control on your form where you will put the policy number
to print. It can be hidden by making its Visible property No. We will call
it txtPolicyNbr
Now, if you are using a table as the report's record source, you need to
change it to a query and and a Where Clause so it filters the report based on
the value in the form control. It would be something like:

WHERE [Policy_Number] = Forms!MyFormName!txtPolicyNbr

If you are using the query builder, put this part in the Criteria row for
the policy number field:

Forms!MyFormName!txtPolicyNbr

Use real names, of course.

Now we need to set up a recordset loop to print all the policies. If you
apply any filtering the the report's record source, you need to apply the
same filtering to this recordset. You only need two fields in the query, the
policy number and the last name.

SELECT Policy_Number, Last_Name FROM PolicyTable WHERE ....(same as you
do for report)

Now, put a command button on the form to run the report. This would be an
example of what you would do:

Dim dbf As DAO.Database
Dim rst As DAO.Recordset
Dim strFileName As String
Dim strPathName As String

strPathName = "Q:\PolicyReport\" 'Use the real folder
Set dbf = Currentdb
Set rst = dbf.OpenRecordset("MyQuery")

With rst
If .RecordCount = 0 Then
MsgBox "No Policies To Process"
Else
.MoveLast
.MoveFirst
Do While Not .EOF
strFileName = strPathName & ![Policy_Number] & "_" *
![Last_Name] & ".rtf"
Docmd.OutputTo acOutputReport, acFormatRTF, strFileName
.MoveNext
Loop
.Close
End With
End If

Post back if you have questions on this.

Set rst = Nothing

--
Dave Hargis, Microsoft Access MVP


:

I have the following task to automate:

I have a report that has a few hundred pages in it. Currently each page of
the report is printed on paper then scanned into an imaging system. Each
page has fields on it that are indexes in the imaging system. For example,
policy number and last name. When the page is imaged the indexes are keyed
into the image system and attached to the image.

The imaging system has a utility that will do this automatically but only if
EACH PAGE of the Access report is saved as a file and each file be named with
the policy number and last name indexes in the filename. So the files would
look like:

POLICYNUMBER1_LASTNAME1.rtf
POLICYNUMBER2_LASTNAME2.rtf
POLICYNUMBER3_LASTNAME3.rtf etc...

I'm wondering if anyone can help me with this? How can I cycle through all
the pages of the report and save only one page at a time as a file? If
possible I would prefer to only run the report one time - not have to
regenerate it for each record because it takes a long time to run. Can
anyone give me something to start with? Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top