Follow Us

Formatted Message Box in Access

Fancy Message Box

How many times have you wished that Microsoft had not done away with the fancy formatted Message Box that we loved in Access 97?

I cannot count the times, myself.

Here is my solution - a replacement function that performs an Eval() on the classic MsgBox() function as a string expression. The Eval function then returns the formatted message box like we all love and miss (at least those of us who used it all the time).

The code is fairly simple. Taking a cue from a forum entry on the Microsoft Access Help Center, I created the function below that replaces the Prompt with appropriate code to create the formatted message.

A couple of important notes:

First, I used the tilde (~) instead of the at (@) sign because e-mail addresses are often included in my messages.  Substitute your favorite seldom-if-ever-used character if the tilde doesn't work for you.

Second, I have VERY often encountered issues with single quotes in string variables.  Apostrophes appear in contractions, Irish names, possessive nouns, and a variety of other situations that may end up as part of a text string that you want to put in quotes.  The Quote() function is a simple one-liner that references another short function called FixQuotes().  These two functions (also included below) ensure that quotes inside quotes don't confuse the issue and cause premature termination of quoted text because of a spurrious quote in the middle of your text string.

Happy coding!

' Replace MsgBox with a formatted version like the old-style messages

' The message box at the top used the following code:

' Msg_Box "Formatted message boxes were great!~Why did Microsoft get rid of them?", vbOKOnly Or vbExclamation, "FORMATTED MESSAGE BOX"

Function Msg_Box(Prompt As String, Optional Buttons As VbMsgBoxStyle, Optional Title As String = "Microsoft Access")

    Dim strPrompt As String
    Dim aPrompt As Variant

    ' If there is a delimiter, then replace the delimiter with the appropriate symbols
    ' otherwise, just send the prompt out as it is.

    If InStr(Prompt, "~") > 0 Then
        aPrompt = Split(Prompt, "~")
        strPrompt = aPrompt(0) & "@" & aPrompt(1) & "@@"
        strPrompt = Prompt
    End If
    ' Wrap the prompt and other parameters in an Eval to get the appropriate
    ' formatted output. The Quote function ensures that any apostrophes or
    ' other single-quotes are handled without crashing the output.

    Msg_Box = Eval("MsgBox(" & Quote(strPrompt) & "," & Buttons & "," & Quote(Title) & ")")

End Function


Function Quote(StringToQuote As Variant, Optional DoubleQuote As Boolean = False) As String

    Quote = "'" & FixQuotes(StringToQuote, DoubleQuote) & "'"

End Function


Function FixQuotes(strToFix As Variant, Optional DoubleQuote As Boolean = False) As String

    If DoubleQuote Then
        FixQuotes = Replace(Nz(strToFix), """", "'")
        FixQuotes = Replace(Nz(strToFix), "'", "''")
    End If

End Function

HostGator Web Hosting
RoboForm: Learn more...