Office Assistant And Msgbox Menus-2 ~ LEARN MS-ACCESS TIPS AND TRICKS

Continued from Last Week. - Access 2003.

This is the continuation of last Week's Article: Office Assistant and MsgBox Menus. Readers may go through that Page first, if you have not already done so, before continuing.

Last week we learned, how to use the Office Assistant for MsgBox in a simple way and saw how to create a Menu and present it to the User, with the Labels Property of the Balloon Object.

Labels().Text Property

We have seen that we can display the Menu from Labels().Text Property in three different ways by setting the Values of Balloon Type Property:

Balloon Type = msoBalloonTypeButtons
The User can click on any of these buttons to select one of the options presented.
Balloon Type = msoBalloonTypeBullets
Balloon Type = msoBalloonTypeNumbers

The second and third Balloon Type Values display the Labels().Text in different styles, but they cannot be selected by the User. So, we can use these options for different situations in Programs.

Let us look a little closer at the overall behavior of the Balloon Object when you display MsgBox with Office Assistant, to understand it better.

If you go back and try those earlier examples, you can see that while a MsgBox with the Office Assistant's Balloon Object is displayed you cannot click or work with any other object in the database unless you dismiss the Balloon by responding to the actions you are suggested to do, like clicking on the OK or Cancel Button and so on. This is true in the case of the normal MsgBox() Function of MS-Access too. But, Office Assistant has some, solutions to this rigid behavior of Message Boxes.

When the Balloon is displayed, you cannot open a Form or Report to check something on it, before clicking the Yes or No Button on the MsgBox because the Mode Property of the Balloon Object is set with the default value msoModeModal.

The Mode Property of Message Balloon

I have not introduced the Mode Property to you in the earlier examples to avoid overcrowding of usage rules. Once you are through with the basics it will be easier to understand other things associated with it better. You have already seen that you can create Message Boxes using Balloon Object of Office Assistant with a few lines of Code and display them in style without using these properties.

The Mode property can be set with three different values to control the behavior of the Balloon Object.

Mode = msoModeModal (default).

This value setting forces the user to dismiss the Balloon by responding to the suggested action before doing anything else, like normal MS-Access MsgBox. You have to click on one of the Buttons (if more than one is showing like OK and Cancel) on the MsgBox to dismiss the Balloon before you are allowed to do anything else.

Mode = msoModeModeless.

This value setting allows the User to access other database objects, while the Balloon is active. But, this forces the use of another Property CallBack, to run a separate Sub-Routine to do the testing of a selection of choices or do different things based on the choice and finally to Close the Balloon Object.

Mode = msoModeAutoDown.

This value setting dismisses the Balloon (MsgBox) automatically if you click somewhere else ignoring the Balloon.

The first and last Mode Property value setting and their usages are very clear. But, the second one (msoModeModeless) needs the CallBack Property Value set to the name of a valid Sub-Routine otherwise the Balloon will not work. You cannot load the CallBack Property with an empty string either because that will trigger an Error.

So let us see how we can re-write the earlier Program to understand the usage of Mode Property sets to the value msoModeModeless and Callback Property with the name of a Sub-Routine to handle the selection of options displayed in the Menu.

The Mode and CallBack Property.

The Modified Code with Mode and CallBack Property Settings and the sample code for the required Sub-Routine MyProcess() is given below:

Public Sub Choices()
Dim bln As Balloon

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Report Options"
    .Icon = msoIconAlertQuery
    .Button = msoButtonSetNone
    .labels(1).text = "Print Preview."
    .labels(2).text = "Print. "
    .labels(3).text = "Pivot Chart. "
    .BalloonType = msoBalloonTypeButtons
    .text = "Select one of  " & .labels.Count & " Choices? " 
    .mode = msoModeModeless
    .Callback = "myProcess"
    .Show
End With

End Sub

Sub MyProcess(bln As Balloon, lbtn As Long, lPriv As Long)
Assistant.Animation = msoAnimationPrinting
Select Case lbtn
    Case 1
        DoCmd.OpenReport "MyReport", acViewPreview
    Case 2
        DoCmd.OpenReport "MyReport", acViewNormal
    Case 3
        DoCmd.OpenReport "MyReport", acViewPivotChart
End Select
 bln.Close
End Sub

The Call Back Property is set with the Sub-Routine name myProcess. When the user clicks on one of the Options from the displayed MsgBox the MyProcess() Sub-Routine is called by the Balloon and passes the required Parameter Values.

There are three parameters passed to the Sub-Routine when called:

The Balloon Object bln.
A Long Integer type value lbtn (BalloonTypeButton) identifying the Option clicked.
A Long Integer type value lPriv (Private) uniquely identifies the Balloon that is called the Sub-Routine, if there is more than one Balloon active in memory at the same time.

NB: There is no such thing as a collection of Balloon Objects. But, you can create an Array of Variables with Balloon Object, define different Property Settings for each of them, and the Show() method is run with their respective index numbers in Programs when you need them to appear.

The lbtn Variable will have the value of the User's choice. This is tested in the Sub-Routine and runs the Docmd.OpenReport action within the Select Case End Select Statements.

The Assistant.Animation = msoAnimationPrinting line is placed in the Sub-Routine, rather than in the main Program as part of the Balloon Object Property setting, to animate the printing action only after the User makes a selection from the displayed Menu otherwise, the printing animation will run before the selection of choices.

The bln.close statement dismisses the MsgBox.

Next week we will learn how to use the CheckBoxes().Text Property Values of the Balloon Object to display a Menu with CheckBoxes in MsgBox.