LEARN MS-ACCESS TIPS AND TRICKS

Hiding Report Lines Conditionally

a.p.r. pillaiJune 26, 2009msaccess reporttricks, Reports 1 comment

Hiding Report Lines Conditionally.

Hiding report lines or report sections conditionally at runtime may not be an everyday requirement. However, when it becomes necessary, it’s interesting to know how to do it with the help of VBA programs. One feature I particularly like about MS Access reports is the ability to write programs directly in the Report Module—especially for highlighting critical information so that users’ attention is drawn to specific details. You can see an interesting example in an earlier post titled Highlighting Reports.

Normally, to suppress something from printing on a report, we would use a query to filter out unwanted items from the source data before previewing or printing. Frankly, using a query is the best method compared to what I’m showing here. To achieve the same result on the report without filtering the source data, we need to use a few tricks—after all, this is all about Tips & Tricks, right?

If you enjoy working with MS Access programs, read on. The code itself is not complicated; a few simple lines are enough, and even a VBA beginner can understand them easily.

It’s enjoyable to do something different, not only to break the monotony of repeating the same tasks but also to gain better insight into doing things more efficiently next time.

Design a Sample Report.

We will create a sample Report quickly to try out this Trick.

Import the Orders and Employees tables from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.

We won’t be using the Employees table directly in the report. Without the Employees table in the database, only the Employee IDs will be displayed when designing the report using the Orders table. There is a reference to the Employees table in the EmployeeID field of the Orders table (for example, the combo box uses the Employees table).

To create the report:
1. Select the Orders table and select Report from the Insert menu.
2. Choose Report Wizard from the displayed list and click OK.
3. From the Available Fields list, select the following data fields and move them one by one to the report (fields list continues…).
  
  Selected Fields List:
  - OrderID
  - CustomerID
  - EmployeeID
  - OrderDate
  - RequiredDate
4. Click Next, select CustomerID as Grouping Level, and move it to the right. Click Next Command Button.
5. Select OrderID for Sorting in the first Sort Control and click Finish to create the sample Report.
  An image of the sample Report is given below:
  Right-click to open Large Image in New Tab/Window.
