Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Message Box that Closes itself after Specified Time

Introduction.

The new Message Box in Microsoft Access is an interesting one, which was in all of our minds, I would say, for a long time.  A Message Box that closes itself, after displaying some useful information and that doesn’t need any feedback from the User and continue execution of the VBA Code.  Now, we could do that and we will be creating a new  Message Box that disappears itself after a specified time. 

It can work as a progress meter of the current process, if it takes a long time to complete, displaying useful information at fixed intervals to the impatient User.  This Message Box is based on the Popup() Method of the Windows Scripting Object.

This message box accepts all the parameters of the Access MsgBox function, except [helpfile] and [context] parameters.  In addition to that it has another optional parameter, the time value in seconds. The time value in seconds determines how long this message box stays visible on the screen.  When the time completes the message box disappears, it will not wait for you to click a button to dismiss it.  If you click on one of the displayed buttons, it closes immediately and will not wait to complete the specified time.  So it works in both ways.

Assume that the time parameter value specified is 5 seconds, It will disappear automatically after 5 seconds.  This is good for displaying useful information to the user if some process or program takes too long to complete, or what process is going to initiate next and so on, to keep the user informed.

MesgBox() is the New Name.

We have given a suitable name MesgBox() to our new Message Box functionThe letter e is inserted between the letter M and s of our Access MsgBox function name.  I think it is easier to remember while writing Code.

The Access MsgBox parameters msgText, buttons, Title are all valid for the new MesgBox function.  Buttons like vbOkOnly, vbOkCancel, and others.  The Icons vbCritical, vbInformation, and others.  To specify the selected default button like in vbOkCancel+vbCritical+vbDefaultButton2.  In the new MesgBox function there is one more parameter the time value in Seconds, to specify how long the message box should remain on Screen before disappearing, expressed in Integer Seconds.

The sample image of the new MesgBox() with n seconds duration is given below:

Exactly after n seconds, it will close itself.  The time value is not displayed on the message box.

Even if you omit the Optional button parameter the Ok button will appear by default.

Note: If you think that it is necessary that the User should know the duration of the message on the screen then concatenate the time value as part of the message.

If you click on one of the displayed Buttons before the full-delay time it will close immediately and will not wait for completing the specified time value as the parameter.  The time value parameter is optional if omitted or is zero value then it behaves like Access MsgBox.  The user should click on one of the displayed buttons to dismiss it.

Access MsgBox() and New MesgBox() Functions.

The new MesgBox() Function needs only a bare minimum of three lines of Code.  Before taking up the complete function Code let us compare Access MsgBox() and new MesgBox() Function Syntax.

1.  Access MsgBox stays on screen and the Code execution stops till the user clicks on one of the displayed Buttons.

NB: If you are on a Mobile device then to view the full length of the Code line touch on the Code Window and slide to the left.

‘Syntax: opt = MsgBox(msgTxt,[Buttons]+[Icon]+[DefaultButton],[Title])

opt = MsgBox("Preparing Report, Please Wait. . .",vbOkCancel+vbInformation+vbDefaultButton2,"Reports")

2.  The new MesgBox closes itself and the Code execution continues after the time specified in seconds as one of its parameters or immediately after the user clicks on one of the displayed buttons, whichever happens first. The delay time expressed in Integer Seconds and is passed as the second parameter to the Function.

‘Syntax: opt = MesgBox(msgTxt,[intSeconds],[Buttons]+[Icon]+[DefaultButton],[Title])

opt = MesgBox(“Preparing Report, Please Wait. . .”,5,vbOkCancel+vbInformation+vbDefaultButton2,”Reports”)

In the new MesgBox Function, the delay time value in seconds is the second parameter after the msgText parameter, the Buttons+Icon+defaultbutton is the third and the Title comes last.  Like the Access message box, all parameters are optional except the first one.

When the above MesgBox function call executes this message box appears with the Cancel Button already selected by default.  If the selected button option is acceptable to the user he can press Enter Key immediately to dismiss the message box or Click on his preferred choice of option.  If not then after 5 seconds the message box will close itself. 

Omitting Time Param works like Access MsgBox().

If selecting an option is mandatory then omit the Time Value parameter, or enter 0 (zero) value for the time parameter to keep the MesgBox on Screen till the User selects one of the displayed Option Buttons.

Note: Our new MesgBox Function is based on Microsoft Windows Script Host’s Popup() Method and accepts all the Access MsgBox Function Parameter Values  (except HelpFile and Context parameters) in a different order. Check the Syntax of Popup() Method given below:

'Syntax:

expression = winShell.Popup(strText, [intSeconds], [strTitle], [intButtons])

Since we have derived our new MesgBox Function from Windows Script Host Object’s Popup() method we have organized the order of parameters for our function in almost the same order as the Access MsgBox() function. But, they will be passed to the Popup() function in the required order.

MesgBox Function VBA Code.

Here is the VBA Code of the new MesgBox() Function.  It takes only three lines of code.

Public Function MesgBox(ByVal msgText As String, _
    Optional ByVal intSeconds As Integer, _
    Optional ByVal intButtons = vbDefaultButton1, _
    Optional TitleText As String = "WScript") As Integer

Dim winShell As Object

Set winShell = CreateObject("WScript.Shell")

MesgBox = winShell.PopUp(msgText, intSeconds, TitleText, intButtons)

End Function

If the user clicks on one of the displayed buttons its corresponding Integer value is returned to the Calling Program.

You may call the MesgBox() function from the Immediate Window (Debug Window) with a different set of Optional Parameters and test it yourself in various ways to familiarize its usage.

The MesgBox() Function Demo Test Subroutine.

We have a demo program to test the new MesgBox() function with different sets of Buttons, Icons, and time values.  When the user clicks on a Button or allowed to close itself then the program checks the returned value and displays a second MesgBox with an appropriate response and disappears after 3 seconds.

Public Sub MesgBox_example()
Dim opt As Integer
Dim Title As String
Dim intSeconds As Integer
Dim optSeconds As Integer
Dim Tip1 As String

Title = "MesgBox_example"
intSeconds = 5
optSeconds = 3
Tip1 = "Click Button before time ends in " & intSeconds & " Seconds" & vbCr & vbCr

'//Enable only one of the four methods given below.

opt = MesgBox("Preparing Monthly Report" & vbCr & "Please wait . . .", intSeconds, vbInformation, "Info")

'opt = MesgBox(Tip1 & "Preparing Monthly Report" & vbCr & "Please wait . . .", intSeconds, vbInformation + vbOKCancel, "Info")

'opt = MesgBox("Cannot Delete Records . . .!", , vbExclamation + vbAbortRetryIgnore + vbDefaultButton3, "Delete")
    
'opt = MesgBox("Database Shutdown . . .?", , vbCritical + vbYesNo + vbDefaultButton2, "Shutdown")

'// Test whether the button clicked or not, if it did then which button/returned value.
Select Case opt
    Case -1 ' No button selected, MesgBox closed automatically after the time specified.
    
        '//In this MesgBox the time parameter is omitted, works like Access MsgBox. Need to click the Button to close.
        MesgBox "No Button Selected." & vbCr & "Click Ok button here to close this MesgBox.", , vbInformation, Title
    
'//The following options work only when the MesgBox button receives the Click.
    Case vbOK '- 1
        MesgBox "Preparing Report" & vbCr & "User's Response Ok", optSeconds, vbInformation, Title
    
    Case vbCancel '- 2
        MesgBox "Not to Prepare Report" & vbCr & "User's Response Cancel", optSeconds, vbInformation, Title
    
    Case vbAbort '- 3
        MesgBox "Record Deletion Aborted.", optSeconds, vbExclamation, Title
    
    Case vbRetry '- 4
        MesgBox "Retrying Record Deletion.", optSeconds, vbExclamation, Title
    
    Case vbIgnore '- 5
        MesgBox "Record Deletion Process Ignored.", optSeconds, vbExclamation, Title
    
    Case vbYes '-  6
        MesgBox "Yes, Shutdown Approved.", optSeconds, vbCritical, Title
        'DoCmd.CloseDatabase
    Case vbNo '- 7
        MesgBox "Database Shutdown Denied.", optSeconds, vbCritical, Title
