Implement User Preferences in MS Access

Thursday May 25th 2006 by Danny Lesandrini

Learn how to provide your users with several useful preference options, including which form opens at startup, Remember and open the last record visited, Identify specific Fax-Printer to use with reports and more.

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 options.

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

Mobile Site | Full Site