amp110 microsoft access macros

Accelerated Computer Training for Working Professionals Factory 2010 Case

Orange Coast Database Associates Course (800)355-9855 or http://ocdatabases.itgo.com

Orange CoastDatabase Associates

Specializing in Microsoft Office,

Access, SQL, and related technologiesClasses custom designed forWorking Professionals

http://www.dhdursoassociates.com

San Juan Capistrano, CA

(800)355-9855

AMP110 Microsoft Access Macros

Factory AMP1101

AMP110 - Introduction to AccessMacros

Quick introduction to AccessMacros for experienced Windowsusers

P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com

FactoryAMP1102

MS Access Introductory (100 Level)Curriculum

AIN100

AIA101

AMP110Macros

AIN104Reports& Forms

AIN102Queries

AIN100T A, B

FactoryAMP1103


Introduction (s)

Facilities

Course Packet (May vary by course and class)

– Student Questionnaire

– Collaterals (Catalogs, etc.)

– PowerPoint handouts for all sessions

– Evaluation form in back

– Training certificate

FactoryAMP1104

AMP110 – Introduction to AccessMacros

Quick pace for experienced windows users

Assumes prior knowledge of Access equivalentto AIA101/AIN100

End-user, not programmer, oriented

Somewhat structured towards personal use foranalysis and reporting

FactoryAMP1105


Macros (90% of class)

Hot Keys, etc.

Switchboards (2007) orNavigation Forms(2010)

Course Topics:

FactoryAMP1106

Introduction to Access Macros

Industry Standard Approach:

Tables Table Designer

Queries Query designer

Reports Wizard then modify

Forms Wizard then modify

Macros Macro Designer

Access has many ways to accomplish tasks. As an acceleratedcourse we will cover only the most common.

FactoryAMP1107


2 Sessions

Lecture

Demo

Student “hands-on” - by the end of theclass the student will have added macros toa small sample application

Exercises are cumulative – later examplesbuild on objects created earlier

Course Format:

FactoryAMP1108


Session 1

– Macro Concepts

Session 2

– Using Macros

– Switchboards (2007) or

– Navigation Forms (2010)

Course Schedule (3 hour sessions):

Factory Access Macros1

Module – Macro Concepts


Microsoft Access

FactoryAccess Macros2

Module Outline

Part 1: Basic Macro concepts

Part 2: Form concepts

Part 3: Building a macro “group:

Part 4: Introduction & Keyboardshortcuts

Two parts:


Module Hands On

– Create macros Simple

Run from form

Add where criteria

Add a condition

Add another command

Custom keys

Students “hands-on”:


Sample Application – Factory2000

Simple cost accountingapplication

Employees work on workorders

Clock labor hours for eachwork order


Factory2000 Relational Database Structure

EmpNo fName lName Rate

12 Bob Smith 15

13 Mary Chavez 20

14 Alicia Parks 25

EmpNo Wono Start End Hours

12 A1 1/1/01 1/31/01 20

14 A2 2/1/01 2/28/01 42

14 B3 1/1/01 2/28/01 40

employees

Labor

Wono Descr Std

A1 Casting 30

B3 Fitting 50

C2 Screws 70

Work_orders


Permanent Links


Microsoft Access

Part 1

Introduction toMacro concepts


Introduction to MacroProgramming

Two ways of “programming” a taskin Access– Macros

– Visual Basic for Applications (VBA)

We will cover Macros primarily

We will also take a look at controlwizards which do use VBA

We do have two Access VBA courses youcan take

Types of Macros

Standalone

Data

Embedded



Introduction to MacroProgramming

Macros are not coded – they aredesigned using a Macro Designer

They run in response to an “event”– like when a form launches or theuser clicks on a button


Some Common Events

Common button event

– “On Click”

Common form events

– On Load

– On Activate

– On Current (record)

– On insert/update/delete (records)

– On Close


Introduction to Macros

Elements of Macros– Actions – what the macro does

– Action arguments – control the specifics of theaction

– Name – Allows you to store multiple macros withina single macro “object”

– Conditions – circumstance under which the macro“fires”


Typical Actions

Apply Filter

Cancel event

Go to control/record

Minimize/Maximize

Open form/report/query

Set value

Select object

Transfer spreadsheet

Transfer text


Macro Design Grid


Introduction to Macros – Actions &Arguments

Create and run a simple macro, mcrFirst,to become familiar with the concepts

– Action = msgbox

– Arguments: “Hello, Macro”

information type

title = “My First Macro”


Designing the First Macro


Designing the First Macro


My First Macro

Design | Macro Tools


Comments

Use thecomment actionto comment yourmacros

Always a goodidea


Submacros

Same idea as a programming subroutine

Used to store common actions that are usedrepeatedly

Submacros are NOT run unless you call themby name

You call them with programming “dotnotation”…

Macroname.submacroname


Submacros


Groups

Another way to create subroutines or blocks ofmacros (submacros)

Hold over from 2007 which we won’t use in thecourse



Conditional Macros (If)

