Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23

    Create PDFs from a report

    I have been using Lebans code to print a report to PDF. This it does superbly.
    The database itself is very simple and consisits of just a few fields in one table.
    Essentially it is a list of 'equipment' and their 'location'. There are a few other fields but nothing major. In the report I have inserted the 'Location' field into the page header and set the Force New Page property to Before Section so that the location header title appears only once when the records spill over a single page. It gives me a nice report with a list of equipment separated into 'location' pages.
    I have trawled the net to try and undersatnd how to code my database to print each 'Location' as a separate PDF with the file name as the location. Currently Leban code does a fantastic job but just prints it as one file with the report title as the file name.
    I am about two steps from some form of institution and also a little punch drunk trying to get my head around it.

    Cheers

  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
    One method would be here (along with the link to the looping code), obviously substituting your export code for the emailing code:

    http://www.granite.ab.ca/access/emai...recipients.htm

  3. #3
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    Just tried it but I can not get it to work.
    I dont think that is going to do what i asked though is it?
    Any further thoughts?
    Thanks.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It isn't an out-of-the-box solution, no. As I said, you'd substitute your export code for the email specific code, but the technique to limit the report to the current location and the loop of those locations should work for you.

  5. #5
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    The code below refers to a form. Could I still use this with a report?
    Thanks

    Me.Filter = "ShipmentsID=" & Forms![Select Load List]![LoadID]
    Me.FilterOn = True

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sure; the code refers to a form, but in the linked example it is running in a report's open event. It filters the report as it opens, so it can be used when emailing or in your case exporting the report. This technique could also be used with a global variable instead of the form reference. In either case, during the recordset loop of locations, you'd set the form or variable to the current location. The report would look there to filter itself.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    Right.
    I get an VB error that says:
    "Compile error:
    User-defined type not defined"
    and the code halts and highlights the MyDB As Database from the line of code at the start which is in:
    Dim MyDB As Database, RS As Recordset
    Dim strBody As String, lngCount As Long, lngRSCount As Long

    Why is it going into error here. I cant see why.

    Thanks

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's probably old 97 code that they should update. You should disambiguate to:

    Dim MyDB As DAO.Database, RS As DAO.Recordset

    Depending on your version, you may first have to add the MS DAO reference in Tools/References.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    Great so far, thank you.
    Code progressed a little. It now hangs at RS.Edit
    Should that be EditMode?
    Cheers

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, but to be honest I would drop these 3 lines:

    RS.Edit
    RS("cpeDateTimeEmailed") = Now()
    RS.Update

    They are designed to update the database to keep track of when the email was sent, which is probably not relevant to your needs. If you want to keep it, what's the error? It could be you have a read-only query as the recordset source.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    While I am not going to try and email results just yet I might in the future.
    The error is a VB Error as before and it says:
    "Compile Error.
    Method or data member not found."

    Cheers again for your help.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    RS is declared as a DAO recordset? What happens if you just type "RS."? As soon as you type the dot, you should get a list of available methods. Edit should definitely be one of them. Can you post the db?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    The line I edited was also:
    "RS As DAO.Recordset" instead of "RS As Recordset"
    It now sequences through nicely. So thank you very very much.
    If I could, could I ask what is going on with the code at the stages I have highlighted in bold?

    Set RS = MyDB.OpenRecordset _
    ("Email - Outstanding Promos")
    lngRSCount = RS.RecordCount
    If lngRSCount = 0 Then
    MsgBox "No promo email messages to send.", vbInformation
    Else
    RS.MoveLast
    RS.MoveFirst
    Do Until RS.EOF
    lngCount = lngCount + 1
    lblStatus.Caption = "Writing Message " & CStr(lngCount) _
    & " of " & CStr(lngRSCount) & "..."
    strTo = RS!cEmailAddress
    intMessageID = Year(Now) & Month(Now) & Day(Now) & Fix(Timer) & "_MabryMail"

    ' Send the email using some technique or other

    RS.Edit
    RS("cpeDateTimeEmailed") = Now()
    RS.Update
    RS.MoveNext
    Loop


    The reason I enquire is that I assume it is appending a date stamp to something somewhere. I have created fields that are labled "cEmailAddress" and "cpeDateTimeEmailed". In the latter field the date stamp is getting inserted but where is the "_MabryMail" appearing? I have created a table with the above fields in called "Email - Outstanding Promos" which I assume the code is scanning to ascertain its values from for emailing out as such.

    If I were to put the emailing on hold for now, could I use this code to query my data and for each "Location" and then output to the Lebans code? If so how or where would I start?

    Again thanks for your help. Me off to bed as it is gone midnight here in the UK but hope you dont mind me tiying up with you tomorrow. Again, thanks for your guidance.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This line:

    strTo = RS!cEmailAddress

    is putting the current email address into a string variable, which would be used when the email is sent. They don't demonstrate that, they just say "Send the email using some technique or other". The other line builds a string that would look like:

    2010118123456_MabryMail

    Again, they don't demonstrate how they would use it, but presumably either for a file name or something. For what you're doing now, you just need a query that gets a listing of your locations. This code would loop through that query, and within the loop you'd have the Lebans code create a PDF for the current location.

    I understand the time issue; my daughter lives in the UK. I will certainly be around tomorrow for follow-up. You can dump anything email related, and where it says "Send the email using some technique or other", you'd have your Lebans code. Right before that you'd want to place the current location into either a form control or global variable, whichever you chose. Using the example from the site it would look like:

    Forms![Select Load List]![LoadID] = RS!LocationFieldName

    You've worked so hard, here's a snippet from an app of mine that does this type of thing:

    Code:
      Do While Not rs.EOF
        Forms!frmStatementFilter.txtEmailCust = rs!CustomerAccount
    
        blRet = ConvertReportToPDF(strReport, vbNullString, _
                                   "c:\" & rs!CustomerAccount & "Statement.pdf", False, False, 0, "", "", 0, 0)
        DoEvents
    
        'I have code here to send an email with the above attached
    
        rs.MoveNext
      Loop
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    Hi Paul,
    Am I right in assuming that your code:
    “Forms!frmStatementFilter.txtEmailCust = rs!CustomerAccount”
    is looking at a form called “frmStatementFilter” and specifically at the field “txtEmailCust” ??
    I can not quite understand what the rs!CustomerAccount element is doing.

    The description suggests that there is a filter of some sort. How are you doing this? I get the feeling it is not just a simple form. I have tried to create a form with the above name and fields but it throws up an error. The error is:
    “Error (2450) Microsoft Access can not find the form “frmStatementFilter” referred to in a macro expression or Visual Basic code.”
    Cheers
    Dave

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Create CrossTable Query report
    By ysrini in forum Reports
    Replies: 1
    Last Post: 02-19-2010, 11:20 AM
  2. How to create and print unique report for each entry???
    By Stelios Panayides in forum Reports
    Replies: 2
    Last Post: 10-05-2009, 08:54 AM
  3. Create a recordset for a report
    By vjboaz in forum Programming
    Replies: 0
    Last Post: 11-18-2008, 03:28 PM
  4. Create a date 'from' - 'to' on Report
    By Jule in forum Reports
    Replies: 7
    Last Post: 12-07-2006, 02:34 AM
  5. Create a calendar report?
    By valkyry in forum Reports
    Replies: 0
    Last Post: 09-14-2006, 11:34 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