ACC: How to Make Empty or Null OLE Object Not Appear on Report

ID: Q90855

The information in this article applies to:

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

If a report has OLE objects that are empty or null, you can set up a report so that empty or null OLE objects do not appear.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

NOTE: This article explains a technique demonstrated in the sample files, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0) and RptSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q145777
   TITLE     : ACC95: Microsoft Access Sample Reports Available on MSL

   ARTICLE-ID: Q175072
   TITLE     : ACC97: Microsoft Access 97 Sample Reports Available on MSL

MORE INFORMATION

The following example uses the sample database Northwind.mdb (or NWIND.MDB In versions 1.x and 2.0).

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or the NWIND.MDB in versions 1.x and 2.0.) You may want to back up the Northwind.mdb (or NWIND.MDB) file or perform these steps on a copy of these databases.

 1. Start Microsoft Access and open Northwind.mdb.

 2. Open the Employees table in Datasheet view and add a few records. Do
    not insert any objects in the Photo field.

 3. Create a module and type the following line in the Declarations
    section if it is not already there:

       Option Explicit

 4. Type the following procedure:

       Function HideNullOle (ctl As Control)
          On Error Resume Next
          ctl.visible = Not IsNull(ctl)
          If Err = 2427 Then ctl.visible = True
       End Function

    The function checks to see if the OLE Object is null. If it is null,
    the control is hidden. If it is not null, the control is not hidden.
    Some OLE Objects take longer to retrieve data and will generate error
    2427, "Object has no value." When this error is generated, the OLE
    Object is not null and the control is not hidden.

 5. Create a new report in Design view based on the Employees table.

 6. If the field list is not visible, click Field List on the View menu.
    Drag EmployeeID, LastName, FirstName, and Photo from the field list to
    the Detail section of the report.

    NOTE: In versions 1.x and 2.0, there is a space in Employee ID,
    Last Name, and First Name.

 7. If the property sheet is not visible, click Properties on the View
    menu.

 8. Set the following properties for the Detail section of the report:

      CanShrink: Yes
      OnFormat: =HideNullOle([Photo])

 9. Add an unbound text box to your report that completely overlaps the
    Photo control, and set the following properties:

      Visible: No
      CanShrink: Yes

    The text box is necessary to cause the items below it to be pulled up.

    of the unbound text box.

12. Open the report in Print Preview and scroll through the pages of the

    report to the last page. Note how the CanShrink property works with
    null or empty OLE objects. When an OLE object is visible, the
    CanShrink property prevents the empty text box from shrinking. When
    the OLE object is invisible, the text box shrinks to nothing.

Keywords          : kbusage IntpOle RptLayou RptProp 
Version           : 1.0 1.10 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998