INSERT BOOK COVER Exploring Getting Started with VBA
INSERT BOOK COVER Exploring Getting Started with VBA for Microsoft Office 2010 by Robert Grauer, Keith Mulbery, Keith Mast, and Mary Anne Poatsy Chapter 3 Customizing Access With VBA Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 1
Objectives • • • Write code in modules Write code forms and reports Work with objects Create functions Use DAO to access data Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 2
Objectives • • Use ADO to access data Add VBA to forms Add VBA to reports Handle errors in VBA Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 3
Introduction to VBA and Access • Within Access, you can create standard VBA modules that will run whenever you execute them. • Create form and report modules that contain procedures that run whenever an event occurs. • Create functions using the same VBA programming code you use for other modules. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 4
Writing Code in Modules • A standard module stores procedures that are available to any event in the application. • Class modules are advanced programs that are used to create custom objects. • An Access object module stores procedures that are available to a specific form or report. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 5
Example Class Module Click Insert – Class Module to adda anew class module Expand and collapse as needed A new class module appears The editor adds End Sub automatically Type Public Sub, and thenthe theprocedure name Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 6
Writing Code for Forms and Reports • Open the object in Design view, and then click Property Sheet in the Tools group. • On the Event Tab, select an event, and then click Build on the right side of the property cell. • The Choose Builder dialog box appears with three options—Macro Builder, Expression Builder, and Code Builder on the Event tab. • Select the Code Builder, and then click OK to open the VB Editor. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 7
Open the Code Builder Property Sheet Form in Design view Select Code Builder, and then click OK to open the VB Editor Click the Build button to create VBA code for an event Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 8
Working with Objects • VBA works with objects in the various object libraries. • Use objects in the object libraries with their resources to accomplish programming tasks. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 9
Understanding Properties • A property is a physical attribute of an object. • Some properties include: – Size – Type of border – Back color • Properties can be changed using VBA code. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 10
Understanding Methods • A method is an action that is performed by an object. • Example of Access methods: – Open – Close – Refresh – Go to new record – Filter Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 11
Understanding Events Form data is being edited User can “undo” the changes by clicking No Advance to the next record to trigger a save Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 12
Creating Functions • Steps to create a custom function include: – Open a module. – Type a statement into the Code window with the following format example: • Function Name. Of. Function(Argument as data type) As Data Type End function – Type the VBA statements to be executed. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 13
Working with Recordsets • A recordset is a set of records in memory selected from a table or query. – Similar to a table or a query • The same data source would be represented in all three cases. • Manipulate recordsets using DAO (Data Access Objects) and ADO (Active. X Data Objects). Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 14
Using DAO to Access Data • To create a recordset in DAO: – First explicitly connect to a database, usually the database that is currently opened. – Create a recordset object based on a table, a query, or an SQL statement. – Then loop through the records in the recordset to count records, find a specific record, or modify a field data. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 15
Find Records in a Recordset Procedure finds employees from the state of CO Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 16
Update Records in a Recordset Procedure finds employees from State = CO, and changes billrate to $170 Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 17
Delete Records in a Recordset Procedure finds employees that are inactive and deletes record from the table Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 18
Insert Records in a Recordset Procedure adds a new record using the Add. New method Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 19
Using ADO to Access Data • ADO (Active. X Data Objects) was designed to connect to a wide range of external data sources. • ADO was designed to connect to relational databases, ISAM data sources, and disk files. • ADO uses the Current. Project. Connection argument. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 20
Link to the ADO Object Library • Click Tools on the menu bar. • Select References in the dialog box. • Click OK. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 21
Adding VBA to Forms • Almost all of the VBA code added to a form is triggered by an event. • Common events include: – – – On Open On Click On Enter On Exit Before Update After Update Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 22
Add an Event Procedure to a Form • To notify the user when a record is being changed: – Add a hidden message to the top of the form. – Make the message visible while it is being changed. – After the record is saved, the message could be hidden again. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 23
Add an Event Procedure (Cont. ) Label is hidden when record is saved Label is visible when record becomes dirty Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 24
Validate Data Before an Update Validation of hire date Validation of exemptions Validation of bill rate Validation of salary Validation of 401 k Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 25
Adding VBA to Reports • Modify the layout of a report before the report prints. • Reports that contain multiple sections may have one or more sections that need to be hidden, even though they contain data. • Use VBA to test if a section matches certain criteria and then tell Access how to respond when condition is met. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 26
Example VBA in Reports Region 500 is hidden Other regions are visible Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 27
Understanding Report Events • Report events are similar to form events. • Once a report runs, most of the events happen in succession with very little intervention by the user. • The On Open, On Load, and On Activate events fire in succession. • A user cannot change the order of events; the order is predefined by Access. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 28
Set the Record Source Based on Form Properties • A report is more versatile if the user sets one or more parameters before it is open. • Use a blank form that contains no record source to set the parameters for the report. • The form would contain all the parameters that affect the report and a method for selecting the parameters. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 29
Setting the Record Source User selects a region Preview the report from the button Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 30
The On Open Event Combo box reference on the parameter form SQL statement becomes the record source Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 31
Handling Errors in VBA • There are two types of errors in VBA: – Handled errors: • Procedures with a built-in error handling routine • Errors can be intercepted and VBA can display a friendly message to the user, with options that make sense. – Unhandled errors: • Otherwise the code will stop working and the user will be presented with a confusing list of options Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 32
Add an Error Handling Routine • To produce a more user-friendly error message, an error routine can be added to the code. • The On Error statement directs the code to skip to a particular line in the procedure (usually at the bottom) where the appropriate message is displayed any corrective action can be taken. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 33
Example Error Handling Routine On Error statement added Exit routine added Error routine added Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 34
Test and Debug Code • The VBA debugger enables the programmer to suspend the execution of VBA code so the following debug tasks can be done: – Check the value of a variable in its current state – Enter VBA code in the Immediate window to view the results – Execute each line of code one at a time – Continue execution of the code – Halt execution of the code Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 35
Setting a Breakpoint in the Code Breakpoint was set Current line shown in yellow Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 36
Summary • Explored the use of VBA in an Access database • Enhanced the functionality of an Access Database using VBA events • Coded events for an Access form or report • Handled errors in an effective and efficient manner Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 37
Questions Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 38
Copyright All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall. 39
- Slides: 39