Hack 2. Personalize Your Access Application
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 optionsthat 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:
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.
1.3.1. 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 onethat is, one record per user, plus a default record for the Admin user. Figure 1-5 shows this structure.
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
1.3.2. 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
1.3.3. 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.