Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Incorrect ColumnOrder value when you delete a column from a table that is opened in datasheet view

Incorrect ColumnOrder value when you delete a column from a table that is opened in datasheet view

View products that this article applies to.

This article applies to either a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file.

Moderate: Requires basic macro, coding, and interoperability skills.

↑ Back to the top

Symptoms

When you delete a column from a table that is opened in the Datasheet view in Microsoft Access, the ColumnOrder property of the ActiveDatasheet property may display an incorrect value.

↑ Back to the top

Resolution

To resolve this problem, create or delete the column by using the Design view of the table.

↑ Back to the top

Workaround

To work around this problem, follow these steps:
  1. Start Access.
  2. Open the database that contains the table where the ColumnOrder property of the ActiveDatasheet property displays an incorrect value.
  3. In the Database window, select the table that displays the incorrect value, and then press CTRL+C.
  4. Press CTRL+V.
  5. In the Paste Table As dialog box, type Table2 in the Table Name box.
  6. Click to select Structure and Data, and then click OK.
  7. Rename the original table.
  8. Rename the Table2 table to the original table name.

↑ Back to the top

More information

Steps to Reproduce the Behavior
  1. Start Access 2003 or an earlier version of Access.
  2. Create a new database.
  3. Create a new table that is named Table1 with the following field properties:
    Field Name      Data Type
Field1          Text
Field2          Text
Field3          Text
Field4          Text
Field5          Text
Field6          Text
Field7          Text
  4. In the Database window, on the Insert menu, click Module.
  5. In the Microsoft Visual Basic editor, paste or type the following code: 
    Sub PrintColumnOrder()
    Dim FRM As Form
    Dim CTL As Control
    
    Set FRM = Screen.ActiveDatasheet
    
    For Each CTL In FRM.Controls
        Debug.Print CTL.ColumnOrder
    Next CTL
    
    Set CTL = Nothing
    Set FRM = Nothing
End Sub
  6. Save the module as Module1.
  7. Create a new form. Add a command button to the form.
  8. To print the ColumnOrder property of the table, add the following code to the OnClick event of the command button:
    DoCmd.OpenTable "Table1", acViewNormal
    PrintColumnOrder
    DoCmd.Close acTable, "Table1", acSaveNo
  9. Save the form as Form1, and then close the form.
  10. Open Table1 in Datasheet view.
  11. Click Field4, and then insert a column. This will add the Field8 column to the right of Field4.
  12. Reposition the Field4 column to the left of the Field8 column, save the changes, and then close the table.
  13. Open the table in the Datasheet view.
  14. To delete the Field4 column, select Field4, and then click Delete Column on the Edit menu.
  15. Close the table.
  16. Open the Form1 form, click the command button, and then press CTRL+G to view the results in the immediate window. Notice that the column order is printed as 1, 2, 3, 5, 5, 6, 7. This is incorrect.

↑ Back to the top

Keywords: KB824179, kbprb, kbdataview, kbtable, kbview, kbpending, kbdisplay

↑ Back to the top

Article Info
Article ID : 824179
Revision : 3
Created on : 3/29/2007
Published on : 3/29/2007
Exists online : False
Views : 420