Results 1 to 5 of 5
  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188

    RunCode - Module to print PDF

    I had the below Module function from another database. The intent is to print a PDF report directly to a folder without prompt. It works great in the original database.



    I tried morphing for use in my current database. Because I wanted to use this again in other scenarios, I added a couple variables. I'm getting the attached error where it's asking for a macro. A macro is not in the equation so I'm guessing I have the syntax wrong. Can anyone give me a clue as to what I'm doing wrong?

    I added these:
    PackListQuery = "qry_PackingList_BBB_CA"
    ReportName = "Packing List BBB CA"

    Code:
    Public Function BBB_CA()
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim MyFileNam As String
        Dim mypath As String
        Dim temp As String
        Dim PackListQuery As String
        Dim ReportName As String
        mypath = "S:\Order Imports\"
        PackListQuery = "qry_PackingList_BBB_CA"
        ReportName = "Packing List BBB CA"
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("SELECT Distinct [FileNam] FROM PackListQuery", dbOpenSnapshot)
        Do While Not rs.EOF
            temp = rs("FileNam")
            MyFileNam = rs("FileNam") & ".pdf"
            DoCmd.OpenReport ReportName, acViewPreview, , "[FileNam]='" & temp & "'"
            DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileNam
            DoCmd.Close acReport, ReportName
            DoEvents
            rs.MoveNext
        Loop
    
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End Function
    Thanks!
    Attached Thumbnails Attached Thumbnails S1.jpg   S2.jpg   S3.jpg  

  2. #2
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Ugh! I think I found my 2 mistakes.

    1) Correct Syntax should be: Set rs = db.OpenRecordset("SELECT Distinct [FileNam] FROM " & PackListQuery, dbOpenSnapshot)

    2) I found a support case noting the Module name cannot be the same as the Function name.

    I think I've got it.

    Thanks!

  3. #3
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    I need some hand holding on the below syntax. I'd like to change some things, but need to understand a couple lines in the below.

    Going line by line...
    temp = rs("FileNam") - this is from the recordset - OK
    MyFileNam = rs("FileNam") & ".pdf" - this is going to be my filename - OK

    I don't understand the below line. It looks like it translates to: rs[FileName]= temp
    DoCmd.OpenReport ReportName, acViewPreview, , "[FileNam]='" & temp & "'"

    In my mind I think this should work:
    DoCmd.OpenReport ReportName, acViewPreview, , MyFileNam ... but it doesn't. Why?

    Code:
    Public Function BBB_CA()
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim MyFileNam As String
        Dim mypath As String
        Dim temp As String
        Dim PackListQuery As String
        Dim ReportName As String
        PackListQuery = "qry_PackingList_BBB_CA"
        ReportName = "Packing List BBB CA"
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("SELECT Distinct [FileNam] FROM " & PackListQuery, dbOpenSnapshot)
        Do While Not rs.EOF
            temp = rs("FileNam")
            MyFileNam = rs("FileNam") & ".pdf"
            'acViewPreview = Print Preview, acViewReport = Direct to Printer
            DoCmd.OpenReport ReportName, acViewPreview, , "[FileNam]='" & temp & "'"
            DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyFileNam
            DoCmd.Close acReport, ReportName
            DoEvents
            rs.MoveNext
        Loop
    
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End Function
    thanks!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    DoCmd.OpenReport ReportName, acViewPreview, , "[FileNam]='" & temp & "'"

    opens a report filtered on criteria. [FileNam] is field in report RecordSource, so filter on that field - retrieve records where FileNam is equal to value of temp. This is same as using WHERE clause in a full SQL statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    That clears it up. In the original application [FileName] would have more than one value and it would loop through each printing a report for each value.

    This application will always have only one value.

    Much appreciated!

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

Similar Threads

  1. Replies: 5
    Last Post: 07-29-2014, 01:05 PM
  2. Using RunCode Macro
    By boywonder in forum Macros
    Replies: 5
    Last Post: 01-13-2014, 06:22 AM
  3. class module vs regular module
    By Madmax in forum Modules
    Replies: 1
    Last Post: 05-01-2012, 03:44 PM
  4. Replies: 4
    Last Post: 05-16-2011, 04:58 PM
  5. RunCode in macro not working
    By LilMissAttack in forum Access
    Replies: 0
    Last Post: 05-27-2010, 05:01 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