microsoft access xp (2002)info.marshall.usc.edu/dept/training/documents/applications/access/... ·...

Group/Summary Operations

Creating a Switchboard

Creating Macro Buttons From Wizards

Creating Macros Manually

Using the Condition Column

Start Up Parameters

Microsoft Access – XP (2002) Switchboards & Macros

USC - Marshall School of Business AIS – User Support

Access_2002-Switchboards.doc April 23, 2003 of 33

Table of Contents DEFINITIONS 3

Switchboard .......................................................................................................................................... 4

MACRO ................................................................................................................................................ 4

Module .................................................................................................................................................. 5

Where Macros/Modules Are Stored ..................................................................................................... 5 ABOUT THE DATABASE 3

DATA ENTRY FORM 4

SWITCHBOARDS 5

Examining A Completed Switchboard ................................................................................................. 6 CREATING A SWITCHBOARD 6

The “Toolbox” Toolbar ........................................................................................................................ 7 USING THE MACRO WIZARDS 8

Example 1: A Button That Runs A Query: “Dollars To Goal” ............................................................ 8

Example 2: A Button That Runs A Query: “Total Current Donations” .............................................. 8

Example 3: Create a Button that Opens a Form: “Main Form” ...................................................... 9 VIEWING VISUAL BASIC CODE 9

CREATING MACROS WITHOUT WIZARDS 10

Macro Basics ...................................................................................................................................... 10

Exploring an Existing Macro .............................................................................................................. 10

Macro 1: A Macro That Goes to a New Record and Moves the Cursor ........................................... 12

Macro 2: A Macro that Filters a Query From a Drop Down Menu Choice ....................................... 13

Understanding Events ......................................................................................................................... 14 SYNCHRONIZED COMBO BOXES 15

Step 1: Create the “Countries” Combo Boxes ................................................................................... 16

Step 2: Creating a Query for the Cities Combo Box ......................................................................... 17

Step 3: Creating the Cities Combo Box .......................................................................................... 18

Step 3: Create the Results Query ........................................................................................................ 19

Step 4: Creating the Macro ................................................................................................................. 20

Step 6: Assigning the Macros to the Combo Boxes .......................................................................... 21 MACROS - THE CONDITION COLUMN 21

Condition Macro 1: Message Boxes .................................................................................................. 22

Condition Macro 2: Macros That Input Data ..................................................................................... 23

Condition Example 3 .......................................................................................................................... 24

Setting the Arguments ........................................................................................................................ 26

Assign the Macro to the Form ............................................................................................................ 26 MACRO: OPTION BOXES 27

Examining the Query: Donation Status ............................................................................................. 27

How Option Groups Work .................................................................................................................. 28 HIDING FORM CONTROLS 31

SETTING STARTUP PARAMETERS 32

Temporarily Bypassing the Startup Limitations ................................................................................. 32

Removing Startup Limitations ............................................................................................................ 32 HIDING DATABASE OBJECTS 33

Unhiding Database Objects ................................................................................................................ 33

ABOUT THE DATABASE This handout utilizes a database called “MacrosXP.mdb” . The database can be found in two

locations:

From HOH300, HOH401, & Bri202, it can be copied from: \\sbalab\apps\prof\training

to you’re A:\ drive or to C:\temp

It can also be downloaded from the web at the following address:

http://www.marshall.usc.edu/computing/training/Handouts.htm

In the same location, there is another database called “MacrosXP-Complete.mdb” that has the

completed examples used in this handout

The purpose of this database is to track which shows customers have seen, how many tickets were

purchased, and how much money they have donated to the theatre. It is made up of 3 tables which are

related by their common field of Client_ID .

“Customer Info” - This table tracks general patron information. Every patron, whether they are a

donor, attendee or both, is listed in this database only once.

“Donor Info” - If a patron has made a donation then the donation amount, date, etc. is tracked in this

database. If they have made more than one donation, their Client_ID will appear more than once.

(Their name, address, etc, is listed in the “Customer Info” table.)

“Tickets Purchased” - If a patron has purchased a ticket then the name of the show, date, number of

tickets, and ticket price will appear in this database. If they have seen more than one show, then their

Client_ID will appear more than once. (Their name, address, etc, is listed in the “Customer Info”

table.)

For more information on relating tables, see the handout: “Access XP - Forms” which can be found at

the following web address: http://www.marshall.usc.edu/computing/training/Handouts.htm

Marshall School of Business University of Southern California

Access_2002-Switchboards.doc January 2003 of 33

DATA ENTRY FORM

If you click on the Forms tab, you will notice that there are three forms:

“Donor Info Subform” - This form accesses fields from the table “Donor Info” . It is never opened

directly but is pulled into the “Main Form” when the “Main Form” is opened.

“Tickets Purchased Subform” - This form accesses fields from the table “Tickets Purchased” . It

is never opened directly but is pulled into the “Main Form” when the “Main Form” is opened. In

addition, it contains a calculated field called Order Total which was created by multiplying the

number of tickets purchased by the ticket price.

“MainForm” - This form is based off the table “Customer info” and contains the two subforms

“Donor Info Subform” and “Tickets Purchased Subform” .

For more information on creating subforms, see the handout: “Forms - Beginning” which can be

found at the following web address:

http://www.marshall.usc.edu/computing/training/Handouts.htm

Note that to create a mainform containing two subforms you must use the “Subform/SubReport”

button found on the Toolbox toolbar while in form “Design View” to create multiple subforms.

The wizards cannot be used to make a form that contains more than one subform.

