When you enter a new record into a SQL Server database you will find that the default values for
fields are not visible until the record has been saved and re-displayed. This short article shows you how
to write some code that will enable your applications to show the defaults before a user starts to type in
data for a new record. This can be an important technique if your application conditionally shows different
control and options based upon fields which should have default values.
The Problem As Seen Through A Linked Table
The sample table below contains several fields with default values,
when you enter a new record the defaults are not going to be shown to the user until after a new record has
been saved. You will get the same situation when users enter data through a form.
Start entering a new record.
Save the record and then the defaults are displayed.
Top of Page
Getting The Defaults For Fields From SQL Server
| The code displayed on the left can be used in SQL Server
2005 to
list the defaults for a particular table. |
The code displayed on the left can be used in SQL Server
2000 to
list the defaults for a particular table.
|
SELECT c.[name], d.[definition] , t.[name] as DataType FROM sys.default_constraints d
INNER JOIN sys.columns c
ON d.[parent_column_id] = c.[column_id]
AND d.[object_id ] = c.[default_object_id]
INNER JOIN sys.types t
ON c.[system_type_id] = t.[system_type_id]
WHERE parent_object_id = OBJECT_ID('dbo.SampleOrder')
|
SELECT COL_NAME(o.[parent_obj],o.[info]) as [Name], [text] as [Definition], t.[name] as DataType
FROM syscomments c
INNER JOIN sysobjects o
ON o.[ID] = c.[ID]
INNER JOIN syscolumns sc
ON sc.[ID] = o.[Parent_obj]
AND sc.[name] = COL_NAME(o.[parent_obj],o.[info])
INNER JOIN systypes t
ON sc.[xtype] = t.[xtype]
WHERE o.[type] = 'D' and o.[Parent_obj] = OBJECT_ID('dbo.titles')
|
Top of Page
Sample Table With Defaults
The defaults will need to be translated, changing for example 1 into -1 for Yes/No Fields,
removing the quotation marks for text fields and translating any functions like 'getdate()' to an
appropriate function.
Form Showing Defaults
Shown below is the form displaying defaults once appropriate code has been added.
Top of Page
Module Code
Create a module and paste in the following code.
Option Compare Database
Option Explicit
Sub util_GetTableDefaults(AccessTableName As String)
' for a specified table get the default values from SQL Server
' plug the values into the forms controls
' SQLTargetTableName includes the schema name if appropriate
Dim SQLTargetTableName As String
Dim SQLConnectionString As String
Dim frm As Form
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Set tdef = db.TableDefs(AccessTableName)
SQLTargetTableName = tdef.SourceTableName
SQLConnectionString = tdef.Connect
Set tdef = Nothing
Dim def As Variant
' Now get all the defaults
Dim rst As Recordset
Dim qdef As QueryDef
Dim sqlstr As String
sqlstr = "SELECT c.[name], d.[definition] , t.[name] as DataType FROM sys.default_constraints d " & _
" INNER JOIN sys.columns c " & _
" ON d.[parent_column_id] = c.[column_id] " & _
" AND d.[object_id ] = c.[default_object_id] " & _
" INNER JOIN sys.types t " & _
" ON c.[system_type_id] = t.[system_type_id] " & _
" WHERE parent_object_id = OBJECT_ID('" & SQLTargetTableName & "')"
Set qdef = db.CreateQueryDef("")
qdef.Connect = SQLConnectionString
qdef.SQL = sqlstr
Set rst = qdef.OpenRecordset
Set frm = Screen.ActiveForm
Dim ctrl As Control
Do While Not rst.EOF
' set the default
' remove and ( ) from the default string
' translate boolean 1 to -1 DataType = bit
' translate getdate to date
def = rst!definition
def = Replace(def, "(", "")
def = Replace(def, ")", "")
If rst!DataType = "bit" And def = 1 Then def = -1
If (rst!DataType = "smalldatetime") Or (rst!DataType = "datetime") And def = "getdate" Then def = Date
' if first or last characters are a single quote then remove this
If VarType(def) = vbString Then
If Right(def, 1) = "'" Then def = Left(def, Len(def) - 1)
If Left(def, 1) = "'" Then def = Right(def, Len(def) - 1)
End If
On Error Resume Next
frm(rst!Name) = def
If Err <> 0 Then
'see if control has been renamed
For Each ctrl In frm.Controls
If ctrl.Properties("ControlSource") = rst!Name Then
frm(ctrl.Properties("ControlSource")) = def
Exit For
End If
Next
End If
Err.Clear
rst.MoveNext
Loop
qdef.Close
rst.Close
db.Close
End Sub
Function util_GetTableDefaultsFromForm(FormsRecordSource As String)
' process the forms recordsource getting the defaults for a single table
' code only designed to work for one table, bt could be generalised
' if table name has spaces then watch for []
Dim FCStart As Long
Dim tn As String
Dim tnEnd As Long
FCStart = InStr(1, FormsRecordSource, "FROM")
FCStart = FCStart + 4
If FCStart = 4 Then
util_GetTableDefaults FormsRecordSource
Else
If Mid(FormsRecordSource, FCStart + 1, 1) = "[" Then
' have spaces in table name
tnEnd = InStr(FCStart + 1, FormsRecordSource, "]")
tn = Mid(FormsRecordSource, FCStart + 2, tnEnd - FCStart - 2)
Else
' no spaces in table name
tnEnd = InStr(FCStart + 1, FormsRecordSource, " ")
' allow for no sort then we have ; after table name
If InStr(FCStart + 1, FormsRecordSource, ";") < tnEnd Then
tnEnd = InStr(FCStart + 1, FormsRecordSource, ";") - 1
End If
tn = Mid(FormsRecordSource, FCStart + 1, tnEnd - FCStart)
tn = Trim$(tn)
End If
util_GetTableDefaults tn
End If
End Function
|
Top of Page
Adding Code To Your Form
On any forms On Current event, add a call to the code after testing for null in
the appropriate key field.
Private Sub Form_Current()
If IsNull(Me.OrderId) Then
util_GetTableDefaultsFromForm Me.RecordSource
End If
End Sub |
Top of Page
Limitations On Code
The code has been written to allow for extracting the defaults for a
single table in the forms record source, and should allow for sorting or
individual fields having been selected in the SQL behind the form. It also
allows for the linked table being renamed.
Top of Page
Download Code
The SQL Server 2005/2000 code can be
downloaded from here Access 2000 Database
MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or
bringing together multiple databases and simplifying security. Click below to improve your
development productivity.
Top of Page
by Andrew Couch Office Access MVP, Director ASC Associates
andy@ascassociates.biz