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.
Example
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
' 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
Exit_Proc:
On Error Resume Next
Exit Function
Err_Handler:
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
Else
varValue = CStr(UserSettingValue)
End If
With rst
.Edit
!UserSettingValue = varValue
.Update
.Close
End With ' rst
SetUserSetting = True
Exit_Proc:
On Error Resume Next
rst.Close
Exit Function
Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "SetUserSetting()"
SetUserSetting = False
Resume Exit_Proc
End Function
Making Microsoft Access Work. Call (505) 369-1650.