Using VBA, I need to rename 1st field in each table

[skydivetom]

Hello:



The attached VBA in form "F_RenameFields scans through all tables and renames existing field [TITLE] into, e.g., [XXX].

For testing purposes only, the position of field [TITLE] has been moved across all sample tables (Table1, Table2, Table3).

This works great!

//

What I need some assistance with is as follows:

- Modify the existing VBA so that it does NOT search for field [TITLE] but instead changes *any field* that is located in the first position in the table.
- For example, in "Table1" the existing field [LASTNAME] would be changed to [XXX].
- Alternatively, in "Table2", the first field happens to be [TITLE] so this would turn into [XXX].
- Finally, in "Table3" [GENDER] now would be changed to [XXX].

My question:
How does the "n.Name" variable [in line "n.Name = Replace(oldname, "TITLE", "XXX")] have to be changed so that it's not looking for [TITLE] but instead rename the 1st field in each table?

[CJ_London]

How about something like

Dim tbl

for each tbl in currentdb.tabledefs
Tbl.fields(0).name=“newname”
next tbl

beware the smart quotes as responding from my phone 📞

[Micron]

That should work as long as no one has ever dragged the field to another column. If so, the notion of 1st is based on appearance, not ordinal position. In other words, if field2 was dragged to field1, the field in the table that appears to be first is not when going by ordinal position.

[CJ_London]

True - in which case loop through the fields collection for a field with the ordinal value of 0.
https://learn.microsoft.com/en-us/of...n-property-dao

note 2 or more fields can be set to the same ordinal value using vba so providing the OP has not done this then should not be a problem

[skydivetom]

CJ_London -- thanks for the info. Looked like a straight-forward modification.

Still I'm getting a run-time error. See attached. What's missing?

Private Sub cmd_MergeTables_Click()
               
    'Declare variables
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim tbl As Object
                
    Set db = CurrentDb()
        
    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then
            With tdf.Fields
                tbl.Fields(0).Name = "XXX"
            End With
        End If

    Next tdf

End Sub

[Micron]

What's missing?

You identifying which line and what the error message (and maybe number) is. You should know that by now?
You don't need table object when you already have declared the tabledef. They are the same thing. Likely your error is that you declared the object but didn't set it.

EDIT -You don't need the with block either + you should set your objects to nothing.

[skydivetom]

Micron -- FYI... I identified the run-time error. See image in post #5. I made reference to it via

"... run-time error. See attached."

[Micron]

I don't see it, but I know why. Sorry about that.