Variables (both private and public) certainly have their uses, but if you need to store some data in a way that persists through database closing and reopening, custom database properties are the way to go. Values stored in these properties can be used in forms, queries and reports, as well as in code.
While you're working through the article you can refer to this sample database to see the variables in action: Custom Properties.mdb
Creating a Property
You can create custom database properties in an Access database by selecting File => Info => "View and edit database properties" (see screenshot below) to open the Properties dialog, unchanged for many versions now regardless of cosmetic changes to Access.
Click the Custom tab of the dialog to see the custom properties; this dialog works much the same as the dialog for Word document properties, at least for properties you create in the interface:
Helpful Tip: As with Word doc properties, there is a trick to adding a new custom property of the Text type -- you can't add a property with no value, so you need to enter (or set in code) a single space when adding a new Text type property, unless you actually need a specific default value for it.
This dialog is useful for entering miscellaneous information that you might want to modify in the interface, but it is generally more useful to create and edit custom database properties in VBA code.
Creating Properties in VBA
Database properties created in code can't be viewed in the Properties dialog, which is either an annoying bug or a useful security feature, depending on how you look at it. The code listed below can be saved as a standard module (or you can import the procedures into an existing module); it has procedures for creating and retrieving data stored in custom properties, as well as listing them to the Immediate Window.
Option Compare Database
Option Explicit
Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties
Public Sub SetProperty(strName As String, lngType As Long, _
varValue As Variant)
'Created by Helen Feddema 31-Mar-2017
'Modified by Helen Feddema 31-Mar-2017
'Called from various procedures
On Error GoTo ErrorHandler
'Attempt to set the specified property
Set dbs = CurrentDb
Set prps = dbs.Properties
prps(strName) = varValue
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err.Number = 3270 Then
'The property was not found; create it
Set prp = dbs.CreateProperty(Name:=strName, _
Type:=lngType, Value:=varValue)
CurrentDb.Properties.Append prp
Resume Next
Else
MsgBox "Error No: " & Err.Number _
& " in SetProperty procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Sub
Public Function GetProperty(strName As String, strDefault As String) _
As Variant
'Created by Helen Feddema 31-Mar-2017
'Modified by Helen Feddema 31-Mar-2017
'Called from various procedures
On Error GoTo ErrorHandler
'Attempt to get the value of the specified property
Set dbs = CurrentDb
GetProperty = dbs.Properties(strName).Value
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err.Number = 3270 Then
'The property was not found; use default value
GetProperty = strDefault
Resume Next
Else
MsgBox "Error No: " & Err.Number _
& " in GetProperty procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Function
Public Function ListCustomProps()
'Created by Helen Feddema 31-Mar-2017
'Modified by Helen Feddema 31-Mar-2017
'Lists custom database properties
On Error Resume Next
Set dbs = CurrentDb
Debug.Print "Custom database properties:"
For Each prp In _
dbs.Containers("Databases").Documents("UserDefined").Properties
Debug.Print vbTab & prp.Name & ": " & prp.Value
Next prp
End Function
Public Function ListAllProps()
'Created by Helen Feddema 31-Mar-2017
'Modified by Helen Feddema 31-Mar-2017
'Lists all database properties
On Error Resume Next
Set dbs = CurrentDb
Debug.Print "All database properties:"
For Each prp In dbs.Properties
Debug.Print vbTab & prp.Name & ": " & prp.Value
Next prp
End Function
Getting a List of Properties
There are two procedures for listing properties in the module: ListCustomProps and ListAllProps.
It would be nice if you could get a list of just your custom properties and their values, but unfortunately that is not the case -- neither of these procedures yields a list of just the custom properties you have created in code. However, you can see your properties (among a long list of other properties) by using the ListAllProps procedure.
The ListCustomProps procedure lists a few built-in properties, as well as any custom properties created in the interface:
Custom database properties:
Name: UserDefined
Owner: admin
UserName: admin
Permissions: 0
AllPermissions: 65536
Container: Databases
DateCreated: 3/31/2017 11:03:59 AM
LastUpdated: 3/31/2017 11:03:59 AM
ReplicateProject: True
If you run the ListAllProps procedure, you will get a much longer list, which (curiously) lists many custom properties created by one or more add-ins that I use, as well as custom properties created in code, and built-in properties:
All database properties:
Name: \\OPTIPLEX-HOME\Users\Helen Feddema Home\Documents\ExpertsExchange\Article Images\Custom Properties.mdb Connect:
Transactions: True
Updatable: True
CollatingOrder: 1033
QueryTimeout: 60
Version: 4.0
RecordsAffected: 0
ReplicaID:
DesignMasterID:
ANSI Query Mode: 0
Themed Form Controls: 1
AccessVersion: 08.50
NavPane Category: 0
Show Navigation Pane Search Bar: 1
UseMDIMode: 1
ShowDocumentTabs: True
Build: 1162
CheckTruncatedNumFields: 1
AppTitle: Custom Properties Demo
StartUpShowDBWindow: True
StartUpShowStatusBar: True
AllowShortcutMenus: True
AllowFullMenus: True
AllowBuiltInToolbars: True
AllowToolbarChanges: True
AllowSpecialKeys: True
UseAppIconForFrmRpt: False
AllowDatasheetSchema: True
WebDesignMode: 0
DesignWithData: True
Show Values Limit: 1000
Show Values in Indexed: 1
Show Values in Non-Indexed: 1
Show Values in Remote: 0
Auto Compact: 0
Track Name AutoCorrect Info: 0
Picture Property Storage Format: 1
ProjVer: 119
StartUpForm: frmCustomProperties
ExclusionChoice: 3
BackupSuffix: _copy
BackupChoice: 2
BackupPrefix: _
PrefixOrSuffix: 2
DateFormat: d-mmm-yyyy
NavPane Closed: 0
NavPane Width: 316
NavPane View By: 0
NavPane Sort By: 1
FrontEndSaveNo: 2
FrontEndSaveDate: 3/31/2017
FolderPath: C:\Users\Helen Feddema\Documents\Address Book
BooleanValue: False
LongValue: 4523
CurrencyValue: 1500
TextValue: Next meeting is in August 2017
IntegerValue: 14
SingleValue: 2545.259
DoubleValue: 14552.36
DateValue: 3/22/2017
A Custom Properties Demo
To demonstrate the use of Access custom database properties, I made an unbound form with controls for entering and displaying custom properties of various data types.
Each data type has a control for selecting or entering a value, which is saved from the AfterUpdate event of the control to a custom property of the appropriate data type. The form's Load event retrieves the values and writes them to the controls.
The Select Folder Path command button uses an Office FileDialog FolderPicker dialog to let you select a folder, whose path is then saved to the FolderPath custom property. This is very handy when you need to use a folder path in code, but don't want to hard-code it. Once the path is selected, it can be retrieved when needed anywhere in your code. You only need to re-select the path if you decide that you need another folder.
The demo form's code is listed below:
Option Compare Database
Option Explicit
Private blnValue As Boolean
Private curValue As Currency
Private dblValue As Double
Private dteValue As Date
Private intValue As Integer
Private lngDataType As Long
Private lngValue As Currency
Private sglValue As Single
Private strFolderPath As String
Private strPrompt As String
Private strPropertyName As String
Private strPropertyValue As String
Private strText As String
Private strTitle As String
Private Sub chkBooleanValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017
On Error GoTo ErrorHandler
blnValue = Nz(Me![chkBooleanValue].Value, False)
strPropertyName = "BooleanValue"
lngDataType = dbBoolean
Call SetProperty(strPropertyName, lngDataType, blnValue)
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Private Sub cmdSelectFolderPath_Click()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017
On Error GoTo ErrorHandler
Dim fd As Office.FileDialog
Dim txt As Access.TextBox
'Create a FileDialog object as a Folder Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
Set txt = Me![txtFolderPath]
strPropertyName = "FolderPath"
With fd
.Title = "Browse for folder"
.ButtonName = "Select"
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then
strPropertyValue = CStr(fd.SelectedItems.Item(1))
Debug.Print "Property value: " & strPropertyValue
lngDataType = dbText
Call SetProperty(strPropertyName, lngDataType, _
strPropertyValue)
txt.Value = strPropertyValue
Else
Debug.Print "User pressed Cancel"
End If
End With
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Private Sub Form_Load()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017
On Error Resume Next
DoCmd.RunCommand acCmdSizeToFitForm
On Error GoTo ErrorHandler
'Load control values from custom properties
strFolderPath = Nz(GetProperty("FolderPath", ""))
Me![txtFolderPath].Value = strFolderPath
strText = Nz(GetProperty("TextValue", ""))
Me![txtTextValue].Value = strText
curValue = Nz(GetProperty("CurrencyValue", 0))
Me![txtCurrencyValue].Value = curValue
dteValue = Nz(GetProperty("DateValue", Date))
Me![txtDateValue].Value = dteValue
intValue = Nz(GetProperty("IntegerValue", 0))
Me![txtIntegerValue].Value = intValue
lngValue = Nz(GetProperty("LongValue", 0))
Me![txtLongValue].Value = lngValue
blnValue = Nz(GetProperty("BooleanValue", False))
Me![chkBooleanValue].Value = blnValue
dblValue = Nz(GetProperty("DoubleValue", 0))
Me![txtDoubleValue].Value = dblValue
sglValue = Nz(GetProperty("SingleValue", 0))
Me![txtSingleValue].Value = sglValue
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.Name & " Form_Load procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Private Sub txtCurrencyValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017
On Error GoTo ErrorHandler
curValue = Nz(Me![txtCurrencyValue].Value, 0)
strPropertyName = "CurrencyValue"
lngDataType = dbCurrency
Call SetProperty(strPropertyName, lngDataType, curValue)
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Private Sub txtDateValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017
On Error GoTo ErrorHandler
If IsDate(Me![txtDateValue].Value) = False Then
strTitle = "Invalid date"
strPrompt = "Please enter a valid start date"
Me![txtDateValue].SetFocus
MsgBox prompt:=strPrompt, _
buttons:=vbExclamation + vbOKOnly, _
Title:=strTitle
GoTo ErrorHandlerExit
Else
dteValue = Nz(Me![txtDateValue].Value, Date)
End If
strPropertyName = "DateValue"
lngDataType = dbDate
Call SetProperty(strPropertyName, lngDataType, dteValue)
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Private Sub txtDoubleValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017
On Error GoTo ErrorHandler
dblValue = Nz(Me![txtDoubleValue].Value, 0)
strPropertyName = "DoubleValue"
lngDataType = dbDouble
Call SetProperty(strPropertyName, lngDataType, dblValue)
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Private Sub txtIntegerValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017
On Error GoTo ErrorHandler
intValue = Nz(Me![txtIntegerValue].Value, 0)
strPropertyName = "IntegerValue"
lngDataType = dbInteger
Call SetProperty(strPropertyName, lngDataType, intValue)
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Private Sub txtLongValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017
On Error GoTo ErrorHandler
lngValue = Nz(Me![txtLongValue].Value, 0)
strPropertyName = "LongValue"
lngDataType = dbLong
Call SetProperty(strPropertyName, lngDataType, lngValue)
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Private Sub txtSingleValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017
On Error GoTo ErrorHandler
sglValue = Nz(Me![txtSingleValue].Value, 0)
strPropertyName = "SingleValue"
lngDataType = dbSingle
Call SetProperty(strPropertyName, lngDataType, sglValue)
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Private Sub txtTextValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017
On Error GoTo ErrorHandler
strText = Nz(Me![txtTextValue].Value, " ")
strPropertyName = "TextValue"
lngDataType = dbText
Call SetProperty(strPropertyName, lngDataType, strText)
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Custom database properties take a bit more time to set up than the alternatives (variables and form control references), but they save lots of time that would otherwise be needed to revise hard-coded paths, or make sure that a form is open so a reference to a control on that form works. With database properties, once they are set, they are available for use at any time, and they never disappear. I think you will find that this technique both saves time and gives you more control over how your application works.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)