Microsoft KB Archive/210599

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

Article ID: 210599

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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.

 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.</li> If the property sheet is not visible, click Properties on the View menu.</li>  Set the following properties for the detail section of the report: <pre class="fixed_text">  CanShrink: Yes OnFormat: =HideNullOle([Photo]) </li>  Add an unbound text box to your report that completely overlaps the Photo control, and set the following properties: <pre class="fixed_text">  Visible: No   CanShrink: Yes The text box is necessary to cause the items below it to be pulled up. </li> On the Format menu, click Send To Back. The OLE object appears on top of the unbound text box.</li> Save the report as Report1 and close it.</li> 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.</li></ol>

Additional query words: remove delete erase

Keywords: kbhowto kbprogramming kbusage KB210599

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.