`
`
`
`
`
`Merrill Communications LLC
`d/b/a Merrill Corporation
`Exhibit 1005 pt. 12
`
`

`

`
`
`

`

`ee
`
`:
`oemo
`
`
`
`PAOoe
`
`

`

`
`
`
`
`

`

`
`
`

`

`Deciding when to use VBA
`
`Finding out where VBA code lives in a database
`
`Building a procedure
`
`Converting macros to Visual Basic
`
`855
`
`856
`
`863
`
`865
`
`

`

`Introducing Visual
`Basic for Rpplications
`
`f you've experimented with hyper(cid:173)
`links and macros, you know that you
`can use them to automate lots of dif-
`ferent database tasks. However, there are some cases where a macro or a hyperlink just
`isn't enough to do the task at hand. For example, if you want to prompt someone for
`the name of a file to import instead of using a macro to import a specific file, you need
`to don your propeller beanie and start programming.
`The programming language for Access 97 for Windows is Visual Basic for Applica-
`tions. This programming language is shared by Visual Basic, Access, and the other
`Microsoft Office applications.
`
`Access provides macros that are easy to use and properties that can be set to run macros.
`Therefore, a legitimate question is why you would need to program in Visual Basic at
`all. In Chapters 20 through 24, for example, we showed you how to accomplish lots of
`tasks with macros. Can't you do everything you need with a macro?
`The answer to that question is both yes and no. Yes, many Access users will never
`have to go beyond macros to accomplish what they need to do. However, no, you can't
`
`

`

`856
`
`CHAPTER 25 • INTRODUCING VISUAL BASIC FOR APPLICATIONS
`
`do everything with macros. Macros have some bad habits that prevent them from
`being ideal for every purpose. In general, you should use macros under the following
`conditions:
`
`• When your focus is simplicity. Macros provide an extremely visual programming
`style. You do not have to learn syntax. You simply select from among the options
`provided and the action you desire is programmed.
`• When you want to create a toolbar or menu. Visual Basic does not provide an
`alternative way of creating these objects.
`• When you want to undertake an action at the time the database opens using the
`AutoExec macro. In this circumstance, you must use the macro.
`• When the built-in error messages from Access are insufficient in case of trouble.
`
`You should use Visual Basic for Applications when you have these goals in mind:
`
`• When your focus is ease of maintenance. Unlike macros, Visual Basic procedures
`can be a part of the forms or reports that contain them. When you copy a form or
`report from one database to another, all of the Visual Basic procedures stored with
`the object are copied with it.
`
`• When Access does not provide a function that you need. If no built-in function can
`perform a particular calculation, you can write your own function in Visual Basic.
`" When you want to respond to error messages creatively. Using Visual Basic, you
`can create your own error messages or take an action that corrects the error with(cid:173)
`out user intervention.
`
`• When you need to check the state of the system. Macros will allow you to run
`another application, but they don't provide access to system level information. You
`cannot check, for instance, whether a file exists using a macro. Visual Basic, how(cid:173)
`ever, allows you access to system level information and actions.
`• When you need to work with records one at a time. Macros perform actions on sets
`of records. Visual Basic allows you to step through records and perform actions on
`each single record while it is in focus.
`• When you need to pass arguments between procedures. You can set the initial argu(cid:173)
`ments for macros, but you can't change them while the macro runs. You can make
`such changes, or use variables for arguments, using Visual Basic.
`
`What
`
`of VIsual Basic?
`
`So where does Visual Basic for Applications hide within the overall structure of Access?
`How do you use all of its wonderful features? To find Visual Basic, you need only look
`behind the Code button on the Form Design or Report Design toolbar. Clicking on this
`
`

`

`WHAT IS THE SHAPE OF VISUAL BASIC?
`
`1857
`
`button opens the code window for the form or report in focus (see Figure 25.1). Using
`this window, you write your custom procedures and functions.
`The code that you insert into a form or report applies only to that form or report or
`to the objects contained within it. Although you can call these procedures from any
`other procedure included in your database, the code still applies only to the form or
`report and the objects contained therein. For code that needs to be accessible to any
`object in your database, create a module using the New button on the Modules tab in
`the database window. Place your global procedures and functions in this code window.
`
`ldrliJ;IIJII
`The code win(cid:173)
`dow for the
`Contacts form in
`the Contact
`Management
`database
`generated by
`the Database
`Wizard.
`
`Private Sub ContactTypeiD_NotinList (NewData As String,
`UsgBox rrDou.ble-click this field to add an entry to
`Response = acDataErrContinue
`End SulJ
`. ·····-··· ···~··· ...
`Private Still ContactTypeiD_DblClick(Cancel As Integer)
`On Error GoTo Err_ContactTypeiD_DblClick
`Dim lngContactTypeiD As Long
`
`'
`
`If IsNull (l!e! [ContactTypeiD]) Then
`Me'[ContactTypeiD] .Text
`
`Visual Basic and Objects
`Visual Basic is an object-oriented programming environment. An object has a set of pro(cid:173)
`cedures and data associated with it. Some objects also have a visual representation,
`though others are available only in Visual Basic code. A good example of an object is a
`form. You draw the form on the screen; it is associated with a set of procedures, and
`
`data is displayed in the form.
`
`

`

`858.
`
`CHAPTER 25 • INTRODUCING VISUAL BASIC FOR APPLICATIONS
`
`Given this definition, reports are also objects, as are any of the controls that you use
`to build both forms and reports. (Technically, tables and queries can also be considered
`objects.) Each object has associated with it properties, which govern the appearance and
`behavior of the object. Each object also has a set of methods defined for it, which are
`actions the object can take. Some objects, including forms, reports, and controls, also
`respond to a set of events.
`You are already familiar with properties for objects. They are the same properties that
`you have been setting for forms, reports, and controls as you have worked with databases
`throughout this book. Methods should also be familiar, since you have used the methods
`(such as Recalc, GoToPage, and SetFocus) associated with forms. Events are very similar to
`event properties into which you could insert a macro; however, you can write code that
`responds to each event, instead of relying on predefined macro actions.
`
`Visual Basic and Events
`Visual Basic uses event procedures to respond to events. By default, VBA never does any(cid:173)
`thing when an event occurs. If you want an object to respond to an event, say a button
`click or the activation of a form, you have to write the code for the procedure. Your code
`overrides the default action, allowing the action you define to take place in response to
`the event.
`Each object defines the events to which it responds. If you look in the code window
`for the Calls form in the Contact Management database, you can get a sense of how
`these events are made available to database developers (see Figure 25.2). The form con(cid:173)
`tains several objects. The drop-down list box in the upper-left corner of the window
`lists all the objects associated with the form. The (General) object represents code that
`affects all the objects in the list. Each object, including the form itself, is designated by
`a name, which is one of an object's properties.
`The drop-down list box in the upper-right corner of the code window lists all the
`events to which an object responds, as shown in Figure 25.3. Each event is given a
`descriptive name such as Click. When you select an event from the list, the code win(cid:173)
`dow displays the frame of the event procedure, consisting of a Private Sub Calls_Event
`(Arguments) statement and an End Sub statement. These two statements are required
`to frame the code responding to the event. They frame the block of code known as an
`event procedure, the set of statements that causes an action in response to an event.
`
`Visual Basic and Statements
`In order to build an event procedure, you have to use Visual Basic statements and func(cid:173)
`tions. If you are not familiar with programming, you may feel overwhelmed by the number
`
`

`

`~Microsoft Access
`
`IDlrlQ;IIJI'M
`The Jist of
`objects in the
`code window
`for the Calls
`form in
`the Contact
`Management
`database.
`
`WHAT IS THE SHAPE OF VISUAL BASIC?
`
`1859
`
`..
`
`I!J~£1
`
`Integer)
`
`forr(l using the Calls button on
`
`of statements available to you. Visual Basic provides flow control statements, such as
`If...Then ... Else ... , which govern the order in which other statements execute. (The two
`statements that frame an event procedure are, in fact, flow control statements.) VBA also
`provides statements that cause an action to take place, such as Beep (which causes the
`system to send a beep to the speaker) or ChDir (which changes the current folder or
`directory). In total, VBA uses 81 such statements. (Help contains a complete reference
`for them.)
`
`II
`The next chapter covers flow control in more detail.
`
`In addition, you can write statements that set an object property, assign a value to a
`variable, use a function, or use a method. To assign a value to an object property, you
`write a statement in the following form:
`
`Set Object.Property = Value
`
`To assign a value to a variable, you use a statement of this form:
`
`Set Variable = Value
`
`

`

`860
`
`CHAPTER 25 • INTRODUCING VISUAL BASIC FOR APPLICATIONS
`
`~ Micr.lsoft Aoi"cess
`
`1#3@1'31
`The Proc list
`and the event
`procedure for
`the Click event
`associated with
`the Calls object
`on the Contacts
`form.
`
`"
`
`l!!lr..J 13
`
`On Error GoTo Err Calls Click
`If IsNull (f!e! [Contact I!>])
`HsgBox rrEnter contact
`Else
`DoCmcl. DoHenuitem acFon/f\evPt·ess
`DoCmcl.OpenForm
`End If
`
`Exit Calls Click:
`Exit :3u.b
`
`Err Calls Click:
`MsgBox Err.Description
`Exit Calls Click
`
`To use a function, first you select a function that calculates something or takes an
`action. Typically, functions return the value they calculate or a code indicating success
`for your program to use. As a result, you often assign the value of a function to a vari(cid:173)
`able for future use. The following statement, for example, uses the Date function to
`collect the system date and stores it in a variable named Today:
`
`Today = Date()
`
`To use a method, you use the same kind of syntax that you use to set a property(cid:173)
`but without an equal sign. You combine the name of the object with the method using
`dot notation to form the statement. One of the most useful object names is Me, which
`names the object currently in focus. The following statement causes the current object
`to redraw itself using the Refresh method associated with it:
`
`Me.Refresh
`
`

`

`WHAT IS THE SHAPE OF VISUAL BASIC?
`
`1861
`
`We mentioned variables just in passing. A variable is a name you give to Access. Access
`sets up an area of memory for storing items and gives it that name. A variable is said to
`have a type, which describes the nature of the item that can be stored in it. By default,
`Visual Basic creates variables of the variant type, which means that anything can be
`stored in it. To create a variable name, you simply use it. Or you can explicitly name it
`in a statement called a declaration at the beginning of your procedure:
`
`Dim strDialStr As String
`This statement says to create, or "dimension" (hence the Dim statement), a variable
`named strDialStr as type String. StrDialStr can therefore contain only strings of charac(cid:173)
`ters. Attempting to assign data of some other type to it will cause an error. Visual Basic
`supports the following variable types:
`
`Integer Whole numbers between -32,768 and 32,768
`.,
`" Long Whole numbers between -2,147,483,648 and 2,147,483,647
`" String Text up to approximately 65,500 characters in length
`" Currency Numbers with up to four decimal places
`between -955,337,203,685.5808 and 955,337,203,685.5807
`38
`" Single Real numbers in the range± 1.40 x 10-45 to± 3.40 x 10
`
`" Double Real numbers in the range± 4.94 ( 10-324 to± 1.79 x 10
`• Variant Can contain any of the preceding data types
`
`308
`
`When you name a variable, you should include the first three letters of its type in its
`name, as shown above. The reason for including the type in the name is that you can
`always tell what can be stored in a variable by looking at its name. Late at night on a
`long project, or coming back to maintain code after two months away from it, you will
`appreciate this convention.
`
`To make a variable available to all procedures and functions in your application,
`code module and precede it with the keyword Public.
`
`u
`Event procedures are not the only procedures you can write in Visual Basic. You can
`also write a procedure as a part of a global code module or at the General level of any
`form or report, and you can call that procedure any time you need to perform the
`action undertaken by the procedure. The Contact Management database uses this type
`
`

`

`860
`
`CHAPTER 25 • INTRODUCING VISUAL BASIC FOR APPLICATIONS
`
`~ Micro;iofl i\cce~s
`
`I!!II§U3
`
`l#@ij;l#¥11
`The Proc list
`and the event
`procedure for
`the Click event
`associated with
`the Calls object
`on the Contacts
`form.
`
`Else
`DoCmd. Dolienuitern acFnn,/1\e;·Pre:os
`DoCmd.OpenForm
`End If
`
`Exit Calls Click:
`-
`Exit Sub
`
`Err Calls C 1 ick:
`MsgBox Err.Description
`Exit Calls Click
`
`To use a function, first you select a function that calculates something or takes an
`action. Typically, functions return the value they calculate or a code indicating success
`for your program to use. As a result, you often assign the value of a function to a vari(cid:173)
`able for future use. The following statement, for example, uses the Date function to
`collect the system date and stores it in a variable named Today:
`
`Today = Date()
`
`To use a method, you use the same kind of syntax that you use to set a property(cid:173)
`but without an equal sign. You combine the name of the object with the method using
`dot notation to form the statement. One of the most useful object names is Me, which
`names the object currently in focus. The following statement causes the current object
`to redraw itself using the Refresh method associated with it:
`
`Me.Refresh
`
`

`

`WHAT IS THE SHAPE OF VISUAL BASIC?
`
`1861
`
`We mentioned variables just in passing. A variable is a name you give to Access. Access
`sets up an area of memory for storing items and gives it that name. A variable is said to
`have a type, which describes the nature of the item that can be stored in it. By default,
`Visual Basic creates variables of the variant type, which means that anything can be
`stored in it. To create a variable name, you simply use it. Or you can explicitly name it
`in a statement called a declaration at the beginning of your procedure:
`
`Dim strDialStr As String
`This statement says to create, or "dimension" (hence the Dim statement), a variable
`named strDialStr as type String. StrDialStr can therefore contain only strings of charac(cid:173)
`ters. Attempting to assign data of some other type to it will cause an error. Visual Basic
`supports the following variable types:
`
`• Integer Whole numbers between -32,768 and 32,768
`" Long Whole numbers between -2,147,483,648 and 2,147,483,647
`• String Text up to approximately 65,500 characters in length
`.. Currency Numbers with up to four decimal places
`between -955,337,203,685.5808 and 955,337,203,685.5807
`38
`45 to± 3.40 x 10
`"' Single Real numbers in the range± 1.40 x 10-
`324 to± 1.79 x 10
`• Double Real numbers in the range± 4.94 ( 10-
`• Variant Can contain any of the preceding data types
`
`308
`
`When you name a variable, you should include the first three letters of its type in its
`name, as shown above. The reason for including the type in the name is that you can
`always tell what can be stored in a variable by looking at its name. Late at night on a
`long project, or coming back to maintain code after two months away from it, you will
`
`appreciate this convention.
`
`To make a variable available to all procedures and functions in your application,
`it with the keyword Public,
`
`u
`Event procedures are not the only procedures you can write in Visual Basic. You can
`also write a procedure as a part of a global code module or at the General level of any
`form or report, and you can call that procedure any time you need to perform the
`action undertaken by the procedure. The Contact Management database uses this type
`
`

`

`860
`
`CHAPTER 25 • INTRODUCING VISUAL BASIC FOR APPLICATIONS
`
`i!hticrgsoJt Ac~;ess ,
`
`litdQ;/#BI
`The Proc list
`and the event
`procedure for
`the Click event
`associated with
`the Calls object
`on the Contacts
`form.
`
`•
`
`1!1~ 13
`
`HsgBox "Enter contact
`Else
`DoCmd. Doiienuitem ac:F~r•li{e,,Pre'"
`DoCmd. OpenForr(l
`End If
`
`Exit Calls Click:
`Exit StUJ
`
`Err Calls Click:
`MsgBox Err.Description
`Exit Calls Click
`
`To use a function, first you select a function that calculates something or takes an
`action. Typically, functions return the value they calculate or a code indicating success
`for your program to use. As a result, you often assign the value of a function to a vari(cid:173)
`able for future use. The following statement, for example, uses the Date function to
`collect the system date and stores it in a variable named Today:
`
`Today = Date()
`
`To use a method, you use the same kind of syntax that you use to set a property(cid:173)
`but without an equal sign. You combine the name of the object with the method using
`dot notation to form the statement. One of the most useful object names is Me, which
`names the object currently in focus. The following statement causes the current object
`to redraw itself using the Refresh method associated with it:
`
`Me.Refresh
`
`

`

`WHAT IS THE SHAPE OF VISUAL BASIC?
`
`1861
`
`We mentioned variables just in passing. A variable is a name you give to Access. Access
`sets up an area of memory for storing items and gives it that name. A variable is said to
`have a type, which describes the nature of the item that can be stored in it. By default,
`Visual Basic creates variables of the variant type, which means that anything can be
`stored in it. To create a variable name, you simply use it. Or you can explicitly name it
`in a statement called a declaration at the beginning of your procedure:
`
`Dim strDialStr As String
`This statement says to create, or "dimension" (hence the Dim statement), a variable
`named strDialStr as type String. StrDialStr can therefore contain only strings of charac(cid:173)
`ters. Attempting to assign data of some other type to it will cause an error. Visual Basic
`supports the following variable types:
`
`• Integer Whole numbers between -32,768 and 32,768
`• Long Whole numbers between -2,147,483,648 and 2,147,483,647
`• String Text up to approximately 65,500 characters in length
`" Currency Numbers with up to four decimal places
`between -955,337,203,685.5808 and 955,337,203,685.5807
`38
`45 to± 3.40 x 10
`" Single Real numbers in the range± 1.40 x 10-
`308
`324 to± 1.79 x 10
`" Double Real numbers in the range± 4.94 ( 10-
`" Variant Can contain any of the preceding data types
`
`When you name a variable, you should include the first three letters of its type in its
`name, as shown above. The reason for including the type in the name is that you can
`always tell what can be stored in a variable by looking at its name. Late at night on a
`long project, or coming back to maintain code after two months away from it, you will
`appreciate this convention.
`
`ceo-rilE.
`To make a variable available to all procedures and functions in your application,
`it with the keyword Public.
`place it in a global code
`
`u
`Event procedures are not the only procedures you can write in Visual Basic. You can
`also write a procedure as a part of a global code module or at the General level of any
`form or report, and you can call that procedure any time you need to perform the
`action undertaken by the procedure. The Contact Management database uses this type
`
`

`

`862
`
`CHAPTER 25 • INTRODUCING VISUAL BASIC FOR APPLICATIONS
`
`of procedure to handle the button clicks in the switchboard. At the General level in
`the Switchboard form is the procedure HandleButtonC!ick. This procedure receives the
`button number as its argument and takes action based on the button number received.
`One function can therefore service several buttons. The OnClick property of each but(cid:173)
`ton is set equal to the name of this procedure. When a click takes place, the procedure
`is called to perform its task.
`You need to remember two things about event procedures. First, event procedures
`cannot return values for the rest of the program to use. You cannot set a variable equal
`to the value of the event procedure. Second, when a procedure is a part of a form or
`report, it has reference only to that form or report. Place your procedures in global code
`modules if you want to be able to use them anywhere in your database application and
`precede them with the keyword Public, rather than Private.
`
`Visual Bask and Functions
`
`Functions are procedures that return a value for use by other statements in the pro(cid:173)
`gram. The return value is set using the function name as a variable, as shown in the
`following function generated by the Database Wizard for the Contact Management
`database:
`
`Function Isloaded(ByVal strFormName As String) As
`Integer
`1 Returns True if the specified form is open in
`Form view
`1 or Datasheet view.
`
`Canst conObjStateClosed
`Canst conDesignView = 0
`
`0
`
`If SysCmd(acSysCmdGetObjectState, acForm,
`strFormName) <> conObjStateClosed Then
`If Forms(strFormName).CurrentView <>
`conDesignView
`Then
`Isloaded = 1 Set the return value
`using
`1 the function name as a
`1 variable.
`End If
`End If
`
`End Function
`
`

`

`BUILDING A SAMPLE PROCEDURE 1893
`
`Don't worry about the statements that seem like Greek in this function right now.
`Wait until you have some experience with Visual Basic before you try to start interpret(cid:173)
`ing them. The main thing to remember about building a function is to set the return
`value and to use the Function and End Function statements to frame it.
`
`c::J>-.riE
`Both functions and subs can take arguments, variables whose values are made
`available to the function for use. These variables are named in the parentheses fol(cid:173)
`lowing the function or subroutine name. When you name them, you use the As
`keyword to name their type, just as you do when declaring a variable. If you do
`not want the function or procedure to modify the value stored in the variable,
`precede the name of the variable with ByVal.
`
`Building a Sample Procedure
`
`Speaking of getting some experience with Visual Basic, here's your chance. In this sec(cid:173)
`tion, we build a real VBA procedure! While this procedure will be simple, it will give you
`the basics. The next two chapters undertake some more complex actions. (Remember,
`we have three chapters to get you started programming with a language that people
`write whole books about. Be patient, give yourself some time, and study the Help files
`and sample code. You'll be an expert in no time!)
`Having read the preceding sections, you have enough background to understand
`how a procedure is built. We have a surprise for you about how Access builds proce(cid:173)
`dures. In many cases, you don't have a great deal of code to write because a Wizard
`writes it for you. (Convenient, no?) As an example, we are going to add a command
`button that prints a copy of the data entry form we built for the database used by our
`client who runs a nonprofit corporation. You can practice on any database form that
`you want. Visual Basic works the same way in all forms.
`To add the button, take these steps:
`
`1. Open the form in design view.
`2. Make sure the Control Wizards button is "in."
`3. Select the toolbox button that draws the command button.
`4. Drag with your mouse to draw the button on the form. After a brief pause the Com(cid:173)
`mand Button Wizard appears (see Figure 25.4).
`§. Select the class of action you want to take in the Categories list. Since we want to
`print a form, select Form Operations. Select the specific action in the Actions list,
`in this case, Print Current Form. Click on the Next button.
`
`

`

`864
`
`CHAPTER 25 • INTRODUCING VISUAL BASIC FOR APPLICATIONS
`
`l#(ijQ;lfJjll
`The first page of
`the Command
`Button Wizard
`allows you to
`select the type
`of action the
`event procedure
`performs.
`
`"
`
`Command Button Wizard
`
`Whotaction do 9ou want to happen whim.the button is pr~ssed?
`
`Different actiol'lS 01r~ available for eac~ category.
`
`Actions'
`Apply Form Filler
`Close Form
`Edit Form Filler
`Open Form
`Print a Form
`
`Refresh Form Data
`
`6. Select whether you want a picture or text to appear on the button face using the
`option buttons provided. If you choose text, enter the text as you want it to appear.
`If you choose a picture, you can use the browse button to select a graphic. Click on
`the Next button.
`7. Enter a meaningful name for your button in the text box on the last Wizard page.
`Make the name a mnemonic for the button's function. Then click on the Finish
`button.
`
`When the Wizard finishes, it has built the click event procedure for you. You can see
`the procedure by right-clicking on the button and selecting Build Event from the menu
`that appears. The procedure created appears below:
`
`Private Sub Print_Form_Click()
`On Error GoTo Err Print Form Click
`-
`-
`-
`
`DoCmd.PrintOut
`Exit Print Form Click:
`-
`-
`-
`Exit Sub
`
`

`

`LEARNING MORE ABOUT VISUAL BASIC FOR APPLICATIONS
`
`865
`
`Err Print Form Click:
`-
`-
`-
`MsgBox Err.Description
`Resume Exit Print Form Click
`-
`-
`-
`
`End Sub
`
`Congratulations! You've just programmed your first procedure, and using excellent pro(cid:173)
`gramming form we might add. This procedure is very straightforward. The first statement
`turns on error handling (more about that in the next chapter). The second statement uses
`the PrintOut method of the DoCmd object to print the form. (The DoCmd object contains
`all the actions you can invoke using macros.) The next program line is a label, which is a
`string of text that carries out no action but can serve as a jump destination. Labels end in
`colons. The line following the colon causes the procedure to end. The remaining three lines
`are a label and error-handling code. (Again, more about that in the next chapter.)
`The greatest thing about writing procedures for common objects in Access is that
`you have a Wizard for each one. You do not have to be an expert programmer to get lots
`of work done in Visual Basic.
`
`Converting M
`
`You can also convert macros you already have written into Visual Basic code. To con(cid:173)
`vert macros associated with a form or report, open the object in design view and select
`Convert Macros to Visual Basic from the Macros submenu on the Tools menu.
`To convert global macros, click on the Macros tab in the Database dialog box. Then click
`on the macro you want to convert. Open the File menu, select Save As/Export, and click on
`the Save As Visual Basic Module option button in the Save As dialog box. Then click on the
`OK button.
`
`Learning
`
`For more information about how to use the features of Visual Basic, take a look at these
`
`sources:
`• Help on Visual Basic from the Office Assistant. You'll find several topics including
`instructions on how to obtain the Microsoft Office 97/Visual Basic Programmer's Guide.
`• Any of the excellent books available on using Visual Basic for Applications in
`Access, including the Microsoft Access 97 Developer's Handbook from Sybex.
`
`

`

`866
`
`CHAPTER 25 • INTRODUCING VISUAL BASIC FOR APPLICATIONS
`
`• The Visual Basic documentation that comes as a part of the Microsoft Developer
`Network CD-ROM product. All the latest documentation on Visual Basic comes
`on this CD every quarter.
`• The code that the Database Wizard generates whenever it creates a database of a
`specific type. You can learn a lot about how Microsoft programmers use Visual
`Basic by studying these examples.
`" Two magazines, the Visual Basic Programmer's foumal and the VB Tech Journal, both
`of which bring you monthly discussions of how to use Visual Basic to accomplish
`specific tasks.
`• The Access/Visual Basic Advisor, a newsletter from Advisor Publications, or Pin(cid:173)
`nacle Publication's Smart Access and Visual Basic Developer.
`
`Here
`
`Having introduced you to Visual Basic in this chapter, in the next two chapters we show
`you how to do some interesting work with this programming language. Chapter 26
`explains how to create your own error messages, and Chapter 27 explains how to use OLE
`Automation to control other programs.
`
`What's New in the
`Access Zoo?
`You've just taken a look at Visual Basic for
`Applications. The most exciting features
`of this part of the Access environment are
`the ability to
`
`• Write database programs using a full(cid:173)
`featured programming language
`
`• Leverage objects provided by Access
`and other programs in Writing yo1.,1r
`own code
`
`• Attach your code directly to your
`database objects
`
`• Make a suite of applications work in
`concert with your data.base to per~
`form complex tasks
`··
`·
`
`

`

`
`
`
`
`

`

`
`
`
`
`

`

`
`
`
`
`
`
`

`

`Building a custom error message with a macro
`
`Building a custom error message with Visual Basic
`
`872
`
`874
`
`

`

`Creating Custo
`
`ven the best planned application
`occasionally screws up. When that
`happens in Access, an error is gener-
`ated. You have two options for handling these errors: let Access and Visual Basic do it
`for you or respond to the errors yourself. The first option is very convenient. You don't
`have to do anything. However, the result of an error is that your application stops exe(cid:173)
`cuting. The user sees a dialog box and whatever was happening stops. Although using
`the cryptic error descriptions that Access provides by default for error handling is con(cid:173)
`venient for you, it is rather rude for users of your application.
`The second option, handling errors yourself, is less convenient for you, but makes
`your application much more user friendly. When you respond to the errors, you can
`interpret the error for your user and provide a suggestion about what to do. In many
`cases, you do not even have to inform the user that an error has occurred. You write
`code to correct the situation and restart the execution of your code.
`This chapter teaches you how to handle errors yourself, and in the process shows
`you a couple of ways to go beyond the Wizard-written code that you learned how to
`create in the last chapter. To learn how to handle errors, you need to learn about flow
`control in Visual Basic programs. But first, we need to focus on the two ways to create cus(cid:173)
`tom error messages: there's one way for macros and one way for Visual Basic.
`
`

`

`872
`
`CHAPTER 26 • CREATING CUSTOM ERROR MESSAGES
`
`4li.IIRI!t- Ill- <GO
`There is one time when error trapping is absolutely necessary: when you use the
`Access Developer's Toolkit (ADT) to create a "run time" version of your applica(cid:173)
`tion. In this case, any untrapped error causes your application to quit completely.
`
`When you are using macros, you can create a custom error message using the MsgBox
`action. To do so, create a new macro by clicking on the New button in the Macros tab
`of the database window. On the macro sheet, select MsgBox as the action and fill in the
`properties. Figure 26.1 shows a completed error macro.
`When you fill in the arguments, you can create a formatted message in the Message
`box using the @ character. Format your message in the following way:
`
`Data Entry Form@This form does not support double-clicking. @Use the buttons at
`the bottom of the form to scroll to the record you want.
`
`"
`
`iit3Q;ll31
`A macro
`designed to
`notify users that
`double-clicking
`has no effect in
`this context.
`
`.Action A.rQuments
`
`l<lessage
`Beep
`Type
`Title
`
`Data Entry Form@This form does nc
`Ves
`Inforrnation
`Data Entry Form Error
`
`Enter the text to c!ispla·/ in the message
`bo>~ title bar, Few example, 1Customer ID
`Validation.' Press Fl for help on this
`argument
`
`

`

`BUILDING CUSTOM ERROR MESSAGES WITH A MACRO
`
`.873
`
`The first thir