Copyright © 2008 ASC Associates, Technical Support support@ascassociates.biz , Sales Enquiries sales@ascassociates.biz
Generating default values for fields in a form

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.


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')

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.


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

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
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.

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.

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz


Copyright © 2008 ASC Associates, Technical Support support@ascassociates.biz , Sales Enquiries sales@ascassociates.biz