Need help with Access VBA code to modify printing an open form

Posted on 2014-08-27
Last Modified: 2016-02-11
Below is the code I have in place right now to print an open form:

Private Sub Command68_Click()
On Error GoTo Err_Command68_Click

    Printer.PaperSize = acPRPS11x17
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.PrintOut acSelection
 DoCmd.RunCommand acCmdZoom75
    Exit Sub

    MsgBox Err.Description
    Resume Exit_Command68_Click
End Sub

When I run this code I get a message "acCmdZoom75 is not currently available".  I am trying to reduce the print size of this form via this line of code but Access is not recognizing it.
Question by:CRGman
    LVL 33

    Expert Comment

    The code you are using refers to menu items in Access 95.  No one alive knows what item 8 on the Form bar is anymore and it was bad code when it was written because there were constants that should have been used instead of "8".  So, I would start again and rebuild the procedure.  Use the wizard if you can't write the VBA yourself and then convert it to VBA using the convert button on the ribbon.
    Here's a sample:
    Private Sub cmdPrint_Click()
    On Error GoTo cmdPrint_Click_Err
        DoCmd.SelectObject acForm, "Form1", True
        DoCmd.RunCommand acCmdPrint
        DoCmd.SelectObject acForm,, False
        Exit Sub
        MsgBox Err.Number & "--" & Err.Description
        Resume cmdPrint_Click_Exit
    End Sub

    Open in new window

    Your problem is most likely that the zoom method applies only to screen viewing.  It doesn't apply to printing.  Access doesn't have the great "fit to page" feature that Excel has.

    Your best solution would be to create a report and print that.  Forms are not optimized for printing and you'll have much better control if you make a report.

    Author Comment

    Thanks...unfortunately that is the answer I was expecting.   I will write a report and new code to print.
    LVL 31

    Expert Comment

    It isn't a good idea to print a form in any case.  The best solution is to create a report, then print it filtered by a value on the form.  See my Access Archon article on this topic:
    Here is a screen shot:
    Printing a Filtered ReportThe code on the form has two filtering methods.
    LVL 31

    Accepted Solution

    And here is a screen shot of the filtered report (for another customer):
    Filtered report

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
    Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now