DefaultValue Property

Applies To

Field object.

Description

Sets or returns the default value of a Field object. For a Field object not yet appended to the Fields collection, this property is read/write (Microsoft Jet workspaces only).

Settings and Return Values

The setting or return value is a String data type that can contain a maximum of 255 characters. It can be either text or an expression. If the property setting is an expression, it can't contain user-defined functions, Microsoft Jet database engine SQL aggregate functions, or references to queries, forms, or other Field objects.

Note You can also set the DefaultValue property of a Field object on a TableDef object to a special value called "GenUniqueID( )." This causes a random number to be assigned to this field whenever a new record is added or created, thereby giving each record a unique identifier. The field's Type property must be Long.

Remarks

The availability of the DefaultValue property depends on the object that contains the Fields collection, as shown in the following table.

If the Fields collection belongs to an	Then DefaultValue is

Index object	Not supported
QueryDef object	Read-only
Recordset object	Read-only
Relation object	Not supported
TableDef object	Read/write

When a new record is created, the DefaultValue property setting is automatically entered as the value for the field. You can change the field value by setting its Value property.

The DefaultValue property doesn't apply to AutoNumber and Long Binary fields.

Specifics (Microsoft Access)

In Microsoft Access, if you set the DAO DefaultValue property to GenUniqueID( ) to create an AutoNumber field, you must also set the Attributes property to dbAutoIncrement. If you don't set the Attributes property in this way, Microsoft Access won't assign the AutoNumber data type to the field, and you'll get an error when you try to view the table in Datasheet view.

The following code shows how you can create a field with the AutoNumber data type, given a TableDef object variable tdf and a Field object variable fld.

Set fld = tdf.CreateField("EmployeeID", dbLong)
fld.DefaultValue = "GenUniqueID()"
fld.Attributes = dbAutoIncrField
tdf.Fields.Append fld

If the DAO DefaultValue property setting is an expression, it can't contain user-defined functions, Microsoft Access domain aggregate functions, SQL aggregate functions, the CurrentUser function, the Eval function, or references to queries, forms, or other Field objects.

Example

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

Function DefaultPrompt(strPrompt As String, _
    fldTemp As Field) As String

    Dim strFullPrompt As String

    ' Ask user for new DefaultValue setting for the specified
    ' Field object.
    strFullPrompt = strPrompt & vbCr & _
        "[Default = " & fldTemp.DefaultValue & _
        ", Cancel - use default]"
    DefaultPrompt = InputBox(strFullPrompt)

End Function

Example (Microsoft Access)

The following example creates a new Field object and sets its DefaultValue property. The procedure then appends the new object to the Fields collection of the Employees table in the TableDefs collection of the database.

Sub NewField()
    Dim dbs As Database, tdf As TableDef
    Dim fld As Field

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Employees table.
    Set tdf = dbs.TableDefs!Employees
    ' Create Field object.
    Set fld = tdf.CreateField("DaysOfVacation", dbText, 20)
    ' Set field properties.
    fld.DefaultValue = "10"
    ' Append fld to Fields collection.
    tdf.Fields.Append fld
    Set dbs = Nothing
End Sub