Access VBA Programming For Dummies

Overview

  • This friendly, easy-to-use guide shows experienced Access users how to use
  • VBA (Visual Basic for Applications) to build Access databases and applications, but also covers programming fundamentals for nonprogrammers
  • Includes practical, ready-to-use VBA code examples that readers can type or copy and paste from the Web into their own database projects
  • Explains basic VBA skills...
See more details below
Other sellers (Paperback)
  • All (19) from $3.32   
  • New (10) from $16.51   
  • Used (9) from $3.32   
Sending request ...

Overview

  • This friendly, easy-to-use guide shows experienced Access users how to use
  • VBA (Visual Basic for Applications) to build Access databases and applications, but also covers programming fundamentals for nonprogrammers
  • Includes practical, ready-to-use VBA code examples that readers can type or copy and paste from the Web into their own database projects
  • Explains basic VBA skills and concepts for nonprogrammers, such as procedures, variables, and loops
  • Covers more advanced topics, such as record sets and other programming activities that are unique to Access programming
  • Author has written more than ninety computer books and has been working with databases since the early 1980s
Read More Show Less

Product Details

  • ISBN-13: 9780764574115
  • Publisher: Wiley
  • Publication date: 8/27/2004
  • Series: For Dummies Series
  • Edition number: 1
  • Pages: 408
  • Sales rank: 488,148
  • Product dimensions: 9.22 (w) x 10.86 (h) x 0.89 (d)

Meet the Author

Alan Simpson is the author of over 90 computer books on databases, Windows, Web site design and development, programming, and networking. His books are published throughout the world in over a dozen languages and have millions of copies. Alan has also taught introductory and advanced computer programming courses at San Diego State University and the UCSD Extension. He has served as a consultant on high-technology, educationoriented projects for the United States Navy and Air Force. Despite that, Alan has no fancy job title because he has never had a real job.

Read More Show Less

Table of Contents

Introduction.

Part I: Introducing VBA Programming.

Chapter 1: Where VBA Fits In.

Chapter 2: Your VBA Toolkit.

Chapter 3: Jumpstart: Creating a Simple VBA Program.

Part II: VBA Tools and Techniques.

Chapter 4: Understanding Your VBA Building Blocks.

Chapter 5: Controlling Access through VBA.

Chapter 6: Programming Access Forms.

Part III: VBA, Recordsets, and SQL.

Chapter 7: The Scoop on SQL and Recordsets.

Chapter 8: Putting Recordsets to Work.

Part IV: Applying VBA in the Real World.

Chapter 9: Creating Your Own Dialog Boxes.

Chapter 10: Customizing Lists and Drop-Down Menus.

Chapter 11: Creating Your Own Functions.

Chapter 12: Testing and Debugging Your Code.

Part V: Reaching Out with VBA.

Chapter 13: Using VBA with Multiple Databases.

Chapter 14: Integrating with Other Office Applications.

Part VI: The Part of Tens.

Chapter 15: Ten Commandments of Writing VBA.

Chapter 16: Top Ten Nerdy VBA Tricks.

Chapter 17: (Way More Than) Ten Shortcut Keys.

Index.

Read More Show Less

First Chapter

Access VBA Programming For Dummies


By Alan Simpson

John Wiley & Sons

ISBN: 0-7645-7411-6


Chapter One

Where VBS Fits In

In This Chapter

* Describing Access

* Discovering VBA

* Seeing where VBA lurks

* Understanding how VBA works

This is a book about using Visual Basic for Applications (VBA), which is a programming language that helps you program, tweak, and squeeze productivity from Access. VBA, which is embedded in Access, is a sophisticated set of programming tools that you can use to harness the power of a packaged application like Access. Just like you need to know how to walk before you can run, you need to know Access before you can start to use Access VBA.

Maybe you want to use Access to manage a large mailing list. Maybe you need Access to manage your whole business, including customers, products, and orders. Perhaps you need to manage enrollments in courses or events. Whatever your reason for using Access, your first step will always be to create the tables for storing your data. From there, you can then create queries, forms, reports, and macros to help manage those data. All these steps take place before you even get into VBA. So in this book, I have to assume that you're already an experienced Access user who needs more than what queries, forms, reports, and macros can provide. If you're new to Access, this is not a good place to start. If you need to brush up further on Access, Access 2003 For Dummies (John Kaufeld, Wiley) or Access 2003 All-in-One Desk Reference For Dummies (Alan Simpson, Margaret Levine Young, and Alison Barrows; Wiley) would be a good place to start.

