intro into database ch 5

Implement a Many-to-Many Relationship

you learned that a many-to-many relationship exists when many rows of data in one table can be associated with many rows of data in a second table. After designing the tables, you still must create the tables and set the field properties in your Access database. -Build the tables on the "one" side of the relationship first. -Define the primary key for each of the "one" side tables. -Build the junction table. The foreign key fields should be the same data type and size as the matching field in the "one" side table. If the primary key in a "one" side table is an AutoNumberdata type, then the foreign key field should use a Number data type. Both fields should have the same number subtype, as indicated in the Field Size property. -Define the primary key for the junction table. Remember that this table represents the "many" side of each relationship.

Create a Database from a Desktop Database Template

- If you have found a template that is appropriate for your needs, you create a new database that is based on that template.

Error Handling

-The submacro displays a message box, which will show the description of the error that occurred. In this case, the submacro also contains an action to stop the execution of the macro. -The OnError action tells the macro what to do if an error occurs while the macro is executing. Because there are hundreds of different errors, including a list of all the possible errors is not practical. They range from "Division by zero" to "An expression you entered is the wrong data type for one of the arguments." Use the Access help feature to research an error description. Use the help information when you review the design of the macro to fix the problem. While submacros are not available in data macros, the OnError action is. A data macro can use either the Next or Fail options in the Go To argument (Figure 5-85).

Use the Macro Builder

:The Macro Builder has many easy-to-use features to help you write a macro. The five most important tasks are adding an action into a macro, editing an action, moving an action, copying an action and deleting an action. -Add a New Action: You must add all comments, program flow items, and actions into the macro window. The Action Catalog pane displays a list of available actions. You open and close the pane by clicking the Action Catalog button [Macro Tools Design tab, Show/Hide group]. Additionally, the Add New Action box displays a drop-down list of the available choices. -Edit an Action: You may need to change an action that you have already entered into the macro. -Move an Action: In a macro containing many actions, you may need to rearrange the order of the actions. -Copy an Action: Sometimes, instead of creating a new action, it is quicker to make a copy of an existing action and edit the copied action. -Delete an Action: If an action was entered in error or is no longer needed, you can delete it from the macro.

Edit Properties of the Lookup Field

A few of the properties are set automatically based on your answers to the Lookup Wizard. As desired, you may wish to modify some of those properties. If necessary, review the functions of those properties in the Create a Lookup Field section in SLO 2.4 Integrating Data Validation in Tables to determine if you need to edit any of the default settings. -Recall that the values in the Limit to List and Allow Value List Edits properties will be overridden by Access if referential integrity is enforced in the relationship. Do not change the Row Source Type and Row Source properties. These are set automatically by the Lookup Wizard and the set values ensure that the lookup field displays the current set of values from the related table.

Named Data Macros

A named data macro is associated with a specific table, but it is not attached to any particular event. Instead, a named data macro is run by another event-driven macro or by a stand-alone macro. -They can use all of the data block actions and all of the event actions described in the Data Macro Actions and Data Block Macro Actions tables. -They can use an additional action, SetReturnVar. SetReturnVar sets the value of a variable to be returned, or passed back, to the object that called the named data macro. -They can use parameters, if needed. Parameters pass a value or an object reference into a macro. The name you assign to the parameter (in the Name box) is the name you use to refer to the parameter inside your macro. -Named data macros are executed by using the RunDataMacro action to call the named data macro. Call a named data macro from an event-driven data macro or from a user interface macro.

User Interface Macros

A user interface macro can contain many different types of actions. Inside the Macro Builder, the available actions are grouped into eight different categories, which are explained in the User Interface Macro Actions table. The general purpose of each action can be inferred from the action name.

Edit or Delete a User Interface Macro

After a macro is built, you may need to edit the macro.

View, Edit, and Save a Table Relationship

After completing the Lookup Wizard, Access automatically creates a relationship between the two tables. However, the Lookup Wizard does not ask you about all the characteristics of a relationship. For example, the wizard does not ask if you want to Cascade Updates. As a result, you may need to view and edit the relationship. Additionally, you may want to view the relationship in the Relationships window to verify that you answered all the questions correctly during the Lookup Wizard. Finally, you may want to change the relationship if the needs of your organization change. It is important that all of the tables involved in the relationship are closed before you attempt to edit a relationship.