Use if to createa macro thatruns only whencertainconditions aremet


Microsoft Access

Part 2

Form concepts


Form Events

Forms and controls have “events”

These “fire” on certain actions. Examples:

– Click a button (onclick event)

– Before table is updated (beforeupdate event)

– After table is updated (afterupdate event)

– Etc.


Form Events (cont’d)

When an event “fires” can execute:

– VBA Code

– Macro

This provides a method for “running” anapplication

Macros will appear in the event drop down list


Event List with Macros


Microsoft Access

Part 3

Building a macroGroup

Building a Macro Group

We will build a series of submacros in a macro.

– Open report

– Open report with filter

– Open report with filter conditionally

These will be run from a form with a buttonusing the OnClick event of the button.



Open Report Macro

Design a simple macro to open(run) a report for the displayedform record

Use rptWork_OrdersFull

Give this macro the namework_Orders Report

Save the “group” as mcrReports

Note: it won’t run right just yet – still hasall records


Designing The Open Report Macro


Invoking the macro

Add a command button to the form

Bring up properties sheet, events tab

Click the drop down for the On Click Event

Select your macro

That’s it!


Use Onclick Event to “Fire” the Macro


Select Macro


Invoking the macro

Open the frmWork_OrdersFinal formin design mode

– Add a command button to the form withcontrol wizard off

– Bring up properties sheet, events tab

– Click the drop down for the On Click Event

– Select your macro

– That’s it!

Save as frmWork_OrdersMacro

(instructor may use AMP110 or other suffix for this class)


Adding Where Conditions to aMacro

Use action argument where

Create a filter like the where clause ofan SQL statement


Using the Expression Builder for the Where Condition


Where Condition (in Zoom Window)


Macros - Adding a where clause

Open mcrOpenReport in design mode

Add a where criteria to preview only the workorder on the screen. Put this in a new submacro.

Click to right of where in macro property sheet

Use the builder to get name of form text control,etc. Your expression will look like this:– [wono]=[Forms]![frmWork_OrdersMacro]!

[Wono]

Save the macro under the same name (instructormay use a slightly different name)


Invoke the macro

Open the frmWork_OrderMacroin design mode

Update the preview button to usethe new submacro

Observe that the form only printsdetail for the work order selectedin the form!


Macro Group w/ where Macroadded


More on Macros

Macros can be invoked depending on“condition”– Uses an if macro

– Macro will run if and only if condition is true

Macros can be run one after another like a“program”. Example:– Do a maketable query , then an append query from

a different file. I.E different downloads from an ERPsystem


Conditional Macros

Modify your mcrOpenReport to check for amissing work order number before doing areport

Add a new action like the mcrFirst to print“Please select a work order number”

Add a condition to each to check to see if wonois null

Use the builder if desired

Name submacro OpenReport_Condition

Save macro group with same name


Using the Builder to Create a Macro Condition


Invoking the Conditional Macro

Modify the formfrmWorkOrders_Macro torun the new macro

Save asfrmWorkOrders_MacroFinal

Test your new form andmacro


Macro Group with new conditionalmacro


Adding Commands to a Macro

Move focus on form back to wonowhen an error occurs

Add a go to control action

Save your macro asmcrOpen_Report_Final


Final Submacro in Design Mode


Macro Messages

Often want to suppress messages whenrunning a macro

– Set warnings off

– Echo off


Microsoft Access

Part 4

Custom keyAssignments


Custom Key Assignments

Can customize keyboard shortcuts in twoways:

– Access keys

– Key Assignment macros


Access Keys (Hot Keys)



Access has many built-in keyboard shortcuts:CTRL-G to open Debug Window, F11 todisplay Database Window, etc. See Help.

Can also create your own using macros – wewill cover this in module 2


Microsoft Access

End of Module


Factory Access - Using Macros1

Module – Using Macros to AutomateYour Application


Microsoft Access

FactoryAccess - Using Macros2

Module Hands On

– Create macros Imports

Exports

Startup (AutoExec)

AutoKeys

Set Values on a form

Data Macros

Macros to code

– Use the control wizard to close aform

Students “hands-on”:



Two ways of “programming” a taskin Access– Macros

– Visual Basic for Applications (VBA)

We will cover Macros primarily

We will also take a look at controlwizards which do use VBA



Elements of Macros– Events – what “fires” the macro

– Actions – what the macro does

– Action arguments – control the specifics of the action

– Conditions – circumstance under which the macro “fires”

Macro design grid – how macro is defined

Macro Designer



Some Available Actions

Apply Filter

Find

Go

Max/minimize

Open

Run

Set

Transfer


Form Events

Forms and controls have “events”

These “fire” on certain actions. Examples:

– Click a button (onclick event)

– Before table is updated (beforeupdate event)

– Before a record is deleted

– After table is updated (afterupdate event)

– After the user leaves a control

– Etc.


Form Events (cont’d)

When an event “fires” can execute:

– VBA Code

– Macro

This provides a method for “running” anapplication


Import/Export Form

Create a simple, unbound form named frmData

