none
Saving a single form to pdf RRS feed

  • Question

  • I have attempted to adapt a piece of VBA code that I use for emailing a pdf of a single form in access, the email code works perfectly but the save version (not surprisingly) doesnt work, it says that the "Method or data member not found", the code is below;

    Private Sub Command133_Click()

    Dim myform As Form

    Dim pageno As Integer

    pageno = Me.CurrentRecord

    Set myform = Screen.ActiveForm

    DoCmd.SelectObject acForm, myform.Name, True

    DoCmd.OutputTo acFormatPDF, pageno, pageno, , 1

    DoCmd.SelectObject acForm, myform.Name, False

    End Sub

    Any help would be appreciated.

    Kind Regards

    Richard

    Friday, June 1, 2018 7:21 PM

Answers

  • Hi Richard,

    If the code you provided is the same form as what we've been working with, try this code instead:

    Dim lngID As Long
    
    lngID = Me.CustomerID
    
    Me.Filter = "CustomerID=" & lngID
    Me.FilterOn = True
    
    DoCmd.OutputTo acOutputForm, Me.Name, acFormatPDF, , True
    
    Me.FilterOn = False
    
    Me.Recordset.FindFirst "CustomerID=" & lngID

    Hope it helps...

    • Marked as answer by R.Nock Monday, June 4, 2018 5:28 PM
    Sunday, June 3, 2018 2:39 PM

