VBA

(1)

A c c e s s 2 0 0 3 :

V B A P r o g r a m m i n g

Instructor’s Edition

NOT FOR PRINTING OR INSTRUCTIONAL USE

(2)

Access 2003: VBA Programming

Series Product Managers:

Series Product Managers: Charles Charles G. G. Blum Blum and and Adam Adam A. A. WilcoxWilcox Developmental Editor:

Developmental Editor: Don Don TremblayTremblay Copyeditor:

Copyeditor: Cathy Cathy AlbanoAlbano Keytester:

Keytester: Bill Bill BatemanBateman Series Designer:

Series Designer: Adam Adam A. A. WilcoxWilcox Cover Designer:

Cover Designer: Abby Abby ScholzScholz

means   graphic, electronic, or mechanical, including photocopying, recording, taping, graphic, electronic, or mechanical, including photocopying, recording, taping, Web distribution, orWeb distribution, or

information storage and retrieval systems

information storage and retrieval systems   without the prior written permission of the publisher.without the prior written permission of the publisher.

For more information

For more information, go to www.courseilt.com., go to www.courseilt.com.

Trademarks Trademarks

ILT

ILT Series Series is a trademark of is a trademark of Axzo PressAxzo Press.. Some of the product names and

Some of the product names and company names used in this book company names used in this book have been used for identification purposes only andhave been used for identification purposes only and may be trademarks or registered trademarks of their respective manufacturers and sellers.

may be trademarks or registered trademarks of their respective manufacturers and sellers.

Disclaimer Disclaimer

We

We reserve the right to revise this publication and make changes from time to reserve the right to revise this publication and make changes from time to time in its contenttime in its content without notice.

without notice. ISBN 1-4239-1354-X ISBN 1-4239-1354-X

Printed in the United States of America Printed in the United States of America 1 2 3 4 5

1 2 3 4 5 GLOBGLOB 06 05 04 03 06 05 04 03

NOT FOR PRINTING OR INSTRUCTIONAL USE

(3)

Access 2003: VBA Programming

Series Product Managers:

Series Product Managers: Charles Charles G. G. Blum Blum and and Adam Adam A. A. WilcoxWilcox Developmental Editor:

Developmental Editor: Don Don TremblayTremblay Copyeditor:

Copyeditor: Cathy Cathy AlbanoAlbano Keytester:

Keytester: Bill Bill BatemanBateman Series Designer:

Series Designer: Adam Adam A. A. WilcoxWilcox Cover Designer:

Cover Designer: Abby Abby ScholzScholz

means   graphic, electronic, or mechanical, including photocopying, recording, taping, graphic, electronic, or mechanical, including photocopying, recording, taping, Web distribution, orWeb distribution, or

information storage and retrieval systems

information storage and retrieval systems   without the prior written permission of the publisher.without the prior written permission of the publisher.

For more information

For more information, go to www.courseilt.com., go to www.courseilt.com.

Trademarks Trademarks

ILT

ILT Series Series is a trademark of is a trademark of Axzo PressAxzo Press.. Some of the product names and

Some of the product names and company names used in this book company names used in this book have been used for identification purposes only andhave been used for identification purposes only and may be trademarks or registered trademarks of their respective manufacturers and sellers.

may be trademarks or registered trademarks of their respective manufacturers and sellers.

Disclaimer Disclaimer

We

We reserve the right to revise this publication and make changes from time to reserve the right to revise this publication and make changes from time to time in its contenttime in its content without notice.

without notice. ISBN 1-4239-1354-X ISBN 1-4239-1354-X

Printed in the United States of America Printed in the United States of America 1 2 3 4 5

1 2 3 4 5 GLOBGLOB 06 05 04 03 06 05 04 03

NOT FOR PRINTING OR INSTRUCTIONAL USE

(4)

C o n t e n t s

Introduction

iii

Introduction

iii

Topic A:

Topic A: About About the the manual...manual... ... ... iviv Topic B:

Topic B: Setting Setting student student expectationsexpectations ... ... ... ixix Topic C:

Topic C: Classroom setup...xiiClassroom setup...xii Topic D:

Topic D: Support...xvSupport...xv

Visual

Visual Basic

Basic Editor

Editor

1-1

Topic A:

Topic A: ObjectsObjects ... ... ... ... ... ... 1-21-2 Topic B:

Topic B: Object Object BrowserBrowser ... ... ... ... 1-141-14 Topic C:

Topic C: CollectionsCollections ... ... ... 1-201-20 Unit summary:

Unit summary: Visual Visual Basic Basic Editor...Editor... ... ... ... 1-231-23

Programming

Programming basics

basics

2-1

Topic A:

Topic A: Working Working with with datadata ... ... ... ... ... 2-22-2 Topic B:

Topic B: ScopeScope ... 2-122-12 Unit summary:

Unit summary: Programming Programming basicsbasics ... ... ... 2-282-28

Control

Control structures

structures

3-1

Topic A:

Topic A: Decision Decision structures...structures... ... ... ... 3-23-2 Topic B:

Topic B: Loop Loop structuresstructures ... ... ... 3-113-11 Unit summary:

Unit summary: Control Control structuresstructures ... ... ... 3-193-19

ActiveX

ActiveX Data

Data Objects

Objects

4-1

Topic A:

Topic A: Getting Getting started started with with ActiveX ActiveX Data OData Objectsbjects ... ... 4-24-2 Topic B:

Topic B: Working Working with with ActiveX ActiveX Data Data Objects...Objects... ... .. 4-64-6 Unit summary:

Unit summary: ActiveX ActiveX Data Data Objects...Objects... ... ... ... 4-204-20

VBA

VBA debugging

debugging tools

tools

5-1

Topic A:

Topic A: Errors Errors in in code...code... ... ... ... 5-25-2 Topic B:

Topic B: DebuDebugging gging techntechniquesiques ... 5-45-4 Topic C:

Topic C: Handling Handling run-time run-time errorserrors ... ... ... 5-205-20 Unit summary:

Unit summary: VBA VBA debugging debugging tools...tools... ... .. 5-245-24

Office

Office integration

integration

A-1

Topic A:

Topic A: Automating Office 2003...A-2Automating Office 2003...A-2 Topic B:

Topic B: Working with Word from Access...A-3Working with Word from Access...A-3 Topic C:

Topic C: Working with Excel from Access...A-5Working with Excel from Access...A-5

Course

Course summary

summary

S-1

Topic A:

Topic A: Course Course summarysummary ... ... S-2S-2 Topic B:

Topic B: Continued Continued learning learning after after classclass ... ... ... S-4S-4

Quick

Quick reference

reference

Q-1

Glossary

G-1

Glossary

G-1

Index

I-1

Index

I-1

NOT FOR PRINTING OR INSTRUCTIONAL USE

(5)

ii

Access 2003: Access 2003: VBA ProgrammingVBA Programming

NOT FOR PRINTING OR INSTRUCTIONAL USE

(6)

iii

Introduction

After reading this introduction, you will know

how to:

A

Use ILT Series training manuals in

general.

B

Use prerequisites, a target student

description, course objectives, and a skills

inventory to properly set students’

expectations for the course.

C

Set up a classroom to teach this course.

D

Get support for setting up and teaching this

course.

(7)

iv

Access 2003: VBA Programming

Topic A: About the manual

ILT

Series

_philosophy

Our goal is to make you, the instructor, as successful as possible. To that end, our training manuals facilitate students’ learning by providing structured interaction with the software itself. While we provide text to help you explain difficult concepts, the hands-on activities are the focus of our courses. Leading the students through these activities will teach the skills and concepts effectively.

We believe strongly in the instructor-led classroom. For many students, having a

thinking, feeling instructor in front of them will always be the most comfortable way to learn. Because the students’ focus should be on you, our manuals are designed and written to facilitate your interaction with the students, and not to call attention to manuals themselves.

We believe in the basic approach of setting expectations, then teaching, and providing summary and review afterwards. For this reason, lessons begin with objectives and end with summaries. We also provide overall course objectives and a course summary to provide both an introduction to and closure on the entire course.

Our goal is your success. We encourage your feedback in helping us to continually improve our manuals to meet your needs.

Manual components

This manual contains these major components:

• Table of contents • Introduction • Units • Appendix • Course summary • Quick reference • Glossary • Index

Each element is described below.

Table of contents

The table of contents acts as a learning roadmap for you and the students.

Introduction

The introduction contains information about our training philosophy and our manual components, features, and conventions. It contains target student, prerequisite, objective, and setup information for the specific course. Finally, the introduction contains support information.

(8)

Introduction

v

Units

Units are the largest structural component of the actual course content. A unit begins with a title page that lists objectives for each major subdivision, or topic, within the unit. Within each topic, conceptual and explanatory information alternates with hands-on activities. Units conclude with a summary comprising one paragraph for each topic, and an independent practice activity that gives students an opportunity to practice the skills they’ve learned.

The conceptual information takes the form of text paragraphs, exhibits, lists, and tables. The activities are structured in two columns, one telling students what to do, the other providing explanations, descriptions, and graphics. Throughout a unit, instructor notes

are found in the left margin.

Appendix

An appendix is similar to a unit in that it contains objectives and conceptual

explanations. However, an appendix does not include hands-on activities, a summary, or an independent practice activity.

Course summary

This section provides a text summary of the entire course. It is useful for providing closure at the end of the course. The course summary also indicates the next course in this series, if there is one, and lists additional resources students might find useful as they continue to learn about the software.

Quick reference

The quick reference is an at-a-glance job aid summarizing some of the more common features of the software.

Glossary

The glossary provides definitions for all of the key terms used in this course.

Index

The index at the end of this manual makes it easy for you and your students to find information about a particular software component, feature, or concept.

(9)

vi

Manual conventions

We’ve tried to keep the number of elements and the types of formatting to a minimum in the manuals. We think this aids in clarity and makes the manuals more classically elegant looking. But there are some conventions and icons you should know about.

Instructor note/icon

_Convention

_Description

Italic text In conceptual text, indicates a new term or feature.

Bold text In unit summaries, indicates a key term or concept. In an independent practice activity, indicates an explicit item that you select, choose, or type.

Code font Indicates code or syntax. Longer strings of ►

code will look ►

like this.

In the hands-on activities, any code that’s too long to fit on a single line is divided into segments by one or more continuation characters (►). This code should be entered as a continuous string of text.

Instructor notes. In the left margin, provide tips, hints, and warnings for the instructor.

Select bold item In the left column of hands-on activities, bold sans-serif text indicates an explicit item that you select, choose, or type.

Keycaps like

e

Indicate a key on the keyboard you must press.

Warning icon. Warnings prepare instructors for potential classroom

management problems.

Tip icon. Tips give extra information the instructor can share with

students.

Setup icon. Setup notes provide a realistic business context for

instructors to share with students, or indicate additional setup steps required for the current activity.

Projector icon. Projector notes indicate that there is a PowerPoint slide

for the adjacent content.

(10)

Introduction

vii

Hands-on activities

The hands-on activities are the most important parts of our manuals. They are divided into two primary columns. The “Here’s how” column gives short directions to the students. The “Here’s why” column provides explanations, graphics, and clarifications. To the left, instructor notes provide tips, warnings, setups, and other information for the instructor only. Here’s a sample:

Do it!

A-1: Creating a commission formula

Here’s how

Here’s why

