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:

The Options Form of my Recipe Database

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.

The User's Name in the Main Menu's Title

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!