DEFINITIONS

Switchboard

A Switchboard is a screen with buttons on it that automates database functions. Its purpose is to

make the database easier to use while keeping sensitive areas of the database protected from misuse.

The switchboard screen itself is created from an unbound form (a form which is not based on a table)

and the buttons on the switchboard activate either macros or Visual Basic code sequences. Also,

calculations and functions may be placed on a switchboard.

MACRO

A Macro is a way to automate a task or a series of tasks in your database.

A macro is an object just as tables, queries, forms, and reports are objects.

Actions: every task you want a macro to perform is called an action. In Access, there are 42

actions that you can select and perform in your macros.

Events: Most macros are triggered by events, such as: opening a form, clicking in a field, pressing

the “ENTER” key, etc.

You can make macros do such things as: automatically opening a form, updating a field, or placing

the hourglass on the screen.



Module

A Module is another object that contains Visual Basic programming code that you can use to add

additional functionality to your database (much as you would Macros). By using Visual Basic,

Access can be used to perform operations its designers did not build into it. When you use the

command button wizard, it creates a procedure in Visual Basic code (not Macro code). Custom

functions can also be created within modules. Visual Basic is a fairly easy programming language, but

it is also extensive and therefore beyond the scope of a simple two hour workshop.

Where Macros/Modules Are Stored

Stand Alone Macros - These are macros you created manually within the Macros tab. They are

called stand alone because they can be made to work alone or assigned to an Access object such as a

form, query, etc.

Form Created Macros/Modules - When you create a command button on a form to automate

operations, the macro is stored within the form as visual basic code and will not appear within the

Macros or Modules tab.

SWITCHBOARDS

A Switchboard is a screen with buttons on it that automate database functions. The purpose of a

switchboard is to make a database easier to use while keeping sensitive areas of the database protected

from misuse. The buttons on the switchboard activate either macros or Visual Basic code sequences.

Switchboards are created from “Unbound” forms. (A form which is not based upon a table.)

The buttons placed on the switchboard activate either macros, events, or Visual Basic procedures

when pressed. This handout will deal primarily with events and macros.



Examining A Completed Switchboard

Below is a completed switchboard which we will recreate. It contains five command buttons which

were created using wizards and three controls which employ macros. To view the form:

1. Open the file: MacrosXP-Complete

2. Click on the Forms tab.

3. Double click the form “Switchboard - Complete” to open it.

4 Click the various buttons to see what they do.

5. Close the database: FILE – CLOSE

CREATING A SWITCHBOARD As mentioned above, a switchboard is created from an unbound form. The steps in this section show

how to create the unbound form.

1. Open the database: MacrosXP

2. Click on the Forms tab.

3. Click on the “New” button.

4. Select “Design View” and click on “Ok”.

5. Resize the form to fill most of the screen

6. Click on the “Save” icon and name the switchboard: Switchboard



Select Objects: Allows the selection of

objects.

Control Wizards: When turned on,

wizards will help with using other tool

box controls.Label: Used for placing text on any part

of your form.

Text Box: Used to create calculations.

Toggle Button: Toggles between two choices.

List Box: Allows selection from a list of choices.

Image: Use to place pictures from a

source outside the database on the form.

Option Group: Used in conjunction

with option buttons, this allows you to

select between a group of choices.

Option Button: Use either with a Yes/No

field or in an option group.

Check Box: Used with Yes/No fields.

Bound Object Frame: Used to display OLE

objects (such as pictures) that are stored in the table

in the form.

Tab Control: Allows you to place

controls behind one another using tabs.

Line: Use to place lines on your form.

More Controls: Opens a list of more

advanced controls.

Command Button: Use to activate macros

& module commands.

Unbound Object Frame: Use to place

OLE objects (sounds, pictures, etc.) from

an outside source in your form.

Page Break: Forces a page break.

SubForm/SubReport: Use to create a

form within a form.

Rectangle: Places a rectangle in the

form.

Combo Box: Allows selection from a

drop down list of choices.

The “Toolbox” Toolbar

When working with forms, the Toolbox contains many of the buttons you will need. If it is not

visible while in form “Design View” , then click the “Toolbox” icon on the menu:

Note that if the “Control Wizards” icon on the Toolbox toolbar is not pressed, the command

wizards will not help you.

USING THE MACRO WIZARDS The macro wizards can be used to generate the Visual Basic code needed to run a macro. The wizards

can be used to perform simple operations without having to create macros in the Macro tab or learn

Access Visual Basic code.

Example 1: A Button That Runs A Query: “Dollars To Goal”

When pressed, the button will open a query called “$ to

Goal” which subtracts the total current donations from the

goal of $50,000.

The query was based on the table “Donor Info” and is

designed as shown here. Note that it employs the SUM

function rather than using the Totals row.

1. With the “Switchboard” form still open and in “Design View” :

2. From the Toolbox , click on the “Command Button” icon:

3. Click in the upper right corner of the form.

4. From the Command Button Wizard , select the category: Miscellaneous

5. In the Action window, select Run Query

and click “Next” .

6. Select the query “$ to Goal” and click “Next” .

7. Select the option Text and type: Dollars to Goal

8. Click on “Next” .

9. Name the button: Dollars to Goal

and click “Finish”.

10. Go into “Form View” and click the command button. It should run the query.

11. Close the query window and then click the “Save” icon to save the form.

Example 2: A Button That Runs A Query: “Total Current Donations”

When pressed, the button will open a query called “Total Current Donations”