1 Open Sales This is an oversimplified sales compensation worksheet. It shows sales totals, commissions, and incentives for five sales reps.

Take the time to make sure your students understand this

worksheet. We’ll be here a while.

2 Observe the contents of cell F4

The commission rate formulas use the name “C_Rate” instead of a value for the commission rate.

For these activities, we have provided a collection of data files designed to help students learn each skill in a real-world business context. As students work through the activities, they will modify and update these files. Of course, they might make a mistake and, therefore, want to re-key the activity starting from scratch. To make it easy to start over, students will rename each data file at the end of the first activity in which the file is modified. Our convention for renaming files is to add the word “My” to the beginning of the file name. In the above activity, for example, students are using a file called “Sales” for the first time. At the end of this activity, they would save the file as “My sales,” thus leaving the “Sales” file unchanged. If students make mistakes, they can start over using the original “Sales” file.

In some activities, however, it may not be practical to rename the data file. Such exceptions are indicated with an instructor note. If students want to retry one of these activities, you will need to provide a fresh copy of the original data file.

(11)

viii

PowerPoint presentations

Each unit in this course has an accompanying PowerPoint presentation. These slide shows are designed to support your classroom instruction while providing students with a visual focus. Each one begins with a list of unit objectives and ends with a unit

summary slide. We strongly recommend that you run these presentations from the instructor’s station as you teach this course. A copy of PowerPoint Viewer is included, so it is not necessary to have PowerPoint installed on your computer.

The ILT Series_{PowerPoint add-in}

The CD also contains a PowerPoint add-in that enables you to do two things:

• Create slide notes for the class

• Display a control panel for the Flash movies embedded in the presentations

To load the PowerPoint add-in:

1 Copy the Course_ILT.ppa file to a convenient location on your hard drive. 2 Start PowerPoint.

3 Choose Tools, Macro, Security to open the Security dialog box. On the Security Level tab, select Medium (if necessary), and then click OK.

4 Choose Tools, Add-Ins to open the Add-Ins dialog box. Then, click Add New. 5 Browse to and select the Course_ILT.ppa file, and then click OK. A message

box will appear, warning you that macros can contain viruses.

6 Click Enable Macros. The Course_ILT add-in should now appear in the Available Add-Ins list (in the Add-Ins dialog box). The “x” in front of Course_ILT indicates that the add-in is loaded.

7 Click Close to close the Add-Ins dialog box.

After you complete this procedure, a new toolbar will be available at the top of the PowerPoint window. This toolbar contains a single button labeled “Create SlideNotes.” Click this button to generate slide notes files in both text (.txt) and Excel (.xls) format. By default, these files will be saved to the folder that contains the presentation. If the PowerPoint file is on a CD-ROM or in some other location to which the SlideNotes files cannot be saved, you will be prompted to save the presentation to your hard drive and try again.

When you run a presentation and come to a slide that contains a Flash movie, you will see a small control panel in the lower-left corner of the screen. You can use this panel to start, stop, and rewind the movie, or to play it again.

(12)

Introduction

ix

Topic B: Setting student expectations

Properly setting students’ expectations is essential to your success. This topic will help you do that by providing:

• Prerequisites for this course

• A description of the target student at whom the course is aimed • A list of the objectives for the course

• A skills assessment for the course

Course prerequisites

Students taking this course should be familiar with personal computers and the use of a keyboard and a mouse. Furthermore, this course assumes that students have completed the following courses or have equivalent experience:

• Windows 2000: Basic or Windows XP: Basic • Access 2003: Application Development

Target student

The target student for this course should be familiar with Access 2003 and Visual Basic for Applications. Students should be able to use various VBA programming elements. Students will get the most out of this course, if their goal is to become proficient in working with objects and collections, and implementing ActiveX Data Objects.

Course objectives

You should share these overall course objectives with your students at the beginning of the day. This will give the students an idea about what to expect, and will also help you identify students who might be misplaced. Students are considered misplaced when they lack the prerequisite knowledge or when they already know most of the subject matter to be covered.

After completing this course, students will know how to:

• Work with objects, use Object Browser, identify collection objects, and use the

Controls collection.

• Manipulate data by declaring variables, combine data by using expressions, get

user input, create Sub and Function procedures, and call one procedure from another.

• Use decision structures and use loop structures.

• Identify ActiveX Data Objects (ADO) architecture and collections, connect to a

database, work with a Recordset, and create a parameter query.

• Identify compile-time, run-time, and logical errors; use the debugging tools; and

write error-handling code.

(13)

x

Skills inventory

Use the following form to gauge students’ skill level entering the class (students have copies in the introductions of their student manuals). For each skill listed, have students rate their familiarity from 1 to 5, with 5 being the most familiar. Emphasize that this is not a test. Rather, it is intended to provide students with an idea of where they’re starting from at the beginning of class. If a student is wholly unfamiliar with all the skills, he or she might not be ready for the class. A student who seems to understand all of the skills, on the other hand, might need to move on to the next course in the series.

Skill

1 2 3 4 5

Work with objects, their properties, methods, and events

Use the Object Browser

Identify collection objects

Use the Controls collection

Examine data types and variables

Use operators and interaction functions

Observe modules and procedures

Examine procedure-, module-, and public-level scopes

Create and call a Sub procedure

Create and call a Function procedure

Use If…Then, If…Then…Else, and If…Then…ElseIf statements

Use the Select Case statement

Use the For…Next, For Each…Next, and Do While…Loop loops

Discuss ADO and ADO collections

Connect to a database

Create a Recordset object

Navigate a Recordset

Add records to a Recordset

Modify a record

Cancel changes to a record

(14)

Introduction

xi

Skill

1 2 3 4 5

Delete a record

Create a parameter query

Discuss types of errors