Use and Modify a Database

After creating a database based on a template, you will want to open the database to fully explore the different components and to see how it functions. Be sure to look at all of the features that have been built into the database. Explore the Database Functionality: This template also contains a Welcomeform (Figure 5-4) that opens automatically when the database is opened and the content is enabled. This form contains a video on how to use the Assets database. When you are using templates, take advantage of any videos and other help resources. They will help you to make the best use of your database. Explore the Navigation Pane, Objects, and Relationships: Many templates open with the Navigation Pane closed. Often this is because the needed functionality has been incorporated into the menu/navigation that is built into the forms. However, to analyze the database design, it may be necessary to view the contents of the Navigation Pane. Modify the Database: You may wish to make modifications that require skills that are covered in the remaining chapters of this book. Additionally, some of the objects in a template may even include features that are more advanced than will be covered in this book. You can use the Access help feature, or you can consult with someone who has advanced knowledge of those features.

Edit or Delete a Data Macro

After you build a data macro, there may be times when you need to edit it. Open the table in Datasheetview. Although the process of entering the changes into a macro doesn't vary, the process to re-open a macro does vary depending on whether it is an event-driven data macro or a named data macro.

Test and Debug a Macro

After you create a macro, it is important to test it to ensure that it performs as planned. With some macros you may not be sure that they are performing as expected unless you develop a specific plan to test the macro. This may involve creating test data and test scenarios in your database. As you are developing your databases, you may need to create or modify the sample data in the table(s) in order to test all possible conditions of a macro. With some macros you may discover that a logic error results from selecting the wrong event to trigger the macro. In addition to creating test data, other tools are available to help you debug macros. With user interface macros, you can debug a macro by adding error handling into a macro or by using the single step utility. With data macros, you can use error handling and the Application Log table.

Build a Named Data Macro

Create a named data macro when you need to have the same set of table actions executed from different objects or events, or if you want the actions to occur only from certain objects or events instead of all the time, or if you want to pass data into the macro. Named data macros use the Macro Builder.However, the macro window includes an additional component, shown in Figure 5-65, which you can use to add parameters into the macro

Build a Stand-Alone Macro

Create a stand-alone macro when you want to call a macro from several different objects or controls or when you want to create a macro that executes automatically when the database is first opened. -Use the Macro Builder to create a stand-alone macro. A macro that runs when a database is first opened is an AutoExec macro. This kind of macro is usually set up to perform some initial actions in the database, like opening the main form in the database, modifying settings to restrict user actions, and opening a login form. -To create a macro that executes automatically when the database is opened, save your stand-alone macro with the name AutoExec. -To bypass the execution of the AutoExec macro, a user can press and hold the Shift key when opening the database.

Build an Embedded Macro

Create an embedded macro when the actions of the macro are unique to a specific object. An embedded macro is built using the actions and events previously described. Before creating an embedded macro, you must first open the Property Sheet of the object or control.

Build an Event-Driven Data Macro

Create an event-driven data macro when the actions of the macro are unique to a specific event in a table. -After you have built the macro, the Ribbon of the Table Tools Table tab updates to highlight the event button where you attached the macro (Figure 5-64). A table can have macros attached to each of the different events. -An event-driven data macro executes automatically whenever the specified table event occurs in the database. For example, a Before Change event occurs every time data is changed in the table. This could be the addition of a brand new record, or a change in the values of an existing record.

Access Templates: Updated: Contacts

Description: Used to track information about people, such as team members, customers, vendors, etc. Contents: The database contains two tables, one query, three forms, two reports, one macro, and one module.

Access Templates: Updated: Students

Description: Used to track information about the students at a school, including details about their guardians. Contents: The database contains six tables, five queries, eight forms, eleven reports, three macros, and one module.

Access Templates: Updated: Task management

Description: Used to track information about work tasks your team needs to complete. Contents: The database contains four tables, six queries, seven forms, nine reports, three macros, and one module.