Although Access has progressed through many versions over the years, VBA has remained relatively unchanged. I used both Access 2002 and Access 2003 to create this book, but the code examples presented in this book should work fine in just about any version of Access. So now, before launching into VBA, take a moment to discuss what tables, queries, forms, and reports are all about, and how VBA fits into the overall scheme of things.

Taking a Look at Access

Access, part of the Microsoft Office suite, is a huge database management system that you work by using modern object-oriented methods. (The term object-oriented stems from the fact that everything you create in Access - a table, form, report, or whatever - is considered an object.

The Access database window, as shown in Figure 1-1, is the main container in which you store all the main objects that make up a single database. The left column of the database window is the Object list, and each name in the list represents a type of object, as summarized here.

  •   Tables: Tables contain the raw data that all other object types display and manage. Data in tables is stored in records (rows) and fields (columns).
  •   Queries: Use queries to sort and filter data as well as define relationships among multiple related tables.
  •   Forms: Access forms are similar to printed fill-in-the-blank forms, but they allow you to view and change data stored in Access tables.
  •   Reports: Reports are objects that define how data should be presented on printed reports.
  •   Pages: Pages are similar to forms, but users can access data in tables through a Web browser rather than directly through Access.
  •   Macros: Macros provide a means of automating certain aspects of Access without programming.

The Modules container, as you'll soon discover, is one of the places where you store VBA code. If you're not already familiar with modules, that's fine. Modules are what this book is really all about. Groups, of course, aren't really separate objects but rather just collections of existing objects. Sort of Access's version of Favorites.

One of the most important things to understand is that you don't use VBA "instead of" other objects like tables and forms. You use VBA to enhance the capabilities of other object types. Therefore, it makes no sense to even try VBA until you have a firm grasp of the purpose and capabilities of those other object types in Access.

Understanding VBA

Visual Basic is a programming language - a language for writing instructions that a computer can read and process. VBA is a programming language that's specifically designed to work with the application programs in Microsoft Office including Word, Excel, Outlook, and of course, Access.

When you write text in a programming language (as opposed to writing in plan English), you're writing code. Programmers use the term code to refer to anything that's written in a computer programming language. For example, Figure 1-2 shows some sample VBA code. The whole trick to learning VBA is learning what all the various words in the language mean so that you can write code that tells Access exactly how to perform some task.

If the sample code shown in Figure 1-2 looks like meaningless gibberish to you, don't worry about it. People aren't born knowing how to read and write VBA code. Programming (writing code) is a skill you have to learn. For now, it's sufficient just to know what code looks like. Knowing what the code means is one of the skills you'll master in this book.

Because VBA code looks like a bunch of meaningless gibberish typed onto a sheet of paper, this begs the question of why anybody would want to learn to read and write some dreadful language like that. The answer to that question lies in the role played by VBA in an application like an Access database.

The ability to use the same code over and over again is key to automating mundane tasks in Access. For example, if you used Access to print checks, you might have to manually type the part of the check where you type the amount in words, like Ninety-two and 99/100 Dollars for $92.99 because Access can't do that translation on its own. But if you could write some code to translate a number like $92.99 into words, you wouldn't need to type all those dollar amounts. Access would just print the correct information as it prints each check.

Access does indeed have a ton of tools that let you create a database without any programming at all. You could easily spend months or years just learning all the things you can do in Access without writing any VBA code. Yet despite the huge number of things you can do without programming, sometimes you will want your database to accomplish some task that's not built into Access. That's where VBA comes in. When you want Access to perform a task that it doesn't already know how to perform, you write the steps to be performed in the VBA programming language.

REMEMBER

When you're writing VBA code or just looking at some VBA code written by someone else, Access doesn't do anything. Access doesn't actually perform the steps described by that code until Access executes the code. When you write VBA code, you're actually writing a set of instructions that Access can perform at any time, over and over again.

Seeing Where VBA Lurks

In an Access database, VBA code is stored in modules. Despite the fancy name, a module is basically an electronic sheet of paper on which VBA code is typed. The two types of modules in Access are

  •   Standard module: A page that contains VBA code that's accessible to all objects in the database.
  •   Class module: A page of code that's attached to every form and report you create. VBA code in the class module is accessible only to the form or report to which the class module is attached.

The main difference between a standard module and a class module is one of scope. VBA code in a standard module has a global scope, which means that the code can be accessed by every object in the database. A class module has a local scope, meaning that its code is accessible only to one form or one report in the database.

I talk about the issue of scope as it becomes relevant throughout this book. Right now, it's not terribly important. For now, the main thing to keep in mind is that modules contain VBA code. Now take a look at where modules are stored within an Access database.

Finding standard modules

A standard module contains VBA code that's accessible to every table, query, form, report, page, and macro within the current database. Like those other objects, standard modules get their own button in the Object list at the left side of the database window (refer to Figure 1-1). When you click the Modules button, the main pane shows the names of standard modules (if any) within the current database, as in the example shown in Figure 1-3.

TIP

Don't be surprised if you click the Modules button in a database, and the main pane is empty. Standard modules don't just happen: You have to create them.

Finding class modules

Like standard modules, class modules contain VBA code that tells Access what to do. Unlike standard modules, however, you won't find any class modules in the database window. Class modules are hidden behind forms and reports in your database.

It might help to define the term class as a class of objects. In Access, tables are one class of objects, queries are another class, forms are another class, reports are another, and so forth. Or looking at it from the other direction, a single form is an object within your database. That single form is also a member of the class of objects known as forms.

Class modules are not global nor public like standard modules. To the contrary, class modules are very private beasts. They bring new meaning to the concept of hermit. Not only are class modules invisible to you most of the time, but they're always invisible to each other. The VBA code in a class module is visible (and usable) only to the form or report to which the class module is attached.

I think that it helps to envision a class module as literally being hidden behind its form, as in Figure 1-4. The VBA code in the class module is always hidden from the other objects in the database. The class module might be hidden from you as well if you don't know how to find it.

You have several ways to get to a form or report's class module, as you'll discover in upcoming chapters. For now, if you just want to open a class module and have a look, here's one way to do it:

1. In the database window, click Forms or click Reports, depending on which type of object you want to open.

2. Right-click the name of any form or report and choose Design View. To see the class module for the open form or report, click the Code button on the toolbar or choose View [right arrow] Code from the Access menu bar (see Figure 1-5).

From VBA to Access

When you open a module, whether it's a standard module or a class module, your screen will change radically. That's because the module opens in the Visual Basic editor, which is a separate program window from Access. In fact, if you look on the taskbar, you'll still see a taskbar button for Access. You can switch back and forth between Access and the editor just by clicking their respective taskbar buttons, as shown in Figure 1-6.

TIP

If the module you open contains any VBA code, that code is visible in the editor Code window, also shown in Figure 1-6. A class module might contain VBA code even if you never wrote a line of VBA code in your life because some of the control wizards in the form and report Design views automatically write VBA code for you behind the scenes. But let's not get ahead of ourselves.

The main thing to keep in mind here is that every time you open a module, you will end up in that Visual Basic editor. You'll discover how to use that program in upcoming chapters. For now, the most important thing to know is how to close it and get back to the more familiar Access program window. Here are two easy ways to close the Visual Basic editor and get back to the more familiar Access program window:

  •   Choose File [right arrow] Close and Return to Microsoft Office Access (see Figure 1-7).
  •   Press Alt+Q.

TIP

You can press Alt+F11 to switch back and forth between Access and the VBA editor at any time.

The Visual Basic editor closes, its taskbar button disappears, and you're returned to the Access program window.

Finding Out How VBA Works

When you open a standard module or class module, there's no telling exactly what you'll see inside. Some modules will be empty; others will already contain some VBA code. It all depends on the life history of the module you open. But one thing is for sure: If any VBA code is in the module, it will likely be organized into one or more procedures.

The term procedure in everyday language usually refers to performing a series of steps in order to achieve some goal. For example, the procedure of getting to work every morning requires a certain series of steps. The same definition holds true for VBA code. A procedure is a series of steps carried out in a specific order to achieve some desired result.

Discovering VBA procedures

A VBA procedure is a series of instructions written in VBA code that tells an application (like Access) exactly how to perform a specific task. In VBA code, each step in the procedure is a single line of code: a statement. When Access executes a VBA procedure, it does so step-by-step, from the top down. Access does whatever the first statement tells it to do. Then it does whatever the second statement tells it to do, and so forth, until it gets to the end of the procedure.

Exactly when Access executes a procedure is entirely up to you. Typically, you want to tie the procedure to some event that happens onscreen. For example, you might want the procedure to do its task as soon as someone clicks a button. Or perhaps you want your procedure to do its thing whenever someone types an e-mail address into a form. I talk about how that all works in Chapter 6. For now, just realize you can tie any procedure you create to any event you like.

When the event to which you've tied your procedure occurs, Access calls the procedure. What that really means is that Access does exactly what the VBA code in the procedure tells it to do. You can envision the process as in Figure 1-8 where

1. An event, such as clicking a button, calls a procedure.

2. Access executes the first line in the called procedure; then it executes the second line in the procedure, and so on.

3. When Access encounters the end of the procedure (which will be either End Sub or End Function), it just stops executing code and returns to its normal state.

Continues...


Excerpted from Access VBA Programming For Dummies by Alan Simpson Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)