Fair point on the autonumber bit - I tend to avoid using these as they're not very relational and do nasty things when you start moving them around...
Originally Posted by apeo
To be honest, I'd have just run a series of
UPDATE table SET field='Default' WHERE field IS NULL;
queries, but here's the equivalent thing in VBA, also picking up the default field value from the table properties where it's available, and confirming each column query before running:
Dim td As TableDef, fl As Field
Dim sq As String, bUseDefault As Boolean
bUseDefault = True ' Setting to false will ignore default value in field properties
For Each td In CurrentDb.TableDefs
If td.Name = "PUT_TABLE_NAME_HERE" Then Exit For
For Each fl In td.Fields
If fl.Type = dbText Then
sq = "UPDATE [" & td.Name & "] SET [" & fl.Name & "]="
If fl.DefaultValue <> "" And bUseDefault Then
sq = sq & fl.DefaultValue
sq = sq & "'" & "PUT_DEFAULT_VALUE_HERE" & "'"
sq = sq & " WHERE [" & fl.Name & "] IS NULL;"
If MsgBox(sq, vbYesNo) = vbYes Then CurrentDb.Execute (sq)