Give Users Some Control of a Microsoft Access Database
Giving users some control over their database experience is a great way to enhance their involvement and satisfaction. To provide this, I added an Options form to my recipe database.
Here is the Options form:
This Options form is bound to a table, “tblDatabase,” where the options settings are stored. These settings are consulted when executing code, and the database behaves accordingly. For example, when the Main Menu loads, the code checks to see if there is a value in an optional UserName field in tblDatabase. If there is, that name is displayed in the form's title. If not, a default name (my name in the default database name) is used instead.
When the Main Menu loads the procedure named Build Title is called:
Private Sub BuildTitle() ' ' Build the title for the Main Menu form using either my name or ' the user's name. ‘ ' Code by Jennifer Neighbors ‘ Dim strRealTitle As String Dim strAliasTitle As String On Error GoTo ErrHandler strRealTitle = DLookup("DatabaseName", "tblDatabase") If IsNull(DLookup("UserName", "tblDatabase")) _ Then Me.lblTitle.Caption = strRealTitle Else Me.lblTitle.Caption = DLookup("UserName", "tblDatabase") _ & "'s Recipe Box" End If exitHere: Exit Sub ErrHandler: MsgBox "Oops! It looks like there has been an error. " _ & "The error is number " & Err.Number & ": " & _ Err.Description & " in " & VBE.ActiveCodePane.CodeModule & _ & ". The program will now attempt to resume normally.", _ vbOKOnly, "Error" Resume exitHere End Sub
The Options form also has a button to delete all sample recipes stored in the database. This enables users to make the database their own by removing the examples and replacing them with their recipes. The button is an additional way for users to have control and feel at ease. It is made possible by adding a Yes/No field (“SampleRecipe”) to the table “tblRecipes,” which marks my examples as such. This field’s default value is “No,” so new recipes added will not be marked as examples. Note that this Yes/No field is not displayed on the recipe detail form; it has been edited in the table’s datasheet view. One line of code can then remove all the examples if the user confirms their wish:
Private Sub cmdDeleteSampleRecipes_Click() ' ' Delete all the example recipes in the database ‘ ' Code by Jennifer Neighbors ‘ If MsgBox("Are you sure you want to delete all the example " _ & "recipes in this database? Once deleted, they cannot be " _ & "recovered.", vbYesNoCancel, "Confirmation Required") = vbYes Then CurrentDb.Execute ("Delete * from tblRecipes WHERE " _ & "SampleRecipe = True") End If End Sub
Have you thought about ways to hand over control to your database users? Let me know in the comments!