LEARN MS-ACCESS TIPS AND TRICKS: msaccess reports

Showing posts with label msaccess reports. Show all posts

Form and Report Open Arguments

a.p.r. pillaiFebruary 05, 2010msaccess reports, Reports 3 comments

Form and Report Open Arguments.

When opening a Report or Form, you can pass several optional parameters as run-time arguments. These arguments help control the behavior of the report or form, such as filtering the output or changing the form’s open mode depending on the user’s profile.

If the current user belongs to a specific User Group with read-only privileges, then the Form can be opened automatically in Read-Only Mode.

If the user does not belong to that group, the form can open in Normal Mode.

The following VBA function, CheckGroup(), checks whether the current user belongs to a specified User Group. Copy the code for this function into a Standard Module.

The CheckGroup() Function.

Public Function CheckGroup(ByVal strUsr As String, grpName As String) As String
'-----------------------------------------------------
'Author : a.p.r. pillai
'Date   : Feb-2010
'URL    : www.msaccesstips.com
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------
Dim wsp As Workspace
Dim GrpArray() As Variant, grpcnt As Integer
Dim GrpOut As Variant, j As Integer

Set wsp = DBEngine.Workspaces(0)

grpcnt = wsp.Users(strUsr).Groups.Count - 1
ReDim GrpArray(0 To grpcnt) As Variant

'User may belong to more than one User Group
'Create an Array of Group Names
For j = 0 To grpcnt
    GrpArray(j) = wsp.Users(strUsr).Groups(j).Name
Next

'Compare Admins with the Array List
'if matches then 'Admins' will be output in grpout Array
GrpOut = Filter(GrpArray(), grpName, True)

CheckGroup = GrpOut(0)

End Function

The CheckGroup() function should be called from a Command Button’s Click event procedure (we will implement it shortly). This function checks the current user’s group and returns the User Group name, which can then be used to open the form in a specific mode.

Private Sub cmdOpenForm_Click()
Dim strGrp

strGrp = CheckGroup(CurrentUser, "Admins")

If strGrp = "Admins" Then
    DoCmd.OpenForm "Products", acNormal, , , acFormReadOnly
Else
    DoCmd.OpenForm "Products", acNormal
End If

End Sub

The CheckGroup() program creates a list of workgroups, checks whether the current user belongs to the Admins Group, and returns the result. If the result is Admins, the Products Form opens in Read-Only Mode; otherwise, it opens in Normal Mode.

Creating the Workgroups array is necessary because a single user can belong to multiple workgroups, such as Admins, Users (default), Supervisor, Manager, Editor, or any other group defined in the Workgroup Information File (.mdw).

The Filter() function searches the array for the text "Admins". If it is found, the value is stored in the GrpOut(0) element.

We cannot use the Filter() function inside a form module subroutine, because it conflicts with the form’s Filter property.

Regarding the Open arguments for forms and reports, you can pass the name of a query as a Filter argument or a WHERE condition (without including the word WHERE).

In addition, there is another parameter called OpenArgs, which allows you to pass a value to a form or report. You can then read this value in the class module of the form or report using the same OpenArgs variable, and use it for any purpose for which it was passed.

OpnArgs Example.

We try out a simple example to learn the usage of a OpnArgs parameter. We need a few objects from the C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.

Import the following from the Northwind.mdb sample database:
- Table: Products
- Table: Categories
- Query: Products by Category
- Report: Products by Category
Open a new Form and create a Combo Box with the Category Name from the Categories Table.

A sample Form image is given below for reference.
Right-click to open Large Image in New Tab/Window.
Select the Combo Box and display its Property Sheet (View -> Properties or press Alt+Enter).
Change the Name Property value to cboCategory.
Create a Command Button and change its Name Property value to cmdOpen and the Caption Property Value to Open Report.
Select Event Procedure in the On Click Event Property and click on the build (...) Button to open the Class Module of the Form with the empty Sub-Routine lines.

Copy and paste the following VBA code, overwriting the existing line, or copy and paste the middle line alone:

Private Sub cmdOPen_Click()
   DoCmd.OpenReport "Products by Category", acViewPreview, , , , Nz(Me!cboCategory, "")
End Sub

Save the Form with the name Open Argument Demo or any other name you prefer.

Modify the Report Module.
Open the Products by Category Report in Design View.
Display the Class Module (View -> Code)

Copy and Paste the following Code into the Class Module:

Private Sub Report_Open(Cancel As Integer)
Dim strFilter As String

If IsNull([OpenArgs]) Then
   Exit Sub
End If

Report.Title.Caption = Report.Title.Caption & " (" & [OpenArgs] & ")"
strFilter = "CategoryName = '" & [OpenArgs] & "'"
Report.Filter = strFilter
Report.FilterOn = True

End Sub

Save the Report with the Code.
Open the Open Argument Demo Form in Normal View.
Select a Product Category Name (say Beverages) in the Combo Box.
Click on the Open Report Command Button.
The Products by Category Report will open with only Beverage Report items, and the heading label is modified to show the Product Category Name.
Delete the Combo Box's current value and click the Open Report Command Button.

This time, all product categories will appear in the report, and the heading will remain unchanged.

In the Report_Open event procedure, we check whether the OpenArgs variable contains a value. If it is Null, the subroutine terminates immediately.

Trial Run of First Two Programs.

Do the following to try the first two Programs given at the top of this page:

Open the Open Argument Demo Form in Design View.
Create a second Command Button on the Form.
Display the Property Sheet of the Command Button (View -> Properties).
Change the Name Property value to cmdOpenForm, and the Caption Property Value to Open Form.
Display the Class Module (View -> Code).
Copy and paste the second Program from the top into the Module and save the Open Argument Demo Form.
Create a Tabular Type Form for the Products Table and save the Form as Products.
Open the Open Argument Demo Form and click on the Open Form Command Button.
If you have not implemented Microsoft Access Security, you are by default the Admin User, a member of the Admins Group, and the Products Form will open in Read-Only mode.

