February 2021 ~ LEARN MS-ACCESS TIPS AND TRICKS

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 response from the User to resume Code execution. Now, we could do that. We will create a new Message Box that disappears itself after completion of the specified time.

It can be used as a progress meter within a process if it takes a long time to complete. It can display useful information about the progress of the process, at intervals of certain fixed cycles of the process to the impatient User.

This Message Box is based on the Popup() Method of the Windows Scripting Object.

It 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 on 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, if you omit the time parameter the message box will wait for a click from the user, like the MsgBox of MS Access.

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 informing the user what process is going to execute next, and so on.

MesgBox() is the New Name.

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

The Access MsgBox parameters msgText, buttons, and Title are all valid for the new MesgBox function. Buttons like vbOkOnly, vbOkCancel, and others. The Icons vbCritical, vbInformation, and others. To specify the Icon and default selected button, like in MsgBox as 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 Seconds.

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

Exactly after n seconds, MesgBox will close itself. The time value is not displayed in 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 a 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 is 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) 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 is allowed to close it, 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 test, the 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.

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.
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.
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 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.