Add =Now() to VBA

[yrstruly]

This VBA adds a modified date/time column to my tables, which is correct. It does not set the auto value, anyway i can do this with a code instead of going through all my tables and manually do it?

Sub AddModifiedDateToAllUserTables()    Dim db As Object
    Dim tdf As Object
    Dim fld As Object
    Dim fieldExists As Boolean
    Dim tableName As String


    Set db = CurrentDb


    For Each tdf In db.TableDefs
        tableName = tdf.Name


        ' ✅ Skip system and temporary tables
        If Not (Left(tableName, 4) = "MSys" Or Left(tableName, 1) = "~") Then
            
            fieldExists = False
            
            ' ✅ Check if ModifiedDate column already exists
            For Each fld In tdf.Fields
                If fld.Name = "ModifiedDate" Then
                    fieldExists = True
                    Exit For
                End If
            Next fld
            
            ' ✅ Add column only if not already present
            If Not fieldExists Then
                ' Add field with DEFAULT Now()
                db.Execute "ALTER TABLE [" & tableName & "] ADD COLUMN ModifiedDate DATETIME DEFAULT Now();", 128
                Debug.Print "✅ Added ModifiedDate with DEFAULT Now() to: " & tableName
            Else
                Debug.Print "ℹ️ Already has ModifiedDate: " & tableName
            End If


        Else
            Debug.Print "⛔ Skipped system/temporary table: " & tableName
        End If
    Next tdf


    MsgBox "ModifiedDate update complete.", vbInformation
End Sub

[CJ_London]

What is the question? To update existing records with now()?

[isladogs]

I believe so...

Add the following line after fieldExists = True and before Exit For

db.Execute "UPDATE [" & tableName & "] SET ModifiedDate = Now();

This will update the field for all records in that table. Is that what you want?
If not add a WHERE filter to the update query to limit the records affected.

[yrstruly]

The purpose is to have a time date olumn, whih capture time stamp every time this row is modified.

[Minty]

The correct syntax for adding the Default value is

CurrentDb.TableDefs("YourTableName").Fields("ModifiedDate").DefaultValue = "Now()"

From here: https://stackoverflow.com/questions/27608708/change-default-value-of-field-through-vba
That assumes the field is already in place.

Another set of descriptions is here : https://stackoverflow.com/questions/...g-in-ms-access

[Welshgasman]

The correct syntax for adding the Default value is

CurrentDb.TableDefs("YourTableName").Fields("ModifiedDate").DefaultValue = "Now()"

From here: https://stackoverflow.com/questions/27608708/change-default-value-of-field-through-vba
That assumes the field is already in place.

Another set of descriptions is here : https://stackoverflow.com/questions/...g-in-ms-access

TBF I did look on how it could be done, and I found that you had to use two statements.
One to add the field. Then the next to set the default value.

Yours seems more intuitive to use.

Of course that setting however created will only work for new records subsequent to it being set.

[yrstruly]

Will it set = "Now()" in the table, not only the code but actucally alter that. So when i click to view the table in desgn mode i should see it for that column.

[Minty]

I'm not sure I understand your question.
Create a copy of a single table, then run the code on that test table to see what happens?

[Micron]

Not sure I see the point of Now() as a default value instead of Date(). When you begin a record you will get a value for Now(). If it takes 5 minutes to finish the record (or worse, I take a coffee break first), then the value at the point of record completion isn't accurate. If time matters, it should be time stamped via code. If the real time doesn't matter, then the value should be Date(). Otherwise, what's the point of time stamping something that is 5, 10 or even 20 minutes off?