The query was based on the table “Donor Info” and is designed as shown here.

It will total all donations made using the Totals row.


2. From the Toolbox , click on the “Command Button” icon

3. Click below the last button created.

4. From the Command Button Wizard , select the category: Miscellaneous

5. In the Action window, select Run Query and click “Next” .

6. Select the query “Total Current Donations” and click “Next” .

7. Select the option Text and type: Total Donations

8. Click on “NEXT”.

9. Name the button: Total Donations and click “Finish” .

10. Go into “Form View” and click the command button. It should run the query.



11. Close the query window and then click the “Save” icon to save the form.

Example 3: Create a Button that Opens a Form: “Main Form”

When clicked, this button will open a form called “Main Form”. Main Form is based on three different

tables: Customer Info, Tickets Purchased, and Donor Info.


2. From the Toolbox , click on the “Command Button” icon

3. Click on the form.

4. In the Category window, select: Form Operations

5. In the Action window, select: Open Form

6. Click “Next” .

7. Select the form “Main Form”


9. Select the option Text and type: Open the Main Form.


9. Name the button: Open Main Form and click “Finish” .

10. Click the “Save” button.

11. Go into “Form View” and click the command button. The form should open.

12. Close the form.

VIEWING VISUAL BASIC CODE

If you are curious about the code the Command Button Wizard is generating for your, the code for

any button can be easily viewed and edited.

To view the code for the “Open Main Form” button created above:

1. Select “Design View” .

2. Right-click the button.

4. From the pop-up menu, select: Properties

2. Click on the Event tab.

3. Click the property which displays “[Event Procedure]” then click the “Build” button

next to it.

4. When finished viewing the code, close the Module window.



CREATING MACROS WITHOUT WIZARDS Using the Wizards is useful but limited. When you need to perform an action that the wizards do not

provide a function for, it is time to move on to creating macros.

Macro Basics

You must save your macros before you can run them.

If you name a macro “Autoexec” , Access will run it whenever you open the database.

A single macro can perform one or many actions.

A Macro object can contain multiple macros. This means that when you click on the Macros tab

in the Database window, each macro listed can contain multiple sub macros. Sub macros are

created by placing a name in the Name column. If there is no name in the name column then

Access will read all of the code in the macro.

Macro code reads from top to bottom in the macro pane window until it finds no more code or it

sees another macro name in the name column.

A blank row will not stop a macro from reading.

Comments are optional and are ignored by Access.

Every task you want Access to perform is called an Action , and there are 47 different actions you

can have a macro perform.

Exploring an Existing Macro

1. Open the database: MacrosXP-Complete (choose: FILE – OPEN)

2. Click on the Macros tab.

3. Select the macro: Macro – Complete

and click the “Design” button.

The Macro window is divided up into 4 parts:

The menus.

The toolbars.

The Action Pane (top part of the window) where the specific action is selected.

The Argument Pane (bottom portion of the window) where parameters are set.

“MacrosXP-Complete” contains several macros:

“Open Mainform – Complete”: This macro opens the form “Mainform - Complete” .

“Show History”: This macro runs the drop down list on the switchboard and runs a query called

“Patron Show History” . It then filters the query to only show the records for the name chosen from

the drop down list.

“Donor History”: This macro runs the drop down list on the switchboard and runs a query called

“Patron Donor History”. It then filters the query to only show the records for the name chosen from

the drop down list.

“Add New Records”: This macro takes the cursor to a new record in the form “Mainform -

Complete” and then positions the cursor in the First_Name field.



Macro Name: For a

macro object to contain

several macros, you must

name the individual

macros in the “Macro

Name” column. Access

reads code from top to

bottom and will stop

reading when it reaches

another macro name. If

you did not use macro

names, access would read

all of the code in the

macro object.

Condition: If you

place a condition in

this column, Access

will only execute the

code next to the

condition if the

condition is true. Note

that true or false, the

line below will be

read. An example of a

condition might be to

check to see if an order

date is before the

required date.

Action: This is the

actual macro code that

is executed by access.

You can select from 49

different actions.

Once an action is

selected, any specifics

are set below in the

arguments pane.

Comments:

Comments are optional

and serve as reminders

or explanations to

yourself for what each

line of code is for.

Access ignores

everything in the

Comments section.

Action Arguments: This area allows the user to specify arguments for the currently selected action.

This pane will change depending on the action selected and always reflects the currently selected

action.

“Condition”: Click this button to display/hide the Conditions column in the

macro pane.

“Macro Names”: Click this button to display/hide the Macro Names column in the

Macros pane.



Macro 1: A Macro That Goes to a New Record and Moves the Cursor

This macro will be assigned to form “Main Form” and will tell Access to begin a new record and

position the cursor in the cursor in the First_Name field.

Step A: Create the Macro

1. Reopen “MacrosXP” by selecting it from the menu: FILE – OPEN


3. Click the “New” button.

4. Click the “Macro Names” button to display that column on the grid.

5. Create the grid shown below including all actions and their arguments.

6. Save the Macro sheet as “My Macros” and then close it.

Step B: Use the Wizards to Assign the Macro to the MainForm

These steps use the Command Button Wizard to assign the macro “My Macros.AddRecord” to the

command button.

1. Click on the Forms tab, select Main Form and click “Design View” .

2. In the Toolbox , select the “Command Button” and then click on the form.

3. In the Categories pane, select: Miscellaneous

4. In the Action pane, select: Run Macro


6. Select: My Macros.New Record


8. Select Text and type (to be displayed on the button): Add a New Record


10. Name the button: Add_Record

11. Click “Finish” .

The arguments for each action are shown

to the right.

For the “GoToRecord” action, the

arguments are Object Type (Form),

Object Name (Main Form), and Record

(New). Offset is not used.

For the “GoToControl” action, the

only argument is the name of the field to

go to (First_Name).



Macro 2: A Macro that Filters a Query From a Drop Down Menu Choice

When the user selects a client’s name from a drop down box on the Switchboard form, their choice

will be used as a filter in a query to only show clients who match. For example, if they select Norma

Desmond, they will only see shows seen by Norma.

Step A: Create the Combo Box

1. Click on the Forms tab and open the form “Switchboard” in “Design View” .

2. Make sure that the “Control Wizards” icon on the Toolbox is active.

3. From the Toolbox , click the “Combobox” button and then click the form.

4. Select: I want the combo box to lookup the values in a table or query and click “Next”.

5. Select the table “Customer Info” then click “Next” .

6. Select the fields: Client_ID , First_Name , Last_Name then click “Next” .

7. Adjust the column widths if necessary and then click “Next”.

8. Select the option: Remember the value for later use and click “Next”.

9. Label the combo box: Attendance History and click “Finish” .

10. Right-click the combo box and select: Properties

11. Click on the All tab.

12. In the Name box, name the combo box: Show History

10. Click the “Save” button.

11. Go into “Form View” .

12. Click the down arrow and select a contact. (Note that at this point nothing else happens.)

Step B: Create a Macro that Runs the Query & Filters


2. Select the macro “My Macros” and then click the “Design View” button.

3. Skip a row down from the previous macro and create the action grid and arguments below.



The code that filters our query basically means the following when stated in plain English: Show only

the clients from this query where the client id is equal to the client chosen from the drop down

menu (called “show history”) on the form called “Switchboard”.

4. Click the “Save” button and close the macro.

Step C: Assign the Macro to the Drop Down List

1. Click on the Forms tab and open “Switchboard” in “Design View” .

2. Right-click the combo box and select: Properties

3. Click on the Event tab.

4. Click in the box for the On Change event.

5. Click the down arrow and select the macro: My Macros - Shows Seen

6. Save the form and go into “Form View” .

7. Test the macro by clicking the down arrow and selecting a name form the list. The query should

run and show only the attendance for the person you selected.

Understanding Events

The macro above used an On Change event to trigger our macro, some other events are listed below.

On Got Focus: Occurs when a control becomes the currently selected control on the screen.

On Before Update: The event occurs before the data in a control (field) is updated (saved).

On After Update: The event occurs after the data in a control (field) is updated (saved).

On Change: The event occurs when the contents of a text box changes.

On Not in List: The event occurs when a value entered into a combo box isn’t in the list.

On Enter: The event occurs before the control actually receives the focus.

On Exit: The event occurs just before the control loses the focus to another control on

the same form.

Note that the order of events (and even which events are available) depends on the object being used

and may differ for Forms, Reports, Text Boxes, Combo Boxes, etc.

Hint: Determining the Detail and Order of Events 1. Select HELP from the menu, then choose the Index tab and search for: Events

2. For details of Events select the subtopic: Events and Event Properties Reference

3. For the order of Events select the subtopic: Find out when events occur

[client_id]=[Forms]![switchboard].[show history]

This is the name of

the combo box

where the user

selects a Client ID

This is the name of

the form the combo

box exists on.

This is the type of

object that our

combo box exists on.

This is referring to the

field in the query

“Patron Show History” that must equal our combo

box selection.

Note, that as an alternate solution, we could have avoided creating this macro completely by placing

the code to the right of the “=” sign in step B directly in the criteria row of the “Patron Show

History” query and then using the command button wizard to create a button to run the query.



SYNCHRONIZED COMBO BOXES

Synchronized combo boxes allow the user to narrow down a list of records by using a succession of

combo boxes. In this example, there is a combo box containing a list of all of the countries of the

world and a second combo box containing all of the cities of the world. When a particular country is

selected in the first combo box, the second combo box only shows cities in the country selected in the

first combo box.

The mechanics of what makes this all happen is illustrated below. The last page of this example shows

the relationship between the “Cities” and “Countries” tables.

Combo_City Cities Combo

Query

Cities

Table

The Country ID selected in

“Combo_Country”

is used as a filter in

“Cities Combo

Query”.

Combo_Country Countries

Table

The “Countries”

table provides the

choices for

“Combo_Country”.

The “Cities” table

provides the unfiltered

data for the query:

“Cities Combo Query” .

The filtered “Cities Combo

Query” provides choices for the

“Combo_City” drop down list.

Donor Info

Tickets

Purchased

Customer Info

Cities

Cities

Query

Results

Synchronized

Combo Boxes

MACRO

data

The macro opens the saved query “Cities

Query Results” and then uses CITY ID selected by the user in “Combo_City” to

filter the “Cities Query Results”

“Cities Query Results” is a saved query that links

4 tables.



Step 1: Create the “Countries” Combo Boxes

Here we will make a combo box that will display a list of countries that the user can select from. The

box will pull the Country ID and Country fields from the “Countries” table but will only

display the names of the countries, not their ID numbers. (Displaying the Country ID is not

necessary although it is the “bound” column.) When the user selects a country from the list, Access

will remember the Country ID associated with the selected country for later use by the Cities combo

box. It remembers Country ID because we are making Country ID the “bound” column.

1. With the form “Switchboard” open, click on the “Design View” icon

