Replacement MsgBox and InputBox

Replacement MsgBox and InputBox

There are times when the standard Access MsgBox doesn't cut it properly in your application - you might want different formatting, or you might want different text on the buttons than are present by default. The same also applies to using the standard InputBox - you might want to use a combo box, or set the text to appear as asterisks. In cases like this, you will need to create your own versions. Below, I will outline how to create one.

First create a new form in your database, and save it as "frmMsgBox".Then make the following changes to the form:

Caption Custom Message Box

Scroll Bars Neither

Record Selectors No

Navigation Buttons No

Dividing Lines No

Auto Center Yes

Control Box No

Min Max Buttons No

Close Button No

Pop Up Yes

Modal Yes

Shortcut Menu No

Next, we add Labels to the form (it is better to use labels, as they can't receive the focus). Add as many as are required for the different styles, and set the captions as required. Next, add two command buttons, and call one "cmdOK" and the other "cmdCancel", and their Captions to "OK" and "Cancel" respectively.

Now, resize the detail section of the form, so that there is a small border around each of the controls, and then resize the window that the form is in, so that the window is about the same size as the form itself, and then save.

Open the Form's Module, and then place some code in the OnClick events for the 2 command buttons:

Private Sub cmdCancel_Click()
    intMsgBox = vbCancel
    DoCmd.Close
End Sub

Private Sub cmdOK_Click()
    intMsgBox = vbOK
    DoCmd.Close
End Sub

I am using the intrinsic VBA constants vbOK (1) and vbCancel (2) because it makes it slightly clearer - you could of course use any value that you wish. Save the form, and then close it.

Now we create a new module, and add a function that will open this form, and then return a value. Firstly, we need to create a global variable, which we declare at the top of the module, and then create the function itself. The whole module should look like:

Option Compare Database
Option Explicit
Public intMsgBox As Integer

Function fMsgBox() As Integer
DoCmd.OpenForm "frmMsgBox"
fMsgBox = intMsgBox
End Function

Now you can call this message box in your database, get back the user's response, and take whatever action is required. You can add as many controls to the form as you wish. A similar approach can be taken to creating your own InputBox, although you will probably need to add another globally declared variable to handle whatever is typed in.

You can download an Access 97 database that has both a MsgBox and an InputBox in.

Top

Last modified at 06/06/2006 14:55:16