PDF reports macro (2000)

Topic

New Reply

I would like to automatically run some PDF reports (about 15 in total).
Rather than keep having to enter than in all the time which is very time consuming is it possible to produce a macro to do this automatically?

See attachment for details.

Justin.

Reply | Quote

Replies

I don’t understand your sentence. Do you want a macro to create a macro?

Reply | Quote

I appear to not have explained myself properly.

I would like to create a macro to run some PDF reports.
Rather than keep having to type them all in which is very time consuming.

Justin.

Reply | Quote

Judging from the screenshots in your attachment, you already have a macro that prints all the reports, to PDF I presume. You didn’t like creating the macro? It would be possible to write VBA code to loop through a series of reports, but
a) You would need to have a way to specify which reports should be printed. This will involve handiwork too.
You would have to learn Access VBA.
But perhaps I’m still not understanding your question.

Reply | Quote

Sorry for any confusion.

Yes l do have a macro to printout all the reports to PDF.
As it is very time consuming having to keep typing in all the names.
The reason l require this is that this task is something l do on a regular basis.

Yes l would like a macro to loop though a series of reports rather thn having to keep typing them in.
I don’t mind learning Access VBA.
What is the first step?

Justin.

Reply | Quote

You will have to specify which reports you want to print to PDF. There are several ways to do this:

• Use a prefix or postfix in the name of the reports, e.g. let the names of the reports to be printed to PDF begin with pdf_. Example: pdf_AOBT Trial.
• Create a table with a single text field. Enter the names of the reports to be printed to PDF (one record per report)
  [/list]There are other methods, some of them more “advanced”, but these are relatively easy. If you indicate which method you would like, we can take the next step.

Reply | Quote

The names need to printed with the .pdf extension at the end therefore l feel it would be best to use the second approach.

Justin.

Reply | Quote

Create a table tblReports with a text field ReportName. Make its size large enough to accomodate the longest report name you have (or are likely to have.) Set the primary key of the table on ReportName.
Save the table design, then enter the names of the reports to be exported to PDF (each report in a separate record.)

Next. switch to the Modules section of the database window, and click New. Copy the following function into the module window:

Function PrintReports()
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strReportName As String

On Error GoTo ErrHandler

‘ Specify connection and open recordset
Set cnn = CurrentProject.Connection
rst.Open “tblReports”, cnn, adOpenForwardOnly

‘ Loop through records
Do While Not rst.EOF
strReportName = rst!ReportName
SysCmd acSysCmdSetStatus, “Processing ” & strReportName
DoCmd.OpenReport strReportName, acViewNormal
rst.MoveNext
Loop

ExitHandler:
‘ Clean up
On Error Resume Next
rst.Close
Set rst = Nothing
Set cnn = Nothing
Exit Function

ErrHandler:
‘ Inform user
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Function

Later, when you are prompted to save the module, click Yes and name it for example basReports. Don’t give it the same name as the function, that would confuse Access.

You can call this function from a toolbar button, or from the On Click event of a command button on a form. Set the On Action property of the toolbar button, or the On Click property of the command button to =PrintReports().

Note: I have assumed that the reports have already been set up to print to PDF.

Reply | Quote

I fully understand what you have told me to do but cannot work out how l get the =PrintReports to work on a toolbar button.
I assume l call this from a macro. If that is the case call you tell me the macro l have to use.

Justin.

Reply | Quote

1. Drag “Custom” from the File category to your toolbar. This will give you a toolbar button labeled “Custom”.
2. Right-click the new toolbar button, and select Properties.
3. Set the caption etc., and enter =PrintReports() in the On Action property.

See screenshot.

Reply | Quote

I have done this.
What is the next step?

Justin

Reply | Quote

Umm, are there more steps? What should they do?

Reply | Quote

I appear to be getting an error.
Can you tell me what it means.

Justin.

Reply | Quote

Check very carefully that the name in the table is exactly the same as the name of the report in the database window.

Reply | Quote

See attachment for table in case l have made an error.

Justin.

Reply | Quote

Since I had problems with the database you posted in your other thread, try this:
– Activate the Visual Basic Editor (Alt+F11)
– Select Tools | References…
– If you see any reference starting with MISSING, clear its check box.
– Click OK.
– Select Debug | Compile
– If there are errors, try to resolve them, then try Debug | Compile … again.
– If you don’t get errors, switch back to Access and try to run the reports.

Reply | Quote

Since I had problems with the database you posted in your other thread, try this:
– Activate the Visual Basic Editor (Alt+F11)
– Select Tools | References…
– If you see any reference starting with MISSING, clear its check box.
– Click OK.
– Select Debug | Compile
– If there are errors, try to resolve them, then try Debug | Compile … again.
– If you don’t get errors, switch back to Access and try to run the reports.

