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

ID: Q132019

The information in this article applies to:

Microsoft Access 2.0

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.

MORE INFORMATION

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]

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

View the Test1 form in Form view and click the Test button. Note that the following messages appear:


       The LocalProducts Table's Caption Property is Null!
       The AttachedProducts Table's Caption Property is Null!

Press the F11 key to switch to the Database window.

Open the LocalProducts table in Design view.

Change the table's Caption property to LocalProducts Caption, and then save the table.

View the Test1 form in Form view and click the Test button. Note that the following messages appear:


       The LocalProducts Table's Caption is [LocalProducts Caption]
       The AttachedProducts Table's Caption Property is Null!

Open the sample database NWIND.MDB.

Open the Products table in Design view.

Change the table's Caption property to AttachedProducts Caption, and then save the table.

Open the CAPTION1.MDB database.

View the Test form in Form view and click the Test button. Note that the following messages appear:


       The LocalProducts Table's Caption is [LocalProducts Caption]
       The AttachedProducts Table's Caption Property is Null!

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.

Follow steps 1-5 in the "Local Table" section.

Create the following form:


      Form: Test2
      ------------------
      Caption: TestForm2

      Command button:
         Name: ButtonTest
         Caption: Test
         OnClick: [Event Procedure]

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

View the Test2 form in Form view and click the Test button. Note that the following message appears:


      The LocalProducts Table's Caption is [LocalProducts Caption]
      The AttachedProducts Table's Caption is [AttachedProducts Caption]

REFERENCES

Microsoft Access "Building Applications," version 2.0, Chapter 5, "Access Basic Fundamentals" pages 113-132

Microsoft Access "Building Applications," version 2.0, Chapter 7, "Object and Collections" pages 155-181

Microsoft Access "Building Applications," version 2.0, Chapter 10, "Handling Run-Time Errors" pages 221-238

For more information about the Field object, search for "Field," and then "Field Object, Fields Collection" using the Microsoft Access Help menu.

For more information about the Caption Property, search for "Caption," and then "Caption Property" using the Microsoft Access Help menu.

Keywords : kberrmsg kbprg MdlDao TblFldp
Version : 2.0
Platform : WINDOWS
Issue type : kbinfo