Results 1 to 5 of 5
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    export report to pdf by cust_id


    All using Access 2016. I am trying to export a rpt to pdf but want it to create separate files filtered on cust_id. But when I run the code; it throws an error: sub or function not defined. My code is as follows:

    First; Declared a variable in a Module (as Public):

    Code:
    Option Compare Database
    Public strRptFilter As String
    In the Open event of my report:

    Code:
    If Len(strRptFilter) <> 0 Then
         Me.Filter = strRptFilter
         Me.FilterOn = True
    End If
    In the Close event of the report:
    Code:
    strRptFilter = vbNullString

    On click of the report( created a cmd button)
    [CODE]
    Code:
    Private Sub cmdRptToPDF_Click()
    Dim rst As DAO.Recordset
     
    set rst = currentdb.openrecordset("SELECT DISTINCT [cust_id FROM [tblRecon] WHERE ... ORDER BY [cust_id;", dbOpenSnapshot)
     
    do while not rst.eof
        strRptFilter = "[cust_id] = " & rst![cust_id]
     
        DoCmd.OutputTo acOutputReport, "rptRecon", acFormatPDF, "C:\Users\slimjen\Desktop\Recon\" & "\" & rst![cust_id] & ".pdf"
        doevents
        rst.movenext
    loop
     
    rst.close
    set rst = nothing
    End Sub
    Please tell me what I am missing?? I tried adding "Public strRptFilter as String" before my Dim statement but to no avail. thanks in advance.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What line is highlighted if you click debug? Your SQL isn't valid, but I assume you shortened it for posting. Is that the actual code? I'd expect certain capitalization that isn't there.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The public module doesn't have the same name as the variable does it?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thanks for replying. For some reason; maybe that I compacted the database; I don't get the error anymore. The code works to the extent where makes a PDF for each custid but still includes all the cust id files in one PDF. Yes; this is the actual code. No the public module does not have the same name as the variable.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd set breakpoint or use Debug.Print to check the value of the variable.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export Report to PDF
    By Ganymede in forum Import/Export Data
    Replies: 2
    Last Post: 03-16-2016, 08:55 AM
  2. Export Report to PDF
    By cuddles in forum Reports
    Replies: 10
    Last Post: 05-21-2014, 09:28 AM
  3. Macro of VBA to Export Report as PDF
    By buck in forum Macros
    Replies: 6
    Last Post: 12-26-2013, 02:44 PM
  4. export report
    By slimjen in forum Forms
    Replies: 7
    Last Post: 04-24-2013, 08:14 AM
  5. Export report to jpg
    By isdm in forum Reports
    Replies: 1
    Last Post: 04-16-2010, 10:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums