Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Office Assistant And Msgbox Menus-2

Continued from Last Week.

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.

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 BalloonType Property:

  1. BalloonType = msoBalloonTypeButtons

    The User can click on any of these Buttons to select one of the Options presented.

  2. BalloonType = msoBalloonTypeBullets
  3. 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.

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 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 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"
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
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:

  1. The Balloon Object bln.
  2. A Long Integer type value lbtn (BalloonTypeButton) identifying the Option clicked.
  3. 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.


No comments:

Post a Comment

Comments subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email


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