CODEWITHSUNDEEP
ms-accessvba
Pablo
Pablo

Reputation: 93

Change default value of field through vba

I am trying to change the default value of a field I just added through VBA. I have this line of code below that I added in to do so but for some reason it is saying the item is not in the collection and I am really confused as to why. Any help would be greatly appreciated!

AddColumn = "ALTER TABLE ShouldImportMetricsIDsTable " & _
"ADD COLUMN [ImportStatus] TEXT"

db.Execute AddColumn

CurrentDb.TableDefs("ShouldImportMetricsIDs").Fields("ImportStatus").DefaultValue = "No"

I have also included the line that adds the field.

Upvotes: 0

Views: 9563

Answers (2)

4dmonster
4dmonster

Reputation: 3031

You just pointed to different tables in ALTER TABLE you referenced ShouldImportMetricsIDsTable and in VBA code ShouldImportMetricsIDs.

P.S. DefaultValue property is build in so you can easily set its value 

CurrentDb.TableDefs("ShouldImportMetricsIDsTable").Fields("ImportStatus").DefaultValue = "No"

but some other field properties are odd so you have to use constructions like that:

Dim p As Property
Set p = CurrentDb.TableDefs("ShouldImportMetricsIDsTable").Fields("ImportStatus").CreateProperty("DefaultValue", dbText, "No")
CurrentDb.TableDefs("ShouldImportMetricsIDsTable").Fields("ImportStatus").Properties.Append (p)

Upvotes: 3

Access_Query
Access_Query

Reputation: 99

I think you can add the default value to the ALTER TABLE expression:

CurrentProject.Connection.Execute "ALTER TABLE ShouldImportMetricsIDsTable " & _
"ADD COLUMN [ImportStatus] TEXT" & _
"DEFAULT ""No"";"

I'm basing my answer off this question: SQL SET DEFAULT not working in MS Access

EDIT: Looking at the answer from HansUp in that link, I edited the answer above to reflect the syntax he uses there. I hope this works for you.

Upvotes: 1

Related Questions