Using VBA to Enhance Forms

When most users hear that they need to utilize VBA to perform a task in Access, they typically think of programming. Unfortunately, this conjures images of grunts and trolls locked away in a dank basement chained to a mainframe terminal feverishly pounding the keyboard and laughing maniacally at hexadecimal displays. Fearing this, most users would rather allow their VCR to blink "12:00 a.m." rather than suffer the wrath of becoming a programmer. Although VBA technically is programming, it's really more about the ability to automate processes rather than inventing new operating systems or other complex algorithmic solutions. Most times, even Microsoft refers to it as Office Automation, hoping to tone down the fear of users. Although the stress here is automation, the developer will not be referred to as an automater—developer, programmer, or creator is still the

So, now that your fears are subdued, turn your focus to the automation concept with this simple example. Suppose that a salesperson acquires a customer lead, and would like to send that lead an informational brochure. For the inexperienced Access user or developer, he or she may open the table (or worse, an Excel spreadsheet), enter the data, and close the table. Next the salesperson may create a query to extract that one entered lead, bind that query to a label report, and then print it. Then he or she opens a Word Document, types a cover letter to the lead, and prints that as well. From there, the salesperson gathers the label and the cover letter from the printer, applies the label to the shipping envelope, stuffs the cover letter and brochure into the envelope, and places it into the outgoing mailbox. It's almost exhausting just thinking about it.

If this were a real daily process for a salesperson, especially one paid on commission, he or she just may go hungry, as most of the day would be spent executing the process of business, rather than focusing on the objective of business.

Imagine now the automation of this simple operation. The user opens a data entry form and enters the new contact (or even an entire batch of contacts). Upon completion of the entry, the user can signal completion of the entry by clicking a button. The application then takes over—extracts the e-mail from each of the entered contact(s) and creates an e-mail with the electronic brochure attached.

This is the idea of VBA programming, especially with Form Automation. Utilizing simple snippets of code, the programmer can easily alleviate the user of mundane processes in a daily routine. When the user is doing work, and not working to do, then you know you have achieved the goal of automation. Now that you're in the proper mindset, becoming proficient with VBA is a less daunting task.

Although the focus of this chapter is the enhancement of forms, keep in mind that the concepts and techniques discussed will apply to reports as well. Although both are different objects, with completely different functionalities, the automation paradigm is the same.

0 0

Post a comment