Network And Print Page Setup-3

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 others 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 the topic of Columns Setting changes (File - -> Page Setup - -> Columns) through 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 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 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

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 MS-Access sample database Northwind.mdb.

1. Import (File - ->Get External Data - - >Import) the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb Sample Database.


2. Click on the Employees Table and select Report from Insert Menu.


3. 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.



4. Remove the check mark from Page Header/Footer Option of View Menu to remove the Page Header/Footer Sections from the Report Design.


5. Design a sample Label in the Detail Section of the Report as shown in the Image given below.






6. 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.


7. Display the Property Sheet of each Text Box (View - ->Properties) and write the expressions in the top and third Text Boxes and insert the Field names Address and Country in second and fourth Text Boxes respectively.


8. Click outside the Text boxes and drag the mouse over them to select all the four Text Boxes together.


9. Display the Property Sheet of the Text Boxes (View - -> Properties).


10. Change the Border Color Property value to 0.


11. Open the Page Setup Dialog Box from File Menu and select Columns Tab.


12. 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 across will be half an Inch wide. The vertical spacing between labels will be 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 translates into arranging the labels across then down order rather than printing the labels at the left column fully then flow out into the second column later (Down, then Across). We prefer the second option, which we will change through our Program.



13. You may set the Margin Values to 0.5 Inches on all four sides on the Margins Tab.


14. Select A4 Paper Size and Orientation to Portrait on the Page Tab and close the Page Setup Dialog Box.


15. Click on the Sorting and Grouping Toolbar Button above. You can find this icon at the top right end (next to the Tool Box Button icon) on the above image.


16. Select FirstName in the Field/Expressions Column and Ascending in the Sort Order Column.


17. Save the Report with the name MyLabels or any other name you prefer.


18. Open the Report in Print Preview and check the arrangement of Labels and values in them. The Employee Names were sorted in First Name Order on the Report. The Label arrangement is now Across, then Down order.


19. Close the Report Preview and open it in Design View.


20. Open the Page Setup Dialog Box and select the Columns Tab.


21. Change the Row Spacing Value to 0 Inch and the Column Spacing value to 0.1 Inch.


22. Leave the Column Layout Value (Across, then Down) setting as it is.


The Column Layout Value and the Column & Row Spacing Values we will change through Program.



23. 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.


24. 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 parameter when the User attempts to print Address Labels. This will ensure that the Address Labels are printed with 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.

---

Forms and Custom Properties
Control Tip Text and Time Delay
External Files List in Hyperlinks
Combo Box Column Values
Drill-Down Inquiry Screen-2