Microsoft Access 2000: A Look at the New Visual Basic Environment

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Rick Dobson, Ph.D.

November 1998

Summary: Discusses the new Microsoft® Access 2000 development environment. (14 printed pages) Includes:

Introduction The VBE and Its Windows Debugging Running Procedures from the Immediate Window Y2K Compliance The Object Browser Conclusion

Introduction

As a programmer, the first thing you'll notice about Access 2000 is its new development environment. This innovation makes cross-component development more natural because Access developers will share a common development environment with Microsoft Excel, Microsoft Word, and Microsoft PowerPoint® developers. However, it forces Access developers to look in new places for old friends (such as the Debug window). Because you can examine and edit form and control properties from the Microsoft Visual Basic® Environment (VBE), this will give developers another point of control over the behavior of their applications.

This article introduces the VBE to Access developers. The VBE is an important place for developers because this is where you will be developing, testing, and debugging your code. You will learn about the VBE windows and navigating from the traditional application development places to the VBE. Selected date/time and form samples will illustrate how to apply the VBE to typical Access development issues.

The VBE and Its Windows

Access 2000 offers at least three routes to the VBE for code not associated with a form or report. First, for those familiar with Office development in the other Microsoft Office family members, from the Tools menu you can choose Macro and then Visual Basic Editor from the Database window (a convenient shortcut for this is ALT+F11). This takes you immediately to the VBE. You can use Alt+F11 to toggle between the Database window and the VBE. Second, if you already have any standard modules, click Modules in the Database window. Next, double-click any standard module you want to view. This opens the VBE with that module in it. Third, if you want to create a new standard module, click Modules in the Database window before clicking New on the toolbar. This opens a blank module in the VBE.

As I write this, the Office development team is working on a fourth route to the VBE that will be familiar to some Access developers. This potential fourth route will enable developers to click a Code button in the Database window with a standard module, form, or report selected. The Code button will open the VBE with the corresponding module.

To get to code that's associated with a form or report, you must first open the object in Design view. You can click the Code button on the Design view toolbar. This opens the object and positions your display at the top of the code. You can move directly to the event procedure for a specific object on a form or report by choosing the Build button (it has an ellipsis on it) next to the event. If there is no event procedure for an object, clicking the Build button and choosing Code Builder will move you to a blank event procedure for the object.

Once you're in the VBE, it is likely you will want to open the Project Explorer and Properties windows. These are convenient for opening and inspecting the other modules in an application. The Project window displays modules not behind a form or report in the Modules folder. Modules behind forms and reports appear in the Microsoft Access Classes folder. To open the Project or Properties windows choose the View menu and the appropriate subcommand. Notice that there are shortcuts for opening these and other windows that help you work effectively in the VBE. You can dock or float the Project and Properties windows by dragging them around the VBE window. Additionally, you can resize them by dragging their top, bottom, or sides.

Choosing a module associated with a form or report can allow you to view and set the objects associated with the class. For the Properties window to show the objects for an Access class, that object must be open in Design view. Double-clicking a module or class in the Project window will always show the corresponding instructions and declarations in the corresponding Code window, but the objects will only appear if the class object is open in Design view.

Figure 1. A view of the Project, Properties, and Code windows in VBE

Figure 1 displays the VBE for an Access file named Chapter2. The Project window highlights the Form_frmSample1 class. In the Properties window, I selected the Label control for the form's title. The Properties window reveals that this control has an event procedure associated with its OnClick event. The Code window to the right of the Project and Properties windows shows the code for this event procedure. You can readily move, resize, and dock any of these windows.

Double-clicking any other Access class or module object in the Project window opens the corresponding Code window. There you can inspect, edit, or copy the code. The Code window has a familiar layout with the Object and Procedure boxes at its top. You can use these to navigate a large module, or open new procedures in an existing one. The Properties window generally makes available for selection the objects in the currently selected Access class or module. While you can edit object properties from this window, it will typically be more convenient and flexible for Access developers to edit object properties from the Design view for a form or report.

Debugging

