Microsoft KB Archive/132019

= ACC2: Reference to Field's Caption Property Can Cause an Error =

Article ID: 132019

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q132019



SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

You may receive an error message if you reference a field's Caption property when the following two conditions are true for the Caption property:


 * The Caption property of a local table is null.
 * The Caption property is not null, but belongs to an attached table.

The error message occurs because, to Microsoft Access, the Caption property does not exist if it has not been assigned a value. Therefore, you must ensure that your code traps the error that results when a Caption property contains a Null value. If the Caption property belongs to an attached table, the error trapping routine must also use the OpenDatabase method.

NOTE: Although this article focuses on the Caption property, this information also applies to other Microsoft Access application-defined properties of the Field object.

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



Local Table
To determine if the Caption property from a local table contains a Null value, follow these steps.

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

 Create new database and save it as CAPTION1.MDB. Import the Products table from the sample database NWIND.MDB. Rename the table LocalProducts. Attach the Products table from the sample database NWIND.MDB. Rename the table AttachedProducts.  Create the following form:

     Form: Test1 --     Caption: TestForm1

Command button: Name: ButtonTest Caption: Test OnClick: [Event Procedure] </li>  Set the command button's OnClick property to the following event procedure.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code.

Sub ButtonTest_Click On Local Error Resume Next Dim i As Integer, UserMessage As String Dim db As Database, tdef As TableDef Dim fld As Field, prp As Property Dim TestCaption As String Set db = dbEngine.Workspaces(0).Databases(0)

' Test the local table. Set tdef = db.Tabledefs("LocalProducts") Set fld = tdef.Fields("Product ID") TestCaption = fld.Properties("Caption") If Err = 3270 Then ' Caption does not exist. UserMessage = "The LocalProducts Tables Caption Property_        is Null!" Else UserMessage = "The LocalProducts Table's Caption is [" &_ TestCaption & "]" End If     MsgBox UserMessage

' Test the attached table. Set tdef = db.Tabledefs("AttachedProducts") Set fld = tdef.Fields("Product ID") TestCaption = fld.Properties("Caption") If Err = 3270 Then ' Caption does not exist UserMessage = "The AttachedProducts Table's Caption Property_        is Null!" Else UserMessage = "The AttachedProducts Table's Caption is [" &_ TestCaption & "]" End If     MsgBox UserMessage ButtonTest_Click_End: Exit Sub ButtonTest_Click_Err: MsgBox Error$ Resume ButtonTest_Click_End End Sub </li>  View the Test1 form in Form view and click the Test button. Note that the following messages appear:

<pre class="fixed_text">      The LocalProducts Table's Caption Property is Null! The AttachedProducts Table's Caption Property is Null! </li> Press the F11 key to switch to the Database window.</li> Open the LocalProducts table in Design view.</li> Change the table's Caption property to LocalProducts Caption, and then save the table.</li>  View the Test1 form in Form view and click the Test button. Note that the following messages appear:

<pre class="fixed_text">      The LocalProducts Table's Caption is [LocalProducts Caption] The AttachedProducts Table's Caption Property is Null! </li> Open the sample database NWIND.MDB.</li> Open the Products table in Design view.</li> Change the table's Caption property to AttachedProducts Caption, and then save the table.</li> Open the CAPTION1.MDB database.</li>  View the Test form in Form view and click the Test button. Note that the following messages appear:

<pre class="fixed_text">      The LocalProducts Table's Caption is [LocalProducts Caption] The AttachedProducts Table's Caption Property is Null! </li></ol>

Attached Table
To determine if the Caption property from an attached table contains a Null value, you must use the OpenDatabase method in Access Basic. This opens the database and provides you with the visibility to an external database's properties.

To determine if the Caption property from an attached table contains a Null value, follow these steps.

NOTE: The code in the following example assumes that the sample database NWIND.MDB is located in the C:\ACCESS\SAMPAPPS" directory. If NWIND.MDB is located in a different directory, modify the code accordingly.

<ol> Follow steps 1-5 in the "Local Table" section.</li>  Create the following form:

<pre class="fixed_text">     Form: Test2 --     Caption: TestForm2

Command button: Name: ButtonTest Caption: Test OnClick: [Event Procedure] </li>  Set the command button's OnClick property to the following event procedure:

Sub ButtonTest_Click On Local Error Resume Next Dim i As Integer, UserMessage As String Dim db As Database, tdef As TableDef Dim fld As Field, prp As Property Dim TestCaption As String Set db = dbEngine.Workspaces(0).Databases(0)

' Test the local table. Set tdef = db.Tabledefs("LocalProducts") Set fld = tdef.Fields("Product ID") TestCaption = fld.Properties("Caption") If Err = 3270 Then ' Caption does not exist UserMessage = "The LocalProducts Tables Caption Property_           is Null!" Else UserMessage = "The LocalProducts Table's Caption is [" &_ TestCaption & "]" End If        MsgBox UserMessage

' Test the attached table using the OpenDatabase method. Set db = OpenDatabase("C:\ACCESS\SAMPAPPS\NWIND.MDB") Set tdef = db.Tabledefs("Products") Set fld = tdef.Fields("Product ID") TestCaption = fld.Properties("Caption") If Err = 3270 Then ' Caption does not exist. UserMessage = "The AttachedProducts Table's Caption Property_           is Null!" Else UserMessage = "The AttachedProducts Table's Caption is [" &_ TestCaption & "]" End If        MsgBox UserMessage ButtonTest_Click_End: Exit Sub ButtonTest_Click_Err: MsgBox Error$ Resume ButtonTest_Click_End End Sub </li>  View the Test2 form in Form view and click the Test button. Note that the following message appears:

<pre class="fixed_text">     The LocalProducts Table's Caption is [LocalProducts Caption] The AttachedProducts Table's Caption is [AttachedProducts Caption] </li></ol>

<div class="references_section">