[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2623
  • Last Modified:

Setting Object Parameters for DoCmd.RunCommand acCmdPrint

I have a Report that is opened from a Form. The Form has 15 sub-Forms on it and the Report has 12 sub-Reports. If I try to Print the Report (the Form remains open) I get a message box telling me that I cannot open any more databases. The Report will Print, but the data for the sub-Reports is missing.
To get around this issue, I’ve put a “Print” cmdButton on the Form which Prints the Report without the error messages and the sub-Report data is shown.
The users of the From/Report are OK with this setup except that they want to be able to have the Print Dialog box show up so that they can chose  the number of copies to Print.
I’ve tried using DoCmd.RunCommand acCmdPrint which opens the dialog box, but it Prints the Form and not the Report. Is there any way to call the dialog box and set the object that it will print?
1 Solution
Chuck WoodCommented:

If all you need is to print more than one copy, you can do this in the code for the command button.

    Dim intCopies As Integer, intPrint As Integer
    intCopies = InputBox("How many copies do you want to Print?", "Copies?", "1")
    For intPrint = 1 To intCopies
        ' use your existing code to print a report here
    Next intPrint
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
DoCmd.RunCommand acCmdPrint prints whatever is Active at the time ... could you (a) open your Report and then (b) run this command? This should set your report as Active ... or perhaps set the focus to your report: Reports!YourReport.SetFocus

Whew ... 15 subforms and 12 subreports ... I pity the fool that has to wade thorough that <g> (apologies to Mr. T)
rm92650Author Commented:
Thanks, cwood-wm-com. Sometimes you just get too close to a project to see the obvious answers. The Input Box works very well for this application.
LSMConsulting—it’s not too complex. 11 of the 12 sub-Reports are simple lists of names that are assigned to a project for 11 different roles. There are 4 tables involved for each role/individual assigned to a job.  tblRole, tblIndividual, tblRole/individualAssignment and tblRole/Job. tblRole/individualAssignment is the Many table between the tblRole  and tblIndividual. The tblRole/individualAssignment ID number is used to assign individuals to a Job ID.
Thanks for the heads up on the acCmdPrint selecting the Active object.
To show the print dialogue and print the report instead of the form, you need to select the report first. The following code should do the trick:

DoCmd.SelectObject acReport, Screen.ActiveReport.Name
DoCmd.RunCommand acCmdPrint

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now