Reply | Quote

After trying this l get no errors.

Any suggestions?
Justin.

Reply | Quote

No errors, but you still get an error when trying to print the reports, I guess. Could you post a stripped down copy of the database? (same recipe as in the other thread)

Reply | Quote

See attachment.

Justin.

Reply | Quote

The only report in the database you attached is named Install AOBT trial. The only record in tblReports contains AOBT open action report.

Reply | Quote

yes l know, this is a trimmed down version of the database.

Justin.

Reply | Quote

OK, but that makes it impossible to test anything. If I match the name in the table to that of the report, the code does try to open the report, then fails because the record source is not available. But it does not complain that the report can’t be found.

Reply | Quote

What information do you require in order to test that it works?

Justin.

Reply | Quote

As far as I could see, the code would work as intended if there had been a record source for the report. But if you wish, you could include the record source for AOBT open action report, with one or two records.

Reply | Quote

Sorry don’t know quite what you mean by record source. Can you explain?

Justin

Reply | Quote

The record source is the table or query behind the report. You can see it in the Data tab of the Properties window.

The database you attached yesterday can’t be used for testing because:

1. The only report name in the table tblReports does not correspond to a report in the database.
2. The only report in the database doesn’t work, since the table or query it is based on is not present. The Properties window lists “Install AOBT trial open actions” as record source, but there is no table or query of that name.

Reply | Quote

See attachment for any updated database.

Justin.

Reply | Quote

The database as posted won’t work, for the following reasons:

1. The table tblReports contains a name “AOBT open action report”, but there is no report of that name in the database. You are supposed to enter the names of the existing reports in this table.
2. The existing report “Install AOBT trial” has a record source “Install AOBT trial open actions”, but there is no table or query of that name in the database. You are supposed to set the record source to the name of an existing table or query.
3. The existing table “Install AOBT Trial” has no records, so any report based on it will be empty.

When I enter the name of the report in tblReports, and set the record source of the report to the existing table “Install AOBT Trial”, the code works correctly, in that it will print an empty report. So the problem you are having is not in the code itself.

Reply | Quote

Do you have any suggestions as to what l can do next?.

Justin.

Reply | Quote

Set the reports to print to PDF.

Reply | Quote

I have done this but get an error.
Any suggestions?

Justin.

Reply | Quote

See post 363811 higher up in this thread.

Reply | Quote

Please can you close this thread as l have resolved problem.

Justin.

Reply | Quote

Unlike some other message boards, we usually don’t “close” a thread in the Lounge.

I’m glad that you managed to resolve the problem. Could you tell us how (briefly), for the benefit of other Loungers who read this thread? Thank you.

Reply | Quote

Hans,

I have’nt actually resolved it. I feel it will take up to much time for me to resolve this issue and will need to spend more time on it to ensure it is fully working. When l have more time l will look into it more.

Justin.

Reply | Quote

Hans,

I have’nt actually resolved it. I feel it will take up to much time for me to resolve this issue and will need to spend more time on it to ensure it is fully working. When l have more time l will look into it more.

Justin.

Reply | Quote

Unlike some other message boards, we usually don’t “close” a thread in the Lounge.

I’m glad that you managed to resolve the problem. Could you tell us how (briefly), for the benefit of other Loungers who read this thread? Thank you.

Reply | Quote

Please can you close this thread as l have resolved problem.

Justin.

Reply | Quote

See post 363811 higher up in this thread.

Reply | Quote

I have done this but get an error.
Any suggestions?

Justin.

Reply | Quote

Set the reports to print to PDF.

Reply | Quote

Do you have any suggestions as to what l can do next?.

Justin.

Reply | Quote

The database as posted won’t work, for the following reasons:

1. The table tblReports contains a name “AOBT open action report”, but there is no report of that name in the database. You are supposed to enter the names of the existing reports in this table.
2. The existing report “Install AOBT trial” has a record source “Install AOBT trial open actions”, but there is no table or query of that name in the database. You are supposed to set the record source to the name of an existing table or query.
3. The existing table “Install AOBT Trial” has no records, so any report based on it will be empty.

When I enter the name of the report in tblReports, and set the record source of the report to the existing table “Install AOBT Trial”, the code works correctly, in that it will print an empty report. So the problem you are having is not in the code itself.

Reply | Quote

See attachment for any updated database.

Justin.

Reply | Quote

The record source is the table or query behind the report. You can see it in the Data tab of the Properties window.

The database you attached yesterday can’t be used for testing because:

1. The only report name in the table tblReports does not correspond to a report in the database.
2. The only report in the database doesn’t work, since the table or query it is based on is not present. The Properties window lists “Install AOBT trial open actions” as record source, but there is no table or query of that name.

