I.6. Use a Splash Screen

You can display a custom logo (instead of the Access logo) while the Access program loads. Name a bitmap the same name as your mdb file (YourAppName.bmp) and put it in the same folder as the MDB. This bitmap image will be displayed instead of the Access startup logo when you launch your application.

Now, with a fast computer, you probably won't notice this logo since it will only be displayed for a fraction of a second. So, you need another splash screen. The first screen of your application should always be a splash screen that shows at least the application name, client's company name, version number, and your company name. This single feature says, "this is a professional application."

When your application starts, it should show a Splash screen with a logo (either yours or your client's) and some basic application information, as shown in Figure I-13.

Figure I.13. Figure I-13

Example code for displaying the Splash screen for a specified number of seconds (3 or 4 seconds seems like a good duration) can be found on the Microsoft Knowledgebase article 101374.

I.6.1. Pop-Up Memo Workspace Form with Spell-Check

Sometimes you want to give your user more room to enter long text into a memo field. Instead of using the built-in Access zoom feature, you can include a workspace feature to zoom into a memo field, allow the user to OK or Cancel their changes, and even invoke the Word spell-checking feature (if Word is installed on their PC). This feature is shown in Figure I-14.

Figure I.14. Figure I-14

The code in the double-click event of the memo field is simple:

Private Sub BusinessComments_DblClick(Cancel As Integer)
On Error GoTo Error_Handler

    Workspace Me.ActiveControl, Me

Exit_Procedure:
    Exit Sub
Error_Handler:
    DoCmd.SetWarnings True
    MsgBox "An error has occurred in this application. " _
    & "Please contact your technical support person and tell" _
    & " them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & _
    Err.Description, Buttons:=vbCritical, title:="My Application"
    Resume Exit_Procedure
    Resume

End Sub

The code in the Workspace procedure looks like this:

Sub Workspace(ctl As Control, CallingForm As Form)
On Error GoTo Err_Workspace

        CallingForm.Refresh
        'Save any data which may have been entered into memo field

        Set gctlWorkspaceSource = ctl

        If ctl.Locked Or Not ctl.Enabled Then
            DoCmd.OpenForm "frmWorkspace", WindowMode:=acDialog, _
            OpenArgs:="ReadOnly"
        Else
            DoCmd.OpenForm "frmWorkspace", WindowMode:=acDialog
        End If

        If IsLoaded("frmWorkspace") Then
            gctlWorkspaceSource = Forms.frmWorkspace.txtWorkspace
            DoCmd.Close acForm, "frmWorkspace"
        End If

Exit_Workspace:
    Exit Sub
Err_Workspace:
    Select Case Err
        Case 3163 'Too much data for field
            MsgBox "The field is too small to accept the amount " _
            & "of data you attempted to insert. As a result, " _
            & "the operation has been cancelled.", vbExclamation, _
            DLookup("ApplicationTitle", "tsysConfig_Local")
            Resume Next
        Case Else
            MsgBox Err.Number & ", " & Err.Description
            Resume Exit_Workspace
    End Select
End Sub

Notice that if the original text box control is locked, the Workspace form is passed OpenArgs of "ReadOnly". This will cause the Workspace form to display the data in a gray, locked text box.

Also, the text on the original form is updated by the Workspace text if the Workspace form is still open. After that, the Workspace form is closed.

When the Workspace form opens, it looks like Figure I-15.

The code in the Workspace form looks like this:

Option Compare Database
Option Explicit
'Note: Uncomment the mode you want to support:
'Const conSpellCheckOption = 0 'No Spell Checking,
Const conSpellCheckOption = 1 'Default Spell Checking

Figure I.15. Figure I-15

'Const conSpellCheckOption = 2 'Word Grammer
'Const conSpellCheckOption = 3 'Word Grammer with Options

Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click

    DoCmd.Close acForm, ObjectName:=Me.Name

Exit_cmdCancel_Click:
    Exit Sub
Err_cmdCancel_Click:
    MsgBox Err.Number & ", " & Err.Description
    Resume Exit_cmdCancel_Click
    Resume
End Sub

If the Cancel button is clicked, the Workspace form is closed. That will prevent any changes from making it back to the calling form.

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

    Me.Visible = False

Exit_cmdOK_Click:
    Exit Sub
Err_cmdOK_Click:
    MsgBox Err.Number & ", " & Err.Description
    Resume Exit_cmdOK_Click
    Resume
End Sub

Notice that if OK is clicked, the Workspace form is merely hidden. It was opened in Dialog mode, so the calling code was paused until now.

Private Sub cmdSpellCheck_Click()
On Error GoTo Err_cmdSpellCheck_Click

    Dim intTemp As Integer

    Select Case conSpellCheckOption
        'This is set in the frmWorkspace declarations section above
        Case 1 'Use default spell checker
            intTemp = fnCheckSpelling(Me!txtWorkspace, False)
        Case 2, 3 'Use MS Word grammer checker
            intTemp = fnCheckSpelling(Me!txtWorkspace, True)
    End Select

Exit_cmdSpellCheck_Click:
    Exit Sub
Err_cmdSpellCheck_Click:
    MsgBox Err.Number & ", " & Err.Description
    Resume Exit_cmdSpellCheck_Click
    Resume

End Sub

Depending on which option is set in the constant, the spell check is performed.

Private Sub cmdSpellOptions_Click()
On Error GoTo Err_cmdSpellCheck_Click

    Call subSetWordSpellingOptions

Exit_cmdSpellCheck_Click:
    Exit Sub
Err_cmdSpellCheck_Click:
    MsgBox Err.Number & ", " & Err.Description

Resume Exit_cmdSpellCheck_Click
    Resume
End Sub

If the Spell Options button is visible, the user can click it to change spell-checking options.

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo Err_Form_KeyDown

    If KeyCode = vbKeyF7 Then
        KeyCode = 0
        If Me!cmdSpellCheck.Visible And Me!cmdSpellCheck.Enabled Then
            Me.cmdSpellCheck.SetFocus
            Call cmdSpellCheck_Click
        End If
    End If

Exit_Form_KeyDown:
    Exit Sub
Err_Form_KeyDown:
    MsgBox Err.Number & ", " & Err.Description
    Resume Exit_Form_KeyDown
    Resume
End Sub

To support the standard usage of the F7 key to check spelling, this code traps the F7 keystroke, and if the spell-checking button is enabled, runs its code.

Private Sub Form_Load()
On Error GoTo Err_Form_Load

    Dim ctl As Control

    'Setup SpellChecker Options
    Select Case conSpellCheckOption
        'This is set in the frmWorkspace declarations section above
        Case 0 'Spell Checker OFF
            Me!cmdSpellCheck.Visible = False
            Me!cmdSpellOptions.Visible = False
        Case 1, 2 'Default Spell Checker, Word Grammer Checker
            Me!cmdSpellCheck.Visible = True
            Me!cmdSpellOptions.Visible = False
        Case 3 'Word Grammer Checker with Options
            Me!cmdSpellCheck.Visible = True
            Me!cmdSpellOptions.Visible = True
    End Select

    'Import data into workspace
    Me!txtWorkspace = gctlWorkspaceSource

    If Me.OpenArgs = "ReadOnly" Then
        Set ctl = Me!txtWorkspace
        With ctl
            .EnterKeyBehavior = False 'Sets to Default / No new line

.Locked = True
            .BackColor = vbButtonFace
        End With
        Set ctl = Nothing

        Me!cmdSpellCheck.Enabled = False
        Me!cmdSpellOptions.Enabled = False
    End If

Exit_Form_Load:
    Exit Sub
Err_Form_Load:
    MsgBox Err.Number & ", " & Err.Description
    Resume Exit_Form_Load
    Resume
End Sub

When the Workspace form loads, the option constant is used to show the appropriate spell-checking buttons. Then the text from the original form is loaded into the text box, and the whole thing is locked down if the original text box was locked.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

    Me.Caption = DLookup("ApplicationTitle", "tsysConfig_Local") _
    & " - " & DLookup("SysConfigDatabaseName", "tsysConfig_System")

Exit_Form_Open:
    Exit Sub
Err_Form_Open:
    MsgBox Err.Number & ", " & Err.Description
    Resume Exit_Form_Open
    Resume
End Sub

This is just the normal form caption setting as described earlier in this appendix.

Private Sub txtWorkspace_GotFocus()
On Error GoTo Err_txtWorkspace_GotFocus

    Dim varX As Variant

    ' jump to end of existing text instead of leaving it
    'all highlighted.
    varX = Len(Me!txtWorkspace.Text & "")
    If Not IsNull(varX) And varX > 0 Then
        Me!txtWorkspace.SelStart = Len(Me!txtWorkspace)
        Me!txtWorkspace.SelLength = 0
    End If

Exit_txtWorkspace_GotFocus:
    Exit Sub

Err_txtWorkspace_GotFocus:
    MsgBox Err.Number & ", " & Err.Description
    Resume Exit_txtWorkspace_GotFocus
    Resume
End Sub

When the Workspace text box receives focus, we make sure that the insertion point jumps to the end of the text, instead of highlighting the whole field. This helps to prevent the user from inadvertently changing of the entire field.

When the Spell button is clicked, the text on the Workspace form is checked for spelling errors, as shown in Figure I-16:

Figure I.16. Figure I-16

The code in the Spell Checking module looks like this:

Option Compare Database
Option Explicit

Public Function fnCheckSpelling(ctl As Control, _
    bUseWordSpellChecker As Boolean) As Boolean
On Error GoTo Err_fnCheckSpelling

'This procedure checks spelling with either the Word Spell
'Checking/Grammar Checking window or the Access default Spell
'Checking only window. Both methods require Word 97 or later
'to work, because the dictionary is used. The Word method
'gives the user more functionality with the addition of the
'grammar checking capability. The downside is that the
'Word checker takes longer to load and unload.

Dim wrdApp As Object
Dim wrdDoc As Object

DoCmd.Hourglass True
'This function is meant for textbox controls only.
Select Case ctl.ControlType
    Case acTextBox
        If Not ctl.Enabled Or ctl.Locked Then
            'Text in control can not be updated.
            GoTo Exit_fnCheckSpelling
        ElseIf IsNull(ctl) Then
            'Nothing to check
            GoTo Exit_fnCheckSpelling
        End If
    Case Else
        GoTo Exit_fnCheckSpelling
End Select

If bUseWordSpellChecker Then
    'Use Word 97's default spell checker (with grammer checker)
    Set wrdApp = CreateObject("Word.Application")

    wrdApp.Visible = False
    Set wrdDoc = wrdApp.Documents.Add

    wrdApp.Selection.Text = ctl
    DoCmd.Hourglass False
    'wrdApp.WindowState = 2 'wdWindowStateMinimize
    wrdApp.Visible = False
    wrdApp.Dialogs(828).Show
    'wdDialogToolsSpellingAndGrammar = 828
    wrdApp.Visible = False
    DoCmd.Hourglass True

    'The cancel button on the Word Spell Checker was NOT pressed.
    If Len(wrdApp.Selection.Text & "") <> 1 Then
        ctl = wrdApp.Selection.Text

        wrdApp.ActiveDocument.Close 0 'wdDoNotSaveChanges = 0
        Set wrdDoc = Nothing
        wrdApp.Quit
        Set wrdApp = Nothing

        fnCheckSpelling = True
        MsgBox "The spelling check is complete.", vbInformation
    End If

Else 'Use Access's default spell checker (without grammer)
        ctl.SetFocus
        ctl.SelStart = 0
        ctl.SelLength = Len(ctl.Text & "")
        If ctl.SelLength <> 0 Then
            DoCmd.Hourglass False
            RunCommand acCmdSpelling
            ctl.SelLength = 0
            fnCheckSpelling = True
        End If
    End If

Exit_fnCheckSpelling:
    On Error Resume Next
    DoCmd.Hourglass False
    If Not (wrdApp Is Nothing) Then
        If Not (wrdDoc Is Nothing) Then
            wrdApp.ActiveDocument.Close 0 'wdDoNotSaveChanges = 0
            Set wrdDoc = Nothing
        End If
        wrdApp.Quit
        Set wrdApp = Nothing
    End If
    Exit Function
Err_fnCheckSpelling:
    DoCmd.Hourglass False
    Select Case Err.Number
        Case 429 'Word not installed
            MsgBox "Microsoft Word 97 (or later) must be " _
            & "installed for the spell checking option to " _
            & "function.", vbExclamation, _
            DLookup("ApplicationTitle", "tsysConfig_Local")
        Case Else
            MsgBox Err.Number & ", " & Err.Description
    End Select
    Resume Exit_fnCheckSpelling
    Resume
End Function

Notice that by setting wrdapp as Object, we are using late binding. Even if Word is not installed on this PC, this code will still compile. When the code attempts to create the Word object at run time, a trappable error will occur and we'll tell the user that Word is required.

If we had used early binding, the Dim statement for wrdApp would have looked like this:

Dim wrdApp As Word.Application

The problem with early binding is that if the object specified (in this case, Word) is not installed on the PC, the VBA code will not even compile. This will prevent your user from using your application at all. That's why it's usually best to use late binding when you are developing applications that will run on other PCs where you don't necessarily know which other programs are installed.

Now we move on to the code to allow the users to set their own spelling options:

Public Sub subSetWordSpellingOptions()
On Error GoTo Err_subSetWordSpellingOptions

    Dim wrdApp As Object
    Dim wrdDoc As Object

    DoCmd.Hourglass True

    Set wrdApp = CreateObject("Word.Application")

    wrdApp.Visible = False
    Set wrdDoc = wrdApp.Documents.Add

    DoCmd.Hourglass False
    wrdApp.Dialogs(211).Show
    'wdDialogToolsOptionsSpellingAndGrammar = 211
    wrdApp.Visible = False
    DoCmd.Hourglass True

Exit_subSetWordSpellingOptions:
    On Error Resume Next
    DoCmd.Hourglass False
    If Not (wrdApp Is Nothing) Then
        If Not (wrdDoc Is Nothing) Then
            wrdApp.ActiveDocument.Close 0 'wdDoNotSaveChanges = 0
            Set wrdDoc = Nothing
        End If
        wrdApp.Quit
        Set wrdApp = Nothing
    End If
    Exit Sub
Err_subSetWordSpellingOptions:
   DoCmd.Hourglass False
   Select Case Err.Number
       Case 429 'Word not installed
           MsgBox "Microsoft Word 97 (or later) must be " _
           & "installed before the spell checking options can " _
           & "be set.", vbExclamation, _
           DLookup("ApplicationTitle", "tsysConfig_Local")
       Case Else
           MsgBox Err.Number & ", " & Err.Description
   End Select
   Resume Exit_subSetWordSpellingOptions
   Resume
End Sub

This is the code that, if the button is enabled, allows the user to change the built-in options for the spell-checking utility.

By adding an area for your users to enter more text and check the spelling, you make your application more powerful and easy to use. Plus, when you add this capability, you don't have to make your memo field text boxes as large, which saves valuable real estate on your forms.