505.369.1650 [email protected]
Microsoft Access database systems are structured so that each user has a dedicated front-end file installed on their computer.  The front-end is the user interface for the system: data entry forms, logic, and reports.  Along with reducing the opportunity for data corruption, the front-end file also allows for a customized user experience.  Settings specific to a user can be stored in the front-end.  The VBA functions below demonstrate a way to set and retrieve those settings.

SetUserSetting() – This function sets a UserSettingValue field value in the UserSettings table.
GetUserSetting() – This function retrieves a UserSettingValue field value from the UserSettings table, and converts it to a selected data type.


Let’s take the example of a Microsoft Access form that has:

  • A text box showing the path to the last payroll file that was imported, txtImportFile.
  • A text box showing the date that the last payroll file was imported, txtImportDate.
  • A button with logic to import a payroll file.

When the form loads, you could populate txtImportFile and txtImportDate from the UserSettings table:

Me.txtImportFile = GetUserSetting("ImportFile", usvtText)
Me.txtImportDate = GetUserSetting("ImportDate", usvtDate)

At the end of the logic that imports a new payroll file, you could set values in UserSettings like this, where strImportFile holds the name of the import file:

SetUserSetting "ImportFile", strImportFile
SetUserSetting "ImportDate", Date


' basUserSettings() Version 1.0.0
' Copyright © 2023 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.
Public Enum USValueType
    usvtBoolean = dbBoolean
    usvtCurrency = dbCurrency
    usvtDate = dbDate
    usvtDouble = dbDouble
    usvtInteger = dbInteger
    usvtLong = dbLong
    usvtSingle = dbSingle
    usvtText = dbText
End Enum
Public Function GetUserSetting(UserSetting As String, _
    UserSettingValueType As USValueType)
' This procedure passes back a UserSettingValue from UserSettings.
' UserSettings
'   UserSettingsID      AutoNumber
'   UserSetting         Short Text
'   UserSettingValue    Short Text
'   UserSettingNotes    Short Text
On Error GoTo Err_Handler

    Dim varValue

    varValue = DLookup("UserSettingValue", "UserSettings", _
        "UserSetting='" & UserSetting & "'")
    If Not IsNull(varValue) Then
        Select Case UserSettingValueType
        Case usvtBoolean
            varValue = CBool(varValue)
        Case usvtCurrency
            varValue = CCur(varValue)
        Case usvtDate
            varValue = CDate(varValue)
        Case usvtDouble
            varValue = CDbl(varValue)
        Case usvtInteger
            varValue = CInt(varValue)
        Case usvtLong
            varValue = CLng(varValue)
        Case usvtSingle
            varValue = CSng(varValue)
        Case usvtText
            ' Leave the value as-is.
        End Select
    End If

    GetUserSetting = varValue

    On Error Resume Next
    Exit Function
    MsgBox Err.Number & " " & Err.Description, vbCritical, "GetUserSetting()"
    GetUserSetting = Null
    Resume Exit_Proc
End Function
Public Function SetUserSetting(UserSetting As String, UserSettingValue) As Boolean
' This procedure saves a value to a UserSettings record.
On Error GoTo Err_Handler

    Dim strSQL As String
    Dim rst As DAO.Recordset
    Dim varValue

    strSQL = "SELECT * FROM UserSettings WHERE UserSetting='" & UserSetting & "'"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    If rst.BOF And rst.EOF Then
        SetUserSetting = False
        GoTo Exit_Proc
    End If

    If IsNull(UserSettingValue) Then
        varValue = Null
        varValue = CStr(UserSettingValue)
    End If

    With rst
        !UserSettingValue = varValue
    End With ' rst

    SetUserSetting = True

    On Error Resume Next
    Exit Function

    MsgBox Err.Number & " " & Err.Description, vbCritical, "SetUserSetting()"
    SetUserSetting = False
    Resume Exit_Proc
End Function