ACC2000: Column Properties in Query May Disappear After You Run DAO Code or a Wizard

• Microsoft Access 2000

SYMPTOMS

The user-defined properties that you set for all columns in a query may be deleted. If you are using Data Access Objects (DAO) code, the query does not return a value for the properties or uses the properties from the table that the query is based on.

You may also receive one or more of the following error messages when you run the query:

Invalid operation.

-or-

Property can be set only when the Field is part of a Recordset object's Fields collection.

-or-

Operation is not supported for this type of object.

-or-

Property not found.

CAUSE

This problem can occur when you have Name AutoCorrect enabled, and you do one of the following:

• You use a wizard to create a query, form, report, or data access page that is based on the query with user-defined properties.


-or-


• You run DAO code that uses the properties of a column in a query with user-defined properties.

RESOLUTION

Open the query in Design view and reenter the properties.

To avoid this behavior, make the Name AutoCorrect command unavailable. To do so, follow these steps:

1. On the Tools menu, click Options.



2. In the Options dialog box, click the General tab.



3. Click to clear the Track name AutoCorrect info check box.



4. Click OK.



5. Quit, and then restart Access.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a new database and import the Customers table from the sample database Northwind.mdb.



2. Create a query based on the Customers table.



3. Add the CustomerID and the CompanyName fields to the query.



4. Right-click the CustomerID field in the query, and then click Properties.



5. Type a text description in the Description box of the Field Properties dialog box.



6. Save the query as Query1, and then close it.



7. Reopen the query in Design view to confirm that the description is still there.



8. Close the query.



9. In the Immediate window, run the following code that displays the property name and value of the CustomerID field.


NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.



Sub GetFieldDescription()

   Dim DB As DAO.Database
   Dim QD As DAO.QueryDef
   Dim Fld As DAO.Field
   Dim prp As DAO.Property

   On Error GoTo err_GetFieldDescription

   Set DB = CurrentDb
   Set QD = DB.QueryDefs("Query1")
   Set Fld = QD.Fields("CustomerID")

   For Each prp In Fld.Properties
      Debug.Print prp.Name, prp.Value
   Next
   Exit Sub

err_GetFieldDescription:

   If Err.Number = 3251 Then
        Debug.Print Err.Number, Err.Description
        Resume Next
   ElseIf Err.Number = 3219 Then
        Debug.Print Err.Number, Err.Description
        Resume Next
   ElseIf Err.Number = 3267 Then
        Debug.Print Err.Number, Err.Description
        Resume Next
   Else
        Debug.Print Err.Number, Err.Description
      Exit Sub
   End If

End Sub 

10. Reopen the query in Design view. Note that the Description property is blank. Also note that the Description property in the Immediate window is not what you typed. It is the description from the Customers table.

Additional query words: pra description format input mask caption display control

Keywords : kberrmsg kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug