Writing Code within Modules

A module is a container for code. You can store various subs, functions, and declarations within a module. To view all modules currently available in your Access database, click the Modules tab of the Access action pane. To view a module, select the module and choose Design from the database window's toolbar (see Figure 2-1).

We'll go into details on the various components of the VBA Editor in the Chapter 4, "VBA Basics," but you'll notice that by default, the VBA Editor contains a Project Explorer, a Properties dialog box, and the main code editor. If you have various modules available in your project, they will be listed in the Project Explorer in the upper-left corner of the screen. The Properties dialog box displays the properties for the currently selected object (either the module itself, or a control or form within the module).

j Microsoft Visual Basic - VBA Samples - [Samples (Code)]

E'ie Edit View Inseir Dehuy iHlÄ'iaiijt^affii'OP'ifc.

Run Tools Add-lns Window Help ill H I a? W ® I Ln 1, Col 1

Type a question for help

Projed - VBA Samples m

E-Ä VBA Samples (VBA Samples)

É QO Microsoft Office Access Class Objects É[email protected] Modules [-nZ Modulel Samples Class Modules


[Properties - Samples


.¡Samples Module


Alphabetic | Categorized |

fttfEiiiisfl Samples

I (General)

Option Compare Database Private blnUnderBudget As Boolean Private blnOverBudget As Boolean Const curBudget = 1000_

Dim intSuits As Integer Dim curSuitPrice As Currency Dim curTotalPrice As Currency curSuitPrice = 100

int-Suits = InputBox ("Enter the desired number of suits",

For i = 1 To intSuits curTotalPrice = curTotalPrice + curSuitPrice If curTotalPrice > curBudget Then blnOverBudget = True

Else blnUnderBudget = False End If


If blnUnderBudget = False Then

OverBudget End If

I Accès gjChapt... I SjJasc ... | ¿¡Micro... Micio..

Writing Code Behind Forms and Reports

Chapter 4 goes into detail about the VBA Editor within Access. But before you dive head first into VBA, you might want to get your feet wet by writing some basic code within a form. Every object on a form has a number of events you can respond to through VBA. Most objects or controls have a click event, a change event, and enter and exit events, just to name a few. You can add code to any of these events and that code will run in response to a user's actions on your form. Chapter 1 introduced you to the Build Event option, a way to build macros, expressions, or code. We'll cover this topic in a bit more detail here. Open an Access database and view a form in design mode. To build code in response to a control's event, click once to select the control. Display the Properties dialog box by clicking the Properties icon on the toolbar or right clicking the Control and choosing Properties. Once you have the Properties dialog box visible, click the Event tab. Choose the control's event in the Properties dialog box and click the Ellipses button (...) next to the Event box. Choose Code Builder from the dialog box that appears to display the VBA Editor, as shown in Figure 2-2.

You'll notice two differences between Figure 2.1 and Figure 2.2. First of all, the Properties dialog box shows all of the properties for the control you selected on your form. From this dialog box you can change any number of properties of the form including size, ControlTipText, and TabIndex. The second difference is subtle, but if you examine the Project Explorer in the upper-left corner of the window,

Figure 2-2

you'll notice that there's a new heading, Microsoft Office Access Class Objects. Under that heading is the name of the currently loaded form that contains the control you're currently working with.

The code window displays the name of the control you're working with and the event you chose. Whenever you choose to build code, a subroutine (sub) is created for you automatically. A function, the other main type of code block you'll write, is used when your code needs to return a value. Since you're writing code in response to an event on the form, you're not actually returning a value. You might update the value of the control on the form within the sub, but the actual sub itself doesn't return a value.

Other than the differences listed previously, the VBA Editor used to build code in response to a control's event is identical to that available in a module. The next chapter will go into detail about the various programming components you'll need to work within VBA.

VBA versus Macros in Access

Now that you've seen a little about how VBA works within Access you might be chomping at the bit to get started. However, there's one other scenario you should consider before jumping into Access programming without looking back: a macro. You can create a macro—a saved series of commands. Unlike in Word and Excel, where you can record your own macros, in Access you'll have to create the macro yourself, step-by-step. A macro enables you to perform a variety of operations within Access in response to the click of a command button or any other programmable event on a form or report.

If you've programmed in Word or Excel, you know that you can create a macro by starting the macro recorder and performing the desired steps. When you stop the macro recorder, all of the operations you've performed, from mouse clicks to keyboard strokes to menu selections, are recorded and saved in VBA code. You can then run the macro at a later time by selecting it from the Macros dialog box or in response to a keyboard or menu shortcut. Once you've recorded your macro, you can examine the VBA code behind the macro by simply choosing Edit from the Macros dialog box. This is one of the easiest ways to learn some VBA code within Word or Excel. For example, if you want to know the VBA code to insert three lines of text at the end of your Word document, just create a Word document start recording a macro, and type your three lines of text. You'll end up with code that looks similar to the following:

Sub InsertName() 1 InsertName Macro

1 Macro recorded 9/21/2003 by Patricia Cardoza Selection.TypeText Text:="Patricia Cardoza" Selection.TypeParagraph

Selection.TypeText Text:="MIS Application Specialist" Selection.TypeParagraph

Selection.TypeText Text:="Pacific Southwest Container" Selection.TypeParagraph End Sub

As you can see, there are some keywords you need to know before you can program Word to do what you want in VBA. Recording a macro in Word first, then perusing the commands can help you to figure out how to write more sophisticated code directly in the VBA Editor. As shown in the previous code listing, TypeText is the method of the Selection object that allows you to enter your own text within the document. TypeParagraph inserts a carriage return in the document. These are just two of the many methods you can use with the Selection object. While very few programmers ever need to use every method of an object, you can write better VBA code by familiarizing yourself with some of the most frequently used methods of the objects you'll be dealing with.

While Word and Excel have the ability to record macros, Access doesn't have this capability. To write VBA code in Access, you'll have to just jump right in and code. However, if you aren't quite ready for VBA code yet, you can still create detailed macros using the Macro Editor in Access. The only limitation is that you can't record a macro; you must create it yourself step-by-step. This book is the Access 2003 VBA Programmer's Reference so we won't spend much time on Macros, but we'll provide a very brief tutorial on creating and using macros in Access here.

Was this article helpful?

0 0

Post a comment