Network And Print Page Setup-3

a.p.r. pillaiJune 19, 2009msaccess reports, Reports No comments

Network And Print Page Setup-3.

Shared MS Access Reports are typically designed for a specific printer on the network. When users attempt to print the report to a different printer, the report automatically adopts that printer’s default page settings—such as paper size, page orientation, and margins—which may cause the report to print incorrectly.

In earlier posts, we learned how to modify page setup values (paper size, page orientation, and margin settings) of reports through VBA code before printing them on network-based printers.

However, the topic remains incomplete without discussing how to change column settings (accessible via File → Page Setup → Columns) programmatically for multi-column reports.

The column settings defined in the Columns tab in the Page Setup dialog are controlled by the PrtMip property of the report. If you followed the previous tutorial on margin settings, you have already created the user-defined data types needed to copy and modify the PrtMip property values in memory. In that case, all you need now is a new Subroutine that adjusts the report’s column settings for a multi-column layout and opens it in Print Preview.

Readers have not visited the earlier Pages.

For the benefit of readers who have arrived directly on this page, I will reproduce the first part of the code below—the segment that declares the two user-defined data types.

Open the VBA Editor (press Alt + F11), insert a new Standard Module (Global Module) by selecting Insert → Module, and then copy and paste the following code (from both code windows) into the module, immediately below the global declaration line:


Option Compare Database

If you have already copied this first part while working on the Margin Settings example, then simply take the code from the second code window below and paste it into the same module where you previously placed the PrtMip property data type declarations.

Alternatively, you may paste it into a different Global Module if you prefer to keep the code segments separate.

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’ll design an Address Labels report using the Employees table imported from the MS Access sample database Northwind.mdb.

Import the Employees Table:
Go to File → Get External Data → Import and import the Employees table from:
```
C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb
```
Create the Report:
Click on the Employees table, then select Report from the Insert menu.
Choose the Design View:
From the list of displayed options, select Design View.
Set Up the Report Layout:
We will design the address labels manually. However, you may use the Label Wizard instead.
Remove Page Header/Footer:
From the View menu, uncheck the Page Header/Footer option to remove those sections from the report design.
Design the Label:
Create a sample label layout in the Detail section of the report, as shown in the image below.

Sample Label and Page Setup in one Image.

Right-click to open Large Image in New Tab/Window.

The Design Task.
Create four text boxes, each approximately 2.75 inches wide and 0.25 inches high, and arrange them close together as shown in the design layout.
Open the Property Sheet for each text box (View → Properties) and do the following:
- In the first and third text boxes, enter the appropriate expressions.
- In the second and fourth text boxes, set the Control Source to the Address and Country fields, respectively.
Click outside the Text boxes and drag the mouse over them to select all four Text Boxes together.
Display the TextBox Property Sheet (View -> Properties).
Change the Border Color Property value to 0.
Open the Page Setup Dialog Box from the File Menu and select the Columns Tab.
Change the values on the controls as shown in the image above.
Look closely at the Columns Tab settings to understand how these values affect the printed layout of the labels.
The labels will be printed in two columns, with half an inch of horizontal spacing between them. Set the vertical spacing between labels to a quarter of an inch. Each label is 3.25 inches wide and 1.4 inches high, including the blank space surrounding the text boxes.

The Column Layout setting—Across, then Down-determines how the labels are arranged on the report. With the current setting, labels are placed across the page first, and then continue down to the next row. However, we prefer the opposite layout—Down, then Across—so that the labels fill the left column first before continuing into the second column. We’ll modify this behavior 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 (next to the Toolbox Button icon) in 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, 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.
The Column Layout Value and the Column & Row Spacing Values will change through the 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 the Enter Key.

Using the PageColumns() program, we have now changed the report’s column layout to Down, Across, and restored the column and row spacing of the labels to the original values specified earlier in 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 the 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 more than one Printer is installed on the user's machine) and set it up as the Default Printer before attempting to print the Report.

Network And Print Page Setup-2

a.p.r. pillaiJune 12, 2009msaccess reports, Reports No comments

Continued from Last Week's Page.

In an earlier article, we discussed how to automatically change the paper size and page orientation of MS Access reports through VBA code for any printer on the network. We achieved this by copying the PrtDevMode property values in memory, modifying them to match the desired paper size and page orientation, and then updating them back into the report’s page settings before printing it on the user’s default printer.

Working with the PrtMip Property.

We will have a similar exercise to change the Margin Settings of the MS-Access Report through the program. This time, we need to work with the PrtMip Property of the Report to change the Margin Values.

The procedure is similar to the previous example. The steps taken in the Program are as follows:

Open the Report in Design View.
Copy the PrtMip Property Values of the Report into a 28-byte-long String Variable and move it into a redefined structured data area for modification.
Change the required Margin Values in Memory.
Update them back into the Report's PrtMip Property.
Save the Report with the changes and open it in Print Preview.

Prepare for a Demo Run.

So let us start.

Open one of your Databases with Reports in it.
Display the Visual Basic Editing Window (Alt+F11).
Insert (Insert -> Module) a new Standard Module (Global Module).

Copy and paste the following code into the new Module and save it.

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 SetMargins(ByVal strName As String)