If you want to debug the code behind a form, you can start by placing a breakpoint in the form module. You can create a breakpoint anywhere in a procedure. Click in the left margin next to a line of code. The VBE marks the breakpoint with the traditional round dot. After setting the breakpoint, you can run up to it by returning to the form in Form view. Do whatever it takes to start the procedure with the breakpoint.

Figure 2. A view of the Code window for the load event of the Form_frmSample1 class

Figure 2 shows the Form_Load event procedure for the Form_frmSample1 class at a breakpoint. The arrow indicates the next line of code that will execute. Therefore, the shot in Figure 2 depicts a situation in which one of four statements in a procedure has completed execution.

Clicking Continue on the Run menu item causes the procedure to complete normally. The Run tool on the VBE standard toolbar (it has a right-pointing arrowhead) has the same effect as choosing the Continue command. Figure 3 shows the result. Notice that the form's caption reads "New Caption." In addition, the cursor rests in the text box that reads "Hi, there!" These results are a consequence of the code after the breakpoint shown in Figure 2.

By the way, the code in Figure 2 shows two ways to move the cursor to the text box. The code runs the SetFocus method. Apply this method directly to the object that is to receive the focus. Developers moving to Visual Basic for Applications (VBA) from an Access macro background may feel more comfortable with the GoToControl method. The DoCmd object makes this macro command available to VBA developers. Notice the GoToControl method requires that the control name be in quotes.

Figure 3. The outcome of running all three remaining lines in Figure 2

You can use the arrow in Figure 2 to remove one or more lines from the execution path. For example, you could drag the arrow to the line that invokes the SetFocus method. This takes the assignment of "New Caption" to the form's Caption property out of the execution path.

Figure 4. The outcome of running the last two remaining lines in Figure 2

Figure 4 shows the result of choosing the Run command with the next command moved to the SetFocus statement. This form looks similar, except its caption reads "My Default Caption."

Figure 5. The outcome of running the last lines in Figure 2

Figure 5 reveals the outcome of dragging the arrow in Figure 2 to the final line of code that makes an assignment to the text box. This, of course, generates an error message because of the attempt to assign a property value without the object having focus.

Much of the functionality of the Debug window from Access 97 is available in Access 2000. The Debug window in Access 97 had Watch and Locals tabs. Each tab provided functionality through a split screen where the Immediate window was available as part of each tab. Access 2000 makes available Watch, Locals, and Immediate windows. Open these windows on your VBE desktop through View menu items. You can drag, drop, and resize them alongside your Code, Project, and Properties windows. To designate which windows are dockable, from the Tools menu, click Options, and then click the Docking tab.

You can use the Watch window to track the values of expressions, variables, and objects as your code executes. After opening a Watch window, you can add variables to watch in it with the Debug | Add Watch command. Highlight a variable you want to watch before invoking the command. When the Add Watch dialog box opens, the highlighted term appears in the Expression text box. Choose a Watch type, and then click OK to close the dialog box. The default Watch type of Watch Expression shows the value of the expression in the Watch window as your code executes.

Figure 6. A sample Watch window depicting the status of expressions as an event procedure executes

If you step through your code in break mode, you can verify the value of critical expressions after each step. For example, Figure 6 reveals the value of frmSample1's Caption property immediately after an assignment statement transforms it from "My Default Value" to "New Caption". The Watch window in Figure 6 shows the value of txtMyTextBox as NULL because the code did not yet execute the assignment statement.

The Locals window can display all the variables in scope while your code executes in break mode. It starts with the Me tab at its top. You can successively expand Me and its selected components to search out the values of all properties and variables. If you require exhaustive detail to help you discover how a program performs, this may be the source for you.

Running Procedures from the Immediate Window

The Immediate window may be your single-most convenient tool for code development and debugging. From it, you can run any function or subroutine that's in scope. You can also evaluate expressions and different ways of writing functions. Before the availability of Watch expressions, the Immediate window was a convenient place to print intermediate results in break mode, and it can still serve related functions in special circumstances.

Figure 7. A sample Immediate window revealing how to run Sub procedures and built-in functions

Figure 7 presents three uses of the Immediate window. Typing MyFirstCalculator invokes the Sub procedure of the same name. When you press the Enter key while you are in the Immediate window, it runs the procedure and prints the result, 3, in the window. The same holds true for the CallSecondCalculator Sub procedure. In this case, the result is 4. The last example demonstrates how to specify arguments for the MySecondCalculator function; just type the name followed by a space, delimiting the arguments with a comma.

You can also use the Immediate window to run built-in and custom functions. If you need to specify a print command to return results from a function, you can use the keyword Print or simply a "?" followed by the function name and a carriage return. The last sample in the Immediate window demonstrates this for the built-in Date function.

Figure 8. A simple function procedure and its invocation from the Immediate window

Figure 8 shows a view of the VBE with a simple function that determines if the date submitted as an argument is in the third millennium. (To match popular conventions, we compute this millennium to start in the year 2000 rather than 2001.) The function Year2KTest accepts a date and returns 3 if the date is in the third millennium, or 0 otherwise. The Immediate window below the Code window shows the outcome for running the function with two dates. The Immediate window confirms this result by returning a 3 for the first day in 2000, and a 0 for the first day in 1999. Note that dates must be delimited with pound signs, for example #8/26/1998#. The following function computes the millennium for a date:

Public Function PopularMillenium(dtmDateIn) As Byte

  MsgBox "This works for dates after 12/31/0099" & _
         " and before 1/1/3000.", vbInformation, _
         "MS Access 2000 Developer's Handbook"

  If dtmDateIn <= #12/31/999# Then
    PopularMillenium = 1
  ElseIf dtmDateIn <= #12/31/1999# Then
    PopularMillenium = 2
  Else
    PopularMillenium = 3
  End If

End Function

The preceding code shows a more sophisticated approach to millennium assessment. It starts by declaring the range of dates for which it is accurate. Then, the function tests its argument against two millennium milestones. If the date doesn't fall in the first or second millennia, the function assumes it belongs in the third millennium. The initial message box statement indicates the range of dates for which the function is accurate.

Y2K Compliance

Access 2000 dates are valid over the range from 1/1/100 through 12/31/2999. This range is sufficient for the vast majority of desktop applications. Those with a need for a range beyond these dates should consider coding dates independently of the Access serial date numbering scheme.

Access 2000 is Y2K compliant. Access 2000, like its predecessors, stores dates with the full four digits. Access 2000 also handles leap years correctly. There are three rules for leap years: 1) years divisible by 4 are leap years; unless 2) they are years divisible by 100; unless 3) they are divisible by 400. Because 2000 is divisible by 400, it is a leap year. Correctly responding to this rule is critical for computing the difference between two dates. Access's General Date and Short Date formats use the operating system's short date format options to determine the correct display. If you set your Regional settings in Control Panel to show dates with four-digit years, all Access General date formats will show the full four digits for years.

The most natural way for you to invoke procedures is still from user events. Access 2000, and its predecessors, enable interactive applications through their ability to respond to user events. Figure 9 shows a form that uses an event procedure along with function procedures to enable a four-function calculator. The form allows a user to type values in the text boxes with labels of Number 1 and Number 2. Then, clicking a function button of /, *, -, or + computes a corresponding outcome in the text box labeled Result.

Figure 9. A simple four-function calculator

The following is Visual Basic for Applications code for the simple four-function calculator shown in Figure 9:

Option Compare Database
Option Explicit

Dim dblResult As Double

Private Sub cmdAddition_Click()
  Me.txtResult = MyAdder
End Sub

Private Function MyAdder()
  dblResult = CDbl(txtNumber1) + CDbl(txtNumber2)
  MyAdder = dblResult
End Function

Private Sub cmdSubtraction_Click()
  Me.txtResult = MySubtractor
End Sub

Private Function MySubtractor()
  dblResult = CDbl(txtNumber1) - CDbl(txtNumber2)
  MySubtractor = dblResult
End Function

Private Sub cmdMultiplication_Click()
  Me.txtResult = MyMultiplier
End Sub

Private Function MyMultiplier()
  dblResult = CDbl(txtNumber1) * CDbl(txtNumber2)
  MyMultiplier = dblResult
End Function

Private Sub cmdDivision_Click()
  Me.txtResult = MyDivider
End Sub

Private Function MyDivider()
  dblResult = CDbl(txtNumber1) / CDbl(txtNumber2)
  MyDivider = dblResult
End Function

The VBA to implement the form in Figure 9 relies on just four pairs of procedures (see preceding code). Four Sub procedures act as the event handler for a click to the four function keys on the form. These event handlers merely call a function procedure that pulls the values from the two text boxes, executes the computation indicated by the function button, and returns that value to the event procedure. The event procedure, in turn, assigns the return value from the function to the third text box on the form. Notice the optional use of the Me prefix before the text box names. Because the code is behind the form with the text box, it is optional whether to specify the form name. The event procedures use Me instead of the form's longer, more formal name, Form_frmCalculator1.

All the function and Sub procedures reside in the code associated with the form. The Dim statement at the top of the code declares a variable that all the procedures in the module can use. Because users can click only one function key at a time, this sharing of dblResult works. The Option Explicit statement requires the declaration of variables before their use. This helps guard against typographical errors, a common source of errors in programs.

The Option Compare Database is a module-level specification that designates string variables' sort in an order determined by the locale ID. You can view and edit the locale ID from the General tab of the Options dialog box. Open this dialog box with the Tool | Options command in the Database window. Click the New database sort order drop-down box to view a new expanded list of locale IDs. These result from a transition to a new database format based on Unicode representation for string characters.

You can view and edit this code in any of several ways. If you want to view the properties of the controls on the form, switch to Form Design view. Then, click the Code button on the toolbar. If you just want to make the code behind the form available, press ALT+F11 from Form view. Then, double-click Form_frmCalculator1.

The Object Browser

The Object Browser is a powerful tool for learning about object models. This tool will become especially important to Access 2000 developers because of the many significant new object model innovations. For example, Access 2000 introduces a new data access development language—ActiveX Data Objects (ADO)—that will eventually make Data Access Objects (DAO) obsolete. There are at least three object models underlying the Access 2000 implementation of ADO.

Recall that you will need to set references to many libraries. Use the Tools | References command to verify or create links to ADO type libraries. Open the Object Browser by clicking its button on the standard VBE toolbar. You can also present the browser with the View menu or by clicking 2.

Figure 10. An Object Browser view of the ADODB Recordset class and a select set of its members

One of the ADO libraries is named ADODB. Select this from the drop-down list at the top of the Object Browser. Notice that this action changes the contents of the Classes and Members lists in the browser. Figure 10 shows the Recordset class selected in the Classes list with a mix of properties, methods, and events appearing in the Members list. Select a member entry and click the ? button for detailed help on that topic.

The Object Browser can also search for specific classes and members with its search engine. Use the second drop-down box to specify a search criterion. This will sometimes develop result sets in several libraries. The Object Browser enables you to examine these separately.

Conclusion

Access 2000 is emerging as an exciting new version of Microsoft's venerable Office database component. This article focuses on the VBE. This new feature in Access 2000 gives Access developers a common development environment with Excel, Word, and PowerPoint developers. It will now be easier than ever for Access developers to build cross-component applications that draw on the best of two or more Office components. The Object Browser can help Access developers learn and apply the object models from these other applications.

Despite the fresh interface the VBE delivers, you can see that under the covers you still have familiar procedures, modules, and debugging tools. The VBE changes how you interface with these development tools—not their basic availability.

In closing, I want to remark that Access 2000 is a major upgrade in several important areas. The new VBE is just the tip of the iceberg. We alluded to a couple of major issues in this article. These are the new ADO models and the new database format. Both of these can have profound effects on how you work with Access. In future articles, I will explore selected Access 2000 features that make it worth your attention.

**Note   **This article is based on a pre-release version of Access 2000. Features may differ or be absent from the shipping version.

Rick Dobson, Ph.D., is an author/trainer/developer. His computer practice has been in full-time operation since 1991, and he delivers Access training on three continents. Microsoft Press recently contracted with him to write its Microsoft Access 2000 Developer's Handbook. You can reach his consulting practice at http://www.cabinc.net/.

This material was originally published in Microsoft Office & VBA Developer magazine, and appears by permission of Informant Communications Group, Inc. (http://www.informant.com/mod/)