Hi All,

Just a quick question relating to saved table layouts. Basically if columns are moved around in normal view then saved the positions of the columns are also saved. In design view the fields ordinal position are still correct.

Is there a way of setting the table display layout to be the same as the field ordinal position? Note I am not letting the user open tables directly but I find myself saving the layout by mistake. I am hoping I can move through all the tables and set the correct positions.

Thanks in advance.

Cheers,
Adam.

You can set the "ColumnOrder" property of the field. But one thing to note, the "ColumnOrder" property is not an inherent DAO (or ADO for that matter) property of a field. It is added to the field via the Access user interface and since the property is a "custom" property from a database engine (ie: JET/ACE) point of view, you can reference it through the DAO.Properties collection of the DAO.Field... see the code below to see what I mean.


Public Sub ResetColumnOrderToOrdinalPosition(strTableName As String)
'Reset column order to the ordinal position, AND reset the width
'to the default column width. These settings effect the Datasheet
'view of a Table object

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs(strTableName)

For Each fld In tdf.Fields
fld.Properties("ColumnOrder") = fld.OrdinalPosition
fld.Properties("ColumnWidth") = -1 'Sets the width to the default width
Next fld

End Sub


As you can see, I set the column width too ... I know you didn't ask for it, but sometimes they go hand in hand. A -1 for column width is the default width. The unit of measure of the ColumnWidth property is a Twip. 1440 twips make an inch.

Excellent datAdrenaline -appreciate the information.

Cheers,
Adam.

You can set the "ColumnOrder" property of the field. But one thing to note, the "ColumnOrder" property is not an inherent DAO (or ADO for that matter) property of a field. It is added to the field via the Access user interface and since the property is a "custom" property from a database engine (ie: JET/ACE) point of view, you can reference it through the DAO.Properties collection of the DAO.Field... see the code below to see what I mean.


Public Sub ResetColumnOrderToOrdinalPosition(strTableName As String)
'Reset column order to the ordinal position, AND reset the width
'to the default column width. These settings effect the Datasheet
'view of a Table object

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs(strTableName)

For Each fld In tdf.Fields
fld.Properties("ColumnOrder") = fld.OrdinalPosition
fld.Properties("ColumnWidth") = -1 'Sets the width to the default width
Next fld

End Sub


As you can see, I set the column width too ... I know you didn't ask for it, but sometimes they go hand in hand. A -1 for column width is the default width. The unit of measure of the ColumnWidth property is a Twip. 1440 twips make an inch.

You are most welcome!! ... Good Luck with your project!!