Dim PrtMipString As str_PRTMIP
Dim PM As type_PRTMIP
Dim rpt As Report
Const TWIPS As Long = 1440
    ' Open the report.
    DoCmd.OpenReport strName, acDesign
    Set rpt = Reports(strName)
    PrtMipString.strRGB = rpt.PrtMip
    LSet PM = PrtMipString

    ' Set margins.
    PM.xLeftMargin = 0.75 * TWIPS
    PM.yTopMargin = 0.5 * TWIPS
    PM.xRightMargin = 0.5 * TWIPS
    PM.yBotMargin = 0.5 * TWIPS

   ' Update property.
    LSet PrtMipString = PM
    rpt.PrtMip = PrtMipString.strRGB

    DoCmd.Close acReport, strName, acSaveYes
    DoCmd.OpenReport strName, acViewPreview

    Set rpt = Nothing

End Sub

Open one of your existing Reports in Design View.
Select File -> Page Setup -> Margins.
Change all four sides (Left, Right, Top, and Bottom) of the Margin settings to 1 Inch.
Save and Close the Report.
Open the Main Switchboard Form of your Application or create a new Form.
Create a new Command Button on the Form.
While the Command Button is in the selected state, display the Property Sheet (View ->Properties).
Change the Name Property Value to cmdPreview and change the Caption Property Value to Print Preview.
Display the Code Module of the Form (View -> Code).
Copy and paste the following lines into the Code Module of the Form.
```
Private Sub cmdPreview_Click()
  SetMargins "MyReport"
 End Sub
```
Replace the name MyReport with your own Report Name.
Save and close the Form.
Open the Form in normal view and click on the Command Button to run the Program and change all four margins of the Report to new values, and open the Report in Print Preview.

Close the Report and open it again in Design View and check whether the margin settings have really changed through the program or not.

Note: If any value falls below the allowable range defined by the printer driver settings, the printer may automatically adjust it to the minimum acceptable value. As a result, you might notice that some settings appear higher than the values you originally specified.

Running SetMargins() Program from PaperandOrient() Sub-Routine.

You can do a sample run of the Program by typing SetMargins "YourReportName" in the Debug Window directly, without the use of a Form or a Command Button.

You can run this program from within the earlier PaperAndOrient() Program to change the Margins also along with the Paper Size and Page Orientation. All three sets of values can be changed by calling the PaperAndOrient() Program alone.

The modified PaperAndOrient() Program is given below:

Public Sub PaperAndOrient(ByVal strName As String)
    Const DM_PORTRAIT = 1
    Const DM_LANDSCAPE = 2
    Const DM_PAPERSIZE = 9
    Dim DevString As str_DEVMODE
    Dim DM As type_DEVMODE
    Dim strDevModeExtra As String
    Dim rpt As Report

   ' Opens report in Design view.
    DoCmd.OpenReport strName, acDesign
    Set rpt = Reports(strName)

    If Not IsNull(rpt.PrtDevMode) Then
        strDevModeExtra = rpt.PrtDevMode
        DevString.RGB = strDevModeExtra
        LSet DM = DevString
        DM.lngFields = DM.lngFields Or DM.intOrientation

        ' Initialize fields.
        DM.intPaperSize = DM_PAPERSIZE
        If DM.intOrientation = DM_PORTRAIT Then
            DM.intOrientation = DM_LANDSCAPE
        End If

       ' Update property.
        LSet DevString = DM
        Mid(strDevModeExtra, 1, 94) = DevString.RGB
        rpt.PrtDevMode = strDevModeExtra
    End If
    SetMargins strName
    DoCmd.Close acReport, strName, acSaveYes
    DoCmd.OpenReport strName, acViewPreview
    Set rpt = Nothing

End Sub

Public Sub SetMargins(ByVal strName As String)
    Dim PrtMipString As str_PRTMIP
    Dim PM As type_PRTMIP
    Dim rprt As Report
    Const TWIPS As Long = 1440

    Set rprt = Reports(strName)
    PrtMipString.strRGB = rprt.PrtMip
    LSet PM = PrtMipString

   ' Set margins.
    PM.xLeftMargin = 0.75 * TWIPS
    PM.yTopMargin = 0.5 * TWIPS
    PM.xRightMargin = 0.5 * TWIPS
    PM.yBotMargin = 0.5 * TWIPS

   ' Update property.
    LSet PrtMipString = PM
    rprt.PrtMip = PrtMipString.strRGB

    Set rprt = Nothing

End Sub

The Measurement Unit is Twips.

The measurements of reports and their objects are handled internally in twips, rather than in inches or millimeters. Although you can specify measurements in standard units such as inches, centimeters, or other regional formats on the property sheets of reports, forms, or controls, MS Access automatically converts these values into twips internally. However, when working in VBA, this conversion must be done manually before changing the property values of objects.

1 Inch = 1440 Twips
1 Inch = 72 Points
1 Point = 20 Twips OR 1 Twip = 1/20 Point

For simplicity, we have used constant values in the program for page size, orientation, and margins. However, you can modify the code to pass these values as parameters—along with the report name—when calling the program for each report. This approach offers greater flexibility, allowing the same program to handle reports with different page settings.

Next, we’ll explore how to modify the values on the Columns tab of the Page Setup dialog box, available from the File menu.

Earlier Post Link References:

Network And Report Page Setup

a.p.r. pillaiJune 05, 2009msaccess reports, Reports No comments

Network And Report Page Setup.

When an MS Access Application is installed on a network, security is one of the major issues that the Database Developer has to tackle. This includes the security of data, objects within the database, and the Database file itself. To learn more about securing a database on a Network, visit the following link:

Microsoft Access Security.

We’re now going to address another common issue often faced by users, which is usually resolved temporarily through alternative methods. When MS Access reports are designed for a specific printer on the network and all users share that printer, there are generally no problems. However, if users attempt to print the report on a different printer—either another network printer or a local printer—the report may not print correctly. Differences in default paper size, page orientation, or margin settings between printers can cause the report layout to appear incorrect when printed.

