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

Continued from Last Week. - Access 2003.

This article is a continuation of last week’s post, “Office Assistant and MsgBox Menus.” If you haven’t read that yet, I recommend going through it first before proceeding with this one.

Last week, we learned how to use the Office Assistant for MsgBox simply 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 Properties in three different ways by setting the Values of the 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 visual styles, but they are not interactive—users cannot select them. These types are therefore best suited for situations where you only need to present information, rather than receive user input.

Let’s take a closer look at the overall behavior of the Balloon Object when displaying a message box through the Office Assistant, so we can better understand how it works.

If you revisit the earlier examples, you’ll notice that when a message box created with the Office Assistant’s Balloon Object is displayed, you cannot interact with any other objects in the database. You must dismiss the Balloon first by responding to one of its options—such as clicking OK or Cancel—before you can continue.

This behavior is identical to that of the standard MsgBox() function in MS Access. However, the Office Assistant provides ways to overcome this restriction.

When a Balloon is displayed, you cannot, for instance, open a Form or Report to verify some information before clicking Yes or No, because the Mode property of the Balloon Object is set to its default value, msoModeModal.

The Mode Property of Message Balloon

I have intentionally not introduced the Mode property in the earlier examples to avoid overwhelming you with too many details at once. Now that you’re familiar with the basics, it will be easier to understand the additional features associated with it.

As you’ve already seen, you can create stylish Message Boxes using the Balloon Object of the Office Assistant with just a few lines of VBA code—without even using this property.

The Mode property can be assigned one of three values, each controlling how the Balloon Object behaves when displayed.

Mode = msoModeModal (default).

This setting forces the user to respond to the Balloon before performing any other action—just like a standard MsgBox in MS Access. The user must click one of the available buttons (such as OK or Cancel) to dismiss the Balloon before continuing with any other task.

Mode = msoModeModeless.

This setting allows the user to access other database objects while the Balloon remains active. However, it also requires the use of another property—Callback—to trigger a separate subroutine that can handle user interactions, evaluate selections or choices, perform the necessary actions, and finally close the Balloon object.

Mode = msoModeAutoDown.

This setting automatically dismisses the Balloon (MsgBox) if you click anywhere else, effectively ignoring it.

The purpose and behavior of the first and last Mode property values are straightforward. However, the second value: msoModeModeless, requires special handling. When this mode is used, the Callback property must be set to the name of a valid subroutine; otherwise, the Balloon will fail to function properly. Leaving the Callback property empty (an empty string) will also cause an error.

Let’s now modify our earlier program to demonstrate how to use the Mode property set to msoModeModeless, along with the Callback property, to handle user selections from a menu displayed in the Balloon.

The Mode and CallBack Property.

The Modified Code with Mode and CallBack Property Settings and the sample code for the required Subroutine 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 Subroutine name myProcess. When the user clicks on one of the Options from the displayed MsgBox, the MyProcess() Subroutine is called by the Balloon and passes the required Parameter Values.

There are three parameters passed to the Subroutine 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 a 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.

lbtn The 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 Subroutine, 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.

bln.close The 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 a MsgBox.