Implement User Preferences in MS Access

A couple of years ago, one of my clients asked me to change the background
color for their four primary forms because one user with poor eyesight needed
greater color contrast. While that user loved the new schema, others hated it
and before long, I was toggling colors like a madman, trying to find a happy
medium. That is, until I decided to give the users the ability to set their own
colors, and along the way, added a couple of other preference options of
various degrees of usefulness. It occurred to me that this process might be of
interest to DBJ readers. Here is how it works.

The image below shows three different forms, each with a different color
schema applied, just to give you an idea of havoc that may be reaped by allowing
users to pick their own colors. While the artsy part of this feature is fun,
the other preferences can really enhance the user experience. For example, here
are some of the things I have applied to user preferences:

  • Allow user to select which form opens at startup

  • Remember and open the last record visited

  • Identify specific Fax-Printer to use with reports

  • Indicate data source for table linking

  • Set flag for running in ‘development’ mode

Of course, you will need to code the logic behind each of these
suggestions, but the process of setting and saving user options remains
relatively consistent. The code, including the preferences form, which you may
import into your project, is available in the download file.

User Preference Option Form

The first part of this process involves using the built-in Access functions
for saving values to and retrieving values from the registry. They are very
simple to use, following the syntax shown below, as described in the Office
help file:

SaveSetting appname, section, key, setting
GetSetting(appname, section, key [, default])

These calls to the registry may be used anywhere in the
application and in my experience, do not produce a performance hit. The code
behind the Preferences form has the highest concentration of such calls, so
that snippet is shown below. Note that I use a constant, cAppName, for the appname
argument. This assures that my registry settings are consistent. The other
arguments, section and key, can and probably should vary, depending on what
sorts of preferences you are saving, but it is entirely up to you how you
organize your registry entries.

Private Sub Form_Load()
On Error Resume Next

‘ Lookup values from the REGISTRY using the GetSetting() method

Me.chkRememberID = GetSetting(cAppName, “Options”, “Remember ID”, False)
Me.fraStartupForm = GetSetting(cAppName, “Options”, “Startup Form”, 1)
Me.txtMinorColor = GetSetting(cAppName, “Options”, “Minor BgColor”, -2147483633)
Me.boxMinorColor.BackColor = Nz(Me.txtMinorColor, -2147483633)
Me.txtMajorColor = GetSetting(cAppName, “Options”, “Major BgColor”, -2147483633)
Me.boxMajorColor.BackColor = Nz(Me.txtMajorColor, -2147483633)

‘ Apply form color formatting to current form

Me.FormHeader.BackColor = Me.txtMinorColor
Me.FormFooter.BackColor = Me.txtMinorColor
Me.fraStartupForm.BackColor = Me.txtMinorColor
Me.lblTitle.BackColor = Me.txtMajorColor
Me.lblStartupForm.BackColor = Me.txtMajorColor
Me.Detail.BackColor = Me.txtMajorColor

End Sub

In the demo app, I have opted to update the registry each
time a value changes, in the AfterUpdate() event. Once saved to the registry, it
is immediately available to the application.

‘ Each time a control value changes, update the REGISTRY
‘ using the SaveSetting() method.
Private Sub txtMajorColor_AfterUpdate()
On Error Resume Next
SaveSetting cAppName, “Options”, “Major BgColor”, Nz(Me.txtMajorColor, -2147483633)
Me.boxMajorColor.BackColor = Nz(Me.txtMajorColor, -2147483633)
End Sub
Private Sub txtMinorColor_AfterUpdate()
On Error Resume Next
SaveSetting cAppName, “Options”, “Minor BgColor”, Nz(Me.txtMinorColor, -2147483633)
Me.boxMinorColor.BackColor = Nz(Me.txtMinorColor, -2147483633)
End Sub

Calling the StartUp Form Preference

The above code sample for frmPreferences Form_Load() event demonstrates how
a color selection may be applied to the BackColor property of form sections
and/or individual controls. This same logic needs to be propagated to all other
forms that you wish to affect by the user-selected colors.

A similar process is used to determine which form opens at startup. It is
customary in my applications to load a splash screen, which handles application
open processes, so the demo database includes a form named frmSplash. When this
form opens, it performs the following test:

intStartupForm = GetSetting(cAppName, “Options”, “Startup Form”, 3)

Select Case intStartupForm
Case 1
‘ no form … close splash
DoCmd.Close acForm, Me.Name
Case 2
DoCmd.OpenForm “frmPublishers”
DoCmd.Close acForm, Me.Name
Case 3
DoCmd.OpenForm “frmPreferences”
DoCmd.Close acForm, Me.Name
Case 4
DoCmd.OpenForm “frmTipOfDay”
DoCmd.Close acForm, Me.Name
Case 5
‘ do nothing. Splash form requested.
End Select

The registry contains a key for
the application named "Startup Form" which holds an integer value
between 1 and 5. If it is missing, then the default value, 3 for frmPreferences,
is substituted. A simple case statement evaluates this integer variable and
opens the appropriate form.

Preferences Many Uses

It should not be difficult to see how this process could be extended to
handle the many and varied needs of your application. Even if you have no
interest in propagating infinite color schemes, there are undoubtedly some
preferences that your users would appreciate being able to preserve.

As mentioned in the introduction, I have one implementation where users can set
the option to return to the last visited record. Imagine how convenient it
would be for users to open their app on Monday morning and immediately be
navigated to the record they left off with last Friday afternoon.

I have one user who wants me to program all their report buttons to send the
results directly to the printer without having to preview it. That’s nice for
them, but a pain to debug problems. Finally, I ended up adding a user
preference option that sets a value for sending reports to the printer or preview
window. My client gets to print them and with the flip of an option, I preview
them. It has saved me hours of dinking around with code, changing DoCmd.OpenReport

The same idea could be applied to any number of development verses production options.
Any of the following could be set at startup based on user preferences saved to
the machine’s registry.

  • Set option to Break On All Errors

  • Turn off developer’s custom error logging

  • Point to development database instead of production

  • Bypass startup checks and/or security options

I have an Access developer friend who is going to chide me
because he has told me many times how I can use Compiler Constants to do the
same thing. However, it isn’t quite the same, because I have to remember to
toggle the constants on and off each time I roll it from production to
development and back. With registry entries, the application stays the same and
the behavior follows the machine, not the mdb file. I don’t have to worry that
my users will observe "developer" type options, because their
registry is not set to do so.

Yes, it takes a little bit of code to propagate the effects of user-saved
preferences, but the benefits they provide to your applications are limited
only by your imagination.


See All Articles by Columnist
Danny J. Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.

Latest Articles