Add Default Value in Table via Code

[EddyLLC]

I am using the following code to create a table in the backend. How would I set the Default value for the new field created (Field1)?

Set Db = OpenDatabase("C:\Backend.mdb")
Set tdfNew = Db.CreateTableDef("flkpNumber")
With tdfNew
.Fields.Append .CreateField("Field1", dbLong, 30)
Db.TableDefs.Append tdfNew
End With

 

[mikey69]

DefaultValue property in the help file will sort you out, I think. Here's the example they give:

This example uses the DefaultValue property to alert the user of a field's normal value while prompting for input. In addition, it demonstrates how new records will be filled using DefaultValue in the absence of any other input. The DefaultPrompt function is required for this procedure to run.

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



Access makes all things possible. It even makes them intelligible