DB Implementation MS Access Macros Programming in MS

DB Implementation: MS Access Macros

Programming in MS Access Modules u u An organized collection of Visual Basic for Application (VBA) code More flexible and powerful than macros, but less secure Macros u A named set of actions that perform operations/tasks · u Tool to automate tasks and extend functionalities without programming · u e. g. open/close a form, print a report, set value of a control Macro actions/functions = subset of commands available in VBA Embedded Macro vs Standalone Macro · Command Button Wizard (embedded macro) → · To perform Common Tasks Macro Builder (standalone macro) → → Create/Modify Macros. Build a list of actions to perform by selecting from list of actions Database Systems Seminar 2

Access Macros: Macro Builder Creating a standalone macro using a macro builder 1. (Tab) Create → (Group) Macros & Code → click Macro 2. Select an action from the dropdown menu (e. g. , Openform) Database Systems Seminar 3

Access Macros: Macro Builder 3. Select the form name to open. 4. Click “Save” icon and enter a macro name. Database Systems Seminar 4

Access Macros: Macro Builder 5. Click “Run” icon to test the macro. 6. Select the saved macro name for the “On Click” event of the command button. Database Systems Seminar 5

Access Macros: Macro Builder Creating an embedded macro using a macro builder 1. Click “…” icon for an event of the command button & select “Macro Builder” 2. Follow the same procedure as a standalone macro. Database Systems Seminar 6

Access Macros: Event Property Sheet u The reason the macro “fires” or runs. · i. e. , the trigger for a macro execution · e. g. , a form control gaining/losing focus, mouse click/movement Action Macro Builder u What the macro does · e. g. load/close/delete object, set field/control value Action Arguments (properties) u What action applies to · Elements e. g. frm. Employee Conditions (i. e. , If) u Conditions that must be true for the macro to run · e. g. Is. Null(Forms![frm. Employee]![Status]) Database Systems Seminar 7

Access Macros: Events Event is the reason the macro “fires” or runs. u u A macro’s execution should be triggered by a specific event Successful macro use depends on attaching to appropriate events · good macro design. · u Example · After Update vs. Lost Focus event → → loss of focus can occur without data entry or edit for data entry trigger, use “After Update” event Associating Events and Macros u Set the appropriate Event Property of the control to the name of the macro that will perform desired actions. Database Systems Seminar 8

Access Macros: Database Systems Seminar Macro Builder 9

Appendix: Log. In Form Creation 1. Create a Login Table. 2. Create a Log. In form (unbound). 3. Create a Log. In Creation form (bound). 4. Set Access Option to display Log. In form when DB opens. Database Systems Seminar 10

Log. In: 1. Create a Log. In Table a. b. Create a table named “Login” with 3 fields. Field Name Data Type Field Size user. ID Short Text 25 password Short Text 25 email Short Text 50 Enter a couple of record. - This table will hold login information Database Systems Seminar 11

Log. In: 2. Create a Log. In Form a. b. Create an unbound form named “frm. Login” as shown below. - User will enter user. ID and password to login to the system. Create macros for the Buttons u u u c. Login: If login error, display error message. Else display the navigation form. Clear: Blank out the input boxes. Create Login: Open the Log. In Creation Form. Set the form’s properties to display as a dialog window. Database Systems Seminar 12

Log. In: 3. Create a Log. In Creation Form a. b. Create a form named “frm. Create. Login” bound to “Login” table. Create macros for Buttons u u u Create Login: If missing input, display error message. Else display the confirmation message and open the login form. Clear: Blank out the input boxes. Exit Application: Exit Access. Database Systems Seminar 13

Log. In: 3. Create a Log. In Creation Form c. Create macros for Text Boxes. u u d. user. ID: If existing ID, display error message. Confirm Password: If it does not match the password, display error message. Create a macro for the form. u Go to New Record on Open. Database Systems Seminar 14

Log. In: 4. Set Access Options Display frm. Login form when DB opens. Database Systems Seminar 15