Move column to front of table

[GIJoeFigure]

In my last post I was looking for a way to set an autonumber field for new tables automatically in code. Now I need to find a way to move the column from the last in line to the first in code, and I haven't had much luck getting this to work yet. I've been trying something like

Forms!Products!ProductName.ColumnOrder = 1

but, it keeps giving me errors. Please help.

 

[PHV]

In a query you choose the column order by the Select list.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AccessGuruCarl]

I'm quessing the form is in datasheet view if your trying to use the ColumnOrder Property, and after you add the field to the table, the field is also appearing as the last column on your form.

If so,
Try this...On the Forms Load Event

ProductName.ColumnOrder = 1

This should do the trick.

You can also click the title, then drag-it to where ever you want it also! Access will then remember it. I use this scenerio to change the column position based on the user logged in, due to having a user that moves one column when she needs to see both Cost & Price. Which caused in-experianced users giving wrong prices when they searched for an item. They were told the first field was the price, so that is what the gave, when actually it was the cost they were giving out. Go figure, I guess no one has time to read the caption/title these days. We as programmers have to do it all...

Hope this helps

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[GIJoeFigure]

AccessGuruCarl,
Is there a way to do that with a table? I just pasted the examples in msdn for the Column Order property. I should've typed:

Tables!CurrentTable!ColumnSpec.ColumnOrder = 1

If there is a way to do this, how or where in the code should this statement be put in?

 

[AccessGuruCarl]

I don't think so, I believe the ColumnOrder Property is only available to datasheets.

You will have to make a temp table, then append the data to the temp table, delete the original table, rename the temp table to the orginal table.

Hope this helps

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[GIJoeFigure]

I actually solved this by using the .OrdinalPosition property. It workwed well and moved the field columns into any order required.

 

[AccessGuruCarl]

Thanks for letting everyone know you solved the problem.

Can you post a piece of the code, so that future viewers of this post... Have an example of what you did.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[GIJoeFigure]

' The following lines move a field to the Leftmost part of the table if it is in the last column.
    cols = tbl2.Fields.Count
    p = tbl2.Fields(cols - 1).Name
    tbl2.Fields(0).OrdinalPosition = 1   ' This line may not be necessary
    tbl2.Fields(cols - 1).OrdinalPosition = 0
    dbs.TableDefs.Refresh
    tbl2.Fields.Refresh

 

[rgbanse]

Function MakeLogTable()
'Create an <ErrorLog> Table in this database
DoCmd.SetWarnings False
Dim ThisDatabase As Database
Dim tdf As TableDef, ErrorID As Field, ErrorCalledFrom As Field
Dim ErrorDescription As Field, ErrorNumber As Field, ErrorCreatedOn As Field, LastEmptiedOn As Field

DoCmd.DeleteObject acTable, "ErrorLog"

Set ThisDatabase = CurrentDb
'Create new table with two fields.
Set tdf = ThisDatabase.CreateTableDef("ErrorLog")
Set ErrorID = tdf.CreateField("ErrorID", dbLong)
'Sets ErrorID as an autonumber field
'ErrorID.Attributes = ErrorID.Attributes + dbAutoIncrField
Set ErrorCalledFrom = tdf.CreateField("ErrorCalledFrom", dbText, 255)
Set ErrorDescription = tdf.CreateField("ErrorDescription", dbText, 255)
Set ErrorNumber = tdf.CreateField("ErrorNumber", dbLong)
Set ErrorCreatedOn = tdf.CreateField("ErrorCreatedOn", dbDate)
Set LastEmptiedOn = tdf.CreateField("LastEmptiedOn", dbDate)
'Append to table fields.
tdf.Fields.Append ErrorID
tdf.Fields.Append ErrorCalledFrom
tdf.Fields.Append ErrorDescription
tdf.Fields.Append ErrorNumber
tdf.Fields.Append ErrorCreatedOn
tdf.Fields.Append LastEmptiedOn

'Append TableDef object.
ThisDatabase.TableDefs.Append tdf
ThisDatabase.TableDefs.Refresh
Set ThisDatabase = Nothing

'Creates unique index on ErrorID field
DoCmd.RunSQL ("create unique index RecordNumber on ErrorLog (ErrorID)WITH DISALLOW NULL; ")

DoCmd.SetWarnings True
End Function


RGB