Access Templates: Updated: Asset tracking

Description:Used to track assets within an organization, such as computers or office equipment. Contents:The database contains two tables, one query, three forms, two reports, one macro, and one module.

Access Templates: Updated: Event management

Description:Used to track information about upcoming events. Contents:The database contains three tables, one query, four forms, five reports, and three macros.

Common Events in Access Objects and Controls: Click events

Event: 1.On Click 2.On Dbl Click Explanation:1.Executes after the object/control is clicked. 2.Executes after the object/control is double-clicked.

Common Events in Access Objects and Controls: Form/report events

Event: 1.On Open 2.On Activate 3.On Deactivate 4.On Close Explanation:1.Executes when a form or report is opened. If an object is already open, and the user simply clicks the object to make it the active object, the On Open event does not occur. Use the On Activateevent instead. 2.Executes after a form or report is already opened and the object gains the focus. 3.Executes when the form or report loses the focus to another object. 4.Executes after a form or report is closed.

Common Events in Access Objects and Controls: Focus-based events

Event:1.On Got Focus 2. On Lost Focus 3. On Enter 4.On Exit Explanation:1.Executes when an object/control receives focus. (Focus refers to the status of a control or object. The active control or object is said to "have the focus." When a user tabs into, or clicks, a control, the On Got Focus event occurs.) 2.Executes when an object/control loses focus. 3.Used only with text box controls. This event happens immediately before the text box obtains the focus. This can be used to provide instructions before that control becomes active. 4.Executes immediately before a control, on a form or report, loses the focus to another control on the same form or report. You can use the On Exitevent to verify that an entry is correct before leaving the field. If it isn't correct, you can include an action in the macro to cancel the exit from happening.

Common Events in Access Objects and Controls: Keyboard events

Event:1.On Key Up 2.On Key Down 3.On Key Press Explanation:1.Executes when the user releases a key that he or she was pressing. The On Key Upevent contains parameters to determine which key was pressed and whether another key was also being pressed. 2.Executes when a key is pressed. The On Key Downevent contains parameters to determine which key was pressed and whether the user was also pressing another key. 3.Executes when a key is pressed, but the On Key Pressevent only contains a parameter about which key was pressed, not whether additional keys were also pressed.

Common Events in Access Objects and Controls: Mouse events

Event:1.On Mouse Down 2.On Mouse Up 3.On Mouse Move Explanation:1.Executes when the mouse is positioned over an object/control and the user presses a mouse button. The On Mouse Down event contains parameters to allow you to determine which mouse button was pressed, whether another key (like Shift) was pressed while pressing the mouse button, and the location of the mouse pointer. 2.Executes when a mouse button is released after having been pressed. The On Mouse Upevent contains parameters to allow you to determine if the user pressed another key while pressing the mouse, along with the location of the mouse pointer. 3.Executes when the user moves the mouse over an object/control.

User Interface Macros-Actions Category Folder: Database Objects

Explanation: Related to changing controls and objects in the database. Actions: GoToControl, GoToPage, GoToRecord, OpenForm, OpenReport, OpenTable, PrintObject, PrintPreview, RepaintObject, SelectObject, SetProperty

User Interface Macros-Actions Category Folder: Data Entry Operations

Explanation: Related to changing the data. Actions: DeleteRecord, EditListItems, SaveRecord

User Interface Macros-Actions Category Folder: System Commands

Explanation: Related to changing the database system. Actions:Beep, CloseDatabase, DisplayHourglassPointer, QuitAccess

User Interface Macros-Actions Category Folder: User Interface Commands

Explanation: Related to controlling what is shown on the screen. Actions: AddMenu, BrowseTo, LockNavigationPane, MessageBox, NavigateTo, Redo, SetDisplayedCategories, SetMenuItem, UndoRecord

User Interface Macros-Actions Category Folder: Filter/Query/Search

Explanation: Related to filtering, querying, and searching records. Actions: ApplyFilter, FindNextRecord, FindRecord, OpenQuery, Refresh, RefreshRecord, RemoveFilterSort, Requery, SearchForRecord, SetFilter, SetOrderBy, ShowAllRecords

User Interface Macros-Actions Category Folder: Data Import/Export

Explanation: Related to importing, exporting, sending, and collecting data. Actions: AddContactFromOutlook, EMailDatabaseObject, ExportWithFormatting, SaveAsOutlookContact, WordMailMerge

User Interface Macros-Actions Category Folder: Macro Commands

Explanation: Related to making changes to the macro. Actions: CancelEvent, ClearMacroError, OnError, RemoveAllTempVars, RemoveTempVar, RunCode, RunDataMacro, RunMacro, RunMenuCommand, SetLocalVar, SetTempVar, SingleStep, StopAllMacros, StopMacro

User Interface Macros-Actions Category Folder: Window Management

Explanation: Related to managing the database windows. Actions: CloseWindow, MaximizeWindow, MinimizeWindow, MoveAndSizeWindow, RestoreWindow

The Application Log table

In data macros the Application Log table is available to help you debug a macro. Inside a database, Access maintains a variety of tables with information about the database. These tables do not appear in the Navigation Pane. The Application Log table (Figure 5-88) contains data related to errors encountered when executing data macros. You can refer to the information in the table to determine what happened when the macro executed. -To view the Application Log table, click the Info button [File tab] and then select the View Application Log Table button.

Pass Values into Parameters

In the Macro Name argument, if you enter a macro that contains parameters, the RunDataMacro action changes and requires you to fill in the values for each parameter. -As part of the macro design process, you should have already determined what fields or controls will have the parameter values. If the macro is being called from a form, it is common to have the parameter values come from text boxes on the form. If the macro is being called from a data macro of a table, then you may want the parameter values to come from the table fields. -Enter the name of the text box control or table field names into the parameter arguments (Figure 5-83). In the example shown here, the parameters come from two text boxes on a form. This macro is called from an embedded macro attached to the OnClick event of a button on the form.

Creating and Using User Interface Macros

Many of the macros you build will be attached to different parts of your database's user interface. Access has two types of user interface macros: stand-alone macros and embedded macros. -A "stand-alone macro" is a separate Access object that appears in the Navigation Pane. You can use a stand-alone macro independently of any other object. Additionally, you can assign it to events in many different objects, allowing you to reuse the same macro. For example, you may want to have the same message box display in many different forms or reports. Create a stand-alone macro to display the message box and then attach that macro to each form or report. -An "embedded macro" is attached to one specific object or control on an object. Embedded macros do not display in the Navigation Pane. They are automatically executed by the object or control. Embedded macros are useful to automate tasks that are unique to a specific form or report in the database.

Add Conditions to a Macro

Many times, the task you are automating with a macro involves asking a question or making a comparison to test a condition. You can add an If block into a macro to allow you to test a condition and then perform different actions based on the result of the test.

The Purpose of a Macro

Most macros execute because an event occurred in the database. An event is something that occurs when an action happens to an object or control. Events are occurring all of the time when you use a database -User Interface macros: are attached to objects and controls, such as forms, reports, and command buttons, that are part of the user interface. They automate actions such as opening objects, customizing form information based on a data value, and displaying message boxes. -Data macros: are attached to tables. These macros are used with table events such as adding, changing, or deleting data. They can help ensure the accuracy of the data in the tables by comparing values across tables or automating tasks based on a change to the data.

Build a Submacro

Occasionally you may need to create a submacro, which is a named, executable set of actions that perform a specific task. Once you create a submacro, you can call that submacro when you need to execute that task. Creating submacros can be a useful organizational approach; you end up with fewer separate, smaller macros when you use submacros. -You can only create a submacro in a stand-alone macro, and the submacro must appear at the end of the stand-alone macro. The only code that can follow a submacro is another submacro.

Delete a Relationship Created with a Lookup Field

Occasionally, you may need to remove a relationship that was not created correctly. If the relationship was the result of using the Lookup Wizard, you most likely will need to delete both the relationship and the lookup attached to the field.

Join Properties