Discuss debugging tools

Set a breakpoint

Add and delete a watch expression

Use Step Into

Work with the Immediate window

Use the Locals window

Write error-handling code

(15)

xii

Topic C: Classroom setup

All our courses assume that each student has a personal computer to use during the class. Our hands-on approach to learning requires they do. This topic gives information on how to set up the classroom to teach this course. It includes minimum requirements for the students’ personal computers, setup information for the first time you teach the class, and setup information for each time that you teach after the first time you set up the classroom.

Student computer requirements

Each student’s personal computer should have:

• A keyboard and a mouse

• A Pentium 233 MHz (or higher) processor • At least 128 MB RAM

• 400 MB of available hard drive space • A CD-ROM drive

• An SVGA or higher resolution monitor set to 800×600 resolution

• A printer driver (An actual printer is not required, but the printing activities will

not work as described unless a printer driver is installed.)

• Internet access, if you want to download the Student Data files from

www.courseilt.com, and for downloading the latest updates from www.windowsupdate.com

First-time setup instructions

The first time you teach this course, you will need to perform the following steps to set up each student computer.

1 Install Microsoft Windows 2000 Professional on an NTFS partition according to the software manufacturer’s instructions. Then, install the latest critical updates and service packs from www.windowsupdate.com. (You can also use Windows XP Professional, but the screen shots in this course were taken using Windows 2000, so students’ screens might look somewhat different.)

2 Adjust the computer’s display properties as follows:

a Open the Control Panel and double-click Display to open the Display Properties dialog box.

b On the Settings tab, change the Colors setting to True Color (24 bit) and the Screen area to 800 by 600 pixels.

c On the Appearance tab, set the Scheme to Windows Classic.

d Click OK. If you are prompted to accept the new settings, click OK and click Yes. Then, if necessary, close the Display Properties dialog box.

(16)

Introduction

xiii

3 Adjust the computer’s Internet settings as follows:

a On the desktop, right-click the Internet Explorer icon and choose Properties to open the Internet Properties dialog box.

b On the Connections tab, click Setup to start the Internet Connection Wizard. c Click Cancel. A message box will appear.

d Check “Do not show the Internet Connection wizard in the future,” and then click Yes.

e Re-open the Internet Properties dialog box.

f On the General tab, click Use Blank, click Apply, and click OK. 4 Install Microsoft Office 2003 according to the software manufacturer’s

instructions as follows:

a When prompted for the CD key, enter the 25-character code included with your software.

b Select the Custom Install and click Next.

c Clear all check boxes except Microsoft Access.

d Check Choose advanced customization of applications, and then click Next. e Next to Microsoft Office Access, click the drop-down arrow and choose Run

all from My Computer.

f Next to Office Shared Features, click the drop-down arrow and choose Run all from My Computer.

g Click Next. Then, click Install to start the installation.

h When the installation has completed successfully, click Finish. 5 Start Access. Then, turn off the Office Assistant as follows:

a If the Office Assistant is not displayed, choose Help, Show the Office Assistant.

b Right-click the Office Assistant and choose Options to open the Office Assistant dialog box.

c Clear Use the Office Assistant and click OK.

6 To prevent macro warnings from appearing when students open tables in Access:

a Choose Tools, Macro, Security. b Select the Low security setting.

c Click OK.

7 Configure Access to always show full menus.

a Choose View, Toolbars, Customize to open the Customize dialog box. b On the Options tab, check Always show full menus.

c Click Close.

8 Disable the Language bar:

a In the Control Panel, double-click Text Services to open the Text Services dialog box.

b Under Preferences, click Language Bar to open the Language Bar Settings dialog box.

c Clear Show the Language bar on the desktop, and then click OK. d Click OK to close the Text Services dialog box.

e Close the Control Panel. 9 Close Access.

(17)

xiv

10 If necessary, install a printer driver. If a printer was connected to the computer during the installation of Windows, there will be a driver installed for that

printer. If not, you should install a standard PostScript printer driver, such as one for the HP LaserJet 5.

11 If necessary, download the Student Data files for the course. You can download the data directly to student machines, to a central location on your own network, or to a disk.

a Connect to www.courseilt.com/instructor_tools.html.

b Click the link for Microsoft Access 2003 to display a page of course listings, and then click the link for Access 2003: VBA Programming, Second Edition. c Click the link for downloading the Data Files for Students, and follow the

instructions that appear on your screen.

Setup instructions for every class

Every time you teach this course, you will need to perform the following steps to set up each student computer.

1 Start Access. Turn off the Office Assistant as follows:

a If the Office Assistant is not displayed, choose Help, Show the Office Assistant.

b Right-click the Office Assistant and choose Options to open the Office Assistant dialog box.

c Clear Use the Office Assistant and click OK.

2 If necessary, reset any defaults that have been changed in previous classes. 3 Close Access.

4 Delete the contents of the Student Data folder, if necessary. (If this is the first time you are teaching the course, create a folder named Student Data at the root of the hard drive.)

5 Copy the data files to the Student Data folder. (See the download instructions in the preceding section.)

(18)

Introduction

_x

_v

Topic D: Support

Your success is our primary concern. If you need help setting up this class or teaching a particular unit, topic, or activity, please don’t hesitate to get in touch with us.

Contacting us

Please contact us through our Web site, www.courseilt.com. You will need to provide the name of the course, and be as specific as possible about the kind of help you need.

Instructor’s tools

The ILT Series Web site provides several instructor’s tools for each course, including course outlines and answers to frequently asked questions. To download these files, go to www.courseilt.com/instructor_tools.html.

(19)

xvi

(20)

1–1

U n i t 1

Visual Basic Editor

Unit time: 65 minutes

Complete this unit, and you’ll know how to:

A

