Selection of Office Assistant ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction - Access 2003.

Since we’ve already experimented with several Office Assistant options in earlier examples, let’s have a bit of fun this time.

If you’ve landed directly on this page, you might enjoy exploring some of our previous related topics first. The links to those articles are provided below:

Message Box with the Office Assistant
Message Box with Menu Options using the Office Assistant
Message Box with Check Box type Menu with the use of Office Assistant
Command Button Animation (Visit this page to link the Microsoft Office Library File to your Project).

We know that MS Access—and other MS Office applications—allow us to choose our preferred Office Assistant character from the Help menu. In this example, however, we’ll take a different approach. We’ll design an MS Access form that automatically organizes and displays all the Office Assistant characters available on your PC. From this form, you can browse through them, pick your favorite, and set it as the default Office Assistant anytime you wish.

A sample screenshot of the program in action is shown below:

We need a small Table with the Office Assistant's File names in a List Box as shown on the left side of the Form above.

Designing a Table and Form.

I created a Table with the Field Names as shown below, to save the names of the Office Assistant Characters list of Files with their location address.

Click to Enlarge
Out of the eleven items, the first seven files are stored in the same location, while the rest are located elsewhere. Before adding these entries to your table, it’s best to run a search on your PC for all files with the '.ACS' extension and use the results to create your list.
To search for these files:
1. Go to Start → Search → For Files or Folders → All Files and Folders.
2. Enter *.ACS In the search box and click Search.
3. When the search is complete, note down the full path of each file found.
4. Enter these full paths into your table.
Next, design a small form and set the Form and Control properties as given below.@@@
Create a List Box on the Detail Section of the Form. Click on the List Box (if it is not in selected state), Display the Property Sheet (View -> Properties), and change the Property Values as shown below:
- Name: AsstList
- Row Source Type: Table/Query
- Row Source: SELECT Assistant.ID, Assistant.asstChar FROM Assistant;
- Column Count: 2
- Column Heads: No
- Column Widths: 0";0.875"
- Bound Column: 1
- Multi-Select: None
- Left: 0.125"
- Top: 0.3125"
- Width: 1.2083"
- Height: 1.4375"
- Font Weight: Bold
Create a Label control on the top of the List Box and change the Caption Property value to Office Assistant.
Draw a Rectangle Control on the right side of the List Box and change the Property values as given below.
- Left: 1.4271"
- Top: 0.3125"
- Width: 1.7188"
- Height: 1.4375"
- Back Style: Transparent
- Special Effect: Sunken
Create two Command Buttons below the List Box as shown on the Design. Display the Property Sheet of the Left Command Button. Change the following Property values as given below:
- Name: cmdSetAsst
- Caption: Set as Default
Change the property values of the right-side Command Button.
- Name: cmdCancel
- Caption: Cancel
Form Property Values.
Now, to change the Properties of the Form, click on the top left corner of the Form (at the intersection where both the vertical and horizontal Scales meet). Display the Property Sheet, click on the All tab of the Property Sheet, if that is not the current one, and change the Property Values as given below.
- Caption: Office Assistant
- Default View: Single Form
- Scroll Bars: Neither
- Record Selectors: No
- Navigation Buttons: No
- Dividing Lines: No
- Auto Resize: Yes
- Auto Center: Yes
- Pop Up: Yes
- Modal: No
- Border Style: Dialog
- Control Box: Yes
- Min Max Buttons: None
- Close Button: Yes
- What's This Button: No
- Width: 3.3333"
- Allow Design Changes: Design View Only
Click on the Detail Section of the Form. Display the Property Sheet, if you have already closed it, and change the Property:
- Width = 2.4271"
Select Save from the File Menu and save the Form with the name Assistant, and stay in Design View, don't close the Form.

The VBA Code.

Display the VBA Module of the Form, View -> Code. Copy and paste the following code into the VBA Module, save, and close the Form:

Dim defaultAssistant As String
Dim strAsst() As String

Private Const m_left As Integer = 500
Private Const m_top As Integer = 225

Private Sub AsstList_Click()
Dim vID As Byte, strFileName, FRM As Form, l As Long
Dim ctrl As Control

On Error GoTo AsstList_Click_Exit:

vID = [AsstList]
With Assistant
    .On = True
    .fileName = strAsst(vID)
    .Animation = msoAnimationGetAttentionMajor
    .AssistWithHelp = True
    .GuessHelp = True
    .FeatureTips = False
    .Left = m_left
    .Top = m_top
    .Visible = True
End With

AsstList_Click_Exit:
Err.Clear
End Sub

Private Sub cmdCancel_Click()
On Error GoTo cmdCancel_Click_Exit
With Assistant
    .On = True
    .fileName = defaultAssistant
    .Animation = msoAnimationBeginSpeaking
    .AssistWithHelp = True
    .GuessHelp = True
    .FeatureTips = False
    .Left = m_left
    .Top = m_top
    .Visible = True
End With
DoCmd.Close acForm, Me.Name

cmdCancel_Click_Exit:
Err.Clear
End Sub

Private Sub cmdSetAsst_Click()
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Load()
Dim db As Database, rst As Recordset
Dim acsFiles As Integer, i As Integer

On Error GoTo Form_Load_Exit
defaultAssistant = Assistant.fileName

acsFiles = DCount("* ", "Assistant")
If acsFiles = 0 Then
   MsgBox "Assistants File Not found. "
   Exit Sub
End If
ReDim strAsst(1 To acsFiles) As String
Set db = CurrentDb
Set rst = db.OpenRecordset("Assistant", dbOpenDynaset)
i = 0
Do While Not rst.EOF
i = i + 1
strAsst(i) = rst![Path]
rst.MoveNext
Loop
rst.Close

With Assistant
  If .On = False Then
    .On = True
  End If
    .fileName = defaultAssistant
    .Animation = msoAnimationBeginSpeaking
    .AssistWithHelp = True
    .GuessHelp = True
    .FeatureTips = False
    .Left = m_left
    .Top = m_top
  If .Visible = False Then
    .Visible = True
  End If
End With
Form_Load_Exit:
Err.Clear
End Sub

Selecting the Office Assistant.

Open the Form in Normal View. Click on the items in the List Box one by one, giving a little time gap for the Assistant Character to appear. When your favorite image appears, click on the Set as Default Command Button to set the current Character as the default Office Assistant. If you change your mind, then click the Cancel Button to retain the existing one.

You can export this Form to your other Projects and use it to link the Microsoft Office Library File.

Note: At the beginning of the Code, I have defined two Constants, m_left = 500 and m_top = 225, to position the Image on the Form when it appears based on my machine's Screen resolution, 1024 x 768. You may change this value to adjust it to your machine's Screen Resolution.