Adding Default Value to Field using code

[EddyLLC]

I am using the following code to create a field in a backend table. It works fine but for the Default Value. No default value is set. I tried using dbInteger with a 0(zero) with the same result. I receive not error message but the default value is not set.

Thanks for the help.
Eddy

Set db = OpenDatabase(DatabasePath)
Set wrkDefault = DBEngine.Workspaces(0)
Set dbsUpdate = wrkDefault.OpenDatabase(DatabasePath, True)
Set tdfF = dbsUpdate.TableDefs("TableF")

With tdfF
Set tdfFieldF = .CreateField("TotFee", dbCurrency)
.Fields.Append tdfFieldF
tdfFieldF.Properties.Append tdfFieldF.CreateProperty("Caption", dbText, "Fee1")
tdfFieldF.Properties.Append tdfFieldF.CreateProperty("Format", dbText, "Currency")
tdfFieldF.Properties.Append tdfFieldF.CreateProperty("Default Value", dbText, "0")
tdfFieldF.Properties.Append tdfFieldF.CreateProperty("Description", dbText, _
"Total Fee1")
End With

 

[ddiamond]

The field object has a default value property that you can set directly. You don't have to do it through the properties collection. Here is an example from the help file:

Sub DefaultValueX()

	Dim dbsNorthwind As Database
	Dim tdfEmployees As TableDef
	Dim strOldDefault As String
	Dim rstEmployees As Recordset
	Dim strMessage As String
	Dim strCode As String

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")
	Set tdfEmployees = dbsNorthwind.TableDefs!Employees

	' Store original DefaultValue information and set the 
	' property to a new value.
	strOldDefault = _
		tdfEmployees.Fields!PostalCode.DefaultValue

tdfEmployees.Fields!PostalCode.DefaultValue = "98052"

	Set rstEmployees = _
		dbsNorthwind.OpenRecordset("Employees", _
		dbOpenDynaset)

	With rstEmployees
		' Add a new record to the Recordset.
		.AddNew
		!FirstName = "Bruce"
		!LastName = "Oberg"

		' Get user input. If user enters something, the field 
		' will be filled with that data; otherwise, it will be 
		' filled with the DefaultValue information.
		strMessage = "Enter postal code for " & vbCr & _

!FirstName & " " & !LastName & ":"
		strCode = DefaultPrompt(strMessage, !PostalCode)
		If strCode <> "" Then !PostalCode = strCode
		.Update

		' Go to new record and print information.
		.Bookmark = .LastModified
		Debug.Print "    FirstName = " & !FirstName
		Debug.Print "    LastName = " & !LastName
		Debug.Print "    PostalCode = " & !PostalCode

		' Delete new record because this is a demonstration.
		.Delete
		.Close
	End With

' Restore original DefaultValue property because this is a 
	' demonstration.
	tdfEmployees.Fields!PostalCode.DefaultValue = _
		strOldDefault

	dbsNorthwind.Close

End Sub

 

[EddyLLC]

I attempted to adapt the code you listed, thanks by the way,
an I get a Type Mismatch error when I try the
Set tdfDedaultQF = db.TableDefs!Est_QF statement. Any thoughts? Thanks.

Dim tdfDefaultQF As Recordset,
Dim tdfDefaultQF As Recordset

Set db = OpenDatabase(DatabasePath)
Set tdfDefaultQF = db.TableDefs!Est_QF
tdfDefault.Fields!TotBlueberryFee.DefaultValue = "0"

 

[EddyLLC]

Never mind. I discovered the problem. As you can see I dimmed the variables as recordsets instead of TableDef's. Too long a day I guess.

Thanks again!