Can I move a field in a table?

[ghudson]

Can I change the order of my fields in my table using VBA? I have an autonumber field that I create [add to a table] using VBA and it always adds the new autonumber field to the end of the table. I need to move the new autonumber field to the beginning of the table.

I am using the function
Creating an AutoNumber field from code I found @ MVPS

Thanks in advance for your help!

 

[NaKin]

If I understand the question correctly all you should have to do is go into the design of the table, highlight the field, then drag it to the position you want. (oh, then save the table.)

 

[ghudson]

My fault for not being more specific. I want to do this using VBA. The db is secured and the users do not have design access to the db objects.

 

[FoFa]

Why does it matter where the field is physically in the table?

 

[ghudson]

The data is being transferred to another system that needs the first column to be the ID [autonumber] field. It would be easier if I could get the table in the order I need before exporting the table data. The daily output has to have the first record start with ID # 1 and so on. I got everything working except the placement of the ID field when I create it in the table.

Basically, I am deleting the records from the table, then I am deleting the ID field and then creating [adding] the ID field with the autonumber data type. Then I append the daily records to the table for output.

Is possible to move the ID field in the table from the bottom to the top [or from the right end to the left end, depending on how you look at it] using VBA?

Thanks!

 

[dcx693]

I don't know if physically moving the order of the fields is possible, but could you output the data from a query instead?

 

[FoFa]

I am with dcx693 on this one, just use a query.

 

[smed]

I know this reply is a bit late, but I had the same problem and have worked out a solution.

Sub SetFieldPosition(strTableName As String, strFieldName As String, intFieldPosition As Integer)
Dim dbs As Database
Dim tdf As TableDef

Set dbs = CurrentDb()
Set tdf = dbs.TableDefs(strTableName)
tdf.Fields(strFieldName).OrdinalPosition = intFieldPosition
dbs.Close
End Sub

HTH
Smed

 

[ghudson]

Thanks smed. That almost works for me. When I call the function it moves to the second position from the left [or top in design view]. What setting are you using to move the field in the table to the first position?

Public Function MoveTableField()
    Call SetFieldPosition("MyTable", "MyField", 0)
End Function

Sub SetFieldPosition(strTableName As String, strFieldName As String, intFieldPosition As Integer)
    
    Dim dbs As Database
    Dim tdf As TableDef
    
    Set dbs = CurrentDb()
    Set tdf = dbs.TableDefs(strTableName)
    tdf.Fields(strFieldName).OrdinalPosition = intFieldPosition
    dbs.Close

    Set dbs = Nothing
    Set tdf = Nothing

End Sub

Thanks!