To work around this issue, users typically need to preview the report, open the Page Setup menu if necessary, and manually adjust the paper size, page orientation (portrait or landscape), and margins before printing. This option is only available if the Report Page Setup command is accessible to the user. If the application uses customized menus and toolbars, this option may not be visible, complicating the process further.

For more details on Customized Menus and Toolbars, visit the following Links:

Reports PrtDevMode Property.

To make printing easier for users, we can modify the PrtDevMode property of a report with VBA to automatically adjust critical parameters—such as paper size, page orientation (portrait or landscape), and margins—before sending the report to the printer. This ensures that the report prints correctly on any printer.

The PrtDevMode property is a 94-byte structure containing multiple parameters that can be modified via code to control the printer behaviour.

For this example, we’ll focus on two simple parameters for our report. Our sample report is designed in landscape mode and is to be printed on A4 paper (210 × 297 mm). We must modify the following member parameters of the PrtDevMode property of the default printer:

Orientation - Valid Values: 1 = Portrait, 2 = Landscape.
PaperSize 9 = A4 (210 x 297 mm)

Working with the Report.PrtDevMode Property Values

The above options (Orientation and Paper size) appear on the Page tab in the Page Setup Dialog box in the File Menu. We are trying to change these values at run-time through the Program.

Open a new Standard Module (Global Module) in your Database, and copy the following code into the module and save it.

Private Type str_DEVMODE
    RGB As String * 94
End Type

Private Type type_DEVMODE
    strDeviceName As String * 16
    intSpecVersion As Integer
    intDriverVersion As Integer
    intSize As Integer
    intDriverExtra As Integer
    lngFields As Long
    intOrientation As Integer
    intPaperSize As Integer
    intPaperLength As Integer
    intPaperWidth As Integer
    intScale As Integer
    intCopies As Integer
    intDefaultSource As Integer
    intPrintQuality As Integer
    intColor As Integer
    intDuplex As Integer
    intResolution As Integer
    intTTOption As Integer
    intCollate As Integer
    strFormName As String * 16
    lngPad As Long
    lngBits As Long
    lngPW As Long
    lngPH As Long
    lngDFI As Long
    lngDFr As Long
End Type

Public Sub PaperAndOrient(ByVal strName As String)
    Const DM_PORTRAIT = 1
    Const DM_LANDSCAPE = 2
    Const DM_PAPERSIZE = 9
    Dim DevString As str_DEVMODE
    Dim DM As type_DEVMODE
    Dim strDevModeExtra As String
    Dim rpt As Report

   ' Opens report in Design view.
    DoCmd.OpenReport strName, acDesign
    Set rpt = Reports(strName)

    If Not IsNull(rpt.PrtDevMode) Then
        strDevModeExtra = rpt.PrtDevMode
        DevString.RGB = strDevModeExtra
        LSet DM = DevString
        DM.lngFields = DM.lngFields Or DM.intOrientation
        'Initialize fields.
        DM.intPaperSize = DM_PAPERSIZE
        If DM.intOrientation = DM_PORTRAIT Then
            DM.intOrientation = DM_LANDSCAPE
        End If

        ' Update property.
        LSet DevString = DM
        Mid(strDevModeExtra, 1, 94) = DevString.RGB
        rpt.PrtDevMode = strDevModeExtra
    End If
    DoCmd.Close acReport, strName, acSaveYes
    DoCmd.OpenReport strName, acViewPreview
    Set rpt = Nothing

End Sub

The User-Defined Types str_DEVMODE and Type_DEVMODE

At the beginning of the code, two new user-defined data types, str_DEVMODE and Type_DEVMODE, are declared. The report’s PrtDevMode property value is copied into this structured data area, allowing us to modify specific elements and update them back into the report before printing.

Within str_DEVMODE, named RGB is defined as a 94-byte string. This 94-byte area contains 26 parameters of various data types and sizes, which are individually defined under the type_DEVMODE structure. By transferring the data from str_DEVMODE (a single block of 94 characters) into type_DEVMODE, we can modify individual parameter values before writing them back into the report’s Page Setup.

NB: If the Database is implemented with Microsoft Access Security, then all Users must have the Report Design Change Authority to run this procedure.

Preparing for a Trial Run.

To try out our Program, open one of your Reports with Landscape Page Orientation in Design View.
Select Page Setup from the File Menu.
Select the Page Tab on the Dialog Box.
Change Orientation to Portrait.
Change Paper Size to A6.
Save the Report and open it in Print Preview to check how it looks with the change.
Close the Report after viewing.
Create a Command Button on an existing Form or on a new Form and keep the Form in Design View.
Display the Property Sheet of the Command Button (Alt+Enter or View -> Properties).
Change the Name Property Value to cmdPreview.
Copy and paste the following code into the Code Module (View -> Code) of the Form.
```
Private Sub cmdPreview_Click()
     PaperAndOrient "MyReport"
End Sub
```
Replace "MyReport" with your own Report Name.
Save the Form and open it in a normal view.
Click the cmdPreview button to run the program, to change the Page setup correctly, and open it in Print Preview.

Type PaperAndOrient "MyReport" in the Debug Window (Ctrl+G) and press Enter to run the Program directly without the Form and Command Button.

Open the Report again in design view and check whether the erraneous changes that you have made manually in the Page Setup Dialog Box, to test the program, have now been corrected through the program or not.

Next, we will see how to change the values on the Margins tab of the Page Setup Dialog Box through the Program.

Earlier Post Link References:

Textbox And Label Inner Margins

a.p.r. pillaiNovember 01, 2008msaccess reports, Reports 1 comment

Textbox And Label Inner Margins.

Whether it’s a Form or a Report, a well-crafted design always draws the attention of both the user and any onlooker. Everyone designs forms and reports—but if you give the same task to five different people, each will produce a unique result based on their individual skills and sense of artistry unless they all rely on the same built-in wizards.

