Image of a Microsoft Access Message Box, with the use of Office Assistant, is shown below. The Message Box pops up after the User changes the Appointment Date field value with a new date and the Message Alert asks the User to reconfirm whether she really needs to replace the earlier Date 20/07/2009 with the new value 25/07/2009 or not.

 

 

The Field Name text is underlined and field values (old and new) are displayed in different Color. Company Logo is displayed above the message text.

 

Want to find out quickly how this works?

 

1. Copy the following VBA Code into a Standard Module in your Database:

    

   Public Function MyAssistant()
   Dim msg As String
   Dim AppointmentDt As Date
   Dim AppointmentDt2 As Date
   Dim logo As String
   
   AppointmentDt = #7/20/2009#
   AppointmentDt2 = #7/25/2009#
   logo = "{bmp D:\Images\CoLogo.bmp}"
   
   msg = logo & vbCr & "Existing {ul 1}Appointment Date:{ul 0}{cf 252} " & AppointmentDt & "{cf 0}" & vbCr & vbCr
   msg = msg & "Replace with {ul 1}New Date:{ul 0}{cf 249} " & AppointmentDt2 & "{cf 0}...?"
   
   With Assistant.NewBalloon
       .Icon = msoIconAlertQuery
       .Animation = msoAnimationGetAttentionMinor
       .Heading = "Appointment Date"
       .text = msg
       .Show
   End With
   
   End Function

    

2. If you have a small Bitmap File (.bmp) somewhere on your machine then change the path in this line logo = "{bmp D:\Images\CoLogo.bmp}" of the Code to your .bmp image location.

 

3. See that you have linked Microsoft Office Object Library File to your Database. If you are not sure how to do it then do the following:

    

   • Press Alt+F11 to display the VBA Editing Window (or Tools – - >Macro – - >Visual Basic Editor), if it is not already visible.
   • Select References from Tools Menu.
   • Find Microsoft Office 11.0 Object Library (or whatever Version available) in the Available List and put a check mark to select it.
   • Click OK to close the Dialog Box.

    

4. Click somewhere in the middle of the above VBA Code you have pasted into your Database.

 

5. Press F5 Key to Run the Code. You will find the above Message Box with Office Assistant displaying your Bitmap Image above the message text.

 

One important point to note here is that the visibility of the actual data displayed within the message text with underline and Color. It catches the eye of the User quickly and looks lot better than showing everything in one Color.

 

Note: Readers those who have not yet learned how to use Office Assistant with MS-Access Message Boxes may read the following Post and copy the Programs presented there into your Library Database or Project. You can download a sample Database with the Code from there too.

 

Message Box with Office Assistant

 

You may also go through the following Posts to learn more interesting ways to use Office Assistant with Message Boxes:

 

1. Message Box with Options Menu

 

2. Office Assistant with Check-Box Menu

 

3. Selection of Office Assistant

 

The AppointmentDt_LostFocus() Event Procedure that displays the above Message Box is given below:

 

Private Sub AppointmentDt_LostFocus()
Dim msg As String

If Me![AppointmentDt].OldValue <> Me![AppointmentDt].Value Then
    msg = AsstLogo & vbCr & "Existing {ul 1}Appointment Date:{ul 0}{cf 252} " & Me![AppointmentDt].OldValue & "{cf 0}" & vbCr & vbCr
    msg = msg & "Replace with {ul 1}New Date:{ul 0}{cf 249} " &  Me![AppointmentDt].Value & "{cf 0}...?"

    If MsgYN(msg, "AppointmentDt_LostFocus()") = vbNo Then
        Me![AppointmentDt].Value = Me![AppointmentDt].OldValue
    End If
    Me.Refresh
End If

End Sub

 

If you check the above message text formatted and stored in the String Variable msg you can see that it uses certain code values within {} (curly brackets) embedded in the Message String to format Underline and Color to the text. Old and New Values from the AppointmentDt field is joined with the message text to show them in color.

 

At the beginning of the message text I have used the word AsstLogo to add the Company Logo above the Message Text. I will explain this after we check the other codes and usage.

 

The MsgYN() User Defined Function we have created for displaying Message Box with Yes and No Option Buttons with the Office Assistant and learned about other options in the earlier Articles mentioned above.

 

There are only two type of Codes used within curly brackets to format the message text:

 

{ul } ' stands for Underline

{cf } ' stands for Color Format and used with 16 different Color Numbers.

 

{ul 1} turns On the underline and {ul 0} turns it Off.

 

{cf 0} (Black Color) is used to change the Color of text to normal message color.

 

Example-1: MsgOK "Hi {ul 1}Michael{ul 0}, Welcome to Tips and Tricks."

Result: Hi Michael, Welcome to Tips and Tricks.

 

Example-2: MsgOK "Hi {cf 250}{ul 1}Michael{ul 0}{cf 0}, Welcome to Tips and Tricks."

Result: Hi Michael, Welcome to Tips and Tricks.

 