6. Open the Report in Print Preview and check the Order Numbers appearing under the first three Customer Groups on the Report.
  
  We will attempt to hide three different Orders (Order IDs 10702, 10625 & 10573 from ALFKI, ANATR & ANTON Customer#39 List, respectively) from appearing on the Report with the following simple lines of Code:
```
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [OrderID] = 10702 Or [OrderID] = 10625 Or [OrderID] = 10573 Then
      Report.Section(acDetail).Visible = False
Else
   Report.Section(acDetail).Visible = True
End If
End Sub
```
  The OnFormat() Event Procedure.
  If you look at the above code, you can see that the code runs under the On Format Event Procedure in the Detail Section of the Report.
7. Open your Report in Design View and display the Code Module (View -> Code).
8. Copy and Paste the above Code into the Report Module.
9. Select Close and Return to Microsoft Office Access from the File Menu to close the VBA Window and return to the Design View of the Report.
  
  Or you can press Alt+F11 to Toggle between the Database Window and the VBA Window. Visit the Page Keyboard Shortcuts to learn more about Keyboard Shortcuts.
10. Click on the Detail Section of the Report and display the Property Sheet (View -> Properties).
11. Check the On Format Event Property, and you can see that the entry [Event Procedure] is appearing there, indicating that the Code that we have pasted Runs on the Format Event of the Report.
  
  There are two more Events associated with the Report's Printing or Previewing action: Print Event and Retreat Event.
  The Report Formatting Passes
  MS Access makes two passes over a report before it is actually displayed or printed. The first, the Format pass, lays out the contents of each report section and performs any necessary calculations for summary information (we’ll cover this in more detail in forthcoming articles). The second pass, the Print pass, prints the report’s contents before previewing or sending it to the printer.
  The Retreat event occurs when you move the page preview to an earlier page. During this event, the Format action runs a second time for the retreated page, and the FormatCount parameter is set.
```
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
```
  is incremented by one. This can have unintended effects if calculations are being performed in this procedure. We will examine this aspect further in upcoming articles.
12. Save the Report and open it in Print Preview.
13. Check whether the Report Lines associated with OrderIDs 10702, 10625 & 10573 are still appearing on the Report or not.
The Parameter Table.
We were using Constant values for OrderIDs in the Program to check and suppress the Report lines from appearing on the Report. We cannot expect the User to open the Report in Design View and change the Program every time to change the OrderID values, if it becomes necessary, before printing the Report. Besides, there may be more than two or three Orders to hide this way. We must provide a much simpler method for the User to input the OrderIDs as parameters to the Program and run the Report based on that.
We will create a parameter table with the key in OrderIDs, read it in the program, compare it to the Report Order IDs, and skip those lines on the Report for matching cases.
1. Create a Table named OrderParam with a single Field named OID with Data Type Number and Field Size Long Integer.
2. Select Primary Key from the Edit Menu to define this field as a Primary Key Field. This will prevent duplicate values from going into the Parameter Table and make it easier to cross-check OrderIDs from the Report.
  Save the Table and open it in Data Sheet View.
3. Key in the OrderIDs 10702, 10625, and 10573 (or any other OrderIDs you would like to hide) in the OrderParam Table.
  Design a Continuous Form for the Table and create a CommandButton at the Footer Section of the Form with HyperLinks to the Orders Report so that the User can launch the Report from the Parameter Screen itself.
  The VBA Code
4. Copy and paste the following Code in Report#39's Code Module, replacing the earlier Code:
```
'Global declarations
Dim cdb As Database, rst As Recordset

Private Sub Report_Open(Cancel As Integer)
     Set cdb = CurrentDb
    Set rst = cdb.OpenRecordset("OrderParam")
    rst.Index = "PrimaryKey"
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
rst.Seek "=", [OrderID]
If Not rst.NoMatch Then
   Report.Section(acDetail).Visible = False
Else
    Report.Section(acDetail).Visible = True
End If
End Sub

Private Sub Report_Close()
   rst.Close
   Set rst = Nothing
   Set cdb = Nothing
End Sub
```
5. Save the Report with the new Code and open it in Print Preview.
6. Look for the OrderIDs in the OrderParam Table to check whether they are really suppressed from the Report or not.
Let us examine what we did in the above Code.
The VBA Code Review.
- We have declared the Database and Recordset objects at the global level of the report module so they can be referenced in all other subroutines.
- In the report’s Open event procedure, we open the OrderParam table, activate its primary key index, and keep it in memory.
- During the Detail Section Format event procedure, we cross-check each OrderID against the contents of the OrderParam table. If an OrderID matches a value in the table, the corresponding report line is hidden. This process continues for all records in the report.
- The OrderParam recordset is closed in the report’s Close event procedure.
Because this checking process references an external table for each record in the report’s source data, there may be a slight delay before the preview or print action completes.
Next, we will explore how this method works with Customer Level Summary Totals in the Customer Footer Section of the Report.

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:

Filter by Character and Sort

a.p.r. pillaiApril 18, 2009Forms, msaccess controls, Utilities 4 comments

Filter by Character and Sort.

Search, find, filter, and sort operations are essential for organizing data into a manageable form, making it easier to locate specific information. We have already explored some of these operations in the following earlier posts titled:

Now, let’s look at a simpler and more user-friendly method for filtering data by typing one or more characters directly into a text box and seeing instant results, rather than entering search text in various controls and clicking multiple buttons or toolbar options.

When the first character is entered into the text box (for example, the letter F), all records with names starting with that letter are instantly displayed. If the filtered list is still large or includes unwanted items, entering additional characters narrows the list. This process can be repeated as needed.

If the Backspace key is used to delete one or more characters, the filtered list will expand accordingly, gradually reversing the process. Since the filtering occurs instantly, the user can see results as soon as a key is pressed.

If you need to transfer values from one of these filtered records to another open form, you can do so by writing a routine for the Double-Click event at the form level. Double-clicking the record selector (the left border of a record) can trigger this action and can transfer the required values to another open form. We’ll look at the sample code for this action at the end of this article.

Sample Image of Form.

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

The limitation of this method is that it is designed for use with tabular-type forms, where multiple records can be viewed simultaneously. The earlier methods (referenced in the previous articles) were intended for single-record forms.

Typically, a search or filter operation of this kind is implemented for a single field, such as Employee Code, Company Code, or Company Name—fields that are most relevant when searching for information.

However, in this example, we’ll take it a step further by offering greater flexibility. We’ll provide a combo box listing all the field names from the form’s source object (table or query). This allows the user to select which field to filter the data on, making the search feature far more versatile.

To follow along with this example, you’ll need the Customers table from the Northwind.mdb sample database. If you’re unsure where to find this file, refer to last week’s post—specifically, the third item in the list of three links provided there.

Sample Data Table, Query & Form.

Import the Customers Table from the Northwind database.
Create a SELECT Query with the following SQL String and save the Query with the name CustomersQ.
```
SELECT Customers.[First Name], Customers.[Last Name], Customers.[Job Title]
FROM Customers;
```
Design a Tabular Form as shown in the Image given above. I have selected only three fields from the Table in this example. If you would like to use some other Fields in the Query, you may do so.
Expand the Form Footer Section if it is not visible (View ->Form Header/Footer).
If the Toolbox is not visible, display it by selecting Toolbox from the View Menu.
De-select the Control Wizards Button (the top right one) if it is already in the selected state on the ToolBox, so that the Wizard will not start when the Combo Box Tool is selected.
Select the Combo Box Tool from the Toolbox and draw a Combo Box on the Footer Section of the Form, as shown in the design above.
Change the Caption value of the Child Label on the Combo Box to Filter/Sort Field:.
Click on the Combo Box to select it and display its Property Sheet (View -> Properties).
Change the following Property Values as given below:
- Name = cboFields
- Row Source Type = Field List.
- Row Source = CustomersQ.
- Column Width = 1.5"
Create a Text Box below the Combo Box.
Change the Caption Value of the Child Label to FilterText.
Change the Name Property Value of the Text Box to FilterText.
Create an Option Group Control with two buttons, the child Label Captions ASC and DESC for the Sorting option, Ascending or Descending order.
Create a Command Button to the right of the Options Group control and change the following Property values:
- Name = cmdClose
- Caption = Close
The VBA Code
Display the VBA Code Module of the Form (View -> Code or Alt+F11).

Copy and paste the following Code into the Module and save the Form with the name Customers or any other name you prefer.

'Global declaration
Dim x, rst As Recordset

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Private Sub FilterText_KeyUp(KeyCode As Integer, Shift As Integer)
Dim i As Integer, tmp, j As Integer, srt As String

On Error GoTo FilterText_KeyUp_Err
i = KeyCode

Select Case i
    Case 8 'backspace key
        Me.Refresh
        If Len(x) = 1 Or Len(x) = 0 Then
            x = ""
        Else
            x = Left(x, Len(x) - 1) 'delete the last character
        End If
        GoSub setfilter
    Case 37, 39 'left and right arrow keys
        SendKeys "{END}" 'ignore action
    Case 32, 48 To 57, 65 To 90, 97 To 122 'space, 0 to 9, A to Z, a to z keys
        x = x & Chr$(i)
        Me![FilterText] = x
        GoSub setfilter
End Select

FilterText_KeyUp_Exit:
Exit Sub

setfilter:
  Me.Refresh
  tmp = Nz(Me!cboFields, "") 'save the value in Combo Box
  If Len(Nz(x, "")) = 0 Then
        Me.FilterOn = False ' remove filter
  Else 'set filter and enable
        Me.Filter = "[" & Me![cboFields] & "]" & " like '" & x & "*'"
        Me.FilterOn = True
  End If
  ' Set sort order
  j = Me!Frame10
  srt = IIf(j = 1, "ASC", "DESC")
  Me.OrderBy = "[" & Me!cboFields & "] " & srt
  Me.OrderByOn = True
  Me![cboFields] = tmp
  Me.FilterText.SetFocus
  SendKeys "{END}"
Return

FilterText_KeyUp_Err:
MsgBox Err.Description, , "FilterText_KeyUp()"
Resume FilterText_KeyUp_Exit
End Sub

Private Sub Form_Close()
Application.SetOption "Behavior Entering Field", 0
Me.FilterOn = False
Me.OrderByOn = False
End Sub

Private Sub Form_Load()
    Application.SetOption "Behavior Entering Field", 2
    Set rst = Me.RecordsetClone
    Me!cboFields = rst.Fields(0).Name
    Me.Refresh
    rst.Close
End Sub

Test Run the Demo Form.

Open the Form in Normal View.
The First Name field will appear as the default value in the Combo Box control.
Click on the Text Box below the Combo Box to set the focus on it.
Type the Character F, and you will see that all records with CustomerID values starting with the letter F are filtered.
If you look at the filtered Field values of records, you may find that the second character of the filter field values is different, and three items have the same letter in the second character position. Besides, the field values are correctly sorted in selected (Ascending/Descending) Alphabetical Order.
Type the second common character next to the earlier character in the FilterText control, and the list of items narrows down. You can further narrow down the filtering progressively by probable characters to the existing text.
Press the Backspace Key to delete the last character typed and to leave the rest of the characters in the Text Box. The list will expand, and all items starting with the letters in the control are back on the List.
Press the Backspace Key again to delete other characters one by one from the TextBox. This time, the Filter action is removed, and all the records are back in the Form.

NB: The Filter Criteria Text Values are limited to the Characters 0 to 9, A to Z, and a to z only.

If you want to try the Filter action on one of the other two fields, select that Field's name from the Combo Box above before trying the filter action explained from Step 19 onwards.

Try it on the third field, Job Title.

Download Demo Database

Download Filter_ByCharSort2007.zip

Animating Label on Search Success

a.p.r. pillaiApril 11, 2009Animations, Forms, msaccess animation 1 comment

Animating Label on Search Success.

We have seen how to find or filter records using values entered into a text box. We have used three different methods to find or filter data after entering text or numeric search values into a text box. But we have not used any visual indicator to announce whether the search operation was successful or not. If the search operation was successful, then the record that matches the criteria will become current or filtered; that was the only indication that the search was successful.

Visual Indicator Design Task.

Here, we will see how to animate (on/off) a Label a few times with an indicative message, announcing whether the search operation was successful or not.

To try an example with the sample VBA Code given below, import the Customers Table and Customers Form from the Northwind.mdb sample database. If you don't know where to look for this database, check the location C:\Program Files\Microsoft Office\Office11\Samples Folder (in Office 2003).
Open the Customers form in the design view.
Expand the Form Footer Section to create a few controls for the Quick Search operation and for our magic label animation. Check the sample image of the Form given below with controls added at the Footer of the Form:

Right-click to open Large Image in New Tab/Window.
Draw a Label on the Form Footer Section and change its Caption to Customer ID to Find:.
Draw a Text Box to the right of the Label and change its Name Property (View -> Properties or Alt+Enter) Value to xFind.
Create a Command Button to the right of the Text Box and change its Name Property Value to cmdFind. Change the Caption of the Command Button to << Find.
Create a Label below the Text Box and change the following Property Values shown against each Property:
- Name = lblMsg
- Caption = x
- Visible = False
Display the Code Module of the Form (View -> Code or Alt+F11)

Animation: Running VBA Code.

Copy and Paste the following Code into the Form Module and save the Form:

'Global Declarations
Dim backcolor As Long, forecolor As Long
Dim L As Integer

Private Sub cmdFind_Click()
'---------------------------------------------------------------
'Author : a.p.r. pillai
'Date    : April-2009
'URL     : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'---------------------------------------------------------------
Dim m_Find, rst As Recordset

On Error GoTo cmdFind_Click_Err

backcolor = -2147483633

m_Find = Me![xFind]
If IsNull(m_Find) Then
    Exit Sub
End If
Set rst = Me.RecordsetClone
rst.FindFirst "CustomerID = '" & [m_Find] & "'"
If Not rst.NoMatch Then
    Me.Bookmark = rst.Bookmark
    Me.lblMsg.Caption = "** Successful ***"
    forecolor = 16711680
    Me.lblMsg.forecolor = forecolor
Else
    Me.lblMsg.Caption = "Sorry, Not found...!"
    forecolor = 255
    Me.lblMsg.forecolor = forecolor
End If
L = 0
Me.lblMsg.Visible = True
Me.TimerInterval = 250

cmdFind_Click_Exit:
Exit Sub

cmdFind_Click_Err:
MsgBox Err.Description, , "cmdFind_Click()"
Resume cmdFind_Click_Exit
End Sub

Private Sub Form_Timer()
L = L + 1
Select Case L
    Case 1, 3, 5, 7, 9, 11, 13, 15, 17
        Me.lblMsg.Visible = True
    Case 2, 4, 6, 8, 10, 12, 14, 16, 18
        Me.lblMsg.Visible = False
    Case 19
       Me.lblMsg.forecolor = forecolor
       Me.lblMsg.Visible = True
       Me.TimerInterval = 0
End Select
End Sub

Private Sub xFind_GotFocus()
     Me.lblMsg.Visible = False
End Sub

The first two lines of code should go at the Global level of the Form Module.

Open the Customers form in Normal View to try out our creation. When you open the form, the Label that we have created below the Text Box will not be visible.
Type record number 55 in the Record Navigation control below.
Highlight the CustomerID code and press Ctrl+C to copy the Customer Code into the Clipboard.
Type 1 in the record navigation control in the Form to make the first record current.
Click on the Text Search Control on the Form Footer to select it and press Ctrl+V to paste the Customer Code from the Clipboard.
Click on the Command Button to find the first record that matches the Customer Code.
If the search operation is successful, the first record that matches the CustomerID will become the current record. A Label positioned below the TextBox will then become visible, flashing the message “Successful” nine times before remaining visible on the screen.
Make the first record current again, and replace a non-existent CustomerID in the search TextBox so that the search operation fails with the modified Value.
Click the Command Button to search for the CustomerID.

This time, we will get the "Sorry, Not Found...!" message. The message flashes nine times and stays on the screen.

How does it work?

In this example, we have made the label visible and hidden intermittently within an interval time of 250 Milliseconds. This method is ideal for all types of Forms with different backgrounds, like the one we used with a Background picture.

A Better Approach.

We can make the Label flash by changing the text Color (rather than hiding and displaying the label as we did in the earlier example) with the same timing mechanism if the Form background has a particular Color.

Create a copy of the Customers Form and rename it to Customers2.
Open the Form in Design View and display the Form's Property Sheet.
Find the Picture Property and delete the WMF image file pathname. This action will display a message asking to reconfirm the delete action and respond to remove the entry.
Remove the After Update property value. A Macro is attached here to run in the after-update event of the form.
Without closing the Property Sheet, click on the Footer of the Form and change the Back Color Property value to -2147483633. This is the normal Form background color when you open a new form in Design View.
Click on the label below the Text Box and change the following Property Values:
- Back Style = Transparent
- Special Effect = Flat
- Border Style = Transparent
Replace the following Select Case ... End Select code segment with the Code given below, in the Sub Form_Timer() Event Procedure:
```
Select Case L
    Case 1, 3, 5, 7, 9, 11, 13, 15, 17
        Me.lblMsg.forecolor = forecolor
    Case 2, 4, 6, 8, 10, 12, 14, 16, 18
        Me.lblMsg.forecolor = backcolor
    Case 19
       Me.lblMsg.forecolor = forecolor
       Me.lblMsg.Visible = True
       Me.TimerInterval = 0
End Select
```
We have changed the lines under the first two Case... Statements in the above code segment to change the Color of the Font.
The first statement changes the Font color to Blue if the search operation was successful, otherwise red.
The line under the second Case... Statement replaces the Font Color with the Form's Background, making the text in the Label invisible.
When the value in the control variable L is an Odd Number, the line under the first Case statement executes; when it is an Even Number, the line under the second Case statement executes. This happens alternatively every 250 milliseconds, and the label animates nine times. When the value in the Control Variable L = 19, the Interval Timer is turned off, and the Label's Font Color is changed based on the search result (Blue or Red), and keeps the Label visible on the Form till the User clicks the TextBox again to enter a new search criterion.
Save the Form and open it in a normal view.
Repeat the procedure explained under Steps 10 to 17 above.

This time, when the search result is successful, the Label will flash blue and red when the search fails. In both situations, the colors are exchanged with the background color intermittently with the Form Background Color Value -2147483633.

If you want to slow down the action, then increase the interval time value from 250 Milliseconds to a higher value or reduce it to flash the Label faster.

Download Demo Database.

AnimatingLabel.zip

Form Background with Gradient Color

a.p.r. pillaiApril 04, 2009Art Work, Forms, msaccess forms 13 comments

Form Background with Gradient Color.

The Form background pictures are not only used for giving the Form a different look, but also for other useful purposes.

Assume that the user is entering data from a pre-printed document, such as a telephone or an electricity bill. In such situations, it can be very effective to scan an image of the source document and use it as the background picture of the data entry form. The Data Fields can be positioned in appropriate locations on the image, matching the physical Document so that it will be easier for the user to find information on the Document and key in exactly the same locations on the Form.

Microsoft Access Form Wizard offers several images that can be used as background pictures, but I couldn’t find the one I wanted — a simple gradient background. So, I decided to create one myself and use it instead.

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

Creating a Sample Image.

The steps are given below to create the image for the Form Background picture.

Opened a new Microsoft Word Document and went through the following steps to create a Gradient bitmap image:
Selected Format - - >Background - - > Fill Effects.
Under Colors, selected One Color.
Clicked on the Color 1 drop-down control and selected a light color.
Used the Darker/Lighter slider control to adjust the color tone.
Selected the Horizontal Radio Button under Shading Styles.
Clicked on one of the Style Variants.
Clicked OK to apply the selected shade as the Background of the Word Document.
Maximized the Word Document Window.
Selected the Prt Scrn key (at the top row, right side). The full-screen image is now captured and copied to the Clipboard.
Opened the MS Paint Program. You can find this in Start -> Programs -> Accessories.
Displayed the Toolbox (View -> Toolbox or press Ctrl+T).
Selected the Select Tool (the one at the top right with a rectangle picture).
Drawn a rectangle from the left top corner of the gradient image area to the right bottom corner to select the image large enough for my Form's width and height. If you find the width is not enough to cover your form, then change the Page Orientation of the Word Document into Landscape mode before capturing it in MS Paint.
Use Ctrl+C to copy the selected area into the Clipboard.
Click outside the selected area to deselect the earlier selection.
Select Clear Image from the Image Menu (Ctrl+Shift+N) to clear the Canvas. Inspected the right Scroll Bar to check whether it is positioned at the top or not. It was positioned slightly down, so I dragged it up to the top. Similarly, adjusted the Scroll bar at the bottom to the position at the left end.
Pressed Ctrl+V to paste the copied image from the Clipboard.
Clicked outside the pasted image to deselect it.
Dragged the Sizing control at the bottom of the Canvas up and stopped at the edge of the pasted image to eliminate the white area of the canvas.
Similarly, adjust the right side of the image.
Select Save from the File Menu to save the image with a name in a convenient location.

If you’ve been following along so far, we’re now ready to move on to the next stage — implementing the gradient image as a background picture. However, there are a few limitations when using images like this as a form background, but with a little trick, we can easily overcome them.

If you’re thinking about the increase in database size, you’re absolutely right to consider it. And if you’re already thinking about saving the image in JPEG or GIF format, then you’re one step ahead of me! Saving the image type as GIF in MS Paint won’t produce good-quality results. If you have access to software like Adobe Photoshop, you can create high-quality GIF images with much better control.

We’ll use the BMP image format with a few smart modifications to preserve excellent image quality without increasing the database size.

But first, let’s see how to change the form’s background picture to the gradient image.

Inserting the Image on the Form Background.

Before opening your database, you can check the current file size in Windows Explorer. It’s a good idea to compact the database first to get an accurate baseline size. After adding the background picture to a Form, inspect the database size again to see how much it has changed.

Open your Database and open a Form in Design View or create a new one.
Display the Property Sheet of the Form (View -> Properties).
Find the Picture Property and click on it.
Click the Build button (...) in the Property Sheet and click it to browse to the location of the image you have saved and select the image.
The next four Properties are used for formatting and displaying the Background Picture.

Picture Type
Picture Size Mode
Picture Alignment
Picture Tiling

Linked or Embedded Image Methods.

The Picture Type property has two options, Embedded and Linked, with Embedded as the default. The Embedded option allows you to open the default image editor directly from within MS Access by double-clicking the image and editing the picture if needed. However, to support this functionality, Access stores not only the image but also additional information required to invoke the image editor. As a result, each embedded image can significantly increase the size of your database. Note that background images added this way cannot be edited by double-clicking them, unlike OLE object images inserted on a Form.

A better option is the Linked setting, which keeps the image stored externally, leaving your database size unaffected. However, in either case, the image file must remain available in the same location relative to your database if it is moved or shared.

The Picture Size Mode property provides three options. The default option, Clip, displays the picture at its actual size, positioned according to the Picture Alignment setting—Top Left, Top Right, Bottom Right, Bottom Left, Center, or Form Center. The Center option places the image both vertically and horizontally centered on the Form, while Form Center aligns it vertically centered along the left edge.

The Stretch/Zoom/Tile Picture Methods

The Stretch Picture Size Mode option will stretch the image to fit the dimensions of the Form, resulting in distortion of the image if it is a picture of something.

The Zoom Picture Size Mode option will attempt to maintain the right proportions of the image when stretched to match the dimensions of the Form. But both the Stretch and Zoom options will show stretch marks when the Form is restored to its original size. This is much more evident when a .jpeg or GIF image is used.

The One Pixel Width Image.

We can modify our earlier .bmp file to a one-pixel-width image and Tile it across the Form, which will give a good quality gradient picture effect, and the image size will also be small.

Open the earlier saved .bmp image in the MS Paint Program.
Select Attributes... from the Image Menu.
Change the Width value to 1, and leave the Height value unchanged.
Check that Units is selected as Pixels.
Save the image with a different name.
Change the name of the background image to the new image in the Picture property of the Form.

Caution: Do not attempt to use the Picture Size Mode property options Stretch or Zoom; MS-Access may hang up.
Set the Picture Tiling Property Value to Yes and save the Form.
Open the Form in Normal View and enjoy your creation.

MS-Access and Reference Library

a.p.r. pillaiMarch 28, 2009msaccess controls, Utilities No comments

MS Access and Reference Library.

When developing an Access database, we often use several object libraries in addition to the default reference libraries. These include Microsoft Data Access Objects (DAO), Microsoft ActiveX Data Objects (ADODB), and Visual Basic for Applications (VBA), among others. They are essential for creating data processing routines in VBA.

I briefly touched on this topic in my first article on this site, Command Button Animation, where I recommended linking these libraries manually before attempting to run the sample program provided there. The same article also listed the essential library files and the procedure to manually attach them to your database.

Some libraries—such as Microsoft Access 9.0 Object Library, Visual Basic for Applications, and OLE Automation—are attached by default when a new database is created. Additional libraries can be added manually as needed. We add new reference libraries to utilize the powerful features not included in Access by default.

For example, we have used the Microsoft Office Library to create customized message boxes, option balloons, check boxes, and other controls. The Office Assistant is an interesting feature, which we used to capture user responses. We even created a wizard that allows users to select an Office Assistant character—such as Clippy, the Cat, or others—directly from Access, without the built-in Office Assistant options.

Databases and tables can be accessed in VBA only when either the Microsoft DAO or Microsoft ADODB library is referenced in your database.

If you have written your own custom functions, you can store them in a separate database on the server. Attach that database as a Library Database, eliminating the need to copy and paste those functions into other databases.

You can use the Object Browser in the VBA editor (View → Object Browser) to explore the properties, methods, and classes provided by each referenced library.

Library Reference and Physical Files.

Most of the time, we work with a fixed set of library references in our databases. These libraries are typically attached manually through Tools → References in the VBA editor, one by one. Although this is usually a one-time setup task, it can become tedious when starting a new project—or necessary again if references are lost, such as after recovering a corrupted database.

Fortunately, this process can be automated. With a simple VBA routine, we can programmatically attach all required library files to a new project or restore missing links automatically whenever needed.

Before we look at the VBA code, let’s review the few preparatory steps required to accomplish this.

Prepare a list of essential Library References and save it in a Text File on the Server if you are developing databases to use on a Network.
Write a VBA routine to read this list from the Text File and attach it to the Project.
To prepare the required Object Library References List, we must know the details of these files and the Folder on the Disk where they can be located. Assume that we need to know the physical file name and location representing the Reference Library Description Microsoft DAO 3.6 Object Library in the Available References list.

To verify this, open the VBA Module Window by selecting View → Code or pressing Alt + F11. Then, from the menu, choose Tools → References. In the References dialog box that appears, scroll through the list and locate the item Microsoft DAO 3.6 Object Library among the other available Microsoft reference libraries. Refer to the image below for guidance.

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

When you select the file Description, the physical File path name and Language are displayed. You can write down the Pathname to prepare the list if you want to. But we will do half of the task differently. We can attach the required files and prepare the list with a small VBA Routine.

Display List of Library Files with VBA.

Following is a list of References that I use regularly to start with my Projects, and we will use them as an example:

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

The first three items will be automatically selected by MS Access when you create a new Database. Others must be added manually.

Attach the above list of Library References manually.

Copy and paste the following lines of code into a Standard Module of your Project and save it.

Public Sub ReferenceList()
Dim Ref As Reference
For Each Ref In Application.References
   Debug.Print Ref.FullPath
Next
End Sub

Press Ctrl+G to display the Debug Window (Immediate Window) if it is not already visible.
Click in the middle of the Code, press F5 to run it, and print the following Path Names of the selected Reference Library Files in the Debug Window.

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
C:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB
C:\WINDOWS\system32\stdole2.tlb
C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
C:\Program Files\Common Files\system\ado\msado15.dll
C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
D:\MDBS\aprRefLib.mde

Project Names of Library Files.

The internal names (Project Names) of the above library files differ from their file names, and it is essential to know the details. If you ever need to remove any of these libraries from the current project through code, you must reference them by their Project Name rather than by the file name.

When you create the MS-Access database with the name abcd.mdb, by default, the same name abcd will be inserted into the Project Name control in the database. You can check the Project Name of your database by selecting the Tools Menu in the VBA Editing Window. You will find a Menu Option like abcd Properties. You can open this option and set a different name in the Project Name control if needed. If you create a Standard Module with the name abcd, you will run into the error: Name Conflicts with Existing Module, Project, or Object Library.

Note: You may change the Project Name, but you are not allowed to use any of the Standard Modules' Names.

You can read this Property Value in programs or in the Debug Window using the statement x = Application.GetOption("Project Name").

Or modify the Project Name with a statement like Application.SetOption "Project Name", "Myabcd"

The list of library files mentioned above also includes their unique Project Names, which are the internal object library reference names that will appear in your database once linked. You can verify these names by opening the Object Browser (View → Object Browser or F2) and clicking the drop-down list. You will also notice that the Project Name of your current database (for example, abcd) appears among the list.

Let us inspect the Project Names of the first three files in the list given above.

VBA
Access
Stdole

When you create a new database, MS Access attaches the three references by default, and they are important too. So we will exclude them from our add/remove operations.
These names are unique irrespective of which version of Access you are using, and the same goes for the other Library References as well.
If you attempt to attach a different version of a reference library that shares the same Project Name, a conflict may occur. To prevent this, we need to pay special attention to two additional references and ensure they are not removed before attaching the items from our list.
abcd (current database project name)
aprRefLib (you can read this as your own Function Library Project Name, and I will come to that later in this article.)

Leaving aside the above five items, we are left with the following four Reference Libraries selected out of the eight items listed above to attach to our new Projects automatically:

C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll

C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL

C:\Program Files\Common Files\system\ado\msado15.dll

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

Open Notepad.exe, copy and paste the above File Names, and save them in your Server's common location where all your MS-Access Projects are installed.
Let us call the Server PathName (the Location and File Name) of the target text file as

\\hosfs03\InhouseSys\CommonLib\RefLib.txt

The first stage of our preparation is now complete. The next step is to create a VBA program that can read the contents of the text file above and automatically link the reference library files (as shown in the second image) to your new project.

The VBA routine is provided below, but you have two options for where to place the code. The second option—copying the code into each new project and running it from there—is not recommended, as it leads to unnecessary duplication. Instead, choose the first option and do some groundwork now by following the steps below. This approach will make future development easier, allowing you to reuse common routines across all projects without duplicating code.

If you haven’t already started building a Reference Library Database of your own, now is the perfect time. It’s straightforward to create, and you’ll quickly see how useful it can be.

Attaching Missing Library Files with VBA.

Create a new Database named MyLib.mdb and save it to your Server's Common Location. Let us insert this location address, like the one we have saved in our Text File with the list.
Open the VBA Editing Window (Alt+F11).
Create a Standard Module (Insert -> Module) to create an empty Code Module with the name Module1. You can change the name of the Module after displaying its Property Sheet (View ->Properties Window).
Copy and paste the following Code into the Module; save and close the VBA Editing Window.

Public Function AddReferences()
Dim j As Integer, i As Integer, msg As String
Dim Ref As Reference, RefObj As Object
Dim RefPath As String, LibName As String
Dim LibPath As String, libAttached() As String
Dim refcount As Integer, chk_flag As Boolean
Dim lib_Attached As String, validate As Boolean

Const LibraryList As String = "\\hosfs03\InhouseSys\CommonLib\RefLib.txt"

On Error GoTo AddReferences_Err

validate = Ref_Retain_Remove()
If validate = False Then
   msg = "Errors Encountered in Validation check, Program aborted. "
   Exit Function
End If

Set RefObj = Application.References
refcount = Application.References.Count
ReDim libAttached(1 To refcount) As String
i = 0
'Prepare list of exiting attached Library Files
For Each Ref In RefObj
    i = i + 1
    libAttached(i) = Ref.FullPath
Next'Open text file with List of required Reference Library files
Open LibraryList For Input As #1
msg = ""
Do While Not EOF(1)
    Input #1, LibPath
    chk_flag = False
    For j = 1 To i'check for missing cases
        lib_Attached = libAttached(j)
        If libAttached(j) = LibPath Then
            chk_flag = True
            Exit For
        End If
    Next
    If chk_flag = False Then 'Reference found missing, add to the Project
        Set RefObj = Application.References.AddFromFile(LibPath)
        msg = msg & LibPath & vbCr
    End If
Loop
Close #1

If Len(msg) <>  0 Then
    msg = "Following References Attached: " & vbCr & vbCr & msg
End If

MsgBox msg, , " AddReferences()"

AddReferences_Exit:
Exit Function

AddReferences_Err:
MsgBox Err.Description, , "AddReferences()"
Resume AddReferences_Exit
End Function

Public Function Ref_Retain_Remove() As Boolean
Dim Ref As Reference, Reflist(), exclusion(1 To 5) As String
Dim ref_count As Integer, strRefName As String
Dim i As Integer, j As Integer, chk_flag As Boolean

On Error GoTo Ref_Retain_Remove_Err

exclusion(1) = "VBA"
exclusion(2) = "Access"
exclusion(3) = "stdole"
exclusion(4) = Application.GetOption("Project Name")
'Replace this line with your own Reference Library Name
exclusion(5) = "aprRefLib"

ref_count = Application.References.Count

If ref_count > 4 Then
    ReDim Reflist(1 To ref_count)
    i = 0
    For Each Ref In Application.References
       strRefName = Ref.Name
       chk_flag = False
       For j = 1 To 5
          If strRefName = exclusion(j) Then
              chk_flag = True
              Exit For
           End If
        Next
        If chk_flag = False Then
            i = i + 1
       'Collect the Reference Library Project Names, if any, other than
       'the Names in the exclusion list to remove them
       'before attaching the new ones, to avoid Project Name conflict.
            Reflist(i) = Ref.Name
        End If
    Next
    ReDim Preserve Reflist(1 To i)'Remove the collected Reference Libraries
    For j = 1 To i
        Set Ref = References(Reflist(j))
        References.Remove Ref
    Next
End If

Ref_Retain_Remove = True

Ref_Retain_Remove_Exit:
Exit Function

Ref_Retain_Remove_Err:
MsgBox Err.Description, , "Ref_Retain_Remove()"
Ref_Retain_Remove = False
Resume Ref_Retain_Remove_Exit
End Function

Note: Before closing the database, you can save a compiled copy of your Library Database by selecting Tools → Database Utilities → Make MDE File and naming it, for example, MyLib.MDE. Save it in the same location as the .mdb file, and you can move the .mdb file to a private location where others cannot access it.

Whenever you add new common routines to the library, you can recompile and replace the existing .MDE file. This makes the updated programs available to all your projects without modifying them. You can attach your Library Database (MyLib.MDE) along with others to all current and future projects.

We have two programs: the second one validates existing attached references and removes unnecessary ones, leaving only the essential references mentioned earlier to avoid conflicts.

The Demo Run from a new Database.

The preparations are in place, and we are going to do a Trial Run.

Create a new Database.
Open the VBA Editing Window (Alt+F11).
Open the Debug Window (Immediate Window) . . . Ctrl+G.
Type the following line in the Debug Window and press Enter so that we can attach your own Common Library File MyLib.mde and run the main program AddReferences() from there:

Note: Change the Server Location address to match your own.

Application.References.AddFromFile("\\hosfs03\InhouseSys\CommonLib\MyLib.mde")

If you select Tools -> Reference, you can see that your own Library File is now attached to your Project.

We can now call the AddReferences program from your Function Library from the Debug Window and attach the other Library Files List we created in the Text File.

Type the following line in the Debug Window and press the Enter key to do that:

AddReferences

You can now check the Reference Library List to confirm that all the required files are in place. In your next project, all you need to do after creating a new Database is to type the following two lines in the Debug Window and press the Enter key to add all required Reference Libraries to your Project at once:

Application.References.AddFromFile("\\hosfs03\InhouseSys\CommonLib\MyLib.mde")

AddReferences

Hiding Report Lines Conditionally.

Design a Sample Report.

The OnFormat() Event Procedure.

The Report Formatting Passes

The Parameter Table.

The VBA Code Review.

Network And Print Page Setup-3.

Readers have not visited the earlier Pages.

Create a Sample Two-Column Labels Report.

Sample Label and Page Setup in one Image.

The Design Task.

Preparing for Test Run of Program

Continued from Last Week's Page.

Prepare for a Demo Run.

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

The Measurement Unit is Twips.

Earlier Post Link References:

Network And Report Page Setup.

Reports PrtDevMode Property.

Working with the Report.PrtDevMode Property Values

The User-Defined Types str_DEVMODE and Type_DEVMODE

Preparing for a Trial Run.

Earlier Post Link References:

Filter by Character and Sort.

Sample Image of Form.

Sample Data Table, Query & Form.

The VBA Code

Test Run the Demo Form.

Download Demo Database

Animating Label on Search Success.

Visual Indicator Design Task.

Animation: Running VBA Code.

How does it work?

A Better Approach.

Download Demo Database.

Form Background with Gradient Color.

Creating a Sample Image.

Linked or Embedded Image Methods.

The Stretch/Zoom/Tile Picture Methods

The One Pixel Width Image.

MS Access and Reference Library.

Library Reference and Physical Files.

Display List of Library Files with VBA.

Project Names of Library Files.

Attaching Missing Library Files with VBA.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Popular Posts

About Me

Blog Archive

Labels

Statcounter