Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PDF reports macro (2000)

    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.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PDF reports macro (2000)

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

  3. #3
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PDF reports macro (2000)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PDF reports macro (2000)

    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.
    [img]/forums/images/smilies/cool.gif[/img] You would have to learn Access VBA.
    But perhaps I'm still not understanding your question.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PDF reports macro (2000)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PDF reports macro (2000)

    You will have to specify which reports you want to print to PDF. There are several ways to do this:
    <UL><LI>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.
    <LI>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.

  7. #7
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PDF reports macro (2000)

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PDF reports macro (2000)

    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.

  9. #9
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PDF reports macro (2000)

    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.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PDF reports macro (2000)

    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.
    Attached Images Attached Images
    • File Type: png x.PNG (10.8 KB, 0 views)

  11. #11
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PDF reports macro (2000)

    I have done this.
    What is the next step?

    Justin

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PDF reports macro (2000)

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

  13. #13
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PDF reports macro (2000)

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

    Justin.
    Attached Images Attached Images

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PDF reports macro (2000)

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

  15. #15
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PDF reports macro (2000)

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

    Justin.
    Attached Images Attached Images

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •