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

ID: Q210599

The information in this article applies to:

Microsoft Access 2000

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

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

SUMMARY

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.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The following example uses the sample database Northwind.mdb.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Start Microsoft Access and open the sample database Northwind.mdb.

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

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


Option Explicit

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.

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

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.

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

Set the following properties for the detail section of the report:


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

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.

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

Save the report as Report1 and close it.

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.

Additional query words: remove delete erase

Keywords : kbusage kbdta AccCon IntpOle RptLayou RptProp
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto