pg_0001

www.LearnAccessVBA.com

How to Use This Book

Welcome to Learn Access 2003 VBA With The Smart Method. This book has been designed to enable students to

master Access 2003 VBA by self-study. The book is equally useful as courseware in order to deliver courses

using The Smart Method teaching system.

Smart Method publications are continually evolving as feedback from our courses is incorporated whenever

we discover better ways of explaining and teaching information technology concepts.

Feedback

At The Smart Method we love feedback – both positive and negative. If you have any suggestions for

improvements to future versions of this book or if you find content or typographical errors the author would

always love to hear from you via Email to:

feedback@learnaccessvba.com

While a response cannot be guaranteed, future editions will always incorporate your feedback so that there

are never any known errors at time of publication.

Downloading the sample files

Only one file is essential

In order to use this book it is only necessary to download the Session 1 sample file from the Internet. The

sample file is available from:

http://www.learnaccessvba.com

You do not have to download all of the files you will find there. The lesson and session files are provided

only for disaster recovery. You’ll build the entire application starting with the single Session 1 file.

Type the above URL into your web browser and follow the full instructions available on this web page.

Session and lesson files

The best way to benefit from the course as a whole is to work through all sessions and lessons in sequence

and build the sample application yourself starting with the Session 1 database that contains only data (no

relationships, validations, reports, queries, forms or VBA code).

Sometimes you may need to use the book as a reference and only wish to complete a specific session or

lesson. In this case you can download the relevant lesson or session file. You can also use these files if you

corrupt your work-in-progress file mid-way through the course.

Problem resolution

There are a lot of incremental files available on the web—one for every lesson in this course. They have all

been tested but if any problems are encountered either downloading or using the sample files please send an

Email to:

feedback@learnaccessvba.com

And we’ll do everything possible to quickly resolve the problem.

pg_0002

Typographical Conventions Used In This

Guide

This guide consistently uses typographical conventions to differentiate parts of the text.

When you see this

Here’s what it means

Dim strCustomer as string

Program code. (All code is printed using

Courier New font, the same font used by the

VBA Editor).

Re-makes of films are quite common, for

example the classic film Get Carter was first

made in 1971 with Michael Caine in the lead

role.

Italics may sometimes be used for emphasis or

distinction. They may be used for entities

such as book titles, names of films and table

names when such items would not be

sufficiently distinct from the surrounding text

without embellishment.

Select File New from the main menu

Click on File from the main menu and then

select New from the drop down menu list.

Press <Ctrl> + <Z>.

You should hold down the Ctrl key while

pressing the Z key.

When a lesson tells you to click a toolbar

button the relevant button will be shown

either in the page margin or within the text

itself.

If you want to read through the book as

quickly as possible you don’t have to read

notes.

Notes usually expand a little on the

information given in the lesson text.

Whenever something can easily go wrong, or

when the subject text is particularly important,

you will see the Important sidebar.

You should always read Important sidebars.

Tips add to the lesson text by showing you

shortcuts or time-saving techniques relevant

to the lesson.

note

You can declare variables

anywhere in your code but it’s

good practice to do this at the

beginning of the sub or

function.

mportant

Do not click the Delete button

at this point as to do so would

erase the entire table.

tip

Set up field validation, format

and lookups before creating

your forms

pg_0003

How to Use This Book

The best way to benefit from the course as a

whole is to work through all sessions and

lessons in sequence and build the sample

application yourself.

Sometimes you may need to use the book as a

reference and only wish to complete a specific

session or lesson. In this case note the file

name shown in the folder icon at bottom left

of each lesson page (Session1 in the example

shown) and download the relevant lesson or

session file from: www.learnaccessvba.com.

Use of American English

Even though this is a British publication American English (rather than British English) spelling has been

used throughout. This is because the Access help system and screen elements all use American English

spelling making the use of British English confusing.

Examples of differences are the British English spelling: Colour and Dialogue as opposed to the American

English spelling: Color and Dialog.

Session1

pg_0004

Learn Access 2003 VBA with The Smart Method

www.LearnAccessVBA.com

Putting the Smart Method to Work

Access version and service pack

This edition was written using Microsoft Access 2003 Service Pack 2 running under the Microsoft XP Service

Pack 2 operating system. You can check your program version by selecting Help About from the Access

main menu. If you then click the System Info… button from the same dialog you can also confirm your

operating system version.

If you are using an earlier operating system or program version this book will be equally relevant but you

may notice small differences between some of the screen grabs in the book and those on your screen. There

is also a small possibility that some code may not execute as described.

Sessions and lessons

The book is arranged into Sessions and Lessons. In a Smart Method course a Session would generally last for

between half an hour and an hour and a half and would represent a continuous period of interactive

instruction followed by a coffee break of ten or fifteen minutes.

When you use this book for self-instruction we’d recommend that you do the same. You’ll learn better if

you lock yourself away, switch off your telephone and complete the whole session without interruption. The

memory process is associative and we’ve ensured that each lesson within each session is very closely

coupled (contextually) with the others. By learning the whole session in one sitting you’ll store all of that

information in the same part of your memory and should find it easier to recall later.

The experience of being able to remember all of the words of a song as soon as somebody has got you

“started" with the first line is an example of the memory’s associative system of data storage.

We’d also highly recommend that you do take a break between sessions and spend it relaxing rather than

catching up on your Emails. This gives your brain a little idle time to do some data sorting and storage!

pg_0005

How to Use This Book

First page of a session

2/ In the next few paragraphs

we informally summarise why

the session is important and the

benefits that you will get from

completing it.

This is important because

without motivation adults do

not learn. For adults, learning

is a means to an end and not an

end in itself.

The aim of the introduction is

to motivate your retention of

the skills that will be taught in

the following session by

allowing you to preview the

relevance of the material that

will be presented. This may

subconsciously put your brain

into “must remember this"

mode—assuming, of course,

that the introduction convinces

you that the skills will be useful

to you!

1/ The first page begins with a

quotation, often from an era

before the age of the computer,

that is particularly pertinent to

the session material. As well as

being fun, this helps us to

remember that all of the real-

world problems we solve with

technology have been around

for a long time.

3/ The session objectives

formally state the precise skills

that you will learn in the

session.

At the end of the session you

should re-visit the objectives

and not progress to the next

session until you can honestly

agree that you have achieved

them.

In a Smart Method course we

never progress to the next

session until all delegates agree

that they are completely

confident that they have

achieved the previous session’s

objectives.

4/A file with the starting point for the session is always

available for download from: www.learnaccessvba.com.

The file name is stated here.

pg_0006

Every lesson is presented on two facing pages

Winston Churchill was well aware of the power of brevity. The discipline of condensing thoughts into one

side of a single sheet of A4 paper resulted in efficient transfer of information.

A tenet of our teaching system is that every lesson is presented on two facing sheets of A4. We’ve had to

double Churchill’s rule as they didn’t have to contend with screen grabs in 1939!

If we can’t teach an essential concept in two pages of A4 we know that the subject matter needs to broken

into two smaller lessons.

Pray this day, on one side of one sheet of paper, explain how the Royal Navy is prepared to meet the

coming conflict.

Winston Churchill, Letter to the Admiralty, Sep 1, 1939

pg_0007

How to Use This Book

Learning by participation

Confucius would probably have agreed that the best way to teach IT skills is hands-on (actively) and not

hands-off (passively). This is another of the principal tenets of our Smart Method teaching system. Research

has backed up the assertion that you will learn more material, learn more quickly, and understand more of

what you learn if you learn using active, rather than passive methods.

For this reason pure theory pages are kept to an absolute minimum with most theory woven into the hands-

on sessions either within the text or in sidebars. This echoes our teaching method in Smart Method courses

where snippets of pertinent theory are woven into the lessons themselves so that interest and attention is

maintained by hands-on involvement but all necessary theory is still covered.

Tell me, and I will forget. Show me, and I may remember. Involve me, and I will understand.

Confucius (551-479 BC)