User Preferences, Toggles, and Rocket Science

Maybe it isn’t rocket science, but maintaining user preferences will make your applications more appealing. Follow along as Danny Lesandrini demonstrates how you can maintain a variety of user-defined options.

I’m not a rocket scientist, and fortunately, I don’t need to be in order to do great things with Microsoft Access. Don’t get me wrong—there are some very complex and exotic things you can do with Access, and they’re really cool. But the pieces that you use over and over again in every application are the simple things—for instance, the code that I use to customize the user experience.

Remember user preferences

On a recent project where I was enhancing an existing Access database application, I became frustrated at having to navigate to my favorite test record each time I reloaded the form. So in order to save my own time, I added code to save the number of the last record visited in the Windows Registry. Each time I reopened the form, the form navigated to that record. Though this ad hoc enhancement wasn’t on the official request list provided by the client, I left the code in place when I put the program into production. The feature got rave reviews from the users.

It wasn’t difficult, but the reward that I got from my user community was immeasurable. In fact, the feature was quite simple to create, requiring only a dozen lines of code and a few minutes to implement. The code simply read the “last visited” record number from the Registry using the GetSetting() function in the Form_Load() event. Some standard, wizard-generated code then navigated to that record. When exiting the form, the current record number is captured in the Form_Unload() event and the Registry entry is updated using the SaveSetting() function. With some minor changes, the following code could be used with any data access form:

Private Sub Form_Load()

  On Error Resume Next

  Dim lngID As Long

  Dim rs As DAO.Recordset

  lngID = GetSetting("MyApp", "Prefs", "Site")

  Set rs = Me.Recordset.Clone

  rs.FindFirst "[SiteID] = " & lngID

  If Not rs.EOF Then

    Me.Bookmark = rs.Bookmark

  end if

  Set rs = Nothing

End Sub

Private Sub Form_Unload(Cancel As Integer)

  On Error Resume Next

  Dim lngID As Long

  lngID = Nz(Me.SiteID, 0)

  SaveSetting "MyApp", "Prefs", "Site", lngID

End Sub

With this code in place, users are automatically returned to their last visited record. This not only saves time for users, it also prevents the irritation associated with performing the same task over and over again. Of course, this assumes that your users return frequently to the same record, as was true in my case. Our “site coordinators” usually wanted to pick up where they left off, viewing information for a particular site.

Toggling UI views and resizing forms

I’ve already demonstrated how simple it is to save user preferences to the Registry. With a little thought, you can, no doubt, imagine dozens of ways to improve your applications by preserving user preferences. Two more examples of features I recently deployed include the following:

  1. Toggling a subform from datasheet view to form view.
  2. Dynamic resizing of a form and its controls.

On my last major project, I couldn’t get agreement from management on whether they wanted subforms displayed as datasheets or in form view. Rather than making an arbitrary decision, I decided to allow the users to choose. In Figure 1 and Figure 2 there’s a button (just above the subform) that allows the user to toggle between views. The code for switching subform views is trivial—it just sets the focus to the subform and executes the appropriate DoCmd.RunCommand command (acCmdSubformFormView to display as a Form, acCmdSubformDatasheetView to display as a datasheet):


DoCmd.RunCommand acCmdSubformFormView

DoCmd.RunCommand acCmdSubformDatasheetView

While this feature is easy to code, it would frustrate your users if the application failed to remember their preferences and they kept having to reset the form to their preferred view. So, in addition to the preceding code, you also need to add code so that each form remembers the last selected view state for each subform. As you might imagine, the code to accomplish this looks a lot like my Registry code for navigating to the last visited record.

Figure 1                                                             Figure 2

To save and retrieve the acCmdSubFormView values from the Registry, I’ll use the SaveSetting() and GetSetting() functions again, only this time with a different key. You can name this key anything you’d like, so long as it’s unique to your application. For this article, I have only one subform, so I’ve used the generic name “Toggle Subform View.” In applications with multiple subforms, the key must be more specific to avoid collisions.

After retrieving the value for the view state of the subform, the value is passed to a function that executes the appropriate RunCommand action. Also, each time the “View As…” button is clicked and the subform is toggled between view states, the caption is changed to reflect the opposite view state of what’s currently displayed. The code for the button and its corresponding ToggleSFView() function looks like this:

Option Compare Database

Option Explicit

Private Const cShow = 1

Private Const cHide = 0

Private Const cApp = "Sample Application"

Private Const cSec = "User Preferences"

Private Const cTSKey = "Toggle Subform View"

Private Sub cmdView_Click()

On Error Resume Next

If cmdView.Caption = "View As Form" Then

ToggleSFView (acCmdSubformFormView)


ToggleSFView (acCmdSubformDatasheetView)

End If

End Sub

Private Sub ToggleSFView(ByVal lngView As Long)

On Error Resume Next

If lngView = acCmdSubformFormView Then


DoCmd.RunCommand lngView

SaveSetting cApp, cSec, cTSKey, _


cmdView.Caption = "View As Datasheet"



DoCmd.RunCommand lngView

SaveSetting cApp, cSec, cTSKey, _


cmdView.Caption = "View As Form"

End If

End Sub

The problem with datasheet view is that when there are a lot of columns, they tend to scroll off to the right beyond the edge of the form boundary. In our example, I only needed only about another inch of space. This lends itself to another cool Access trick: dynamically resizing forms and controls.

In Figure 2, the form and some of its controls are wider than those in Figure 1. This is done through the Click() event of the Show/Hide button in the upper right corner of the form. The code not only modifies the Width property of the form and some of its controls, it also stores the Show/Hide value in the Registry. Each time the form is opened, the last saved value for Show/Hide state is “remembered” and the form is displayed according to those values. Since the code is similar to what you’ve already seen, I won’t repeat it here, but the entire sample application is available in the Download file.

Registry values

All of these user preference values are stored in a special place in the Windows Registry under the key HKEY_CURRENT_USER\Software\VB and VBA Program Settings\Your Application Name. Figure 3 shows the Registry settings generated by the sample code in the Download file associated with this article. I called my program “Sample Application” and assigned the preference values to a section named “User Preferences.” The great thing about using GetSetting() and SaveSetting() is that you really don’t have to worry about where in the Windows Registry your values are stored—VBA handles all that for you. Of course, if you want to get fancy, you can access and modify other parts of the Registry with VBA code, but that’s beyond the scope of this article and, quite frankly, overkill for this solution.

 Figure 3

Final thoughts

As I was proofreading this article, it struck me that some of this code could be improved by making it more generalized. You could create one function that handles the toggling of any subform by passing the object’s name as an input parameter. Also, as I already mentioned, you could bypass the GetSetting() and SaveSetting() methods and use more complex VBA code to access the Windows Registry directly. But then I couldn’t brag about how simple this solution is, and really, that’s my point.

Your download file is called  Lesandrini_Toggles.accdb

About Danny Lesandrini

Danny J. Lesandrini holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.
This entry was posted in VBA. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.