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


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q210599
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to 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.

NOTE: This article explains a technique demonstrated in the sample file, RptSmp00.mdb. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

231851 ACC2000: Microsoft Access 2000 Sample Reports Available in Download Center

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. The following example uses the sample database Northwind.mdb.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Start Microsoft Access and open the sample database 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.
  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.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbhowto kbProgramming kbusage KB210599
©2004 Microsoft Corporation. All rights reserved.