Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Selection of Office Assistant


Since, we have tried many options with Office Assistant earlier we will do some fun stuff this time. Those who entered straight into this page may like to look into the following Topics. The links to the earlier Articles are given below:

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

We all know that we can select the Office Assistant of our choice from Help Menu of MS-Access or other MS-Office Applications as well. Instead, we will design a Form in MS-Access to organize all the Office Assistant Characters available on our PC on the Form, display them and select the one that we like, set it as the default Office Assistant, as we please.

A sample image of the Program Run is given below:

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

Designing a Table and Form

  1. Create a Table with the Field Names as shown below, save it with the name Assistant and enter the Data from the List.

    Out of eleven items the first seven files are on the same location and others are found elsewhere. Before entering the above list into your Table it is a better idea if you run a search for all the files with the File Extension .ACS on your machine and use those files for the list.

    To search for the files select Start -> Search -> For Files or Folders -> All Files and Folders and enter the search term *.ACS on the Search Control and Click Search. When the search is completed, note down the correct path of all the files displayed and then enter them into your Table with the full Path Name.

  2. Design a small Form and set the Properties of the Form and controls as given below to match the design.
  3. Create a List Box on the Detail Section of the Form. Click on the List Box (if it is not selected), 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
  4. Create a Label on the top of the List Box and change the Caption to Office Assistant.
  5. Draw a Rectangle Control at the right side of the List Box and change the Properties as given below.
    • Left : 1.4271"
    • Top : 0.3125"
    • Width : 1.7188"
    • Height : 1.4375"
    • Back Style : Transparent
    • Special Effect : Sunken
  6. Create two Command Buttons below the List Box as shown on the Design. Display the Property Sheet of the Left Command Button. Change the Properties as given below:
    • Name : cmdSetAsst
    • Caption : Set as Default
  7. Change the properties of the right-side Command Button.
    • Name : cmdCancel
    • Caption : Cancel

    Form Property Values.

  8. Now, to change the Properties of the Form click on the left top 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
    • Whats This Button : No
    • Width : 3.3333"
    • Allow Design Changes: Design View Only
  9. 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"
  10. Select Save from File Menu and save the Form with the name Assistant and stay in Design View, don't close the Form.

    The VBA Code

  11. 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
    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
    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]
    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
    End Sub

    Selecting the Office Assistant

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

You can export this Form into your other Projects and use it there after linking the Microsoft Office Library File to your Project.

NB: 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.

Download Demo Database


No comments:

Post a Comment

Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is alway...


Blog Archive

Recent Posts