2. Activate the Toolbox .

3. On the Toolbox , inactivate the “Control Wizards” button (make sure it is not depressed).

4. On the Toolbox , click the “Combo Box” button.


6. Right mouse click the new combo box and select: Properties

7. Click on the All tab and scroll to the top of the Properties window.

8. Enter the settings shown below. Leave all other settings at their defaults.

9. Rename the label control (the one on the left) as desired.

10. Save the form and click the “Form View” icon

11. Click the down arrow you just created. You should see a list of countries. Note that at this point,

nothing else will happen.

12. Closed the form.

Name: This is what we are naming our combo box.

Row Source type: This is the method Access will use to place a list on the drop down

menu. The list will come from a table or query.

Row Source: Tells access which fields to display and sorts by country.

Column count: Tells Access how many columns to access from the table. (The first 2)

Column Widths: Tells Access which columns to display and what their widths will be. In

our case, Country ID will not be displayed (0) but Country will (1).

Bound Column: Tells Access which column to remember when the user selects a country.

In our case, access will remember column 1 which is the Country ID number.

Name: Comb_Country

Row Source Type: Table/Query

Row Source: Select [Country ID],[Country] from Countries order by [Country];

Column Count: 2

Column widths: 0”;1”

Bound Column: 1



Step 2: Creating a Query for the Cities Combo Box

This combo box will list the cities but will not be directly based upon the cities table. Instead, it will

be based upon a query that is based upon the Cities table. This is necessary because we wish to filter

the list of cities based upon the Country ID the user selected in the Countries combo box.

1. Click on the Tables tab.

2. Select the table “CITIES” .

3. From the menu, click: INSERT – QUERY

4. Click “Design View” then click “Ok”.

5. Place the fields: Country ID, City ID, and City in the Field row sorting by City .

6. Click in Criteria row of the Country ID column and type the following:

8. Click the “Totals” button and set all fields to: Group By (This prevents duplicate cities.)

9. Save the query as: Cities_Combo_Query

10. Close the query without running it.

This query looks to see what Country ID the user selected in the Country combo box on the Customer

data form. It then uses the Country ID associated with the country as a criteria for listing only cities

that are in that country. For example, if the user selected “USA” in the “Combo_Country” combo

box, then the query will insert the Country ID for USA in the criteria row in the Country ID column and list only those cities that have a Country ID of USA.

TIP

The code below will with list all cities in the world if the user does not select a country. It uses an IIF

statement and the ISNULL function. Basically, it reads: If the Combo_Country field is blank, then

list cities for all countries, otherwise, list only cities from the country the user selected in the

“Combo_Country” combo box.

IIf(IsNull([Forms]![Customer Data]![Combo_Country]),[Country ID],[Forms]![Customer

Data]![Combo_Country])

[Forms]![switchboard]![Combo_Country]

The name of the object

containing

“Combo_Country”.

The type of object

“Combo_Country” is

located on (a form).

The name of the object containing

the criteria to be used in this query.



Step 3: Creating the Cities Combo Box

In this step, we will create the combo box that displays the cities. This combo box uses the query,

“Cities_Combo_Query” as its data source.


2. Activate the Toolbox .

3. Inactivate the “Control Wizards” button (make sure it is not depressed).

4. On the Toolbox , click the “Combo Box” button.


6. Right mouse the new combo box and select: Properties

7. Click on the All tab and scroll to the top of the Properties window.

8. Enter the settings shown below. Leave all other settings at their defaults.

9. Save the form and then click the “Form View” icon.

The combo boxes will both contain lists. Further, the country selected in the first combo box will filter

the cities listed in the second combo box; however, the data on the form will not be affected. Note that

it will be necessary to requery (press “F9”) after selecting a Country for the City combo box to reflect

the change. We will fix this later with our macro. Also, no records are displayed as of yet.

10. Select a country and press “F9”.

11. Select a city.

12. Close the form.

Name: Comb_City

Row Source Type: Table/Query

Row Source: Cities_Combo_Query

Column Count: 3

Column widths: 0”;0”;1”

Bound Column: 2

Name: This is what we are naming our combo

box.

Row Source type: This is the method Access

will use to place a list on the drop down menu.

The list will come from a table or query.

Row Source: Tells Access which table or query

contains the list it is to display.

Column count: Tells Access how many

columns to access from the query. (The first 3)

Column Widths: Tells Access which columns

to display and what their widths will be. In our

case, neither Country ID nor City ID will

be displayed (0) but City will (1).

Bound Column: Tells Access which column to

remember when the user selects a city. In our

case, access will remember column 2, which is

the City ID .



Step 3: Create the Results Query

Eventually, when the user selects a city, a query will run which displays all customers that live in the

selected city. A macro is actually what is used to filter the query and we could also use a macro to filter

a table; however, we need data that is from two different tables. “Customer Info” does not contain

the City ID field that we are using as a filter.

1. Click on the Tables tab.

2. Select the table “Cities” .

3. From the menu, click: INSERT – QUERY

4. Click “Design View” then click “Ok” .

5. Click the “Show Tables” icon.

6. Add the following tables:

Customer Info

Tickets Purchases

Donor Info

7. Close the Show Tables window.

8. Connect the “Customer Info” table to the “Cities” table by clicking and dragging between the

two City fields.

9. Double click the join line connecting the “Donor Info” and “Customer Info” tables.

10. Create an Outer Join by selecting: Include all records from Customer Info and only those

records from Donor Info where the join fields are equal. 11. Click “Ok” to close the Join Properties window.

