Access Hacks

Hack #2. Personalize Your Access Application

Build personalization functionality so that users can set up the application in ways that work best for them.

There is no reason to limit all users to using an Access application in the same way. It's easy to overlook this capability because Access allows you to designate only one opening form in its startup options—that is, unless you tap into its database-opening events. Then, you can choose which form will open, what its properties are, and more. You can effectively make all facets of the application unique to a particular individual or profile. Here are a few items you can tailor this way:

Forms

Specify the opening form, how it's displayed, and what functionality it includes

Data sources

Specify which personalization tables, internal or external, are needed for each user's tasks

Reports

Show or hide details

This hack shows you how to use the AutoExec macro to run an opening function that delivers a personalized interface to the user. For this to work, you must first create a database table to store user preferences, and then, when the database starts up, you must be able to identify the user to the database. You can do this in a number of ways: for instance, a pop-up input box can ask for a name or initials (possibly with a password), a command-line switch can provide the user identity, or, if the Access security model is in use, the user ID can be made available through the CurrentUser property.

Storing Preferences

User preferences are stored in a table that has a field for each personalization facet. You determine which features to personalize. For example, a Long datatype field can store the preferred background color, a text field can store the name of the preferred opening form, and so on. Figure 1-4 shows such a table, aptly named Customized, with a few preferences filled in. The field name indicates the preference, and the actual value in the field is the setting.

Figure 1-4. A table to hold single user preferences

This table is perfect for databases that are distributed to local client machines. In this configuration, only one user uses an instance of the database. Therefore, the table is structured to store the preferences of just a single user. A key point about the table is that it always has just a single record. That single record contains a field for each personalized item.

In a shared database configuration (such as when all users are using a network copy), the table needs to have an additional field to identify each user. The number of records this table ends up containing matches the number of users, plus one—that is, one record per user, plus a default record for the Admin user. Figure 1-5 shows this structure.

Tip

It's a good idea to leave a record for the Admin user. This is the default Access user account and is present even when the security model isn't used. When no security login is used, the CurrentUser property defaults to Admin.

Figure 1-5. A table to hold multiple user preferences

All that's left to complete this hack is to give users a way to select their preferences. No, users aren't expected to enter such a cryptic thing as the numerical representation of a color! So, we'll use a form (what else!) to capture preferences. This unique form serves to just manage preferences; it has no other interaction with the database. Figure 1-6 shows the structure of such a form.

Figure 1-6. A form in which users select their preferences

Once the selections are made on the form, the Save Preferences button writes the preferences to the table. For a single-user table, a simple SQL insert does the trick, like this:

	Update Customized Set FormBackGroundColor=8454143, FontSize='Small',
	OpeningForm='Receivables', ShowReportDetails='No'

For the multiuser configuration, the extra field is in the SQL statement:

	Update Customized Set FormBackGroundColor=8454143, FontSize='Small',
	OpeningForm='Main Form', ShowReportDetails='Yes' Where UserName='Susan'

These SQL statements are assembled using the values of the form controls. ActiveX Data Objects (ADO) is used to update the values in the table. After the SQL statement is assembled, the Execute method of the Connection object runs the update:

	Private Sub cmdSave( )
	  On Error GoTo err_end
	  Dim conn As ADODB.Connection
	  Set conn = CurrentProject.Connection
	  Dim ssql As String
	  ssql = "Update Customized Set " & _
        "FormBackGroundColor=" & _
		Me.groupFormColor & ", " & _
		"FontSize='" & _
		Choose(Me.groupFontSize, "Small", "Large") & "', " & _
		"OpeningForm='" & Me.lstForms & "', " & _
		"ShowReportDetails='" & _
		Choose(Me.groupReportDetail, "Yes", "No") & "'"
      conn.Execute ssql
	  conn.Close
	  Set conn = Nothing
	  MsgBox "Preferences updated!"
	  Exit Sub
   err_end:
     conn.Close
	 Set conn = Nothing
	 MsgBox Err.Description
   End Sub

Applying the Preferences

Just storing the preferences does nothing, so let's crack this application open a little wider. One of the preferences selects which form to display at startup. The AutoExec macro is used here to run a function that uses the last saved preference setting. As before, if this is a single-user installation, one type of table is used, but in a multiuser configuration, the username plays a role.

Here are two functions that can be called by the AutoExec macro. The AutoExec macro's RunCode action is used with the function name as the parameter. In either case, the DLookup function grabs the opening form preference and opens that form. The difference is in whether the DLookup function filters to a username. In the first function, it doesn't, but in the second function, it does:

	Function open_up_single( )
      On Error GoTo err_end
	  Dim myform As String
	  myform = DLookup("OpeningForm", "Customized")
	  If Not IsNull(myform) Then
        DoCmd.OpenForm myform
      Else
        DoCmd.OpenForm "Switchboard"
      End If
	  Exit Function
	err_end:
      MsgBox Err.Description
    End Function

	Function open_up_multi_user( )
      'On Error GoTo err_end
	  Dim myform As String
	  Dim username As String
	  myform = _
        DLookup("OpeningForm", "Customized", "UserName ='" & _
		CurrentUser & "'")
      If Not IsNull(myform) Then
        DoCmd.OpenForm myform
      Else
        DoCmd.OpenForm "Switchboard"
      End If
	  Exit Function
	err_end:
	  MsgBox Err.Description
	End Function

Note that an If…Else block handles opening the default Switchboard form in case a null value is returned.

You need to implement how to use other types of preferences, such as including report details or using a different font size, when and where it makes sense for the given preference. For example, here's how you can change the background color of a form in the open event of the form:

	Private Sub Form_Open(Cancel As Integer)
	Me.Detail.BackColor = DLookup("FormBackGroundColor", "Customized") 
	End Sub

Using the Hack

All that's left now is to decide how to handle opening the customization form. You can make this action available on a toolbar, via a menu, or via a macro. A great idea is to put it into a custom group of commonly used objects. See "Help Users Find the Objects They Need" [Hack #1] to learn about making custom groups.