1 / 54

Automating Database Processing

Chapter 6. Automating Database Processing. Chapter Introduction. Design and implement user-friendly menu Called navigation form Macros Automate repetitive tasks Add functionality to reports and forms. Tools Covered in This Chapter. Access Options Action Catalog AutoExec macro

barney
Download Presentation

Automating Database Processing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 6 Automating Database Processing

  2. Chapter Introduction • Design and implement user-friendly menu • Called navigation form • Macros • Automate repetitive tasks • Add functionality to reports and forms Succeeding in Business with Microsoft Access 2010

  3. Tools Covered in This Chapter Access Options Action Catalog AutoExec macro AutoKeys macro Breakpoints Events Macros Succeeding in Business with Microsoft Access 2010

  4. Tools Covered in This Chapter Macro groups Macro security Navigation Forms Splash screen Trust Center Visual Basic for Applications (VBA) Succeeding in Business with Microsoft Access 2010

  5. Level 1 Objectives: Automating Tasks with Switchboards and Macros Create a well-designed navigation form to provide a user interface for a database Automate tasks by creating basic macros Specify what happens when a database opens Succeeding in Business with Microsoft Access 2010

  6. Understanding Navigation Forms • Navigation Form • Special kind of form • Appears when database opened • Makes database navigation similar to a website • Main navigation form and subform control automatically built in • Drag and drop object to form and create tab • Selecting tab allows instant view of the objects Succeeding in Business with Microsoft Access 2010

  7. Understanding Switchboards (continued) • Can add buttons to main navigation form to work with • Tables • Forms • Queries • Reports • Appear in hierarchical format Succeeding in Business with Microsoft Access 2010

  8. Typical Switchboard Design Succeeding in Business with Microsoft Access 2010

  9. Designing a Navigation Form Main navigation form serves as menu for users when database is opened Control user access to tables Review objects, plan categories to organize the objects on tabs Sketch a design for the navigation form Succeeding in Business with Microsoft Access 2010

  10. Creating a Navigation Form • Create lower level of the hierarchy first • Create tabs by drag and drop of object onto form • Create main navigation form then created • Click Navigation button in forms group of Create tab • Select tab layout • Change title and change format, if necessary • Drag forms or reports from Navigation Pane to the [Add New] tab • Use Layout view and Form selector button to change form location or size • Repeat until all needed forms are on the navigation form Succeeding in Business with Microsoft Access 2010

  11. Creating a Navigation Form, Cont. • Preview all forms in Form View • Test to ensure working correctly • Create forms for reports as created for forms • Preview and test all tabs to ensure working correctly • Create main form to provide easy navigation through database • Last item on main form should be a button to close the database Succeeding in Business with Microsoft Access 2010

  12. Formatting a Main Menu • Steps to changing the format of a main menu • Open the navigation form in design view • Change formatting elements • Color • Labels • Images • Lines • To change size and position properties, double click Form Selector button and change entries on form’s property sheet Succeeding in Business with Microsoft Access 2010

  13. Understanding Basic Macros • Macro • Action or series of actions • Save time and ensure accuracy • Automate repetitive tasks • Composed of series of actions organized in sequence in which they should be performed • Create macro • Using Macro window Succeeding in Business with Microsoft Access 2010

  14. Common Macro Actions Succeeding in Business with Microsoft Access 2010

  15. Creating a Macro • Create Access macro in Macro window • Select the Create tab, and then click the Macro button in the Macro & Code group • Action catalog • Select features wanted • Actions to be performed • Click list arrow in action column • Click Add New and scroll through list to action you want Succeeding in Business with Microsoft Access 2010

  16. Creating a Macro (continued) • Action arguments • Provide additional information for performing macro action • Arguments needed vary depending on selected action chosen • Save macro with descriptive name, using mcr prefix Succeeding in Business with Microsoft Access 2010

  17. Running a Macro • Three ways to run a macro: • In Macro Tools design tab, click the Run button in the Tools group • On database Tools Tab, click the Run Macro button in the Macro group • Select the macron in the Run Macro dialog box, click • In the Navigation Pane, click Macros, right-click the macro name, and then click Run Succeeding in Business with Microsoft Access 2010

  18. Editing a Macro • Use Macro Builder as in creating • Rearrange order of actions • Drag action line to new position • Insert new action between two existing actions • Click Add new and select the action • Move or drag the new action between the two other actions • Delete action • Click action then click the delete button Succeeding in Business with Microsoft Access 2010

  19. Setting Startup Options • Startup options • Access performs when database opens • Set options using Access options feature • Bypass Startup Options • Press and hold down shift key when opening database Succeeding in Business with Microsoft Access 2010

  20. Specifying Startup Options in the Startup Dialog Box • Options • Display form • Display navigation pane • Allow full menus Succeeding in Business with Microsoft Access 2010

  21. Startup Dialog Box Succeeding in Business with Microsoft Access 2010

  22. Creating a Splash Screen • Splash screen • Displays when database opens • Contains • Logo • Text • Can only display splash screen or Navigation form automatically Succeeding in Business with Microsoft Access 2010

  23. Creating a Splash Screen (continued) • AutoExec • Special macro • Runs when database opens • Before Access Options dialog box options • Can use to display splash screen • Create splash screen form Succeeding in Business with Microsoft Access 2010

  24. Creating an AutoExec Macro Create macro that contains actions Access should perform when database opened Save macro using name AutoExec Succeeding in Business with Microsoft Access 2010

  25. Level 1 Summary • Navigation Form • Special kind of form • Appears when database opened • Macro • Use to automate repeated actions • Access options dialog box • Splash screen Succeeding in Business with Microsoft Access 2010

  26. Level 2 Objectives:Creating Advanced Macros Consolidate automated tasks by creating a macro group Specify conditions and events for macros Troubleshoot macros Succeeding in Business with Microsoft Access 2010

  27. Understanding Macro Groups • Macro group • Two or more macros placed within same macro file • Consolidate related macros • Manage large numbers of macros • Only group name displayed as macro object in database window Succeeding in Business with Microsoft Access 2010

  28. Creating a Macro Group • Logical to organize similar macros within group • To create: • Click Create tab on the ribbon, click Macro button in the Macros & Code group • Click the Action Catalog button in the Show/Hide group on the Design tab • Add the Submacro Action to the Macro Builder • Enter unique name in the Submacro box • Click Add New Action arrow and select an action, select or enter the arguments • Repeat for each macro you want in group • Save the macro group using mcr prefix Succeeding in Business with Microsoft Access 2010

  29. Running the Macros in a Macro Group • Clicking Run Button on the Design tab runs only the first macro in the group • Run particular macro within group • Click the Run Macro button in the Macro group on the Database Tools tab • Select the list arrow to choose the macro name • Click the macro name • Click OK Succeeding in Business with Microsoft Access 2010

  30. Assigning AutoKeys to Macros in a Macro Group • AutoKeys macro group • Assign macro to key or key combination • Type key or key combination • Use special notation • ^ for CTRL • + for Shift • Uses subset of SendKeys command • Built-in commands to send keystrokes to Access databases • Save group as AutoKeys Succeeding in Business with Microsoft Access 2010

  31. Examples of Key Combinations for the Autokeys Macro Group Succeeding in Business with Microsoft Access 2010

  32. Creating the Navigation Form to run macros using Command buttons • Add Command button to a form • Click button to run a macro • Makes database actions easier for users • Click the Button button in the Controls group • Click in from where button is to be placed • Command button wizard starts • Select category and then action to be completed • Repeat until all need buttons are on form • Save form Succeeding in Business with Microsoft Access 2010

  33. Specifying Conditions and Events for Macros • Specify conditions for performing macro actions • Assign macro to control • Event-driven programming • Running macro when user interacts with object • Event • State, condition, or occurrence that Access recognizes • Event property • Specifies how object responds when event occurs Succeeding in Business with Microsoft Access 2010

  34. Specifying Where Conditions in a Macro • Where condition argument • Refer to controls on form that are not currently active • Use control’s complete name • Format • [Forms]![formName]![controlName] Succeeding in Business with Microsoft Access 2010

  35. Troubleshooting Macros • Macros containing arguments • Especially error prone • Error types • Syntax error • Logic error • Run-time error • Troubleshooting tools • Single step through execution of macro, using debug window • Print macro code • Use Visual Basic Editor Succeeding in Business with Microsoft Access 2010

  36. Printing Macros • Open Documenter dialog box • Click macros tab • Select macro by clicking its check box • Click options button to select details of macro to print • Properties • Actions • Arguments • Permissions Succeeding in Business with Microsoft Access 2010

  37. Single Stepping a Macro • Runs macro one action at time • Pausing between actions • Make sure actions appear in correct order and with correct arguments • Use Single Step button • Macro Single Step dialog box • Buttons • Step • Stop All Macros • Continue Succeeding in Business with Microsoft Access 2010

  38. Setting Breakpoints • Breakpoint • Code inserted in macro • Signals where to stop macro • Examine values of actions, controls, and arguments macro using • Open macro in the Microsoft Visual Basic for Applications window • Shows macro as it appears in VBA code • Examine details of macro code in VBA window Succeeding in Business with Microsoft Access 2010

  39. Setting Breakpoints • Immediate pane • Displays current value of controls and arguments • Display using ? operator Succeeding in Business with Microsoft Access 2010

  40. Level 2 Summary Macro group Conditions for macros Assign macro to buttons Debug macros Succeeding in Business with Microsoft Access 2010

  41. Level 3 Objectives:Macro Conditions Assign a macro to an object event Run a macro when a form opens or a report prints Use a macro to validate data Succeeding in Business with Microsoft Access 2010

  42. Exploring Macro Conditions • Macro conditions • Logical expressions that result in true or false answer • Depending on outcome • Macro can perform one set of actions or another • Enter macro conditions • Open macro in macro builder • Drag the program flow action to macro builder • Set condition to test • If condition true • Access performs SetValue action Succeeding in Business with Microsoft Access 2010

  43. Exploring Macro Conditions • Add Else If condition to perform actions if False • Provides alternate action to perform Succeeding in Business with Microsoft Access 2010

  44. Assigning a Macro to an Object Event • Assign macro to object • By specifying macro name in event property of object • Event categories • Report • Section Succeeding in Business with Microsoft Access 2010

  45. Report Events and Section Events Succeeding in Business with Microsoft Access 2010

  46. Using Message Boxes with Forms • Form events can also trigger macros • Use to • Validate data • Set values • Navigate between forms • Filter, find, and print records Succeeding in Business with Microsoft Access 2010

  47. Form Events Succeeding in Business with Microsoft Access 2010

  48. Using Message Boxes with Forms • MsgBox command • Opens message box • Displays warning or informational message • MsgBox ("message" sum of button and icon values "title") • Returns value depending on which button clicked • Create condition that checks to see which button users click Succeeding in Business with Microsoft Access 2010

  49. Using Message Boxes with Reports • When conditions in macro references name of control from source • Source must be open when condition tested • Can hide window while macro tests condition • MsgBox action • Opens message box and displays warning or informational message • Similar to using MsgBox command Succeeding in Business with Microsoft Access 2010

  50. Using Message Boxes with Reports (continued) • MsgBox action arguments • Message • Beep • Type • Title Succeeding in Business with Microsoft Access 2010

More Related