Programming & Security

Posted: 28 Aug 2009 12:32 AM PDT

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 have learned how to use Office Assistant for MsgBox in a simple way and seen how to create a Menu and present it to the User, with the Labels Property of the Balloon Object.

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

BalloonType = msoBalloonTypeButtons

Buttons

BalloonType = msoBalloonTypeBullets

BalloonType = msoBalloonTypeNumbers

The second and third BalloonType Values displays 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 little closer to 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 solution 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 default value msoModeModal.

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 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 separate Sub-Routine to do the testing of 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 with 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 set with the value msoModeModeless and Callback Property with the name of a Sub-Routine to handle the selection of option displayed in the Menu.

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 CallBack 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 identifying the Balloon that called the Sub-Routine, if there are more than one Balloon active in memory at the same time.

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

The lbtn Variable will have the value of 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 Check-Boxes in MsgBox.