All replies

  • Hi Richard,

    "Doesn't work" where? Which line is highlighted when you get the error?

    Friday, June 1, 2018 7:33 PM
  • The first line that reads Private Sub Command133_Click()
    Friday, June 1, 2018 7:37 PM
  • I would always recommend against printing or outputting a form, but instead output a report.  Forms are designed for use as a user interface with the data, whereas reports are designed for presentation of the data.

    You might like to take a look at InvoicePDF.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates how to output a report (the invoice) as a PDF file in a number of contexts, including automatically saving the file to a folder.

    Ken Sheridan, Stafford, England

    Friday, June 1, 2018 7:37 PM
  • The first line that reads Private Sub Command133_Click()

    Hi,

    Is that the answer to the first or second question or both?

    Do you know which "method" or "data member" it cannot find?

    Friday, June 1, 2018 7:41 PM
  • Hi,

    It was the answer to your question, the only line that is highlighted is the 1st line but it seems to go through everything before stating an issue, I see a brief file window that shows 1 file being made rather than compiling all records in the string so I am pretty sure that it is selecting the form I want to save it just cant cope with the writing of the file which is strange as I have no problem with the email version of the code so all I have tried to do is change email to save, and remove the email specific details.

    Kind Regards

    Richard

    Friday, June 1, 2018 7:49 PM
  • Hi Richard,

    Thanks for the clarification. Sounds like it's something we'll have to see to be able to help you. Have you tried stepping through the code? Also, you might try adding an error handler to see if you can trap where the problem is happening.

    Just my 2 cents...

    Friday, June 1, 2018 8:15 PM
  • Hi,

    This morning it has decided to highlight this line of code;

    Saturday, June 2, 2018 12:58 PM
  • Hi .theDBguy,

    Not sure if this needs to be asked in a separate thread but as you suggested earlier I looked into setting up a error handler, I found this article http://allenbrowne.com/ser-23a.html and followed every instruction and it isn't working, would you be able to let me know what I am doing wrong?

    Saturday, June 2, 2018 1:15 PM
  • Hi,

    This morning it has decided to highlight this line of code;

    Hi Richard,

    Let's see if we can fix the highlighted line first.

    Try changing it to this:

    DoCmd.OutputTo acOutputForm, Me.Name, acFormatPDF

    Let us  know what you get with that.

    Hope it helps...

    Saturday, June 2, 2018 2:32 PM
  • Hi .theDBguy,

    Not sure if this needs to be asked in a separate thread but as you suggested earlier I looked into setting up a error handler, I found this article http://allenbrowne.com/ser-23a.html and followed every instruction and it isn't working, would you be able to let me know what I am doing wrong?

    If we're able to fix the PDF problem with the above code changes, we could probably tackle the issue with the error handler in a separate thread. If we do, we'll need to see exactly what you tried.

    Cheers!

    Saturday, June 2, 2018 2:34 PM
  • Hi .theDBguy,

    I have managed to create a pdf but it is for the full database, can we tweak the code to only produce a single page for the current form?

    Saturday, June 2, 2018 4:41 PM
  • Hi .theDBguy,

    I have managed to create a pdf but it is for the full database, can we tweak the code to only produce a single page for the current form?

    Hi Richard,

    Glad to hear you're, at least, making progress. So, if we want to print just the current form, then you could try replacing the code with something like this:

    Dim lngID As Long
    
    lngID = Me.ID
    
    Me.Filter = "ID=" & lngID
    Me.FilterOn = True
    
    DoCmd.OutputTo acOutputForm, Me.Name, acFormatPDF, , True
    
    Me.FilterOn = False
    
    Me.Recordset.FindFirst "ID=" & lngID
    

    The above assumes your table/form has a primary key field called ID. If so, just replace it with the name of your ID field.

    Hope it helps...

    Saturday, June 2, 2018 5:35 PM
  • Hi .theDBguy,

    I have tried the code above and changed the ID for the one on the table but now get this error;

    Once I click OK the screen shows this;

    Saturday, June 2, 2018 6:00 PM
  • Hi Richard,

    Not sure if I mentioned this already but it is advisable to avoid using spaces when naming database objects.

    In any case, try enclosing the name of the field in square brackets. For example:

    lngID = Me.[Collection Reference]

    Hope it helps...

    Saturday, June 2, 2018 7:22 PM
  • Hi,

    I tried that and it brought up the same error, I renamed the box using an underscore instead of a space and tried removing the space entirely but got the same issue.

    I dont understand how it can work with saving a single form as a pdf and then emailing it but not just saving the single form as a pdf, perhaps I am being naive.

    Just in case I havent put it on before, here is the save as pdf then email code.

    Private Sub Command299_Click()
        Dim Subject As String
        Dim MyFileName As String

        On Error GoTo CmdEmailPDF_Click_Err

        Subject = Me.ID & "-" & "The Studio Enquiry Details" & ".pdf"
        MyFileName = Me.ID & "_" & "The Studio Enquiry Details" & ".pdf"

        'Open The Studio Enquiry Details in preview mode
        DoCmd.OpenForm "The Studio Enquiry Details", , , "[CustomerID]=" & Me.CustomerID

        'Send a PDF of the form to an email"
        DoCmd.SendObject acForm, "The Studio Enquiry Details", acFormatPDF, , _
            "info@studiowales.com", , Subject, "Here is your latest customer enquiry.", False
        'Close the form
        DoCmd.Close acForm, "The Studio Enquiry Details", acSaveNo
        Exit Sub

    CmdEmailPDF_Click_Err:
        MsgBox Error$
    End Sub

    I hope this helps get to the bottom of it.

    Sunday, June 3, 2018 1:43 PM
  • Hi Richard,

    If the code you provided is the same form as what we've been working with, try this code instead:

    Dim lngID As Long
    
    lngID = Me.CustomerID
    
    Me.Filter = "CustomerID=" & lngID
    Me.FilterOn = True
    
    DoCmd.OutputTo acOutputForm, Me.Name, acFormatPDF, , True
    
    Me.FilterOn = False
    
    Me.Recordset.FindFirst "CustomerID=" & lngID

    Hope it helps...

    • Marked as answer by R.Nock Monday, June 4, 2018 5:28 PM
    Sunday, June 3, 2018 2:39 PM
  • Hi .theDBguy,

    No joy I'm afraid, the code I uploaded was used on a seperate database that I created a few months ago so I have changed the Customer ID to CollectionReference.

    I have put screen shots of the error code and highlighted line below;

    It seems like I've found a difficult one this time.

    Sunday, June 3, 2018 4:44 PM
  • Hi Richard,

    The error message "Invalid use of Null" implies there is no current value assigned to CollectionReference. If this is your table's Primary Key, and you're looking at an existing record on the form when you click the button, then it would be impossible because a Primary Key cannot have Null values.

    So, either CollectionReference is not the primary key of your table, or you're looking at a blank form

    Can you post a screenshot of your table's design view?

    Are you able to share a sample copy of your database with just test data?

    Sunday, June 3, 2018 6:51 PM
  • Hi .theDBguy,

    I am such an idiot, I tried it on another form and it worked perfectly!

    I bet you're getting sick of me, I know I do sometimes :)

    Thank you for your help.

    Kindest Regards

    Richard

    Monday, June 4, 2018 5:24 PM
  • Hi Richard,

    Congratulations! Glad to hear you got it to work. Good luck with your project.

    Monday, June 4, 2018 5:31 PM