12. Double click the join line connecting the “Tickets Purchases” and “Customer Info” tables.

10. Create an Outer Join by selecting: Include all records from Customer Info and only those

records from Tickets Purchases where the join fields are equal 11. Click “Ok” to close the Join Properties window.

12. Place the following fields on the grid:

City ID , First_Name , Last_Name , City , Country , Show_Title , and

Amount_Donated.

13. Sort by Last_Name in ascending order.

14. Save the query as “Cities_Query_Results” .

15. Close the query.



Step 4: Creating the Macro

Up to now we have one combo box that filters the contents of the other, but the user must press “F9”

(Requery) to update the city and the “Cities_Query_Results” query does not open at this point. This

macro will connect the City Combo box (“COMBO_City”) to “Cities_Query_Results” and press

“F9” for the user.


2. Click the “New” button to create a new macro.

3. From the menu, select: VIEW – MACRO NAMES to view the names column.

4. Create the macros shown below.

ACTION GRID

ARGUMENTS FOR THE ACTIONS SELECTED ON THE ACTION GRID

[City ID]=[Forms]![Switchboard].[Combo_City]

The code for the Where Condition can be interpreted as: Only show records, where the CITY ID equals what is shown in the “Combo_City” combo box on the form called “Switchboard” .

[Forms] is used to identify what type of object “Switchboard” is. They are separated by the !

[Switchboard] us used to specify the location of “Combo_City”. They are separated by the period.

5. Save the macro as: Synchronized Combo Boxes

6. Close the macro.



Step 6: Assigning the Macros to the Combo Boxes

Here we will assign our macros to the objects in our form.

Synchronized Combo Boxes.Requery Cities: is assigned to the “Combo_Country” combo box.

Synchronized Combo Boxes.Filter By City: will be assigned to the “Combo_City” combo box.

Assigning macros to the Combo Boxes


2. Double click the “Combo_Country” combo box to view its Properties .

3. Click on the Event tab in the Properties window.

4. Click in the row for the After Update event

5. Click the down arrow and select: Synchronized Combo Boxes.Requery Cities

6. With the Properties window still open, click on the “Combo_City” combo box.

7. Click on the Event tab in the Properties window.

8. Click in the row for the After Update event.

9. Click the down arrow and select: Synchronized Combo Boxes.Filter By City

Using the Combo Boxes

1. Close the Properties window.

2. Save the form.

3. Click the “Form View” icon.

4. Select a country.

5. Select a city.

The query should run and only list people who live in the city you selected.

Example of the “Cities” Table

Example of the “Countries”

Table

The relationship between the

“Cities” and the “Countries”

tables. Note that they do not

have to be related for this to

work.



MACROS - THE CONDITION COLUMN

The Condition Column

The Condition column allows for the creation of “If-Then-Else” conditions that control under what

circumstances Access will run the macro code in the Action column.

When you place a criteria in the Condition column (such as “[Quantity]>5” for example), the code

placed in the Action column of the same row as the condition will only be executed if the

condition is true.

Code placed on the next line down will be executed whether the condition above is true or false.

If you only want the code on the next line to be executed if the condition is true, then either:

- Place the same condition on the line below.

- Or place three dots … on the line below which is like saying “repeat the condition above”.

As with all macros:

The macro will keep reading the lines below until:

A name is encountered in the Macro Name column.

Or the “StopMacro” action is encountered in the Action column.

Condition Macro 1: Message Boxes

A Message Box is a pop-up window that can be used to alert the user. When combined with the

Condition column, this can be an effective method of ensuring that the user enters data as desired.

In this example, when you go to the record of a person whose status is “No Checks”, a message box

will open with a notice not to accept checks from the patron.

Step A: Creating the Macro


2. Click the macro “My Macros” and then click the button.

3. If necessary, click the “Condition” icon to make the Condition column visible.

4. Skip a row after the last macro and enter the name for your macro: NoChecksMSG 5. Create the macro as shown below.

6. Save and close the macro.



Step C: Assign the Macro to the Form

1. Click on the Forms tab and then open the form “mainform” in “Design View” .

2. From the menu, click: EDIT – SELECT FORM

3. From the menu, click: VIEW - PROPERTIES

4. In the Properties window, click the Event tab.

5. Click in the row for the On Current event.

6. Click the down arrow and select the macro: My Macros.NoChecksMSG

7. Close the Properties window.

8. Save the form then go to “Form View” .

9. Scroll through your records. When you get to a record that has a status of “No Checks” in the Status field, you will get a pop-up warning.

Condition Macro 2: Macros That Input Data

In this example, if the user purchases more than 6 tickets for a show, they get a 10% discount on the

total cost. The “SetValue” action is used in this macro to place the results of our calculation into the Total field. This is different then simply placing a calculation in a form. Calculations are “display

only”, they do not write to the table, but the “SetValue” action does place data in the actual table.


2. Click the macro “My Macros” and then click the “Design” button.

3. Click the “Condition” icon to make the Condition column visible.

4. Create the macro shown below.



Assign the Macro to the Form

Although “mainform” is the form that we open when we wish to enter data, “Tickets Purchased

Subform” is the form that contains the fields that are actually typed in. Therefore, we need to assign

our macro to “Tickets Purchased Subform” . Because the user may decide to enter data into the Ticket_Price or No_of_Tickets fields in a random order, we will assign the macro triggering

event to both fields.

1. From the Forms tab open the form “Tickets Purchased Subform” in “Design View” .

