MS Access quick tutorial and basics for developing an automated database application -
Microsoft (office) Access is a powerful PC based database solution and is a member of the Microsoft Office Suite. It allows you to quickly develop customized database solution for your organization. It gives you powerful control of your data, enabling you to filter, sort, and report your data easily. You can import data from various sources, and automate the process. It can also make data entry more easier and accurate.
Microsoft (MS) Access is a relational database system that offers a powerful graphical user interface that makes easy use for novice users to quickly get started. And for more advanced users a development environment that is fully programmable with SQL (Select Query languages) for manipulating and calculating data and VBA (Visual Basic for Applications) for developing a user interface, automating processes and processing data through the use of procedures (a series of programming instructions that perform a actions in the database) or functions (programmable calculations). MS Access can be linked to dynamically retrieve and send data to MS SQL Server, Oracle, or any ODBC enabled data container, including MySQL and PostgreSQL.
Before you create an Access database, you must answer the following questions:
What is the purpose of this database and who will use it?
What tables (data) will this database contain?
What queries and reports do the users of this database need?
What forms do you need to create?
Answering the above questions will lead you to a good database design, and help you create a database that is useful and usable.
MS Access provides two ways Access database (MS Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The MS Jet database engine manages the data.).
You can create your database manually, creating and customizing each object one by one, and creating Macros and Modules to automate and process functions.
OR, you can create a database by using the Database wizard. The wizard lets you choose from one of the built-in templates, and customize it to some extent. Then it creates a set of tables, queries, forms, and reports, and also a switchboard for the database. The tables do not have any data in them. Use this method if one of the built-in templates closely match your requirements.
You can use a Database Wizard to create in one operation the required tables, forms, and reports for the type of database you choose — this is the easiest way to start creating your database. The wizard offers limited options to customize the database.
- Click New on the toolbar.
- In the New File task pane, under Templates, click On my computer.
- On the Databases tab, click the icon for the kind of database you want to create, and then click OK.
- In the File New Database dialog box, specify a name and location for the database, and then click Create.
- Follow the instructions in the Database Wizard.
When you create a database, you store your data in tables — subject-based lists that contain rows and columns. For instance, you can create a Contacts table to store a list of names, addresses, and telephone numbers, or a Products table to store information about products.
Access databases depend heavily on tables, you should always start your design of a database by creating all of its tables and then creating other objects. Before you create tables, carefully consider your requirements and determine all the tables that you need.In an Access database, table properties are attributes of a table that affect the appearance or behavior of the table as a whole. Table properties are set in the table's property sheet, in Design view. For example, you can set a table's Default View property to specify how the table is displayed by default.
A field property applies to a particular field in a table and defines one of the field's characteristics or an aspect of the field's behavior. You can set some field properties in Datasheet view (Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.). You can also set any field property in Design view by using the Field Properties pane.
Every field has a data type. A field's data type indicates the kind of data that the field stores, such as large amounts of text or attached files.
Text: Use for text or combinations of text and numbers, such as addresses, or for numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. Stores up to 255 characters. The FieldSize property controls the maximum number of characters that can be entered.
Memo: Use for lengthy text and numbers, such as notes or descriptions. Stores up to 63,999 characters.
Number: Use for data to be included in mathematical calculations, except calculations involving money (use Currency type). Stores 1, 2, 4, or 8 bytes; stores 16 bytes for Replication ID (GUID). The FieldSize property defines the specific Number type.
Date/Time: Use for dates and times, stores 8 bytes.
Currency: Use for currency values and to prevent rounding off during calculations. Stores 8 bytes.
AutoNumber: Use for unique sequential (incrementing by 1) or random numbers that are automatically inserted when a record is added. Stores 4 bytes; stores 16 bytes for Replication ID (GUID).
Yes/No: Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off. Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) values are not allowed.
Stores 1 bit.
OLE Object: Use for OLE objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data) that were created in other programs using the OLE (OLE: A program-integration technology that you can use to share information between programs. All Office programs support OLE, so you can share information through linked and embedded objects.) protocol. Stores up to 1 gigabyte (limited by disk space).
Hyperlink:
Use for hyperlinks (hyperlink: Colored and underlined text or a graphic that you click to go to a file, a location in a file, a Web page on the World Wide Web, or a Web page on an intranet. Hyperlinks can also go to newsgroups and to Gopher, Telnet, and FTP sites.). A hyperlink can be a UNC path (universal naming convention (UNC): A naming convention for files that provides a machine-independent means of locating the file. Rather than specifying a drive letter and path, a UNC name uses the syntax \\server\share\path\filename.) or a URL (Uniform Resource Locator (URL): An address that specifies a protocol (such as HTTP or FTP) and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet, for example: http://www.microsoft.com/.). Stores up to 2048 characters.
Lookup Wizard: Use to create a field that allows you to choose a value from another table or from a list of values using a combo box—-choosing this option in the data type list starts a wizard to define this for you. Requires the same storage size as the primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) that corresponds to the Lookup field (Lookup field: A field, used on a form or report in an Access database, that either displays a list of values retrieved from a table or query, or stores a static set of values.)—-typically 4 bytes.
An index helps MS Access find and sort records faster. Access uses indexes in a table as you use an index in a book: to find data, it looks up the location of the data in the index. You can create indexes based on a single field or on multiple fields. Multiple-field indexes enable you to distinguish between records in which the first field may have the same value.
Deciding which fields to index:
You'll probably want to index fields you search frequently, fields you sort, or fields that you join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) to fields in other tables in queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.). However, indexes can slow down some action queries (action query: A query that copies or changes data. Action queries include append, delete, make-table, and update queries. They are identified by an exclamation point (!) next to their names in the Database window.) such as append queries (append query: An action query that adds the records in a query's result set to the end of an existing table.), when the indexes for many fields need to be updated while performing these operations.
Primary Key:
The primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) of a table is automatically indexed, and you can't index a field whose data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) is OLE Object (OLE Object data type: A field data type that you use for objects created in other applications that can be linked or embedded (inserted) in an Access database.).
For other fields, you should consider indexing a field if all the following apply:
- The field's data type is Text, Number, Currency, or Date/Time.
- You anticipate searching for values stored in the field.
- You anticipate sorting values in the field.
- You anticipate storing many different values in the field. If many of the values in the field are the same, the index may not significantly speed up queries.
- Multiple-field indexes
If you think you'll often search or sort by two or more fields at a time, you can create an index for that combination of fields. For example, if you often set criteria for LastName and FirstName fields in the same query, it makes sense to create a multiple-field index on both fields.
When you sort a table by a multiple-field index, MS Access sorts first by the first field defined for the index. If there are records with duplicate values in the first field, MS Access sorts next by the second field defined for the index, and so on.After you've set up different tables for each subject in your MS Access database you need a way of telling MS Access how to bring that information back together again. The first step in this process is to define Relationships (Relationship: An association that is established between common fields (columns) between tables. A Relationship can be one-to-one, one-to-many, or many-to-many between your tables. After you've done that, you can create queries, forms, and reports to display information from several tables at once. This coordination between tables is accomplished with relationships between tables. A relationship works by matching data in key fields— usually a field with the same name in both tables. In most cases, these matching fields are the primary key.
One-to-Many Relationship:
A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.When a one-to-many relationship is created if only one of the related fields is a primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) or has a unique index (unique index: An index defined by setting a field's Indexed property to Yes (No Duplicates). A unique index will not allow duplicate entries in the indexed field. Setting a field as the primary key automatically defines the field as unique.).
A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes.
A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields— the foreign keys (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) from the two other tables.
Many-to-Many Relationship:
In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) consists of two fields— the foreign keys (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table.
One-to-One Relationship:
In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table.
Indeterminate Relationship:
If you drag a field that isn't a primary key and doesn't have a unique index to another field that isn't a primary key and doesn't have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, MS Access displays a default join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) line between the tables, but referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.) won't be enforced, and there's no guarantee that records are unique in either table.
Referential integrity:
Referential integrity is a system of rules that MS Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data. You can set referential integrity when all of the following conditions are met:
- The matching field from the primary table (primary table: The "one" side of two related tables in a one-to-many relationship. A primary table should have a primary key and each record should be unique.) is a primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) or has a unique index (unique index: An index defined by setting a field's Indexed property to Yes (No Duplicates). A unique index will not allow duplicate entries in the indexed field. Setting a field as the primary key automatically defines the field as unique.).
- The related fields have the same data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).). There are two exceptions. An AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field can be related to a Number field with a FieldSize property setting of Long Integer, and an AutoNumber field with a FieldSize property setting of Replication ID can be related to a Number field with a FieldSize property setting of Replication ID.
- Both tables belong to the same MS Access database. If the tables are linked tables (linked table: A table stored in a file outside the open database from which Access can access records. You can add, delete, and edit records in a linked table, but you cannot change its structure.), they must be tables in Microsoft Access format, and you must open the database in which they are stored to set referential integrity. Referential integrity can't be enforced for linked tables from databases in other formats.
- You can't enter a value in the foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) field of the related table that doesn't exist in the primary key of the primary table. However, you can enter a Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) value in the foreign key, specifying that the records are unrelated.
In MS Access you use queries to view, change, and analyze data in different ways. You can also use them as a source of records for forms, reports, and data access pages (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.). There are several types of queries in MS Access.
Select queries:
A select query is the most common type of query. It retrieves data from one or more tables and displays the results in a datasheet where you can update the records (with some restrictions). You can also use a select query to group records and calculate sums, counts, averages, and other types of totals.
Parameter queries:
A parameter query is a query that when run displays its own dialog box prompting you for information, such as criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) for retrieving records or a value you want to insert in a field. You can design the query to prompt you for more than one piece of information; for example, you can design it to prompt you for two dates. Access can then retrieve all records that fall between those two dates.
Parameter queries are also handy when used as the basis for forms, reports, and data access pages. For example, you can create a monthly earnings report based on a parameter query. When you print the report, Access displays a dialog box asking for the month that you want the report to cover. You enter a month and Access prints the appropriate report.
Crosstab queries:
You use crosstab queries to calculate and restructure data for easier analysis of your data. Crosstab queries calculate a sum, average, count, or other type of total for data that is grouped by two types of information — one down the left side of the datasheet and another across the top.
Action queries -
An action query is a query that makes changes to or moves many records in just one operation. There are four types of action queries:Delete Queries: A delete query deletes a group of records from one or more tables. For example, you could use a delete query to remove products that are discontinued or for which there are no orders. With delete queries, you always delete entire records, not just selected fields within records.
Update Queries An update query makes global changes to a group of records in one or more tables. For example, you can raise prices by 10 percent for all dairy products, or you can raise salaries by 5 percent for the people within a certain job category. With an update query, you can change data in existing tables.
Append Queries: An append query adds a group of records from one or more tables to the end of one or more tables. For example, suppose that you acquire some new customers and a database containing a table of information on those customers. To avoid typing all this information into your own database, you'd like to append it to your Customers table.
Make-Table Queries: A make-table query creates a new table from all or part of the data in one or more tables. Make-table queries are helpful for creating a table to export to other MS Access databases (MS Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) or a history table that contains old records.The power of queries lies in being able to bring together or perform an action on data from more than one table or query. For example, you might want to view a customer's information with the orders the customer placed. To see this information, you need data from the Customers and Orders tables.
When you add more than one table or query to a query, you need to make sure their field lists (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) are joined to each other with a join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) line so that MS Access knows how to connect the information.
Join line
If you previously created relationships MS Access automatically displays join lines when you add related tables in query Design view. If referential integrity is enforced, MS Access also displays a "1" above the join line to show which table is on the "one" side of a one-to-many relationship and an infinity symbol to show which table is on the "many" side.Types of joins -
Inner joins:
Once tables and queries are joined, and you've added fields from both tables or queries to the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) in query Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.), the default join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) tells the query to check for matching values in the join fields. This is called an inner join (inner join: A join where records in two tables are combined in a query's results only if values in the joined fields meet a specified condition. In a query, the default join is an inner join that selects records only if values in the joined fields match.) When it finds matches, it combines those two records and displays them as one record in the query's results.
Outer joins:
If one table or query doesn't have a matching record in the other table or query, neither record appears in the query's results. If you want the query to select all the records from one table or query whether or not it has matching records in the other table or query, you can change the join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) type to an outer join (outer join: A join in which each matching record from two tables is combined into one record in the query's results, and one table contributes all of its records, even if the values in the joined field don't match those in the other table.).
Unequal joins:
If you want the query to select records based on the value in the join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) field being greater than, less than, not equal to, greater than or equal to, or less than or equal to the value in the other join field, you must create an SQL statement (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.) in SQL view (SQL view: A window that displays the SQL statement for the current query or that is used to create an SQL-specific query (union, pass-through, or data definition). When you create a query in Design view, Access constructs the SQL equivalent in SQL view.).A form is a type of a database object that is primarily used to enter or display data in a database. You can also use a form as a switchboard that opens other forms and reports in the database, or as a custom dialog box that accepts user input and carries out an action based on the input.
- A data-entry form
- A switchboard form
- A custom dialog box
Most forms are bound to one or more tables and queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) in the database. A form's record source (record source: The underlying source of data for a form, report, or data access page. In an Access database, it could be a table, query, or SQL statement. In an Access project, it could be a table, view, SQL statement, or stored procedure.) refers to the fields in the underlying tables and queries. A form need not contain all the fields from each of the tables or queries that it is based on.
A bound form stores or retrieves data from its underlying record source. Other information on the form, such as the title, date, and page number, is stored in the form's design:
- Graphic elements, such as lines and rectangles, are stored in the form's design.
- Data comes from the fields in the underlying record source.
- A calculation comes from an expression, which is stored in the form's design.
- Descriptive text is stored in the form's design.
You create a link between a form and its record source by using graphical objects called controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program.
You use controls to display data or choices, perform an action, or make the user interface easier to read.). The most common type of control used to display and enter data is a text box:
- Labels display descriptive text.
- Text boxes display data from and enter data into the Products table.
- A text box uses an expression to calculate a total.
- You can also open a form in PivotTable view or PivotChart view to analyze data. In these views, you can dynamically change the layout of a form to present data in different ways. You can rearrange row headings, column headings, and filter fields until you achieve the desired layout. Each time you change the layout, the form immediately recalculates the data based on the new arrangement.
You can create a variety of different reports in MS Access ranging from the simple to the complex. Begin by thinking about your report's record source. Whether your report is a simple listing of records or a grouped summary of sales by region, you must first determine which fields contain the data you want to see in your report, and in which tables or queries they reside.
After you choose your record source, you will usually find it is easiest to create your report by using the Report Wizard. The Report Wizard is a feature in Access that guides you through a series of questions and then generates a report based on your answers.
Choose a record sourceA report consists of information that is pulled from tables or queries, as well as information that is stored with the report design, such as labels, headings, and graphics. The tables or queries that provide the underlying data are also known as the report's record source. If the fields that you want to include all exist in a single table, use that table as the record source. If the fields are contained in more than one table, you need to use one or more queries as the record source. Those queries may already exist in your database, or you may need to create new queries specifically to fit the needs of your report. You can find links to more information about queries in the See Also section.
Understand the report sectionsIn MS Access, the design of a report is divided into sections. You can view your report in Design view to see its sections. To create useful reports, you need to understand how each section works. For example, the section in which you choose to place a calculated control determines how Access calculates the results. The following list is a summary of the section types and their uses:
Report Header: This section is printed just once, at the beginning of the report. Use the report header for information that might normally appear on a cover page, such as a logo, a title, or a date. When you place a calculated control that uses the Sum aggregate function in the report header, the sum calculated is for the entire report. The report header is printed before the page header.
Page Header: This section is printed at the top of every page. For example, use a page header to repeat the report title on every page.
Group Header This section is printed at the beginning of each new group of records. Use the group header to print the group name. For example, in a report that is grouped by product, use the group header to print the product name. When you place a calculated control that uses the Sum aggregate function in the group header, the sum is for the current group.
Detail This section is printed once for every row in the record source. This is where you place the controls that make up the main body of the report.
Group Footer: This section is printed at the end of each group of records. Use a group footer to print summary information for a group.
Page Footer: This section is printed at the end of every page. Use a page footer to print page numbers or per-page information.
Report Footer: This section is printed just once, at the end of the report. Use the report footer to print report totals or other summary information for the entire report.
Understand Report Controls
Controls are objects that display data, perform actions, and let you view and work with information that enhances the user interface, such as labels and images. Access supports three types of controls: bound, unbound, and calculated:
Bound control: A control whose source of data is a field in a table or query is a bound control. You use bound controls to display values from fields in your database. The values can be text, dates, numbers, Yes/No values, pictures, or graphs. A text box is the most common type of bound control.
Unbound control: A control that doesn't have a source of data (a field or expression) is an unbound control. You use unbound controls to display information, lines, rectangles, and pictures. For example, a label that displays the title of a report is an unbound control.
Calculated control A control whose source of data is an expression rather than a field is a calculated control. You specify the value that you want in the control by defining an expression as the source of data for the control. An expression is a combination of operators (such as = and + ), control names, field names, functions that return a single value, and constant values.Macros are a set of actions that you can create to help you to automate common tasks. By using groups of macros, you can perform several tasks at once.
A macro is a set of one or more actions (action: The basic building block of a macro; a self-contained instruction that can be combined with other actions to automate tasks. This is sometimes called a command in other macro languages.) that each perform a particular operation, such as opening a form or printing a report. Macros can help you to automate common tasks. For example, you can run a macro that prints a report when a user clicks a command button.
When you create a macro, you enter the actions you want to carry out in this portion of the Macro window (Macro window: The window in which you create and modify macros.). You can specify arguments for an action in this portion of the window.
A macro can be one macro composed of a sequence of actions, or it can be a macro group (macro group: A collection of related macros that are stored together under a single macro name. The collection is often referred to simply as a macro.). You can also use a conditional expression (conditional expression: An expression evaluated and compared to a value; for example, If...Then and Select Case statements. If the condition is met, one or more operations are performed. If it isn't met, the operation is skipped.) to determine whether in some cases an action will be carried out when a macro runs.
The following macro is composed of a series of actions. MS Access carries out these actions each time the macro runs. To run this macro, you refer to the macro name Review Products.
Macro Group
If you have numerous macros, grouping related macros in macro groups (macro group: A collection of related macros that are stored together under a single macro name. The collection is often referred to simply as a macro.) can help you to manage your database more easily.
The name in the Macro Name column identifies each macro. When you run a macro in a macro group, Microsoft Access carries out the action in the action column and any actions that immediately follow with a blank Macro Name column.
You can run a macro in a macro group in an event or event procedure (event procedure: A procedure that is automatically executed in response to an event initiated by the user or program code, or that is triggered by the system.) by typing the macro group name followed by a period and then the macro name.
Conditional actions
In some cases, you may want to carry out an action or series of actions in a macro only if a particular condition (condition: Part of the criteria that a field must meet for searching or filtering. Some conditions must be used with a value; for example, the field Author with the condition equals with the value Jane.) is true. For example, if you're using a macro to validate data in a form, you might want to display one message in response to one set of values entered in a record and another message in response to a different set of values. In cases like these, you can use conditions to control the flow of the macro.
A condition is a logical expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) that evaluates to True/False or Yes/No. The macro follows different paths depending on whether the condition is true or false.
When you run the macro, MS Access evaluates the first conditional expression (conditional expression: An expression evaluated and compared to a value; for example, If...Then and Select Case statements. If the condition is met, one or more operations are performed. If it isn't met, the operation is skipped.). If the condition is true, Microsoft Access carries out the action in that row and any of the immediately following actions that are preceded by an ellipsis (...) in the Condition column.
MS Access then runs any additional actions in the macro that have a blank Condition column until it reaches another expression, a macro name, or the end of the macro.
If the condition is false, MS Access ignores the action and any immediately following actions that are preceded by an ellipsis in the Condition column and moves to the next action row (action row: A row in the upper part of the Macro window in which you enter macro names, actions, conditions, and comments associated with a particular macro or macro group.) that contains another condition or a blank Condition column.
The following macro runs the MsgBox and the StopMacro actions only when the expression in the Condition column is true (when there is a Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) value in the SupplierID field).
Action arguments
Action arguments are additional information required by some macro actions— for example, the object affected by the action or special conditions under which the action is carried out. After you add an action to a macro, you set the arguments for the action in the lower portion of the Macro window (Macro window: The window in which you create and modify macros.). These arguments give Microsoft Access additional information on how to carry out the action.
Tips for setting action arguments:
In general, it's a good idea to set action arguments in the order they're listed, because choices for one argument may determine those for arguments that follow. If you add an action to your macro by dragging a database object (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.) from the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), Microsoft Access automatically sets appropriate arguments for that action.
If an action has an argument that calls for the name of a database object, you can set the argument and the corresponding object type argument automatically by dragging the object from the Database window to the argument box. You can use an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) preceded by an equal sign (=) to set many action arguments.
Copyright © 2008 ACC Technology. All rights reserved. Privacy Statement
Products