Microsoft KB Archive/247488: Difference between revisions

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - """ to """)
 
Line 187: Line 187:


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


   For Each prp In Fld.Properties
   For Each prp In Fld.Properties

Latest revision as of 12:50, 21 July 2020

Article ID: 247488

Article Last Modified on 12/14/2002



APPLIES TO

  • Microsoft Access 2000 Standard Edition



This article was previously published under Q247488

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

This article applies only to a Microsoft Access database (.mdb).


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, a form, a report, or a data access page that is based on the query with user-defined properties.


-or-

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


RESOLUTION

IMPORTANT: If you have Access 2000 running on Microsoft Windows 2000, Microsoft Office 2000 Service Release 1 (SR-1) will not correct this problem.

To resolve this problem on Windows 2000, obtain Windows 2000 Service Pack 1 (Win 2000 SP1). For information on downloading SP1 or obtaining the Windows 2000 SP1 CD, please visit the following Microsoft Web site:

To resolve this problem on non-Windows 2000-based computers, obtain Microsoft Office 2000 Service Release 1 (SR-1). To resolve this problem, obtain Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a).

To obtain SR-1/SR-1a, click the article number below to view the article in the Microsoft Knowledge Base:

245025 OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a)


Even though Office 2000 SR-1 corrects the problem of deleting user-defined properties, the DAO code may not return the expected information. For example, if the table that your field comes from has text in the Description property, the code returns the table's description instead of the query's description. 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 that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Windows 2000 Service Pack 1.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a new database and import the Customers table from the sample database Northwind.mdb.
  2. On the Tools menu, click Options.
  3. On the General tab, ensure that Perform Name Autocorrect is selected. Click OK.
  4. In the Database window, click Queries, and then click New.
  5. Create a query based on the Customers table that you imported earlier.
  6. Add the CustomerID and the CompanyName fields to the query.
  7. Right-click the CustomerID field in the query, and then click Properties.
  8. Type a text description in the Description box of the Field Properties dialog box, such as the following:

    This is a test

  9. Save the query as Query1, and then close it.
  10. Reopen the query in Design view to confirm that the description is still there.
  11. Close the query.
  12. In the Database window, click Modules, and then click New.
  13. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

    Type the following code into the new module:

    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
                        
  14. In the Immediate window, enter the following:

    GetFieldDescription

    Note that the property name and value of the CustomerID field are displayed.
  15. Reopen the query in Design view. Note that the Description property for CustomerID is blank.


REFERENCES

For additional information about a similar issue involving ActiveX Data Objects affecting queries in an Access database, click the article number below to view the article in the Microsoft Knowledge Base:

256101 ACC2000: Properties Are Missing from Queries After You Access TableDef Object with ADOX Catalog Object



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

Keywords: kberrmsg kbbug kbfix kbwin2000sp1fix kbdta kbwin2000presp1fix KB247488