Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Access Cookbook, 2nd Edition [Electronic resources] - نسخه متنی

Ken Getz; Paul Litwin; Andy Baron

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید










Recipe 5.3 Programmatically Change Margin and Column Settings for Reports



5.3.1 Problem


You'd like to give
your applications' users some control over report
layout, especially in designating column and margin settings. You
could just let them loose in report design mode, but
you'd like to maintain a little control over their
actions. Is there some way to modify these layout settings from VBA?


5.3.2 Solution


Starting with Access 2002, each
form and report object includes a Printer property. Retrieving this
property gets you an object with many properties, several of which
deal with margin and column settings for reports.

You can use properties of a report's Printer object
to retrieve and set layout properties. You'll find
properties representing the left, top, bottom, and right margins; the
number of columns; and the size, spacing, and item order of the
columns. In addition, the Printer object contains the Data Only
option in the File Page Setup dialog. This solution
demonstrates how to use the print layout properties provided by the
Printer object.

Load and run the form frmPrintSettings from

05-03.MDB . Figure 5-3 shows
the form (which emulates Access's File
Page Setup dialog) after the report Report1 has been selected from
the list of reports. Choose a report from the drop-down list, and the
form will load that report in preview mode. You can change the
settings for the selected report by typing new values into the text
boxes. To save the changes to the selected report, click on Save
Settings. You'll see those changes in the preview
window.


Figure 5-3. frmPrintSettings provides the same functionality as the Access File Page Setup dialog


Some of the items on the form are available only if
you've specified more than one column for the Items
Across value, so you'll want to use a number greater
than 1 in that field. (Because this example opens the report in
preview mode, changes you make aren't saved with the
report. If you want to permanently save the report with the new
settings, you'll need to modify the code so that the
report opens in design view; changes you make will then be saved with
the report when you close and save it.) The following sections
explain both how to use the sample form from

05-03.MDB and how to work with these properties
and your own objects.

To use the sample form in your own applications, follow these steps:

  1. Import the form frmPrintSettings into your application. This form
    allows you to choose from the existing reports in your database.

  2. Once you've chosen the report (which the form will
    open in preview mode), you can alter print layout settings. Once
    you're done, you can print the report.



5.3.3 Discussion


The sample form for this topic
does very little workit simply copies values from the Printer
property of the selected report to controls on the form, converting
from twips (1/1,440 inch) to inches for display purposes. When you
click Save Settings, the code writes the settings back to the
appropriate properties of the report's Printer
property.

Access's
Printer object provides a number of properties dealing with print
layout. Table 5-1 describes the subset of Printer
object properties used in this example form.

Table 5-1. Printer object properties associated with print layout

Property


Comments


LeftMargin


Distance between the left edge of the paper and the object to be
printed (in twips)


TopMargin


Distance between the top edge of the paper and the object to be
printed (in twips)


RightMargin


Distance between the right edge of the paper and the object to be
printed (in twips)


BottomMargin


Distance between the bottom edge of the paper and the object to be
printed (in twips)


DataOnly


If True (-1), Access prints just data, not labels, control borders,
gridlines, and display graphics; if False (0), Access prints all
elements


ColumnSpacing


Distance between detail section columns (if ItemsAcross > 1), in
twips


DefaultSize


If True (-1), Access uses the width and height of the design-mode
detail section when printing; if False (0), Access uses the values
specified in the ItemSizeWidth and ItemSizeHeight properties


ItemLayout


acPRHorizontalColumnLayout (Across, then Down), or
acPRVerticalColumnLayout (Down, then Across) for
multiple-columned reports


ItemSizeWidth


Width of the detail section; if the DefaultSize property is False and
the ItemsAcross property is greater than 1, the width of each column
(in twips)


ItemSizeHeight


Height of the detail section (read-only)


ItemsAcross


Integer that specifies the number of columns across the page for
multiple-columned reports


RowSpacing


Vertical distance between detail sections (in twips)

After you select a report on the
sample form, the combo box's AfterUpdate event
procedure calls the following code, which opens the report in preview
mode, then copies the report's properties to the
controls on the form:

strReport = Me.cboReportList
DoCmd.OpenReport strReport, View:=acViewPreview
With Reports(strReport).Printer
Me.txtLeft = ToInches(.LeftMargin)
Me.txtRight = ToInches(.RightMargin)
Me.txtTop = ToInches(.TopMargin)
Me.txtBottom = ToInches(.BottomMargin)
Me.chkDataOnly = .DataOnly
Me.txtXFormSize = ToInches(.ItemSizeWidth)
Me.txtYFormSize = ToInches(.ItemSizeHeight)
Me.txtCxColumns = .ItemsAcross
Me.txtYFormSpacing = ToInches(.ItemSizeHeight)
Me.chkfDefaultSize = .DefaultSize
Me.txtXFormSpacing = ToInches(.ColumnSpacing)
Me.grpRadItemOrder = .ItemLayout
End With

Don't forget that
all the measurements in the Printer object are stored in twips. The

ToInches function simply divides its parameter
value by 1,440 and adds the text
"in." to its output value. The
corresponding

FromInches function does the
oppositeit strips off extra text and multiplies its parameter
value by 1,440 to convert back to twips. Why 1,440? A twip is defined
as 1/20 of a point. There are 72 points per inch and 20 twips per
point; therefore, 72 x 20 = 1,440 twips per inch.

When you click Save Settings, the
command button's Click event procedure copies data
back to the properties of the report, like this:

Dim strChosen As String
Dim rpt As Report
strChosen = Me.cboReportList
With Reports(strChosen).Printer
.LeftMargin = FromInches(Me.txtLeft)
.RightMargin = FromInches(Me.txtRight)
.TopMargin = FromInches(Me.txtTop)
.BottomMargin = FromInches(Me.txtBottom)
.DataOnly = Me.chkDataOnly
.DefaultSize = Me.chkDefaultSize
If Not .DefaultSize Then
.ItemSizeWidth = FromInches(Me.txtXFormSize)
.ItemSizeHeight = FromInches(Me.txtYFormSize)
End If
.ItemsAcross = Val(Me.txtCxColumns)
.RowSpacing = FromInches(Me.txtYFormSpacing)
.ColumnSpacing = FromInches(Me.txtXFormSpacing)
.ItemLayout = Me.grpRadItemOrder
End With

The
combo box containing the list of reports uses a common but
undocumented technique. The Access system tables (check Tools
Options View System Objects to see
the system tables in the database container) contain information
about the current database. One table in particular, MSysObjects,
contains a row for each object in the database. To fill the combo box
with a list of reports, you can use this SQL expression:

SELECT Name FROM MSysObjects WHERE Type = -32764 ORDER BY Name;

The Name column includes the name for each object, and the Type
column contains -32764 for reports (or -32768 for forms). Microsoft
suggests using DAO or ADO instead of querying against the system
tables to retrieve lists of items; however, our method is much faster
and much simpler for filling lists. This method has worked in every
version of Access so far; we can only assume it will continue to do
so.

The Printer object provides one
more bit of unexpected behavior: unless you've set
the DefaultSize property to False, you cannot set
the ItemSizeWidth or ItemSizeHeight
propertiesyou'll trigger a runtime error if
you try. The sample code determines the value in the DefaultSize
property and attempts to change the other two properties only if
doing so won't cause an error.


/ 232