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 better than what we did earlier, better and attractive to the User as well as to the outside world.

Data processing has its own importance and I am not overlooking it here. Every Application that we develop is different and a challenge in 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, to the developer and to the User as well. I think that is one of the reasons why Windows or Microsoft Office Applications comes out with more features and better looking Programs all the time.

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:

Click to Enlarge

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.

I will be preparing sample databases on most of the Articles that I have published so far and will upload them into the site shortly, so that you can download and try them out instantly.

We have moved recently into our own site: www.msaccesstips.com and I am in the process of updating the site and dealing with the initial teething problems. Once that is over I will concentrate on the above task.

1. Open the Form OptionCheck that we have created in the earlier example in 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.

 

• Number of elements in the OptionArray() Variable 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 the Case -2 and next two statements underneath it. Here it is inserted for demonstration purpose only.

 

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
Next

ForceEntry:

With Bal
   .Animation = msoAnimationWritingNotingSomething
   .Icon = msoIconAlert
   .Heading = strTitle
   .Text = strText
   .BalloonType = msoBalloonTypeButtons
   For X = 1 To i
     .Checkboxes(X).Text = obj(X)
   Next
   .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
  Next

  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

MsgGetChk_Exit:
Exit Function

MsgGetChk_Err:
MsgBox Err.Description, , "MsgGetChk"
Resume MsgGetChk_Exit
End Function

 

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

 

• put check marks in more than one item, if so, forces to select one item only.

 

• clicked OK Button without selecting any of the Options. If so, asks to select one of the options before clicking OK Button and suggests clicking Cancel Button if there is a change of mind.

 

7. If the office Assistant is not visible, turn it on, Help – > <b>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 every thing went through well you will see the Office Assistant with Check Boxes as shown in the image at the top.

 

10. Try clicking the OK Button with check marks in more than one item. Try clicking OK Button without putting check mark in any of the options.

 

Download Demo Database

Configure Outlook for Lotus Notes

MS-Access and Email

Dynamic Report

MS-Access & Mailmerge-3

MS-Access & Mailmerge-2