Work with objects, their properties,

methods, and events.

B

Use Object Browser to get information

about objects.

C

Identify collection objects and use the

Controls collection.

(21)

1

–

2 Topic A: Objects

Explanation Visual Basic for Applications, or VBA, is a programming language that comes as a part of the Microsoft Office suite and is shared among Office applications.

You can decide to go further into OOP as time allows.

VBA is based on Object Oriented Programming (OOP). OOP is a programming concept in which all the elements of a program are considered as objects. Objects are a way of organizing and relating variables, data, and procedures. The objects can be visible things like forms, command buttons, and text boxes, or more conceptual, like reports, queries, or even whole applications. The details and subtleties of OOP are outside the scope of this unit, but a basic understanding is sufficient to start programming.

An object is an entity that has certain characteristics and behavior. These characteristics are known as the properties of the object. These properties can be set at either design time or run time of a project. Design time is when you design forms, place controls in the form, set properties, and write code for the project. Run time is when the project is executing. You can write VBA code to manipulate forms, reports, command buttons, and other controls. Visual Basic Editor (VBE) is the environment in which you write, edit, and test VBA code. The VBE helps you in manipulating object properties and methods. For example, you can change the text in a text box and also hide the text box by writing relevant code in VBE. You can access VBE through any Microsoft Office

application.

Each Office application has its own set of objects or object model. For example, Word contains document and paragraph objects, and Access contains table and form objects. An object’s method is a behavior or action that is performed by the object and can be used to accomplish specific tasks. For example, Rename is a method that changes the name of an object. An application consists of two types of objects, individual objects and collection objects. A collection object is an arranged group of items that can be treated as a unit. For example, the Forms collection is a collection of all currently open forms and each form in this collection is an individual object.

An event is something that happens to an object that it can respond to. For instance, some events are when a form opens, a button is clicked, or a text box loses focus. You can write code that will run when an event occurs.

Access objects

The tables, queries, and forms that are part of the Access application are also individual objects by themselves. In Access and other Office applications, one object can contain other objects. For instance, a form object contains controls such as buttons and check boxes, and a table object contains objects such as fields and indexes. In VBA, the

applications themselves are also objects.

Generic object types

Generic object types are those that you can use when you are not sure what type of object you want to work with. Both Access and VBA provide generic object types. For example, Object is a generic object type that VBA provides and Access provides Control as its generic object type. You can assign any type of object to this generic object type. You can assign any type of control to it.

(22)

Visual Basic Editor

1

–

3

Do it!

A-1: Discussing objects

Questions and answers

1 What is an object?

An object is an entity that has specific characteristics and behavior. For example, in Access, tables, forms, and controls are objects.

2 Is VBA shared among Office applications? If yes, why?

Yes, VBA is shared by all Office applications because it is part of the Microsoft Office suite.

3 Is Access an object in VBA? If yes, why?

Yes, Access is an object because applications are also objects in VBA and can be programmed.

4 When do you use generic object types?

You use generic object types when you are not sure what type of object or control you need to use.

5 What generic object types are available in VBA and Access?

VBA provides the Object type and Access provides the Control type.

If time allows, have students think of other simple objects to model, and list the relevant properties, methods, and

events.

6 Consider modeling a real-world object – a toaster. What are its properties, methods, and events?

Answers will vary. Properties: plugged in (a true/false property), color, size, number of slots, toasting time. Methods: toast bread, change toasting time, pop toast up. Events: lever pushed down, toasting time dial changed, lever manually popped up.

(23)

1

–

4 Object properties

Explanation You can modify properties and methods. You can also attach code to the events of an object to change its appearance, behavior, and the action it performs. You can also use VBA objects to create procedures that perform more complex tasks than simply

executing a set of recorded actions. A procedure is a block of VBA program code that performs a specific task.

The Properties window

The Properties window displays the name of the object, its properties, and the current value of the properties, as shown in Exhibit 1-1. The two tabs in this window are Alphabetic and Categorized tabs. The Alphabetic tab displays the properties of the selected object alphabetically, whereas the Categorized tab displays the properties for the selected object by category, such as Data, Event, Format, and Other. For example, properties, such as Caption, BorderStyle, and AutoCenter are in the Format category.

Object box

Property names Property values

Exhibit 1-1: The Properties window

(24)

Visual Basic Editor

1

–

5

Do it!

A-2: Examining the Properties window

Here’s how

Here’s why

1 Start Access Choose Start, Programs, Microsoft Office, Microsoft Office Access 2003.

2 Open Objects From the current unit folder.

Help students navigate to the current unit folder.

3 From the Objects bar, select

Forms

If necessary.

4 SelectfrmOrder (If necessary.) You’ll examine the properties of this form.

5 Click (The Code button is on the Database toolbar.)

To open the Visual Basic Editor window. The Toolbox also appears.

6 Click the Close button on the upper right corner of the Toolbox

(If necessary.) To close the Toolbox. The frmOrder object appears in Design view in Access.

7 Click the Code button To switch back to the VBE window.

Tell students that the Properties window does not always open by itself, and they may have to select it from the Standard toolbar or choose from the View menu.

If students don’t need to close the toolbox, have them switch back to

Access manually. _{Maximize the Code window of the}

frmOrder form

8 In the Properties window, observe the Name property

This is the name assigned to the form object.

Tell students that they

need to scroll down. 9 In the Properties window, observe_{the WindowHeight and}

WindowWidth properties of the form

WindowHeight and WindowWidth show the height and width of the form window.

Tell students to scroll to

view the tasks. 10 In the Properties window, click_the_Categorized_tab

To view the properties grouped according to the tasks they perform.

11 Observe the Properties window The properties are categorized based on the tasks such as Data, Event, Format, and Other.

12 In the Properties window, click theAlphabetic tab

To view the properties in alphabetical order.

(25)

1

–

6 Changing properties

Explanation While working with an object, you might need to change some of its properties. For example, you might want to change the caption of a form to reflect the data it contains. You change a property of an Access object by selecting the object and then editing the property value in the Properties window. The object immediately reflects the change.

Some properties have predefined values from which you can select one. These values appear in a list in the value column of the Properties window. You can select the value you want to assign to the property from this list. For example, you can select a value for the BorderStyle property of a form, such as 0-None or 2-sizable.

Do it!

A-3: Modifying the object properties

Here’s how

Here’s why

might need to scroll up. 1 In the Properties window, observe_{the Caption property}

The caption frmOrder appears as the property value.

2 Click (The View Microsoft Office Access button is on the Standard toolbar of the VBE window.) To switch to the Microsoft Access window.

Students can also switch by clicking the relevant buttons on the task bar.

ChooseView,Form View (To view the form in Form view.) The caption of the form reads frmOrder.

3 ChooseView,Design View To return to the Design view of the form.

4 Click To open the VBE window.

5 In the Properties window, double-click Caption

You’ll change the caption of the form.

6 Edit the box to read

Order Form

To change the caption of the form, frmOrder, to Order Form.

Press

e

7 Click (The View Microsoft Office Access button is on the Standard toolbar of the VBE window.) To switch to the Microsoft Access window.

8 ChooseView,Form View To view the form in Form view. The caption of the form reads Order Form.

9 Update and close the form To return to the Database window.

(26)

Visual Basic Editor

1

–

7 Using the Code window

Explanation You can control object properties by writing VBA code in the Code window. Each object that appears in the Project Explorer window has a separate Code window associated with it, like the one shown in Exhibit 1-2. To open the Code window, you double-click a form or module in the Project Explorer window. A module is made up of statements, declarations, and procedures that are stored as a single unit. A module contains one or more related procedures. The two types of modules in Access are Standard modules and Class modules. Class modules contain the definition for a new object. The procedures defined in a Class module will become the properties and method for the object. Standard modules contain procedures that are available to other procedures throughout your project. You can add a new module in a project by choosing

Insert, Module. The Code window that includes the code for a specific form or its control is also a Standard module by itself. It is known as Form module.

The list box in the upper left corner of the Code window that lists the form and other objects in the form is called an Object list . The list box in the upper right corner of the Code window that lists all the events for the object displayed in the Object list is called Procedure list . When you select an event from the Procedure list, the procedure

associated with that event appears in the Code window. If you select an object from the Object list, the definition of the object’s default procedure appears in the Code window. In such cases, you don’t need to select the event from the Procedure list. For example, when you select Form from the Object list, the Load event procedure for the form appears in the Code window by default.

Procedure list Object list

Exhibit 1-2: A sample Code window

(27)

1

–

8

Following is the general syntax for setting object properties through code:

object.property = value

In this syntax, objectrepresents the name of the object, property is the property

name, and valueis the value you want to assign to the property. The object name and the property name are separated by a period (.). There are no spaces before and after the period.

For example, to change the caption of the form named frmRetailer, you would enter the code:

Form_frmRetailer.Caption ="Retailer Form"

In this example, the Caption property of the form frmRetailer is set to Retailer Form. When you type a period after an object name, the Auto List option of VBA displays a list of object members, such as data type or property from which you can choose. This helps you to avoid incorrectly typing a name that is not a method or property of the specified object.

Do it!

A-4: Writing code to change object properties

Here’s how

Here’s why

1 SelectfrmRetailer You’ll change the caption of this form by using code.

2 Click To open the Code window.

Tell students to maximize the Code window if necessary.

3 Click as shown

The Object list appears, providing the objects associated with the form.

4 From the Object list, select Form

(You’ll write a code to change the caption of the form.) These lines of code appear automatically. In the first line, Form_Load() is the name of the event procedure. The Load event of a form occurs when the form is opened and its controls are displayed.

5 Type the following code:

Form_frmRetailer.

An Auto List appears, providing the objects and data types from which you can choose.

(28)

Visual Basic Editor

1

–

9

6 From the list, select Caption

Tell students that they might need to scroll down the list.

Press

t

The word Caption appears automatically in the code line.

Type the following code:

="Retailer Form"

To change the form caption to Retailer Form.

7 Switch to Form view (Click the View Microsoft Office Access button and choose View, Form View.) Notice that the caption of the form reads Retailer Form.

Update the database Click the Save button on the Form View toolbar.

8 Close the form Click the close button on the form.

(29)

1

–

10 Using methods

Explanation The actions of an object are defined by the methods attached to it. For example, Quit is a method of the Application object. You can use this method to quit Microsoft Access. The Quit method has a parameter, which can have one of the following values:

• acQuitPrompt: Displays a dialog box that prompts you to save the changes and

then quits Microsoft Access with or without saving according to your answer. You can also close the dialog box by clicking the Cancel button.

• acQuitSaveAll: Saves all objects and then quits Microsoft Access. • acQuitSaveNone: Quits Microsoft Access without saving the changes

(30)

Visual Basic Editor

1

–

11

Do it!

A-5: Using a method

Here’s how

Here’s why

1 SelectfrmOrder You’ll apply a method to an object in this form.

Open the Code window

2 From the Object list, select

cmdClose

You’ll apply the Quit method to the Close button.

Application.

An Auto List appears, providing object classes and data types from which you can choose.

Quit acQuitPrompt Quit method helps you quit the application.

4 Close the Code window Click the Close Window button on the upper right corner of the Code window.

5 Switch to Form view Click the View Microsoft Access button and choose View, Form View.

6 Edit the Quantity box to read 200 To provide a new quantity.

7 Click Close A message box appears, prompting you to save changes.

8 Click Yes To save the database and close Access.

(31)

1

–

12 Attaching code with events

Explanation You might want a procedure to run in response to a specific user action, such as clicking a button. You can do this by associating the code with an event of an object. Events include user actions such as mouse clicks, double-clicks, and opening or closing of forms.

When you associate code with an event, the code is executed when the event occurs or when the event is triggered by another action. You use event procedures to associate code with an event. The events for the object selected in the Object list are listed in the Procedure list. For example, you can write code for a form’s Activate event to display a message informing that you cannot change the data in the form when a user activates it. The Activate event of a form occurs when the form becomes the active window.

To program an event, double-click the object to display the Code window. Then, select the object, select an event from the Procedure list, and enter the code you want.

(32)

Visual Basic Editor

1

–

13

Do it!

A-6: Associating code with an event

Here’s how

Here’s why

1 Start Access

2 Open Objects

3 SelectfrmRetailer

Open the Code window

4 From the Object list, select Form

5 Click as shown

The events for the Form object are listed in the Procedure list.

need to scroll up. From the Procedure list, select_Activate

You’ll attach code to the Activate event of this Form object.

MsgBox

Press

q

The Auto Quick Info feature appears, showing the complete syntax.

("You cannot edit data in this form.")

This code will display a message box with the specified text.

7 Click (The Save Objects button is on the Standard

toolbar.) To update the database.

8 Switch to Form view A message box appears showing the text you specified in the MsgBox function.

ClickOK To close the message box.

9 Close the form Click the Close button.

(33)

1

–

14 Topic B: Object Browser

Explanation You might need to see the in-built properties and methods of an object when creating procedures in a module. The in-built properties and methods of an object are the

properties and methods that are provided by the object itself. For example, you can use the in-built methods, such as Load, Hide, and Show to manage forms in VBA. You can use the Object Browser to view the properties and methods of an object. You can also search for a specific method or an event of an object in the Object Browser. Object libraries provide the information about available objects in VBA to the Object B rowser.

Object libraries

An object library is a file that contains all information about the object model of an application. Each application, such as Word, Excel, or Access, has its own object library. The relevant object library is copied to the hard disk when you install the application. You can set a reference to an application’s object library by selecting the relevant object library from the References dialog box. You can choose Tools,

References from VBE to open the References dialog box. After setting a reference to an application’s object library, you can use the objects of that application in your code. For example, if you need to print the contents of a table in a specified format by using Word because printing in Access does not provide formats, you can select Microsoft Word

11.0 Object Library from the References dialog box. Then, you can use all Word objects in your code.

The Object Browser window

Object Browser is a window that displays the classes, properties, methods, events, and constants in the available object libraries. A class is the basic pattern used when an object is created. Classes, such as App, Collection, or Control, define the methods, properties, and events for an object. An object is created at runtime from a class

contained in the object libraries. You can use the Object Browser window to examine all the objects that are available in VBA and in Access. You can also display help information on any object by using the Object Browser window.

To display the Object Browser, choose View, Object Browser in VBE, or click the Object Browser button on the Visual Basic toolbar. Pressing F2 in the VBE can also open Object Browser.

(34)

Visual Basic Editor

1

–

15

Project/Library list

Methods/Properties list Objects/Modules list

Details pane

Exhibit 1-3: The Object Browser window

The Object Browser window, as shown in Exhibit 1-3, uses various object libraries and displays the following information:

• Project/Library list. This displays the available object libraries, from which

you can select one. A library is a file that contains all information about various classes, objects, procedures, and functions. By default, the Project/Library list contains <All libraries>.

• Classes list. This displays the various classes that are available in the object

library selected in the Project/Library list. The properties, events and methods of an object are defined in class.

• Members Of <classname> list. This displays the methods and properties that

belong to the object that is selected in the Classes list.

• Details pane. This displays some explanation about the selected project, library,

class, methods, or properties. For example, if you select a method from the Objects/Modules list, the Details pane displays the method name, its syntax, arguments (if available), and the name of the library to which it belongs.

(35)

1

–

16

Do it!

B-1: Examining the Object Browser window

Here’s how

Here’s why

1 SelectfrmProduct

Open the Code window Click the Code button on the Database toolbar.

Tell students that they can also press F2 to open the Object Browser window.

2 ChooseView,

Object Browser

(To open the Object Browser window. Maximize the window, if necessary.) You’ll view the various objects and classes of the Access library and their methods and events by using Object Browser.

3 Observe the Project/Library list By default, All Libraries appear.

4 From the Project/Library list, selectAccess

You’ll view the information about the Access library.

5 Observe the Objects/Modules list It shows all the objects and modules that are available in the Access library.

6 From the Objects/Modules list, selectForm

Scroll down the list.

7 Observe the Methods/Properties list

(Scroll through the list.) It shows all the methods and properties that are available for the Form object. A green brick indicates methods and a hand icon pointing to an index indicates properties.

8 From the Methods/Properties list, selectWindowHeight

(In the lower part of the list.) To view the details of this property.

9 Observe the Details pane It shows the details of the WindowHeight property.

(36)

Visual Basic Editor

1

–

17 Object Browser’s search feature

Explanation You use the search feature of the Object Browser to view the methods, properties, and events of an object. To do so, enter the name of the project, database, or object whose information you want to view, then click the Search button. The Object Browser searches for the text you entered and displays the results in the Search Results section, as shown in Exhibit 1-4.

Search Text box Search button

Search Results section

Exhibit 1-4: The Object Browser with the Search Results section

(37)

1

–

18

Do it!

B-2: Searching in the Object Browser

Here’s how

Here’s why

1 From the Project/Library list, selectAll Libraries

2 In the Search Text box, enter

Form_frmRetailer

You’ll use the Object Browser to view the methods, properties, and events of all objects in Form_frmRetailer.

3 Click (The Search button is in the Object Browser window.) To start search. After the search is complete, the Search Results section is added to the Object Browser window.

4 Observe the Search Results section

This displays the VBAWindow1 as library and Form_frmRetailer as class.

. _{5 Observe the Classes list} The classes in VBAWindow1 appear in bold.

need to scroll down. 6 Observe the members of the_{‘Form_frmRetailer’ list}

The methods, properties, events, or constants that have code written for them appear bold.

7 Under Members of

‘Form_frmRetailer’, select

Form_Activate

Observe the Details pane of the window

This section displays the method name, its syntax, and arguments, if available.

8 From the Project/Library list, selectVBA

You’ll search for a function called ‘cInt’ provided by the VBA library.

9 Edit the Search Text box to read

cInt

To provide the name of the function.

10 Click To start the search.

11 Observe the Search Results section

This displays VBA as library, Conversion as class, and Clnt as member.

12 Observe the Classes list This displays the class Conversion in a dotted box.

13 Observe the Members of ‘Conversion’ list

This displays the function Clnt in a dotted box.

(38)

Visual Basic Editor

1

–

19

14 Close the Object Browser Click the Close Window button on the upper right corner of the Code window.

15 Switch to Access

Close the form To return to the Database window.

(39)

1

–

20 Topic C: Collections

Explanation A collection object consists of multiple objects that have been put together and can be treated as a single unit. The collection object helps you refer to a group of related items as a single object. For example, you can declare all controls in a form as a single

collection in which the controls might be of the same type, or of different types. A collection object has properties and methods of its own that you can use to manage the objects contained in it. You can create your own collection object, called custom collection, which consists of objects of a specific type that are defined by the user. In this case, you can define your own properties and methods for the object. Collections help you to simplify the code if you need to perform the same operation on all the objects in a collection.

Because collection is an object type in VBA, you must declare a collection object to create a new collection. You use the following syntax for declaring a collection:

Dim CollectionName as new Collection

To add an item to a collection, you use the Add method. To remove an item, use the Remove method. To remove all items in a collection, use the Remove method in a loop.

Built-in collections

Built-in collections are the collections provided by the application itself. Access has several built-in collections. For example, you’ll probably have a number of forms in your application. All forms that are open at any point of time are part of the Forms collection. Each form might contain several controls, such as labels, text boxes,

command buttons, and option buttons. These controls are part of the Controls collection.

Do it!

C-1: Understanding collection objects

Questions and answers

1 What is a collection object?

A collection object is a set of multiple objects that have been put together and can be treated as a single unit. These objects might be of the same type or of different types.

2 What are built-in collections?

Built-in collections are those provided by Access. For example, the Forms collection and Controls collection are built-in collections.

3 What is the Forms collection?

It is a collection of all forms that are open at any point of time in an application.

4 Is a form a collection object?

Yes, each form, by itself, is a collection object.

(40)

Visual Basic Editor

1

–

21 Controls collection

Explanation A control on a form or a report is a control object. A control has methods and properties of its own. An Access application consists of multiple form and report objects. Each form consists of several controls that belong to the Controls collection of that form. In the same way, each report consists of several controls that belong to the Controls

collection of that report. You use the Control object type if you need to take some action with all controls of the same type.

Do it!

C-2: Using the Controls collection

Here’s how

Here’s why

1 Open frmLogin From the Database window.

Observe the form (By default, user name and password are in the respective text boxes.) You’ll write code to clear the text boxes by using Controls collection.

2 Close the form To return to the Database window.

3 Open the Code window By default, the Object list displays General and the Procedure list displays Declarations.

4 In the General Declarations section, type the following code:

Dim Ctl as Control To declare Ctl as a variable of the Control generic object type.

Press

e

5 From the Object list, select Form The Form_Load event procedure appears in the Code window.

6 In the Form_Load event procedure, type the following

code:

For Each Ctl In Form_frmLogin If TypeOf Ctl Is TextBox Then

Ctl.SetFocus Ctl.Text = "" End If

7 Click (The Save Objects button is on the Standard

toolbar.) To update the database.

8 Close VBE Click the Close button.

(41)

1

–

22

9 Switch to Form view The form appears with a blank user name and password.

10 Close the form

11 Close the database

(42)

Visual Basic Editor

1

–

23 Unit summary: Visual Basic Editor

Topic A In this topic, you learned about an object. You learned how to modify properties and use methods by using a Code window. You also learned how to associate code with an event by using an event procedure.

Topic B In this topic, you learned how to use the Object Browser. You learned that you can search methods, properties, and events of an object by using the Object Browser. Topic C In this topic, you learned about collection objects. You also learned how to use the

Controls collection.

Independent practice activity

1 Open Practice Objects. 2 Open VBE.

3 Modify the Caption property of frmLogin form to read Login Form by using the Properties window.

4 Switch to Form view and ensure that form caption is changed to Login Form. 5 Update the database.

6 Use a method to close the database. (Hint: Use the Quit acQuitSaveAll method in the Click event of the cmdQuit button of frmLogin.)

7 Update the database, switch to Access, and test whether or not you can close the database.

8 Open Practice Objects.

9 Open and observe frmEmployee.

10 Open the Code window for frmEmployee.

11 Using control collections, write a code to clear the text in all text boxes. 12 Update the database.

13 Switch to Form view and ensure the validity of your code. 14 Close the form.

15 Open the code window for frmEmployee.

16 Use Object Browser to search for the methods used in frmEmployee. (Hint: From the Project/Library list, select All Libraries, and enter Form _ frmEmployee in the Search Text box.)

17 Close Object Browser. 18 Close VBE.

19 Close the database.