Reply | Quote

Sorry don’t know quite what you mean by record source. Can you explain?

Justin

Reply | Quote

As far as I could see, the code would work as intended if there had been a record source for the report. But if you wish, you could include the record source for AOBT open action report, with one or two records.

Reply | Quote

What information do you require in order to test that it works?

Justin.

Reply | Quote

OK, but that makes it impossible to test anything. If I match the name in the table to that of the report, the code does try to open the report, then fails because the record source is not available. But it does not complain that the report can’t be found.

Reply | Quote

yes l know, this is a trimmed down version of the database.

Justin.

Reply | Quote

The only report in the database you attached is named Install AOBT trial. The only record in tblReports contains AOBT open action report.

Reply | Quote

See attachment.

Justin.

Reply | Quote

No errors, but you still get an error when trying to print the reports, I guess. Could you post a stripped down copy of the database? (same recipe as in the other thread)

Reply | Quote

After trying this l get no errors.

Any suggestions?
Justin.

Reply | Quote

See attachment for table in case l have made an error.

Justin.

Reply | Quote

Check very carefully that the name in the table is exactly the same as the name of the report in the database window.

Reply | Quote

I appear to be getting an error.
Can you tell me what it means.

Justin.

Reply | Quote

I have done this.
What is the next step?

Justin

Reply | Quote

1. Drag “Custom” from the File category to your toolbar. This will give you a toolbar button labeled “Custom”.
2. Right-click the new toolbar button, and select Properties.
3. Set the caption etc., and enter =PrintReports() in the On Action property.

See screenshot.

Reply | Quote

I fully understand what you have told me to do but cannot work out how l get the =PrintReports to work on a toolbar button.
I assume l call this from a macro. If that is the case call you tell me the macro l have to use.

Justin.

Reply | Quote

Create a table tblReports with a text field ReportName. Make its size large enough to accomodate the longest report name you have (or are likely to have.) Set the primary key of the table on ReportName.
Save the table design, then enter the names of the reports to be exported to PDF (each report in a separate record.)

Next. switch to the Modules section of the database window, and click New. Copy the following function into the module window:

Function PrintReports()
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strReportName As String

On Error GoTo ErrHandler

‘ Specify connection and open recordset
Set cnn = CurrentProject.Connection
rst.Open “tblReports”, cnn, adOpenForwardOnly

‘ Loop through records
Do While Not rst.EOF
strReportName = rst!ReportName
SysCmd acSysCmdSetStatus, “Processing ” & strReportName
DoCmd.OpenReport strReportName, acViewNormal
rst.MoveNext
Loop

ExitHandler:
‘ Clean up
On Error Resume Next
rst.Close
Set rst = Nothing
Set cnn = Nothing
Exit Function

ErrHandler:
‘ Inform user
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Function

Later, when you are prompted to save the module, click Yes and name it for example basReports. Don’t give it the same name as the function, that would confuse Access.

You can call this function from a toolbar button, or from the On Click event of a command button on a form. Set the On Action property of the toolbar button, or the On Click property of the command button to =PrintReports().

Note: I have assumed that the reports have already been set up to print to PDF.

Reply | Quote

The names need to printed with the .pdf extension at the end therefore l feel it would be best to use the second approach.

Justin.

Reply | Quote

You will have to specify which reports you want to print to PDF. There are several ways to do this:

• Use a prefix or postfix in the name of the reports, e.g. let the names of the reports to be printed to PDF begin with pdf_. Example: pdf_AOBT Trial.
• Create a table with a single text field. Enter the names of the reports to be printed to PDF (one record per report)
  [/list]There are other methods, some of them more “advanced”, but these are relatively easy. If you indicate which method you would like, we can take the next step.

Reply | Quote

Sorry for any confusion.

Yes l do have a macro to printout all the reports to PDF.
As it is very time consuming having to keep typing in all the names.
The reason l require this is that this task is something l do on a regular basis.

Yes l would like a macro to loop though a series of reports rather thn having to keep typing them in.
I don’t mind learning Access VBA.
What is the first step?

Justin.

Reply | Quote

Judging from the screenshots in your attachment, you already have a macro that prints all the reports, to PDF I presume. You didn’t like creating the macro? It would be possible to write VBA code to loop through a series of reports, but
a) You would need to have a way to specify which reports should be printed. This will involve handiwork too.
You would have to learn Access VBA.
But perhaps I’m still not understanding your question.

Reply | Quote

I appear to not have explained myself properly.

I would like to create a macro to run some PDF reports.
Rather than keep having to type them all in which is very time consuming.

Justin.

Reply | Quote

I don’t understand your sentence. Do you want a macro to create a macro?

Reply | Quote