End Select


End Sub

Save the Code in Access Global Module.

Copy the MesgBox() and the MesgBox_Example() VBA Code into a Global Module and save the Code.  Compile the Code to ensure that everything is in order.

In the MesgBox_example() the first MesgBox() Function Calling line is already enabled.  You may click somewhere within the Code and press the F5 Key to Run the Code.  It will display the message box with the Ok Button alone and after five seconds the MesgBox will close itself.  The returned value is –1 in Opt Variable.

The returned value is tested in the next section and displays an appropriate message.  In this particular message line under the Case –1 testthe time value parameter is omitted and the message box behaves like the Access MsgBox.  When the time parameter value is omitted or zero (0) then you must click on a button on the message box to close it. 

Run the same option a second time and this time click on the Ok button before the message box closes. The Clicked button-value is returned to the Opt Variable.  The Ok button-click returns the value 1.  Based on the returned value it displays an appropriate message and closes it after 3 seconds.

Disable the tried-out line by inserting the comment symbol at the beginning and enable the next line by removing the comment symbol.  Test the enabled line with different buttons, Icons by repeating the above method.

A Critical Message Box:

The MesgBox() function is called directly from the Debug Window (Immediate Window) with the function parameters as given below:

msgTxt = "Database Shutdown . . .?"

MesgBox msgTxt,,vbYesNo+vbCritical+vbDefaultButton2,"Shutdown"

Points to Note.

  1. Take Note of these Side effects:

    Since it is Windows Script-based control, even if you minimize the Access Application Window the MesgBox will appear on the Windows Desktop.

  2. If the Time Value parameter is omitted then the user needs to click on a Button, like Access MsgBox to dismiss the MesgBox Control. In between if the user clicks somewhere other than the MesgBox control, it will disappear and goes behind the Access Application Window, and stays on the Windows Desktop.

  3. Our VBA Program is still in executing state and waiting for the response of the User on the MesgBox Control. At this time if you try to close the Database it will ignore the attempt and will not provide any clue.  But, you may use the Exit Access Option from Office Button to Shutdown Access Application altogether.

We are familiar with Microsoft Windows Common Controls, like TreeView, ListView, ImageList, and others.  The above function works with Windows Script Object’s Popup() method.  Unlike Windows Common Control (the MSCOMCTL.OCX) you don’t need to attach the Windows Script Host Object Model (C:\Windows\SysWOW64\wshom.ocx file) to the Reference Library List.

If you are facing any issues in using this Windows feature please visit the Microsoft Support Page for suggestions to correct your issue.

Here is the Code of MesgBox() Function with Error-trap lines inserted.

Public Function MesgBox(ByVal msgText As String, _
    Optional ByVal TimeInSeconds As Integer, _
    Optional ByVal intButtons = vbDefaultButton1, _
    Optional TitleText As String = "WScript") As Integer

On Error GoTo MesgBox_Err
Dim winShell As Object

Set winShell = CreateObject("WScript.Shell")

MesgBox = winShell.PopUp(msgText, TimeInSeconds, TitleText, intButtons)

MesgBox_Exit:
Exit Function

MesgBox_Err:
winShell.PopUp Err & " : " & Err.Description, 0, "MesgBox()", vbCritical
Resume MesgBox_Exit
End Function

Since you understood the advantages and disadvantages of both the functions MsgBox and MesgBox use them sparingly.  If you share your Application with the new MesgBox() Function then ensure that it works in the new location.

The TreeView Control Tutorial Session Links.

  1. Microsoft TreeView Control Tutorial
  2. Creating Access Menu with TreeView Control
  3. Assigning Images to TreeView Control
  4. Assigning Images to TreeView Control-2
  5. TreeView Control Check-Mark Add Delete Nodes
  6. TreeView ImageCombo Drop-Down Access Menu
  7. Re-arrange TreeView Nodes by Drag and Drop
  8. ListView Control with MS-Access TreeView
  9. ListView Control Drag Drop Events
  10. TreeView Control With Subforms
Share:

MSA GURU : Access Tips & Tricks App

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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