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

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

10. On the Format menu, click Send To Back. The OLE object appears on top of the unbound text box.

11. Save the report as Report1 and close it.

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.