We will use it to drive a series of very usefulimport and export macros


Import/Export Form


Export Macro

Design a simple macro toexport our three main tablesto text files – use transfer text

Export order is not critical

Let’s put them into a datamacro group

Group name mcrData

Macro name export_all


Designing The Export Macro

Note: can use saved spec


Invoking the macro

Open the frmData form in design mode

– Add a command button to the form with wizardenabled

– Select run macro

– Select your macro

– That’s it!

Save with same name (instructor will use adifferent name for this working form)


Use Control Wizard to “Fire” the Macro


Select Your Macro


New Data Form with Macro Button


Export Your Data

Run the export macro – check for files


Macros - Adding the Imports

Open mcrOpenData in design mode

Add the imports

Call the macro import_all

Link the two parent text files towork_orders_linked and employees_linked

Import the labor_export text file tolabor_import


Creating the Import Macro


Invoke the macro

Open the frmData form

Add a button to run the importmacro

Click on it

Observe that the tables in yourdatabase are updated!


Updated Data Form


New Tables after Running Macro



Key combination goes in macro name field

Use Send Keys codes to create the macro

Can have conditions if desired, too


Create Import/Export AutoKeys


Automating Startup

Put VBA code in the form Open and/or Loadevent, or

Use a startup (old DOS autoexec concept)macro

– Create a macro with the name Autoexec – it will“fire” when your app starts. Hold down the shift keyto bypass it.


Autoexec Macro


Create an AutoExec Macro

Modify your import/export macro

Keep the import part only

Save as autoexec

Delete your linked tables and the labor import

Close and restart your database – notice thetables were added again automatically


Setting a Value

Set value can be used to do calculations, etc.

Two action arguments– The item that will have its value set

– The expression to put in


First Add an Overhead Control

Add an unbound text box to thefrmwork_OrdersMacro form

Call it txtOverhead


Form with Overhead Control


Create Set Value Macro


Item to set


Value Expression


Invoke the Macro

Use the form’s on current event

This fires whenever we move to a new record

When the event fires run the macro


Invoke the Macro

Note: thisIs a form event


Using the Macro


Using Set Value

Update frmData_Macro with two newcontrols…

– Checkbox which says wether or not to hide the workorder selection text box

– Textbox for work order

Add a macro which will hide and show theabove text box


Adding the Macro


Item to Set


Value Expression to Hide/Unhide


Form with New Controls

Data Macros

Data macros fire when a certain event takesplace within a table – similar to a trigger inOracle or SQL Server

– After insert

– After update

– Etc.


Data Macros

Add an overtime rate field to the employeestable

Add a data macro to update this field when theregular time rate changes


Data Macros


Before Change Macro


Table with Data Macro



Macros to Code

Can convert macros to VBA code

A handy way to get your VBA subroutinestarted

In general, programmers will use code, notmacros

But as we have seen Macros can be quitehandy


Macros to Code


Macro to Code


Add a Close Button

Open frmData_Macro in designmode

Add a close form button

Select close form fromoperations

Run the form

That’s it!


Final Data Macro Form

Factory Access - Using Macros52

Microsoft Access

End of Module


Please fill out and turn in yourend-of-course evaluations ifthis is your last module

Factory Access Navigation1

Module – Navigation


Microsoft Access

Access navigation

Newer Access 2010 databases use the newNavigation forms

Older MDB databases still have theSwitchboard Manager tool available

FactoryAccess Navigation2

Creating a Navigation form

Select the desired style


Navigation Forms

Drag and drop forms and reports one by one


Adjust properties

Adjust properties as desired and save asfrmNavigation


Set startup options (File | Options)

Set the Access options to open the navigationform when it starts up



Navigation Form

Create a navigation form foryour application


Switchboards

Create via a switchboard manager tool

Creates a tree of launch forms – mainform is called switchboard and is placedwith your other form objects


Launching Switchboard Manager


Switchboard Manager

Yes – we want to create a new switchboard!


Switchboard Manager


Switchboards

Create a switchboard for yourapplication

FactoryAccess Switchboards13

Add a macro to minimize databasewindow

Create macro (add to mcrForm macro group)

Invoke on form load of switchboard form


Updated mcrForm


Add to main switchboard loadevent


Switchboard test

Close your database

Reopen your database – the database windowshould be minimized

Factory Access Navigation17

Microsoft Access

End of Module


Please fill out and turn in yourend-of-course evaluations.

Notes


Notes


Notes


Accelerated Computer Training for Working Professionals

Orange Coast Database Associates Course (800)355-9855 or http://ocdatabases.itgo.com

Orange CoastDatabase Associates

Specializing in Microsoft Office,

Access, SQL, and related technologiesComputer Training, Programming & Consulting

32422 Alipaz St., Suite A-15

San Juan Capistrano, CA

(800)355-9855 (Toll Free) | (949)489-1472 (Direct) | (949)485-6284 (Fax)

http://www.dhdursoassociates.com | [email protected]

Accelerated Computer Training

amp110 microsoft access macros

Technology