Setting a Default Value for a Table Column

Specifying a default value for a field automatically enters that value in the field each time a new record is added to a table unless the user provides a value for the field. Using DDL, you can add a default value for an existing column with the SET DEFAULT clause. The required syntax is as follows:

ALTER TABLE table_name ALTER [COLUMN] column_name SET DEFAULT default-value;

Notice that [COLUMN] in the above syntax is optional.

©Hands-On 19-14: Setting a Default Value for a Field

This hands-on uses the tblSchools table created in Hands-On 19-2.

1. In the Visual Basic Editor window, insert a new module.

2. In the module's Code window, enter the SetDefaultFieldValue procedure as shown below.

Sub SetDefaultFieldValue()

Dim conn As ADODB.Connection Dim strTable As String Dim strCol As String Dim strDefVal As String Dim strSQL As String

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection

Part III

strTable = "tblSchools" strCol = "City" strDefVal = "Boston" strSQL = "ALTER TABLE " & strTable & _ " ALTER " & strCol & " SET DEFAULT

& strDefVal conn.Execute strSQL


conn.Close Set conn = Nothing Exit Sub ErrorHandler:

MsgBox Err.Number & Resume ExitHere End Sub

:" & Err.Description

I tblSchools : Table

I tblSchools : Table

Rdd Name | Data Type


SchoolId Text

SchoclWame Text

Gty Text

VearEstablshed Dateline

General Lockup

Field Size


input Mask


DeFadt Vdue


Validation Rule

Validation Text


Alow Zero Length



Unicode Compression

IME ftode

No Contre*

[ME Sentence Mode


Smart Tags

Figure 19-5: After running the procedure in Hands-On 19-14, the Default Value property in the Table Design window is set to Boston.

0 0

Post a comment