2. Right mouse click the text box control “Ticket_Price” and select: Properties

3. In the Properties window, click the Event tab and select the On Lost Focus event.

4. Click the down arrow and select the macro: My Macros.QuantityDiscount

5. Left mouse click on the text box control “No_of_Tickets”.

6. In the Properties window, select the On Lost Focus event.

7. Click the down arrow and select the macro: My Macros.QuantityDiscount

7. Go to “Form View” .

8. Change the number of tickets purchased or ticket price to test the macro.

9. Save and close the form.

Condition Example 3

To a point, this example is very similar to “Condition Macro 2” except that it gives a 10% discount

based on whether or not the patron has made a donation, not on the quantity of tickets purchased. This

macro is also more complicated because it must look into a subform different from the subform where

the calculation is taking place. If you recall, the donation information is kept in the “Donor Info

Subform” and the ticket information is kept in the “Tickets Purchased Subform” .


2. Click the macro “My Macros” and then click the button.

3. If necessary, click the “Condition” icon to make the Condition column visible.

4. Skip a row after the last macro and enter the name for your macro: DonationDiscount 5. Create the macro shown in the figure above. The conditions cover three possibilities:

What to do if the Amount Donated is greater than zero.

What to do if the Amount Donated is blank. (This uses the ISNULL( ) function which

would return a “True” condition if the field contained a blank.)

What to do if the Amount Donated is equal to zero. (A blank is not considered zero.)



In English, this condition might read: If the Amount_Donated field which exists in the form

“Donor Info Subform” which exists in the form called “Main Form” is greater than 0, then

execute the action to the left

[Forms]![mainform]![Donor Info subform]![Amount_Donated]>0

This is the name of

the field we are

basing our condition

on.

This is the name

of the subform,

containing the

field we are

basing our

condition on.

This is the name of the

mainform, containing the

subform.

This is the type of

object we are

accessing.



Setting the Arguments

1. In the Action pane, select the SetValue action for the “>0” condition.

2. Enter the Item & Expression code shown below:

3. In the Action pane, select the SetValue action for the “ISNULL” condition.


5. In the Action pane, select the SetValue action for the “=0” condition.


7. Save and close the macro.

Assign the Macro to the Form

Although “mainform” is the form that we open when we wish to enter data, “Tickets Purchased

Subform” is the form that contains the fields that are actually typed in. Therefore, we need to assign

our macro to “Tickets Purchased Subform” . Because the user may decide to enter data into the Ticket_Price or No_of_Tickets fields in a random order, we will assign the macro triggering

event to both fields.

1. On the Forms tab, open the form “Tickets Purchased Subform” in “Design View” .

2. Right mouse click the text box control “Ticket_Price” and select: Properties

3. In the Properties window, click the Event tab and select the On Lost Focus event.

4. Click the down arrow and select the macro: My Macros.DonationDiscount

5. Left mouse click the text box control “No_of_Tickets” .

6. For the On Lost Focus event, select the macro: My Macros.DonationDiscount

7. Save and close the form.

8. Open the form “mainform” in “Form View” .

8. Change the number of tickets purchased or ticket price to test the macro.

Note:

This macro is not without its faults. If the user happens to move the current record indicator in the

“Donation Info Subform” to a new record, Access will not see any donations.

Item: [Forms]![Main form]![Tickets Purchased Subform]![Total]

Expression: [Ticket_Price]*[No_of_Tickets]*0.9


Expression: [Ticket_Price]*[No_of_Tickets]


Expression: [Ticket_Price]*[No_of_Tickets]



MACRO: OPTION GROUPS

An Option Group is an interface that allows the user to pick from a

list of choices. When the user selects an option and clicks “Print” ,

a macro runs which prints the appropriate labels from a report called

“Customer Labels” . This report is based on a query called

“Donation Status” which in turn is based on two tables:

“Customer Info” & “Donor Info”

Examining the Query: Donation Status

The report (“Customer Labels”) was created using the Label Wizard , basing it on the query

“Donation Status”. The query is shown below:

Grouping is used because some patrons have made more than one donation and we do not want more

than one label for each person.

Status: IIF([Amount_Donated]Is Null,”Not Donated”,”Donated”)

The IIF Statement is combined with the IS NULL operator to look for blanks. When a blank is found,

the words “Not Donated” appear in the Status column. If it is not blank, “Donated” appears in the

Status column. These two choices will be used later on as a Where condition in the macro.

The query uses an outer join specifying that all

records in “Customer Info” and only matching

records from “Donor Info” are shown. Any

person who hasn’t make a donation will have a

blank in the Amount_Donated field.

Customer

Info

(Table)

Donation

Status

(Table)

Donation

Status (query)

Customer

Labels (Label

Report)



Step 1: Creating the Option Group

1. On the Forms tab, select the form “Switchboard”.

2. Select “Design View” and then click “Ok”.

3. On the Toolbox , verify that the “Control Wizards” icon is selected.

5. From the Toolbox , click on the “Option Group” icon.


7. In the pop-up window, type the options as shown:


9. Select either option concerning the default choice.


11. Leave the values set to: 1, 2, & 3 and click “Next” .

12. Select Option Buttons as the type of control and

choose any style you desire, then click “Next” .

13. For the Caption of the frame, type: Mailing Labels and click “Finish”.

14. Click the “Save” icon to save your form.

How Option Groups Work

When you place either radio buttons, check boxes, or toggle switches in an Option Group , only one of

the items can be selected at a time. When one is selected, the Option Group temporarily stores its