Most users focus primarily on the information presented in a report and want it organized and accurate. However, how that information is presented is entirely up to you—and it often depends on how much time you can invest in the design process. Remember, you typically design a report only once as part of a project, so it’s worth doing it thoughtfully.

Your report may eventually circulate far and wide, through faxes, emails, or shared databases, to reach a much wider audience. When compared with other reports circulating around, you’d want someone to pause and ask, “Who designed this one?” Fortunately, Microsoft Access provides all the tools you need to create visually appealing, professional-quality reports and forms. With just a bit more time and creativity, you can achieve remarkable results using the simple yet powerful tools Access offers.

Precision Positioning of Data.

Here, I’d like to introduce you to a few important properties of TextBoxes and Labels on a report—and show you how a few simple design adjustments can transform an ordinary layout into a professional, visually appealing report.

The image below shows a Tabular Report created using the Report Wizard in Microsoft Access.

Right-click to open Large Image in New Tab/Window.

Wizards are excellent tools for quickly laying out all the objects on Forms or Reports with default formatting for font type, size, and style—saving a significant amount of design time. All that’s left is to fine-tune the layout to suit your preferences.

A Sample Project.

If you’d like to try this simple design step by step, import the Shippers table from the sample database at
C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb. (That path is for Access 2003 — if you’re using a different version, look for the appropriate \Office##\ folder.) After importing, select the Shippers table, choose Report from the Insert menu, and pick Auto Report: Tabular from the options. Access will generate the report in moments. A preview of the report in Print Preview is shown below:

Right-click to open Large Image in New Tab/Window.

The modified version of the same Report in Print Preview below:

Right-click to open Large Image in New Tab/Window.

The transformation was easy with only a few changes to the above design, and I know which change you have noticed first. If I have turned on the borders of the TextBoxes and Labels alone, then the Print Preview will look like the one below:

Right-click to open Large Image in New Tab/Window.

Make the following changes to the above design:

Delete the thick line under the Header Labels.
Point the Mouse on the vertical ruler to the left of the Header Label Shippers so that it turns into an arrow pointing to the right, and then click and drag along the ruler downwards so that you can select all the Labels and Text Boxes in the Report Header, Page Header, and Detail Sections together.

Alternatively, you can click on an empty area of the Report and drag the Mouse over all the controls to select them. Do not select the Page Footer Section Controls. We don't need them in this Report.
Display the Property Sheet (View -> Properties) and change the following Values:
- Border Color = 9868950
- Special Effect = Flat
- Border Style = Solid
- Border Width = Hairline
You need to change only the Border Color Value; others will be there as default. If not, then change them as given above.
Select all the Field Header Labels alone in the Page Header Section, as we did in Step 2 above. Select Format -> Align -> Left to arrange the labels close together horizontally without leaving gaps between them.
Display the Property Sheet of the selected Labels (View -> Properties) and change the Top Property Value to 0 and Height Property Value to 0.4167 Inches.

Centralizing Text Vertically.
Centralize the Text horizontally within the Labels by changing the Text Align Property Value to Center, while all the Labels are still in the selected state.
Select all the TextBoxes in the Detail Section together and select Format -> Align -> Left to arrange the TextBoxes close together without leaving gaps between them.
Display the Property Sheet of the TextBoxes (if you have already closed it) and change the Top Property Value to 0 and the Height Property Value to .2917" so that the data lines are not too close and crowded when Previewed/Printed.
If there is a gap below the Labels in the Page Header Section and below the TextBoxes in the Detail Section, then close them by dragging up the Detail Section Header and the Page Footer Bars.
Delete all the Page Footer Section controls. Close the gap by dragging the Report Footer Bar up.
Next, resize the Report Header label containing the Shippers heading so that it spans the combined width of all the field header labels in the Page Header section. You can do this either by adjusting it manually by eye or by opening the Property Sheet for each header label, noting their individual Width property values, adding them together, and then setting the Width property of the Shippers heading label to that total.
Change the Height Property Value to 0.416, and the Text Align Property Value to Center.
Save your Report with a Name of your choice.
With the above modifications, the Report Print Preview image is shown below and needs to be corrected.

Right-click to open Large Image in New Tab/Window.

The Report looks good, but with a few more cosmetic changes, it will look even better.

The Field Header Labels' Caption Text must be vertically centered.
The Shipper ID Numbers and other field values are too close to the Border Line, and they should be positioned a little away from the border.
Open the Report in Design View and select all the Field Header Labels together as we did earlier.
Display the Property Sheet and drag the right scroll bar of the Property Sheet down to the bottom. There, you will find the Inner Margin Properties that you can use to position the Text within the Controls.
NB: These Properties are available only in MS Access 2000 and later versions.
Right-click to open Large Image in New Tab/Window.
Change the Top Margin Property Value of Header Labels to 0.1".
Select the Text Controls together on the Detail Section and change the Top Margin Property Value to 0.0701".
Select the Shipper ID TextBox in the Detail Section, and change the Right Margin value to 0.1".
Select the Company Name TextBox, and change the Left Margin Value to 0.0597" and set the same value for Phone Number also.
Save your Report and open it in Print Preview. It is similar to the 3rd Image from the top of this page.

Although the explanation may seem lengthy, once you understand the steps, you can complete the design in just a few minutes.

Multiple Parameters For Query

a.p.r. pillaiOctober 25, 2008msaccess reports, Queries 1 comment

Multiple Parameters For Query.

Queries are an essential component of data processing, and we rely on them extensively in various ways. One of the main challenges when creating queries is how to filter data in a user-friendly manner, making the process seamless for the user. To address this, we employ several methods that allow users to easily pass values as criteria to the queries.

You can create Parameter Queries by inserting variables, such as:[EnterSalesDate]', Into the Criteria row of a query. When run, the query will prompt the user to enter the parameter value, allowing them to filter records directly. To define the data type for a parameter variable, use the Parameters… option from the Query menu while in Design View.
You can place TextBoxes or Combo Boxes on a Form, where the user can enter or select values before running a Report or viewing data. The underlying queries reference these controls in their Criteria rows—for example, Forms![MyForm]![myDateCombo]. Based on the values entered or selected, the queries filter the data accordingly, producing the desired results in Reports or data views.
Another way to filter records is by specifying a range of values. For example, to retrieve Sales records for a particular period, the query criteria for the Sales Date might be : Between #01/01/2008# AND #03/31/2008# if constants are used. Alternatively, these values can be dynamically passed from TextBoxes on a Form, allowing the user to specify the date range interactively.
In such cases, I prefer to create a small table—let’s call it a Parameter Table—with a single record and two fields: StartDate and EndDate. Then, I create a Datasheet Form for this table and embed it as a Sub-Form on the Main Form. This allows the user to conveniently enter the date range values directly into the table.

This table is included in the main query, with the StartDate and EndDate fields placed in the Criteria row using the expression:
```
Between [StartDate] AND [EndDate]
```
It is important to note that the Parameter Table should contain only one record; otherwise, the main table’s results will be duplicated if the Parameter Table has multiple records. To prevent this, set the Allow Additions property of the Datasheet Form to No, so the user cannot inadvertently add more records.
When the user clicks a button to generate the Report or other outputs based on this date range, the Parameter Sub-Form can be refreshed first to update the values in the table. After that, the query can be executed to reflect the latest StartDate and EndDate values.
The above example retrieves all data between StartDate and EndDate. However, sometimes we need to filter specific, non-sequential values—for instance, Employee Codes 1, 5, 7, and 8. In such cases, we are forced to enter the codes manually in the Criteria row of the query, using one of several methods, as illustrated in the sample image below:

Query Parameter Input Methods.

I would like to share another method I use to let users select parameter values for reports—by simply checking boxes in a Parameter Table.

For example, assume that our company has several branch offices across the country, and management occasionally requests reports for selected branches. Since branch names remain constant, we can enable users to pick the required branches by placing check marks beside them. The check-marked entries can then serve as criteria for filtering data.

To illustrate this method more clearly (and to keep it simple), let’s use a list of months as an example. We will see how the selected months are used as criteria for the main query. The image below shows how this list of months appears to the user in a datasheet form, displayed as a subform on the main form.

We will need two queries for this process—one to filter the selected months from the list, and a second (the main query) that uses the results of the first query as parameters to filter data for the report.

The first query should return the values 3, 6, 9, and 12, based on the month selections shown in the image above. The following SQL statement can be used to achieve this result:

Query Name: Month_ParamQ

SELECT Month_Parameter.MTH
FROM Month_Parameter
WHERE (((Month_Parameter.[SELECT])=True));

When the user selects or deselects check marks on the parameter screen, these changes may not update in the underlying Month_Parameter table. To ensure the latest selections are reflected, we must refresh the Month_Parameter subform before opening the report that retrieves data from the main query (which uses the above query as its criteria).

To handle this, include the following statement in the On_Click() event procedure of the Print Preview command button:

Private Sub cmdPreview_Click()
     Me.Month_Parameter.Form.Refresh
     DoCmd.OpenReport "myNewReport", acViewPreview
End Sub

Now, how can the selected months filtered in the Month_ParamQ be used in the Main Query as a criterion? The third method we used earlier as a criterion in the first Image given above. I will repeat it below:

IN(1,5,7,8)

Here, we will compare the EmployeeID values with the numbers 1, 5, 7, 8, and select records that match any of these numbers as output.

Similarly, all we need to do here in the Main Query is to write this as a Sub_Query in the Criteria Row to use the Month Values from the Month_ParamQ. The above criteria clause, when written in the form of a sub-query, will look like the following:

IN(SELECT MTH FROM MONTH_PARAMQ)

The User doesn't have to type the Report Parameter values; they can select required items from a list, click a Button, and the Report is ready.

No Data and Report Error

a.p.r. pillaiSeptember 28, 2008msaccess reports, Reports 2 comments

No Data and Report Error.

Report Source Query or Table can end up with no output records. In that case, some of the controls with the formula in the Report will show #Error. An image of a sample report is given below:

Right-click to open Large Image in New Tab/Window.

The #Error message at the top-right corner appears because the control contains a formula intended to display the reporting period. Similarly, the controls to the right of the word TOTAL—which display subtotals, totals, and detail line values—also show errors. This occurs when the report’s underlying query returns no records for the period selected by the user.

Although this is not a critical issue, it is considered poor practice to present or archive such a report with visible errors, especially if it needs to be shared as a NIL REPORT or retained for audit or future reference.

The modified version of the report shown below resolves this issue. It includes a clear comment, displays zero values in the summary controls, and correctly prints the reporting period.

Right-click to open Large Image in New Tab/Window.

I have made a few modifications to the Report Design to add a hidden label at the footer of the Report with the Caption: *** Congratulations *** Nothing Pending to show up when there are no output Records for the Report. The Visible Property of the label is set to No. In the Detail Section under the Description Column, it shows *** NIL REPORT ***. The period for which the Report is prepared is also shown to the right, above the Detail Section headings.

The Report Period (DateFrom and DateTo) is normally entered into a Parameter Table and joined with the Report Source Table in a Query to use them for criteria and for displaying on the Report.

Right-click to open Large Image in New Tab/Window.

Few Changes in the Report

Created two Text Controls (with the names From and To, respectively) at the Report Header Section to the right of the Control name STAFFNAME to load the DateFrom and DateTo Values from the Report Parameter Table with the DLookup() Function:

=DLookUp("DateFrom","Report_Param")

The second control has the expression to read DateTo from the Report_Param Table, and both values are used in the expression (=" Period: " & [frm] & " To " & [To]) to format the values to show the output as in the second image given above. These are all the cosmetic changes required in the Report.

Temporary Table for Report.

The major change is creating a temporary table with a single blank record, which should have the same structure as the Source Table or Query, and attaching it to the Report. If your Report is using a Table as Report Source Data, then make a copy of the structure of the Table and add a tmp_ prefix to the table name, like tmp_myReport. If it is a Query, then create a Make-Table Query using the Report Source Query and create a temporary table. Add a blank record in the temporary table. If your Report Table has a Text Field that is displaying the value on the Report, then type *** NIL REPORT *** in that field. Fill numeric fields with 0 and keep all other fields Empty.

How the Trick Works.

The trick is that when the Report is opened by the User, we will check whether the original Report Source Table or Query has any records in it or not. If not, swap the Temporary Table with the Report Source Table or Query. The hidden Label's Visible Property will be set to Yes to display the comment *** CONGRATULATIONS *** NOTHING PENDING. Since the temporary table has a single blank record, the Summary Controls will not show errors.

We need a few lines of VBA Code in the Report_Open() Event Procedure to check and swap the Report Source Table or Query.

A few lines of VBA Code.

Private Sub Report_Open(Cancel As Integer)
Dim i As Integer

i = DCount("*", "myReport")

If i = 0 Then
   Me.RecordSource = "tmp_MyReport"
   Me.lblMsg.Visible = True
End If
End Sub

Copy and paste the above code in the Report's VBA Module and make changes to insert the correct Table/Query and tmp_myReport names.

Dynamic Report

a.p.r. pillaiOctober 27, 2007msaccess reports, Reports 9 comments

Dynamic Report.

Designing reports in Microsoft Access is quite straightforward, as we already know. Once a process is in place—using macros or programs to prepare the source data for a standard report—the rest becomes routine. You simply design the report and add it to the report list. After that, every time a user runs the report, it’s ready for preview or printing.

Although the underlying data may change based on parameters such as the reporting period, the report structure itself remains unchanged. There is no need to redesign the report or modify its data source, since all standard elements—such as the main heading, reporting period, preparation date, and page numbers—are already incorporated during the initial design phase.

We are now going to explore a type of report that cannot be planned using the usual rules or based on a fixed data structure. In this case, the structure of the source data is unpredictable—field names may change every time the report is generated. When the data structure is dynamic, you cannot permanently place field names in the report design, as is typically done.

To make matters more challenging, the number of fields included in the report may also vary. An even more complex requirement is calculating summary values—both at the group level and as grand totals in the report footer—when the structure and data content are constantly changing.

When someone asks for something difficult, the easy way out is to say it's not possible—and many users might accept that, especially if they’re unfamiliar with programming. But in the software world, when someone says “no,” it often just means they don’t yet know how to do it. I’ve been there myself. Still, once I say it can’t be done, the thought nags at me. I start asking: Can I really let it go, or is there a way to make it work?

Often, it just takes time—time to think, plan, organize, test a few ideas, and gradually build something functional. The number of steps doesn’t matter; what matters is the final result. Once it works, I can always go back and refine it. That said, there are situations where we must honestly accept the limits and stick to “no”—but only after we’ve explored every possibility.

Let’s get down to problem-solving instead of going in circles. I was just being a little philosophical earlier. Honestly, the task isn’t as big as it might sound now, especially after all that bragging.

Report Preparation.

Before diving into the Report preparation, let me first show you some sample data and the Report format we’ll be working with. The sample records are taken from two familiar tables in the Northwind.mdb database—Employees and Orders, which we’ve already used in earlier examples.

Right-click to open the Large Image in a New Window.

In the above table, the Shipped Date ranges from July 10, 1996, to May 6, 1998. Our task is to prepare a report formatted as shown below, organized by employee, Year-wise, and Month-wise.

Data table view:

Right-click to open the Large Image in a New Window.

When a sample Report is designed using the above data as a source, it looks like the image given below.

Right-click to open the Large Image in a New Window.

The report is designed to display data for 12 months, with the data selection criteria set between 199607 (July 1996) and 199706 (June 1997) in the report’s underlying query. The Detail section includes the necessary fields, while the Report Footer contains controls configured with summary formulas to calculate month-wise totals. The field headings are labeled using month-year formats for clarity. Up to this point, everything functions smoothly — the report opens in Print Preview or prints without any issues.

Report Data Selection Criteria

However, when the data selection criteria are changed to target a different period, the report fails to run. It throws an error in the first field that does not match any of the existing TextBox control sources on the report layout and refuses to open in Print Preview or Print mode. This occurs because the field names in the source query no longer align with those hard-coded in the report design, making it unsuitable for handling dynamic or varying data structures.

An alternative approach is to create a Report Table with fixed field names such as M1, M2, through M12, representing data for January to December. An Append Query can be used to populate this table with the desired report data. The report itself can then be designed using these permanent field names, ensuring a consistent structure.

To dynamically reflect the reporting period in the field headers, we need a way to define header labels using formulas based on the report period parameters. However, this method introduces a constraint: the user must generate report data within a single calendar year—either for the full 12 months or a shorter period—not spanning multiple years. This limitation ensures that the field mappings remain consistent and avoids mismatches during report generation.

If the user performs a month-wise trial run of a Report with a date range of two calendar years, the data for the earlier year/month will appear at the extreme right. This reversal disrupts the logical sequence of the report. In addition, dynamically updating the field headings to correctly print the shifted months introduces another challenge.

As a result, this cannot be considered a well-structured report. The misalignment between the data layout and the user's expectations significantly reduces the overall report usability.

Sample Data Preparation Queries.

We found a practical remedy for this issue by introducing a small VBA routine in the report’s code module. This routine dynamically adjusts the report's final layout based on the underlying source data structure. It is executed automatically each time the report is opened for preview or print, ensuring the design always aligns with the current dataset, regardless of how the fields or reporting period vary.

To get started with the program, import the Employees and Orders tables from the Northwind.mdb sample database if they are not already available in your project. If you're unsure about the Northwind Database location, refer to the Saving Data on Forms, Not in the Table page for detailed guidance on where to find it.

Copy and paste the following SQL string into the SQL View of a new query, and save the query with the name indicated below:

Query Name: FreightValueQ0

SELECT [FirstName] & " " & [LastName] AS EmpName,
 Val(Format([ShippedDate],"yyyymm")) AS yyyymm,
 Orders.Freight
FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE (((Val(Format([ShippedDate],"yyyymm"))) Between 199607 And 199706));

Query Name : FreightV_CrossQ
TRANSFORM Sum(FreightValueQ0.Freight) AS SumOfFreight
SELECT FreightValueQ0.EmpName
FROM FreightValueQ0
WHERE (((FreightValueQ0.yyyymm)<>""))
GROUP BY FreightValueQ0.EmpName
PIVOT FreightValueQ0.yyyymm;

Report Design Task

After creating the above queries one by one, design a Report based on the FreightV_CrossQ query as its data source. A sample layout of the Report design is shown below.
Right-click to open the Large Image in a New Window.
In the Detail section of the Report, create 13 Text Box controls. Make the leftmost Text Box wider to accommodate the Employee’s Name, and use the remaining 12 boxes for monthly data (Month 1 to Month 12). Rename the Text Boxes using the Name property as follows: M00 for the Employee Name field, and M01 through M12 For the monthly fields, moving left to right. Ensure that the Control Source property of all these Text Boxes remains empty at this stage.
In the Report Footer Section, create 13 TextBox controls and name them from T00, T01 to T12. Leave the Control Source Property empty.
In the Header Section, create 13 Label Controls, name them L00, L01 to L12, and leave the Caption Property empty.
Create a Label at the Top and set the Caption property value to EMPLOYEE-WISE FREIGHT VALUE LISTING.
While the Report is still in design view, select the Save option from the File menu and save the Report named FreightVal_Rpt.
Select Code from the View menu to display the VBA Module of the Report.

Copy and Paste the following Code into the VBA Module:

Automating Report Control's Data Reference Setting.

Private Sub Report_Open(Cancel As Integer)
Dim db As Database, Qrydef As QueryDef, fldcount As Integer
Dim rpt As Report, j As Integer, k As Integer
Dim fldname As String, ctrl As Control, dtsrl As Date
Dim strlbl As String, fsort() As String

On Error GoTo Report_Open_Err

Set db = CurrentDb
Set Qrydef = db.QueryDefs("FreightV_CrossQ")
fldcount = Qrydef.Fields.Count - 1

If fldcount > 12 Then
   MsgBox "Report Period exceeding 12 months will not appear on the Report."
   fldcount = 12
End If

Set rpt = Me

ReDim fsort(0 To fldcount) As String
For j = 0 To fldcount
    fldname = Qrydef.Fields(j).Name
    fsort(j) = fldname
Next

'Sort Field names in Ascending Order
For j = 1 To fldcount - 1
    For k = j + 1 To fldcount
        If fsort(k) < fsort(j) Then
            fsort(j) = fsort(k)
        End If
    Next
Next

For j = 0 To fldcount
'Monthwise Data    
Set ctrl = rpt.Controls("M" & Format(j, "00"))
    ctrl.ControlSource = fsort(j)
    Set ctrl = rpt.Controls("T" & Format(j, "00"))
    If j = 0 Then
        ctrl.ControlSource = "=" & Chr$(34) & " TOTAL = " & Chr$(34)
    Else
        ctrl.ControlSource = "=SUM([" & fsort(j) & "])"
    End If

'Header labels
If j = 0 Then
    Me("L" & Format(j, "00")).Caption = "Employee Name"
Else
    dtsrl = DateSerial(Mid(fsort(j), 1, 4), Right(fsort(j), 2), 1)
    strlbl = Format(dtsrl, "mmm-yy")
    Me("L" & Format(j, "00")).Caption = strlbl
End If
Next

Report_Open_Exit:
Exit Sub

Report_Open_Err:
MsgBox Err.Description, , "Report_0pen()"
Resume Report_Open_Exit
End Sub

After copying the code, minimize the VBA Editor window and open the Report Property Sheet. Check whether the On Open property is set to [Event Procedure]. If it is not, the code has not been correctly linked to the event. In that case, select [Event Procedure] from the drop-down list, then click the Build button (...) to open the code window.

In the code module that appears, you will see an empty Report_Open() subroutine. Cut the code you previously pasted (excluding the top and bottom lines that were copied from the web page), and insert it within this Report_Open() ... End Sub block. Finally, remove any orphaned or duplicate lines, save the changes, and close the editor.

Print Previewing the Report.
Open the Report in Print Preview. The Report should display the values from the source Query, with the correct heading labels, and the Report Footer summary.
Open the first Query in Design View and modify the criteria to set different date ranges, ensuring that the selected range does not exceed 12 months (a shorter period is acceptable). Then, run the Report to verify the output.

If the selected period exceeds 12 months, the Report will display a message indicating that the selection exceeds the allowed range. It will then proceed to open with the data that fits within the maximum number of available fields.

If the selected period is less than 12 months, the unused rightmost controls will remain blank. In either case, the Report will still open, allowing the user to view the available contents.