DB Implementation: MS Access Macros & Expressions

1. DB Implementation:MS Access Macros & Expressions S511 Session 9, IU-SLIS

2. Outline • Expressions in Access • Access Macros • Macro Actions • Functions • Creating a Macro MS Access File for Lecture S511 Session 9, IU-SLIS

3. MS Access Expressions: Intro • What is an “expression” in MS Access? • Combination of functions, operators, constants, & identifiers that evaluates to a single value • Identifier  name of field, control, or property (e.g., Forms![Orders]![OrderID]) • Operator  arithmetic (+ - * /), comparison (= < >), logical (Not And Or), text (& +) • e.g.,=Sum([Purchase Price])*0.08 • What does it do? • Perform calculations • Manipulate (e.g., combine, extract) text • Check data values • Expression Builder • MS Access tool to help build expressions • Can select from the lists of • Built-in functions, operators, and constants. • Identifiers • Database object (table, query, form, report) • Object elements (fields, controls, properties) MS Access 2007: Expression Builder S511 Session 9, IU-SLIS

4. Access Expressions: Usage • Where to use expressions • Control Source property in Form or Report • =DateAdd("yyyy",10,[Employees]![HireDate]) • Default property in Form or Report • =Date() • Validation Rule property in Table or Form • In ("Mr.","Mrs.","Ms.") • Criteria in Query • Between#1/1/1998# AND#12/31/1998 • Calculated Field in Query • Age: DateDiff(“yyyy”,[BirthDate],Date()) • SQL Statements in Query or Macro • Action Argumentsand Conditions in Macro • Arguments for VBA functions, statements, and methods S511 Session 9, IU-SLIS

5. Access Expressions: Specifying Values • Specify values in expressions using • Contants (i.e, literal values) • number, string, or dates; exactly as written • e.g. 10, “Bloomington”, #1/1/1998# • Access contants • True, False, Null, “” (empty string) • Functions • Returns values of calculation/operation • e.g. Date(), Sum([Sales]) • Field, Control, Property Identifiers • Syntax • ObjectType![ObjectName]![Control/FieldName].PropertyName • e.g. Forms![Orders]![OrderDate].DefaultValue • ! operator indicates that what follows is a user-defined item. • . operator indicates what follows is an item defined by MS Access. • Only need to specify enough parts of an identifier to make it unique MS Access 2007: Expression Syntax S511 Session 9, IU-SLIS

6. Access Expressions: Functions MS Access 2007: Operators MS Access 2007: Functions S511 Session 9, IU-SLIS

7. Access Expressions: Examples • =IIf(IsNull([Initial]), [FName]&“ ”&[LName], [FName]&“ ”&[Initial]&“ ”&[LName]) • Display “Firstname Lastname” if no initial, “Firstname Initial Lastname” otherwise • =[Sales]/Sum([Sales]) • Display the proprotion of current sales • =DLookup(“[Name]”, “qryEmployees”, “[EmpD]=Forms![frmEmployees][EmpID]”) • Display Name in qryEmployees, whose EmployeeID matches that entered in the active form • =DSum(“[OrderAmount]”, “Orders”, “[CustomerID]=‘Doe’”) • Display the total order amount of the cutomer “Doe” • Year([OrderDate])=Year(Now()) AND Month([OrderDate])=Month(Now()) • Query Criteria: where order date is in current year and month • Areacode: Mid([Phone], 2, 3) • Query Field: display 3 characters starting with the 2nd character in [Phone] • = Nz([UnitsInStock],0) + Nz(UnitsInOrder,0) • Form Control Source: show total number of units • SELECT ALL from [Employees] WHERE [LastName]=“Doe” • SQL: return Employees rows with LastName = “Doe” S511 Session 9, IU-SLIS

8. Programming in MS Access • Modules • An organized collection of Visual Basic for Application (VBA) code • More flexible and powerful than macros, but less secure • Macros • A named set of actions that perform operations/tasks • e.g. open/close a form, print a report, set value of a control • Tool to automate tasks and extend functionalities without programming • Macro actions/functions = subset of commands available in VBA • Command Button Wizard • To perform Common Tasks • Macro Builder • Build a list of actions to perform by selecting from list of actions S511 Session 9, IU-SLIS

9. Access Macros: Elements • Event • 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 • What the macro does • e.g. load/close/delete object, set field/control value • Action Arguments (properties) • What action applies to • e.g. frmEmployee • Conditions • Conditions that must be true for the macro to run • e.g. IsNull(Forms![frmEmployee]![Status]) S511 Session 9, IU-SLIS

10. Access Macros: Events • Event is the reason the macro “fires” or runs. • A macro’s execution should be triggered by a specific event • Successful macro use depends on • attaching to appropriate events • good macro design. • 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 • Set the appropriate Event Property of the control to the name of the macro that will perform desired actions. S511 Session 9, IU-SLIS

11. Access Macros: Grouping • Simple/Embedded Macro • A single macro • Actions are executed sequentially from top to bottom • Macro Group • Related macros grouped together in a macro object • Individual macros in a macro group are referenced by • MacroGroupName.MacroName • e.g. mcrOpenCloseGRP.CloseImOpen S511 Session 9, IU-SLIS