Introduction
Shared MS-Access Reports are normally designed for a particular Printer on the Network. When Users attempt to print this Report on a different Printer the Report will change to the default Page Settings of that Printer, like Paper Size, Page Orientation, and the Report may not print correctly.
We have familiarized how to change Page Setup Values (Paper Size, Page Orientation, and Margin Settings) of Reports through VBA Programs before printing the Report on Network-based Printers in the earlier Posts.
This subject is not complete unless we touch on the topic of Columns Setting changes (File - - > Page Setup - - > Columns) through the Program for multi-column Reports.
The Columns Tab values of Page Setup Dialog Control are also modified through the PrtMip Property Values of Report. If you have gone through the earlier Post on Margin Settings then you have already created the User Defined data types to copy PrtMip Property Values into memory for modification. In that case, you need only a new Sub-Routine to modify the Report Column settings of a multi-column Report and open it in Print Preview.
For Readers not Visited earlier Pages
For the benefit of the readers who have landed on this page directly; I will reproduce the first part of the Code (declaring two User-defined data types segment) below.
Open the VBA Editing Window (Alt+F11); insert a new Standard Module (Global Module) - (Insert - - > Module), copy and paste the following Code (from both Code windows) into the Module immediately below the global declaration line Option Compare Database.
Those who have already copied the first part for the Margin Setting example take the code from the second Code window and paste it into the same Module where you have placed the data type declarations for PrtMip Property earlier. You can paste the code into a different Global Module if you prefer to do so.
Private Type str_PRTMIP strRGB As String * 28 End Type Private Type type_PRTMIP xLeftMargin As Long yTopMargin As Long xRightMargin As Long yBotMargin As Long fDataOnly As Long xWidth As Long yHeight As Long fDefaultSize As Long cxColumns As Long yColumnSpacing As Long xRowSpacing As Long rItemLayout As Long fFastPrint As Long fDatasheet As Long End Type
Public Sub PageColumns(ByVal strName As String) Dim PrtMipString As str_PRTMIP Dim PM As type_PRTMIP Dim rpt As Report Const PM_HORIZONTALCOLS = 1953 Const PM_VERTICALCOLS = 1954 Const TWIPS = 1440 ' Open the report. DoCmd.OpenReport strName, acDesign Set rpt = Reports(strName) PrtMipString.strRGB = rpt.PrtMip LSet PM = PrtMipString ' Create two columns. PM.cxColumns = 2 ' Set 0.25 inch between rows. PM.xRowSpacing = 0.25 * TWIPS ' Set 0.5 inch between columns. PM.yColumnSpacing = 0.5 * TWIPS PM.rItemLayout = PM_VERTICALCOLS ' Update property. LSet PrtMipString = PM rpt.PrtMip = PrtMipString.strRGB DoCmd.Close acReport, strName, acSaveYes DoCmd.OpenReport strName, acViewPreview Set rpt = Nothing End Sub
Create a Sample two Column Labels Report.
The next step is to create a multi-column sample Report to test our Program. We can design a Report for Address Labels with the Imported Employees Table from the MS-Access sample database Northwind.mdb.
Import (File - - >Get External Data - - >Import) the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb Sample Database.
Click on the Employees Table and select Report from Insert Menu.
Select Design View from the displayed list of options.
We are going to design Address Labels manually. If you prefer the Label Wizard in the displayed list you may use that instead.
Remove the checkmark from the Page Header/Footer Option of the View Menu to remove the Page Header/Footer Sections from the Report Design.
Design a sample Label in the Detail Section of the Report as shown in the Image given below.
Sample Label and Page Setup in one Image
The Design Task
Create four Text Boxes about the size of 2.75 inches long and 0.25 Inches high each and arrange them close by as shown on the design.
Display the Property Sheet of each Text Box (View - ->Properties) and write the expressions in the top and third TextBoxes and insert the Field names Address and Country in seconds and fourth Text Boxes respectively.
Click outside the Text boxes and drag the mouse over them to select all the four Text Boxes together.
Display the Property Sheet of the TextBoxes (View - -> Properties).
Change the Border Color Property value to 0.
Open the Page Setup Dialog Box from File Menu and select Columns Tab.
Change the values on the controls as shown in the image above.
Look carefully at the settings in the Columns Tab to understand what would be the result of those values, when the Labels are printed.
The Labels will be printed in two columns and the spacing between both Columns will be half an inch wide. The vertical spacing between labels will be a quarter of an inch. The Width of a Label is 3.25 Inches and 1.4 Inches high, including the blank spaces around the text boxes
The Column Layout (Across, then Down) setting is very important to note because the arrangement of Labels on the Report depends on it. The current settings translate into arranging the labels across then down order rather than printing the labels at the left column fully then flowing out into the second column later (Down, then Across). We prefer the second option, which we will change through our Program.
You may set the Margin Values to 0.5 Inches on all four sides of the Margins Tab.
Select A4 Paper Size and Orientation to Portrait on the Page Tab and close the Page Setup Dialog Box.
Click on the Sorting and Grouping Toolbar Button above. You can find this icon at the top right end (next to the ToolBox Button icon) on the above image.
Select FirstName in the Field/Expressions Column and Ascending in the Sort Order Column.
Save the Report with the name MyLabels or any other name you prefer.
Open the Report in Print Preview and check the arrangement of Labels and values in them. The Employee Names were sorted in the First Name Order on the Report. The Label arrangement is now Across, then Down order.
Preparing for Test Run of Program
Close the Report Preview and open it in Design View.
Open the Page Setup Dialog Box and select the Columns Tab.
Change the Row Spacing Value to 0 Inches and the Column Spacing value to 0.1 Inches.
Leave the Column Layout Value (Across, then Down) setting as it is.
The Column Layout Value and the Column & Row Spacing Values will change through Program.
You can run the Program PageColumns() directly from the Debug Window (Immediate Window) for testing. Press Alt+F11 Keyboard Shortcut to display the VBA Editing Window and press Ctrl+G to bring up the Debug window.
Type PageColumns "MyLabels" in the Debug Window and press Enter Key.
Through the PageColumns() Program we have now changed the Report Column Layout to Down, then Across, and the Column, Row spacing of Labels to the original settings we have initially specified on the Page Setup Dialog Box.
You can run the Program through a Button Click Event Procedure from your Main Switchboard by adding the following lines of sample code:
Private Sub cmdPreview_Click() PageColumns "YourReportName" End Sub
You can call PageColumns() Program and pass the Report Name as a parameter when the user attempts to Print Address Labels. This will ensure that the Address Labels are printed with the correct settings on any Printer on the Network.
NB: The User must select the printer (if she has more than one Printer installed on her machine) and set it up as Default Printer before attempting to print the Report.
No comments:
Post a Comment
Comments subject to moderation before publishing.