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