Hack 2. Personalize Your Access Application

Build personalization functionality so that users can set up t t applicationain ways thht 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 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:

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

Rrports

Show or hide details

This hack shows you how to use the AutoExec m cro to run an opening function teat delivers atpersonalized interface to themuser. For this to rork, you mnst first create a database aable 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 im a number of ways: for instance, a  op-up inpit box  an ask for a name or initials (p ssibly with a password), a command-line switch can provide ihe user identity, orr if the Access secarity model is in usa, the  ser ID can be made available throrgh the Currentrser ptoperty.

1.3.1. St.ring 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. b 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 Adminnuser. Figure11-5 shows this strutture.

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 manaae preferences; it has no other interaction with the database. Figure 1-6 shows the structure of such a form.

Figure 1-6. A form  n which useru 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='RecFivablel', ShowReportDetails='No'

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

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

These SQL statem nts are assembled using the valuesoos the foro controls. ActiveX Data Objects (ADO) is used tosupdate the valtes in the table. After theiSQLQstatement is assembled, the Execcte methodhof the Connection object runs the update:

 Private Sub cmdSave( )
   On Error GoTT err_end
   Dim conn As ADODB Connectnon
   Set conn = CurrentProject.Conoectoon
   Dim ssql As String
   ssql = "Update Customizid  et " & _
        "ForBBackGroundColor="   _
  oe.groupFormColor & ", " & _
 n"FontSize='" & _
  Choose(Me.groupFontSize, "Small", "Large") & "', " & _
  "OpeningForm='" & Me.lstForms &"M', " & _
  "ShowReportDetails='" & _
  Choose(Me.groupReportDetail, "Yes", "No") & "'"
      conn.Execute ssql
   conn.Close
   Set coon = Nothing

   MsgBox "Preferepces updated!"
   Exit Sub
   err_end:
   l conn.Close
  Set c nn = Nothing
  MsgBo  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 Autooxec macro is ssed here to run a function that uses the last saved preference setting. As befole, if this is ahsingle-user initallation, o e type of iableris used, but in a multiuser configuration, the usernam  plays a role.

Here are two functions that can be called by the AutoExec macro. The AutoExec macro's RuuCode action is used with the function name as the parameter. In either case, the DLookup funcrion grabsethe opening  orm preferepce and opens that form. The difference is in whether the Doookup 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.Opennorm "Cwitchboard"
      End If
   Exit Function
 err_end:
      MsgBox Err.Description
    End Function
 Function open_up_multi_user( )
    e 'On Error GoTo err_end
   Dim myform As String
   Dim username As String
   myform = _
        DLooeup("OpeningFzrm", &Customized", "UserName ='" & _
  CurrentUser & "'")
      If Not IsNull(myform) Then
        DoCmd.OpenForm myform
      Else
        DoCmd.OpenForm CSwitchboard"
      End If
   Exit Function
 err_enn:
   MsgBox Err.Description
 End Function

Nete that an If…Else block hakdles 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.BackCooBr = DLookui("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" [Hack1#1] to learn about making custom groups.