Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Office Assistant with Check Box Menu

Introduction - Access2003.

We have seen the usage of Office Assistant for Message Boxes and Message Box with Options Menu in the earlier Article.

Like I said before when we design Applications there must be something different in it, something better than what we did earlier, better and attractive to the User, as well as to the curious onlooker too.

Data processing has its own importance and I am not overlooking it here. Every application that we develop is different and has challenges on its own merit and we have to devise new methods or techniques to deal with the issues in them.

But, repeating the same type of Application design and using the same kind of controls like the Command Buttons or Message Boxes over and over again is a boring experience, for the developer and for the User as well. I think that is one of the reasons why Windows or Microsoft Office Applications come out with more features and better-looking Programs all the time.

Check-box Type Menu.

We will try the Check Box type Menu Options in Message Box, with Office Assistant. We will use the same sample Form that we have created for running the earlier program. An image of the run of the Program is given below:

If you have not tried the earlier example, please read the Article on MsgBox with Options Menu and go through the preparations that I have mentioned there, in order to run the programs given here without errors.

  1. Open the Form OptionCheck that we have created in the earlier example in the design view.
  2. Create a second Command Button on the Form.
  3. Click on the Command Button to select it and display the Property Sheet (View -> Properties). Change the following property values as given below.
    • Name = cmdChk
    • Caption = Get Checked
  4. While the Form is still in design view, display the VBA Module of the Form (View -> Code).
  5. Copy and paste the following code into the VBA Module of the Form and save the Form.

    Private Sub cmdchk_Click()
    Dim OptionArray(1 To 5) As String
    Dim i As Integer, msg As String, title As String
    OptionArray(1) = "Display Report Source Data"
    OptionArray(2) = "Print Preview"
    OptionArray(3) = "Print"
    msg = "Please Select an Option"title = "Report Options"
    i = MsgGetChk(msg, title, OptionArray())
    If i > 0 Then
        Me![optTxt] = i & " : " & OptionArray(i)
    End If
    Select Case i
        Case -2
           msg = "Cancelled"
           Me![optTxt] = i & " : Cancelled"
        Case 1
            'DoCmd.OpenForm "myForm", acNormal
            msg = "Display Report Source Data"
        Case 2
            'DoCmd.OpenReport "myReport", acViewPreview
            msg = "Print Preview"
        Case 3
            'DoCmd.OpenReport "myReport", acViewNormal
            msg = "Print"
    End Select
            MsgOK "Selected: " & msg
    End Sub

    This is a copy of the same program that we have used for Baloon Type Options which is already there in the Form Module. I have made changes in two places in the Code to use it for this example.

    • The number of elements in the OptionArray() Variable is reduced to three after removing the Cancel item because the OK and Cancel buttons will be included in the main program.
    • Included testing for the Cancel condition in the Select Case . . . End Select statements and terminates the program if the user clicks on the Cancel Button. When you implement this method into your Project you can remove Case -2 and the next two statements underneath it. Here it is inserted for demonstration purposes only.

    The Main VBA Program

  6. Copy and paste the following main program into a Global VBA Module in your Project and save it.
    Public Function MsgGetChk(ByVal strText As String, ByVal strTitle As String, ByRef obj) As Integer
    Dim X As Integer, i, c As Integer, k As Integer
    Dim Bal As Balloon, vSelect As Integer
    On Error GoTo MsgGetChk_Err
    Set Bal = Assistant.NewBalloon
    i = 0k = UBound(obj)
    k = IIf(k > 5, 5, k)
    For X = 1 To k
       If Len(obj(X)) > 0 Then
         i = i + 1
       End If
    With Bal
       .Animation = msoAnimationWritingNotingSomething
       .Icon = msoIconAlert
       .Heading = strTitle
       .Text = strText
       .BalloonType = msoBalloonTypeButtons
       For X = 1 To i
         .Checkboxes(X).Text = obj(X)
       .Button = msoButtonSetOkCancel
      vSelect = .Show
      If vSelect = -2 Then
            MsgGetChk = vSelect
            Exit Function
      End If
      c = 0
      For X = 1 To i
        If .Checkboxes(X).Checked Then
           MsgGetChk = X 
    ' get the item checked by the user
           c = c + 1 
    'more than one item checked
           If c > 1 Then
              Exit For
           End If
         End If
      If c > 1 Then
         strText = "Select only one item. "
         GoTo ForceEntry
      End If
      If c = 0 Then
         strText = "Select one of the Options or Click Cancel! "
         GoTo ForceEntry
      End If
    End With
    Assistant.Visible = False
    Exit Function
    MsgBox Err.Description, , "MsgGetChk"
    Resume MsgGetChk_Exit
    End Function

    This main program is almost the same as the Options Menu that we have used in our earlier example. The statement labels(X).Text = MaxArray5obj(X) changed to .Checkboxes(X).Text = obj(X). Added a few more lines of code to check, whether the User

    • Put checkmarks in more than one item, if so, forced to select one item only.
    • Click the OK Button without selecting any of the Options. If so, asks to select one of the options before clicking the OK Button and suggest clicking Cancel Button if there is a change of mind.
  7. If the office Assistant is not visible, turn it on, Help -- > Show the Office Assistant, or press Alt+H followed by Alt+O.
  8. Right-Click on the Office Assistant and select Choose Assistant. Click on the Next or Back Button to select the Office Cat Character.

    The program works with any Office Assistant Image but the initial Animation Type: msoAnimationWritingNotingSomething set in the Program works fine with the Office Cat and I love the Office Cat.

  9. Open the OptionCheck Form in normal View. Click on the Get Checked Command Button. If everything went through, well you will see the Office Assistant with CheckBoxes as shown in the image at the top.
  10. Try clicking the OK Button with checkmarks in more than one item. Try clicking the OK Button, without putting a checkmark in any of the options.

Download Demo Database


No comments:

Post a Comment

Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


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