[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2014-08-27
Medium Priority
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
  • 2
  • 2
LVL 40

Expert Comment

ID: 40288015
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, Screen.ActiveForm.name, 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

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

Expert Comment

by:Helen Feddema
ID: 40290573
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

Helen Feddema earned 2000 total points
ID: 40290587
And here is a screen shot of the filtered report (for another customer):
Filtered report

Author Closing Comment

ID: 40800389

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

868 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