Using The Message Box in VBA And Access Macros
If you know anything about Access databases and that data is automatically bound to either a table or a simple query meaning data is saved to disk then on some occasions you may want to control this action. Using the message box in VBA or as a macro procedure can intercede and prompt users before committing to disk.
This is just one example of a prompt you can add and call to your database which will help those workflows and the smooth running of the database application. Having a message prompt appear at strategic moments will add the extra layer of control.
Using The Message Box in VBA And Access Macros – Two approaches…
Users can create a message box in VBA (known as MsgBox) or as a macro which is a predefined alert prompt with a series of buttons, icons and the ability to add a message with an optional title too.
Which one should you use? The later versions of Microsoft Access (including 2007 and 2010) makes it even easier to use a macro especially 2010 now has a newer design tool and is deemed the first choice. Though it can also be used in earlier versions, writing VBA code was the preferred option but at the end of the day it really boils down to what functionality the overall calling routine is going to run that may decide which approach.
Simple routines like running several back to back action queries (with the alert options disabled) maybe easier to write in a macro where as a routine that needs to validate values in different recordsets and return a true or false in order to proceed maybe better in VBA code.
There are occasions where only a message box in VBA could be used in more complex procedures that would include communicating with other applications which macros simply cannot do.
Using Message box in Macros approach
You may want to use this method to simply display a message at the end of running a report, provide simple macro navigation or to passively inform users an update is to occur or even get the user to make a decision to proceed (or not) using a logical test too (for another time perhaps!).
Here’s a simple macro design view and how it looks when you run it…
You can get this type of message box to return an response where the user clicks one of two or three button options but it requires two things:
- Using the older MsgBox command which acts as a function returning a unique value of the button clicked.
- Having some sort of way to test the logical response to which button was clicked using an If…Then…Else command flow.
The code may look something like:
=MsgBox(“Can you see the difference?”,35,”Access Macros”)
which is part of the expression inside the logical test statement.
To be fair, this might be better suited for next approach using VBA!
Using Message box in VBA code approach
If you haven’t coded in VBA before then this require some pre-learning of the code, the tools including the VBA editor and their components.
The following code example displays the same simple message box used with the mcaro approach:
To change the icons and buttons, you change the different constants (vbInformation) to other pre-defined lists of constants.
To convert this as a returning value and make it into a function will require some additional code and again you will need to tune into another post for more details.
If you would like to know more about how to program within this powerful application, I recommend starting by learning about macros and my eBook may be the way forward.