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

ExitHere:

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

Description

SchoolId Text

SchoclWame Text

Gty Text

VearEstablshed Dateline

General Lockup

Field Size

Format

input Mask

Caption

DeFadt Vdue

Boston

Validation Rule

Validation Text

Required

Alow Zero Length

Yes

Indexed

Unicode Compression

IME ftode

No Contre*

[ME Sentence Mode

None

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