Once a relationship is created, Access uses the properties of the relationship when a query is created. The join type property determines how to match the data in the two related tables. -Inner Join: An inner join returns only records that have matching values in both tables. An inner join is the most common type of join. -Left Outer Join: A left outer join returns all the records from the table on the left side of the join and only the records that have a matching value from the table on the right side of the join. -Right Outer Join: A right outer join returns all the records from the table on the right side of the join and only the records that have a matching value from the table on the left side of the join. -Change the Join Type: When a relationship is created, Access assigns it an inner join type. If the queries that will use this join are more likely to need one of the other two join types, you can change the join type of the relationship. Changing a relationship's join type only affects the way queries, and any object built from a query, work.

Access Templates

Some of the Access templates are designed to work on the web. When you create a database built from one of these templates, you must link it to a SharePoint server running Access Services. -You can choose from several templates that are included with Access. Additionally, other templates are available on Office.com.

Use a Lookup with Multiple Fields

The lookup fields you have created so far have all displayed one field in the lookup list. Access allows you to display more than one field. This is useful when the field being displayed is not very intuitive for the user. This is often the case when an ID value is used as the primary key. -Although Access recommends hiding the key column, it is a better design practice to actually store that key column and just display the additional fields in the list.

EditRecord Data Block

The EditRecord data block edits the values of an existing record. Because this action appears within the ForEachRecord block, it operates on the data specified in that block. Alias is an optional argument that you can use to provide an alternate name to refer to the record.

ForEachRecord Data Block

The ForEachRecord data block repeats the listed actions for each record. The ForEachRecord data block has two arguments, which we explain in the following material; only the In argument is required. -The In argument can contain a table name, a query name, or an SQL statement. This argument tells Access what data to use. -The Where Condition argument can contain an expression to limit the records that are affected. If this condition is omitted, all the records are processed. -As desired, an Alias argument can also be added. An alias provides an alternate, usually shorter name, for the table or query specified in the In argument.

The Single Step Tool

The Macro Builder contains the Single Steptool, which can execute the macro one action, or step, at a time. When a macro executes in Single Step mode, the Macro Single Stepdialog box displays (Figure 5-86). The dialog box displays the Macro Name, Condition, Action Name, Arguments of the action, and the Error Number. If no error occurred in a step, the error number is 0. The condition box contains information only when the action is part of an If block. Depending on the information you see in the dialog box, you select one of the three buttons: -Step: Advances the macro to the next step. -Stop All Macros: Stops the execution of all macros that are running. This leaves the macro in Single Step mode the next time the macro executes. -Continue: Exits Single Step mode and continues the execution of the macro. Since Single Step mode has been turned off, if you want to debug the macro in the future you need to turn it back on.

The Macro Builder and Macro Design

The steps to initiate a new macro vary depending on the type of macro. Those steps are covered separately in SLO 5.5: Creating and Using User Interface Macros and SLO 5.6: Building and Using Data Macros. However, independently of the way a macro is created, you write and edit the content of all macros using the Macro Builder (Figure 5-44). -Macro design: macro window, Action Catalog, program flow, Action, Comment, Arguments, group, If block, submacro

Run a Stand-Alone Macro

There are three different ways to run a stand-alone macro: -Double-click the macro name in the Navigation Pane. -Call the macro from another macro using the RunMacro or OnError macro action. Enter the name of the macro in the Macro Name argument of the RunMacro or OnError action. -Attach the macro to an event property of an object or control.

Add Parameters to a Named Data Macro

Using a parameter in a named data macro allows you to pass data into the macro. The data can come from another data macro, a form, or a user interface macro. Parameters increase the power and usability of data macros. When you create a new named data macro, the macro window includes a "parameter area" at the top of the macro. You assign a name to each parameter you add. Inside the macro you refer to the data value using the parameter name.

Data Macros

You can use data macros to ensure that the data in tables is accurate. Additionally, you can use them to automate tasks related to adding, deleting, or changing data. Because these macros are attached to a table, they execute any time a table's data is updated. You can create data macros to respond to any of the five events in the following table:

Define a Relationship with the Lookup Wizard

You create relationships after the foreign keys have been added as fields into the related tables. While you can add data into the table on the "one" side of the relationship, you should create the relationships before any data is added into the tables that contain the foreign keys. This helps ensure that all the data in the related tables has referential integrity.