Access VBA Macro Programming

To specify that any combination ofSHIFT,CTRL, andALTshould be held down while

several other keys are pressed, enclose the code for those keys in parentheses. For example,

to specify to hold downSHIFTwhileEandCare pressed, use+(EC). To specify to hold down

SHIFTwhileEis pressed, followed byCwithoutSHIFT, use+EC.

If you useSendKeysto drive another application, be aware that the keyboard is still active

and can have disastrous results if it is touched whileSendKeysis running.

Some years ago, I wrote aSendKeysprogram for a major bank in the UK to work with a

time-recording application. The program ran overnight and generated timesheets for use on

Friday morning. One Friday morning, there were no timesheets because the program had

gone haywire. The reason for this was that during the evening, a cleaner had been dusting and

managed to press theRIGHT ARROWkey on the keyboard, throwing off my careful sequence

of keystrokes. This meant that instead of going down one particular column, it went down the

next one, and the keystrokes had no effect. After that incident, when the program was run on

a Thursday evening, the keyboard was always placed behind the monitor out of harm’s way.

SendKeysis not the most elegant way of doing things, but in this particular case it was the

only option available given the application involved. Because of thousands of users on the

application, it put immense pressure on the server on a Friday morning with people logging

on and generating timesheets, and it certainly led to the server going down and a lot of

unhappy project managers.

TheSendKeysoption saved the day in this particular case and allowed the timesheets to

be generated overnight. Managers were pleased and the servers stayed up!

Message Boxes.

In many of the examples in this book, I have used theMsgBoxfunction to communicate

results to the user. You can write code to place the result into a particular cell on the

spreadsheet, but the message box is an extremely easy way to send data back to the user. It

only needs one command, along with the line of text you wish to display to provide a

professional-looking message box onscreen. So far it has only been used in its simplest form:

MsgBox "Hello World",vbInformation

Figure 5-1 shows the result of this. It does look slightly different from the message boxes

that you see in other programs. The caption in the title bar says “Microsoft Access.” In

addition, there is no icon and there is only one option button.

You can very easily customize the message box’s title bar and icon to suit your needs.

MsgBox "Hello World", vbInformation

This will cause the message box to look more professional, with a proper icon and a

meaningful title. When you typed this line of code, you probably noticed that when you get

66 Microsoft Access 2010 VBA Macro Programming