assigned value. In our case the three possible values are 1, 2, & 3. The Condition column of our

macro uses this later as a filter to determine which labels to print. Note that option choices must be

numbers.

Step 2: Naming the Option Group

Because we will be using the name of the option group in our macro code, we need to give it a

descriptive name prior to creating the macro.

1. Have the form “Switchboard” open in “Design View” .

2. Right mouse click the Option Group frame and select: Properties

(The title bar of the properties window should say: “Option Group: Frame [number]”)

3. Click on the All tab and scroll up to the Name line.

4. In the Name column type: LabelChoice

5. Close the Properties window and then save and close the form.



Step 3: Creating the Macro

1. Click on the Macros tab and then open “My Macros” in “Design View” .

2. Make sure both the Condition and Name columns are visible.

3. Skipping a row, place the cursor in the Names column below the last macro.

4. Create the macro shown below.

5. Save and close “My Macros” .

Macro Name Condition Action Description

Labels [LabelChoice]=1 OpenReport This “Open Report” line will print labels for all

patrons if the user selects option 1 (Print All

Labels” in the option group called

“LabelChoice”.

[LabelChoice]=2 OpenReport This “Open Report” line will print labels just for

Donators if the user selects option 2 (Print All

Labels” in the option group called

“LabelChoice”.

[LabelChoice]=3 OpenReport This “Open Report” line will print labels for non

donating patrons if the user selects option 3 (Print

All Labels” in the option group called

“LabelChoice”.

Note that the Where Condition tells Access to

look in the Status column of the query upon

which the “Customer Labels” report is based.

It then uses either “Donated” or “Not Donated”

as a criteria for deciding which labels to print.



Step 4: Assigning the Macro to the Labels Form Through a Button

In this step, we will create a button which will run the macro.

1. On the Forms tab, open the form “Switchboard” in “Design View” .

2. On the Toolbox , verify that the “Control Wizards” icon is selected.

3. In the Toolbox , select the “Command Button” and then click on the form.

4. Click in the Miscellaneous category and then select the action: Run Macro


6. Select the macro: My Macros.Labels and then click on “Next” .

7. Click in the Text box and type: PRINT


8. Name the button: Print_Labels then click “Finish” .



HIDING FORM CONTROLS

To limit the user’s ability to affect the form itself, you may wish to hide such things as Close buttons,

Scroll Bars , etc. This section shows how to alter the controls on the form.

1. Open the form “Switchboard” in “Design View” .

2. Right-mouse click the box in the form’s upper left corner to set the properties for

the entire form.

3. Select: Properties

4. Click on the Format tab.

5. Make the settings shown below:

6. Save the form and go into “Form View” to see the changes.

Views Allowed: Form -

Allows only form view. The other choice was “datasheet”.

Scroll Bars: Neither - Hides the scrollbars.

Record Selector: No - Hides the record selectors.

Navigation Buttons: No - Hides the navigation buttons.

Dividing Lines: No - Hides lines between records.

Auto Resize: Yes - Resizes the form to show all records.

Auto Center: Yes - Centers the from.

Control Box: No - Hides the control box.

Min Max Buttons: None: - Hides the Min/Max buttons.

Close Button: No: - Hides the close button on the form.

What’s This Button: No: - Hides the ? button.



SETTING STARTUP PARAMETERS Here we can have the switchboard load automatically when the file containing it is opened, hide menu

items, toolbars, and the database window, thus preventing users from going places we may not wish

them to be.

1. From the menu, click on: TOOLS - START UP

2. Click the “Advanced” button to see all options.

3. Set the Display Form to: Switchboard

4. Remove checks from all boxes. This will remove the users access to most Access menus and all

icons thus forcing them to use your switchboard and removing the chances of them harming your

database.

5. Click on “Ok” and close the database.

6. Reopen the database. Only your switchboard and a limited menu should be visible.

Temporarily Bypassing the Startup Limitations

To open the database without the startup limitations in effect, simply hold down the “SHIFT” key on

the keyboard while opening the database.

Removing Startup Limitations

To permanently remove the startup options, you must open the database with the “SHIFT” key held

down, and then choose from the menu: TOOLS - STARTUP and then place the checks

back in the boxes and remove the form “Switchboard” from Display Form .



HIDING DATABASE OBJECTS

If you need to display the Database window but would like to hide certain database objects such as

saved queries, tables, forms, reports, or macros, you can. This is a wise precaution if you have several

people working on a database and you do not wish them to delete one of your objects. This is a two

step procedure:

First: Hide the objects.

Second: Tell Access not to display hidden objects.

Step 1: Hide the Object:

1. Right-click the table, query, report, etc.. to be hidden.


3. Check the Hidden attribute and click on “Ok” .

(The object appears dimmer but still visible.)

Step 2: Don’t Display Hidden Objects:

1. From the menu, click on: TOOLS - OPTIONS.

2. Click on the View tab.

3. Remove the check from Hidden Objects and click on “Ok” .

The object should be invisible.

Unhiding Database Objects

To unhide a database object, you merely need to reverse the procedure above.

1. From the menu, click on: TOOLS - OPTIONS.

2. Click on the View tab.

3. Check Hidden Objects and click on “Ok” . (The object is now visible but dim.)

4. Right-click the table, query, report, etc., to be unhidden.


6 Uncheck the Hidden attribute and click on “Ok” . (The object is now completely visible.)

microsoft access xp (2002)info.marshall.usc.edu/dept/training/documents/applications/access/... ·...

Documents