Wrapping the call to MsgBox

The MsgBox() function in Microsoft Access lets you display a standard Windows dialog to get a Yes/No answer from the user. The dialog is reliable and familiar but the VBA call to this function can be a bit verbose:

If MsgBox ("Are you sure", _
  vbQuestion + vbYesNo, _
  "Delete record") = vbYes Then...

The MsgBox function then gives you a return value of 6 or 7 so you need to write further code to determine whether the user said 'Yes' or 'No'. The task is not difficult but the task does become tedious.

You can make life a lot easier by writing a function to wrap the MsgBox call. We use a function named "Confirm" which takes the text and title as parameters and converts the numeric return from MsgBox into True or False.

The body of the function is very simple:

Public Function Confirm (strMessage As String, strTitle As String) As Boolean
Dim bytChoice As Byte

bytChoice = Msgbox(strMessage, _
  vbQuestion + vbYesNo, _
  tcTitle)

If bytChoice = vbYes Then
  Confirm = True
Else
  Confirm = False
End If

End Function

The VBA code to use this function is much easier to write than the call to MsgBox because you can embed it into an If statement:

If Confirm("Are you sure", "Delete record") Then...

An additional bonus in our work is that we have no longer have to remember the different calling conventions and the different names for the constants in the FoxPro and Access messageboxes. Both languages use the standard Windows dialog and both require a parameter of 36 and will return 6 or 7 as the result. The difference is that FoxPro calls it with the MessageBox() function rather than MsgBox() and has constants named IDYES and MB_ICONQUESTION corresponding to vbYes and vbQuestion in Access Basic. We've now got a "Confirm()" function written in both languages so we don't have to worry about these annoying differences any more.

Related Items

Custom Toolbars

Your Access database will look more impressive if you add custom toolbars.

Read More

Trust Center in Access 2007

Trust Center in Access 2007

Using the Trust Center in Access 2007

Read More

Access 2002 and Office XP

Notes on the launch of Microsoft Access 2002 and Office XP

Read More

Frequent, automatic backups

How to create backups automatically in FoxPro or Access using the Windows Scripting Host

Read More

Visual Basic equivalent of FoxPro file name function

VBA equivalents of FoxPro functions to manipulate names of files and folders

Read More