You may copy the text string part of the above examples into the first program given above and try them out to find out.

You can use any of the following 16 Color Numbers in the {cf } format string:

 

Color Codes:

 

• Black : 0
• Dark Red : 1
• Dark Green : 2
• Dark Yellow : 3
• Dark Blue : 4
• Dark Magenta : 5
• Dark Cyan : 6
• Light Gray : 7
• Medium Gray : 248
• Red : 249
• Green : 250
• Yellow : 251
• Blue : 252
• Magenta : 253
• Cyan : 254
• White : 255

 

The only difficulty you may experience while formatting the message text is to type all the codes in curly brackets and it is likely that you may make errors in balancing the opening or closing brackets. In such cases that part of the message text or the field values added to it will not appear in the Message Box when displayed.

 

There is an easy way out to this problem. Define all the above Color Codes as Global Constants in VBA Standard Module and use the Constants wherever we need them in the message text. You can declare the Color Values as Constants in a Standard Module like the following:

 

Constant declaration Examples:

 

    Public Const AsstUlOn as String = "{ul 1}"

 

    Public Const AsstUlOff as String = "{ul 0}"

 

    Public Const AsstGreen as String = "{cf 250}"

 

    Public Const AsstBlack as String = "{cf 0}"

 

Then we can re-write the example-2 Code given above as:

 

Example-2:

 

MsgOK "Hi " & AsstGreen & AsstUlOn & "Michael" & AsstUlOff & AsstBlack & ", Welcome to Tips and Tricks. "

 

No curly brackets or color codes to memorize, if you know the name of the Color you frequently uses then you can format the message quickly the way you want it.

 

To display Company Logo above the Message Text (or below if you like) you can use two types of Images, Bitmap Image (.bmp) or Windows Meta File (.wmf) Image.

 

Bitmap Image usage: {bmp ImagePath}

 

Windows Meta File usage: {WMF ImagePath sizing_factor}

 

The sizing_factor determines the width of the .wmf file displayed and is omitted for Bitmap Files. You can use .wmf Image without the sizing_factor Value as well.

 

The complete Global Constant declarations for above Color values are given below. You may copy them into the Standard Module of your Common Library Database to use across your Applications on the Network or into one of your Databases to try them out before implementing it in your other Applications.

 

Public Const AsstLogo As String = "{bmp D:\Images\CoLogo}"
Public Const AsstUlon As String = "{ul 1}"
Public Const AsstUloff As String = "{ul 0}"
Public Const AsstBlack As String = "{cf 0}"
Public Const AsstDarkRed As String = "{cf 1}"
Public Const AsstDarkGreen As String = "{cf 2}"
Public Const AsstDarkYellow As String = "{cf 3}"
Public Const AsstDarkBlue As String = "{cf 4}"
Public Const AsstDarkMagenta As String = "{cf 5}"
Public Const AsstDarkCyan As String = "{cf 6}"
Public Const AsstLightGray As String = "{cf 7}"
Public Const AsstMediumGray As String = "{cf 248}"
Public Const AsstRed As String = "{cf 249}"
Public Const AsstGreen As String = "{cf 250}"
Public Const AsstYellow As String = "{cf 251}"
Public Const AsstBlue As String = "{cf 252}"
Public Const AsstMagenta As String = "{cf 253}"
Public Const AsstCyan As String = "{cf 254}"
Public Const AsstWhite As String = "{cf 255}"

 

You may modify the Image Path on the first line to point it to the Bitmap Image on the Network’s common folder accessible to your Application Users so that all your MS-Access Applications on the Network can use this image.

 

Use the Image Path Name in UNC format (like \\ServerName\FolderName\ImageName.bmp) so that if the Disk Drive mapping is changed to a different letter like K: or J: etc. the programs will not loose contact with the image on the Network Path.

 

Instead of adding the AsstLogo Constant along with the message text every time like:

 

Msg = AsstLogo & vbCr & "Hi Michael,. . . " it is better if you add it to the main program code so that it is used automatically to all the messages before they are displayed. See the modified Code segment on the main program given above:

 

Public Function MyAssistant()
With Assistant.NewBalloon
    .Icon = msoIconAlertQuery
    .Animation = msoAnimationGetAttentionMinor
    .Heading = "Appointment Date"
 .text = AsstLogo & vbCr & msg
    .Show
End With
End Function

 

Modify the above Code segment of the Main Program you have copied from the earlier Article: Message Box with Office Assistant to use the Company Logo in all your Message Boxes.

 

During special Occasions like Christmas or Valentines Day you can replace the Company Logo Image with other images suitable to these occasions to give surprises to your Application Users.

 

Check the sample Message Boxes with Christmas Images:

 

Or the Image of King of Pop Music:

 

Image Source: Britanica Encyclopedia

 

 

---

Filter by Character and Sort

Animating Label on Search Success

Form Background with Gradient Color

MS-Access and Reference Library

InputBox and Simple Menus