Trending ▼   ResFinder  

quantitative

65 pages, 0 questions, 0 questions with responses, 0 total responses,    0    0
davinder84
   		+Fave Message
ProfileTimelineUploads
 Home > davinder84 >

Formatting page ...

CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Access 2007 Quick Reference T his Quick Reference is intended to support your efforts in database management and database application development by supplying easily accessed, in-depth information arranged by activity. This is not intended as a substitute for the step-by-step procedures described in the chapters in the main part of this book. Instead, it is a tool to help you find answers to specific questions. For example, if you need help creating just the right filter to retrieve records from a table or query, refer to the Retrieving and Distributing Information section. If you need help with the syntax of a specific macro action, look in the Programming with Macros and SQL section. The following paragraphs briefly describe the contents of each section in this Quick Reference. The first section, Storing Information, provides details about creating the structure for storing, entering, and viewing data, including the following topics: Database specifications Database templates available in Office Access 2007 Database objects and properties Relationships and joins Tables and indexes, including data types and field properties Display formats and input masks User Interactions addresses the ways the user can interact with Access objects to carry out actions and create forms for data management, including the following topics: Using built-in ribbon commands Creating forms and reports, including lists of form, report, section, and control properties Retrieving and Distributing Information discusses ways to extract specific records from a table by applying filters or running queries. It also includes help with creating expressions. This section includes the following specific topics: Filtering records Creating expressions Creating queries, including query properties, specifications, and aggregate functions Printing reports, including setting page options and controlling the print process Information Exchange discusses the ways you can exchange information with other Access databases or other applications, including the following topics: Using compatible external file formats Setting import and export specifications 1 QR.indd 1 1/4/07 2:51:50 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 2 Microsoft Office Access 2007: The Complete Reference Changing the Access Environment describes how to alter the Access workplace with custom features, including the following topics: Using built-in wizards, builders, and managers Setting default options Programming with Macros and SQL discusses the basic aspects of two programming languages that you can use within Access: Macros and SQL. Specifically, the topics covered in this section include the following: Macro specifications Macro actions and their arguments SQL SELECT statement syntax Storing Information This section contains lookup information relevant to the topics covered in Chapters 1 through 6. You can find information about such subjects as database specifications and templates, database and table properties, the definition and illustration of relationships and joins, the rules that govern table construction and field definition, and a complete inventory of toolbars, toolbar buttons, and shortcut keys. Database Specifications Access places certain maximums on several database attributes. Table QR-1 lists the imposed limits. Attribute Maximum Permitted File size (.accdb file) 2 GB, or limited by available storage space if you have linking tables Total number of objects in the database, including tables, queries, forms, reports, data access pages, macros, and modules 32,768 objects Number of modules, including the number of forms and reports with Has Module properties set to True, whether procedures exist or not 1000 modules Number of characters in an object name 64 characters Number of characters in a password 14 characters Number of characters in a user or group name 20 characters Number of concurrent users 255 users TABLE QR- 1 Limits on Database Attributes QR.indd 2 1/4/07 2:51:50 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Category Templates Business 3 Assets Contacts Events Issues Marketing Projects Projects Sales Pipeline Tasks Education Faculty Students Personal Contacts Sample Northwind 2007 TABLE QR- 2 Predesigned Database Templates Database Templates Access provides many predesigned database templates that you can use to create a new database. Choose among four categories of templates from Microsoft Office Online under the Template Categories title bar: Business, Education, Personal, and Sample. Table QR-2 lists the templates that are available in each of these categories. Database Objects A database is an organized collection of related information used for a specific purpose. Access is an object-oriented database management program in which a database contains six types of objects: Tables contain all the data in the database and are related to form a complete source of information for an organization. Queries extract specific data from a specific set of records from one or more tables. Forms are used to view and enter data in an organized and often graphical way. Reports are used to present printed information, often grouped and organized to provide summary and analytical information. Macros carry out actions in response to specific events. Modules are programs written in Visual Basic that carry out actions in response to specific events. Object Naming Rules When you name an Access object, you must follow these rules: No two objects of the same type can have the same name in the same database. A table cannot have the same name as a query. QR.indd 3 1/4/07 2:51:50 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 4 Microsoft Office Access 2007: The Complete Reference A field, control, or object cannot have the same name as a property, built-in function, or other reserved word in the Access vocabulary. A name can include any combination of letters, numbers, and spaces, up to 64 characters. A name cannot contain a leading space. A name can include most punctuation marks, except for periods (.), exclamation marks (!), accent graves (`), and brackets ([]). A name cannot include any ASCII control characters, ASCII 00 through ASCII 31. If you plan to program with Visual Basic, don t include spaces in object names. Instead, replace them with underscore (_) characters in the code. Database Properties Database properties are useful in locating a specific database in a large system. Database properties fall into five categories: General properties include the filename, type, location, size, and the dates the database was created, last modified, and most recently accessed. They also include checked attributes such as Read Only, Hidden, Archive, and System. Summary properties include descriptive information such as title, subject, author, manager, company, keywords, category, comments entered by the user, and the hyperlink base for the database. Statistics properties include the same items as General properties plus editing information, such as revision number, total editing time, and the name of the user who last saved the file. Contents properties include the names of all of the objects in the database grouped by type. Custom properties include 27 database properties, some of which the user can choose and set to a value that can help locate the database using the Search feature. To set database properties, open the database and click the Microsoft Office button, point to Manage, and choose Database Properties. Then click the appropriate tab. Figure QR-1 shows the Summary tab of the Database Properties dialog box, and Figure QR-2 shows the Custom tab. Relationships and Joins Tables in an Access database can be related in one of three different ways: One-to-many, in which one record in one table, the parent table, can have many matching records in a second table, the child table. The most common type of relationship. One-to-one, in which each record in one table has one and only one matching record in the second table. Used mostly for lookup. QR.indd 4 1/4/07 2:51:50 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference FIGURE Viewing summary database properties FIGURE QR.indd 5 QR - 1 QR - 2 5 Viewing custom database properties 1/4/07 2:51:51 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 6 Microsoft Office Access 2007: The Complete Reference Many-to-many, in which many records in one table can have many matching records in a second table. Implemented in Access by creating a third table to act as a junction table between the original two with one-to-many relationships to both. The primary key in the junction table is the combination of the primary keys of the two related tables. Referential integrity is a set of rules that preserve the defined relationships between tables. For example, one rule prevents the user from deleting a record from the one side of a one-tomany relationship while matching records are still in the table on the many side. Figure QR-3 shows some of the relationships in the Northwind 2007 sample database. Relationship lines with the 1 and infinity ( ) symbols at the ends are specified to enforce referential integrity. The relationship between the Employees table and the Purchase Orders table doesn t enforce referential integrity, as you can see by the lack of symbols on the line. The primary keys, which ensure each record is unique, appear with a key icon in the table field lists. The Order Details table is the junction table that relates the Products table to the Orders table. The primary key of the Order Details table is the combination of the OrderID (primary key field of the Orders table) and ProductID (primary key field of the Products table). The type of join that links the related tables determines which records from each table are included FIGURE QR.indd 6 QR - 3 Northwind Traders database relationships 1/4/07 2:51:51 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference FIGURE QR - 4 7 The Join Properties dialog box in the relationship. Figure QR-4 shows the Join Properties dialog box with a choice of three types of joins: Inner join includes only records in which the joined fields from both tables are equal. The most common type of join. Also called equijoin. Left outer join includes all the records from the one side of the relationship and only those records from the many side where the joined fields are equal. Right outer join includes all the records from the many side of the relationship and only those records from the one side where the joined fields are equal. Self join is another type of join that allows Access to look up a field value in the same table. The appearance of the relationship line in the Relationships window indicates the type of join. A line with no arrows indicates an inner join. The line joining the Suppliers table with the Products table has an arrow at the many end of the line, which indicates a left outer join. The relationship line between the Employees and the Purchase Orders tables shows an arrow at the one end of the line, which indicates a right outer join. Table Specifications To keep databases within limits, Access has placed certain restrictions on table characteristics. Table QR-3 describes these limitations. QR.indd 7 1/4/07 2:51:51 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 8 Microsoft Office Access 2007: The Complete Reference Attribute Maximum Allowed Number of characters in the table name 64 characters Number of characters in a field name 64 characters Number of fields 255 fields Number of tables open at one time 2048 or less Table size 2 GB, minus space needed for system objects Number of characters in a text field 255 characters Number of characters in a memo field 65,535 manually, 2 GB programmatically OLE Object field size 1 GB Number of indexes 32 indexes Number of fields in one index 10 fields Number of characters in a validation message 255 characters Number of characters in a validation rule 2048 characters Number of characters in a table or field description 255 characters Number of characters in a single record, excluding Memo and OLE Object fields 4000 characters when Unicode Compression is set to Yes Number of characters in a field property 255 characters TABLE QR- 3 Limits on Table Characteristics Table and Index Properties Setting table and index properties can help maintain database integrity and screen data for viewing. Table QR-4 describes table properties and Table QR-5 describes index properties. Table Fields Field names can contain up to 64 characters including spaces and must follow all the naming rules listed in the Database Objects section of this Quick Reference. Each table can contain up to 255 fields of the types described next. Data Types Table QR-6 lists the 11 data types provided by Access. An additional option in the Data Types drop-down list is Lookup Wizard, which you can use to create a lookup field. The data type of the lookup field must be compatible with the bound column with which it s associated. Field Properties Table QR-7 describes the field properties, and Table QR-8 indicates the correlation between data types and field properties. A field property setting that requires text can include up to 255 characters. QR.indd 8 1/4/07 2:51:52 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Property Description Default View The view used when the table opens. Choice of Datasheet (default), PivotTable, or PivotChart. Description A more informative description of the table than the table name. Can help identify the table and its uses. Up to 255 characters. Display View on SharePoint Do Not Display (default) or Follow Database Setting. Filter The filter expression saved with the table. Can be applied when table data is used as the basis for a form or report. Filter On Load Applies the saved filter when the table opens. Default No. Link Child Fields The field or fields in the child table that link it to the master table. Link Master Fields The field or fields in the master table that link it to the child table. Order By The sort order saved with the table. Can be applied when table data is used as the basis for a form or report. Order By On Load Applies the saved sort order when the table opens. Default Yes. Orientation The reading order, alignment, and visual appearance of bidirectional text and documents. Default is Left-to-Right. Subdatasheet Expanded Automatically displays the subdatasheet without clicking the plus (+) sign. Default No. Subdatasheet Height The height measure for displaying the subdatasheet with the master datasheet. Default 2 inches. Subdatasheet Name The name of a related subdatasheet, if any. Default Auto. Also None or choose from the list of tables in the database. Validation Rule Defines a rule that applies to a complete record. Often used to compare values in two or more fields in the same record. Up to 2048 characters. Validation Text 9 Message displayed in the status bar when the validation rule is violated. Up to 255 characters. TABLE QR- 4 Table Properties Property Description Field Name Name of the field to be indexed. Choose from the drop-down list. Ignore Nulls Set to Yes to exclude records with Null values from the index. Index Name Name of index. Multiple field indexes may contain up to 10 fields. If left blank, the field named in the Field Name column is assumed to be part of the index named previously. Primary Set to Yes if this index is the primary key for the table. Sort Order Order of indexed field in the index. Choose Ascending or Descending. Unique Set to Yes if every value in the index must be unique. TABLE QR- 5 Index Properties QR.indd 9 1/4/07 2:51:52 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 10 Microsoft Office Access 2007: The Complete Reference Data Type Size Used to Store Attachment Up to 2 GB of data. Individual files maximum 256 MB. Pictures, graphics, or any type of file. AutoNumber Size depends on selected field size: Long Integer: 4 bytes Replication ID: 16 bytes Access-generated unique value for each record. Currency 8 bytes Monetary values. Date/Time 8 bytes Date and/or time values to be used in calculations or sorted chronologically. Hyperlink 1 GB Hyperlink address, UNC path, or URL. Lookup Depends on values in the list. A limited list of valid values for the field; activates the Lookup Wizard. Memo Up to 65,536 characters. Long and variable text relating to other field data. Number Size depends on selected field size: Byte: 1 byte Integer: 2 bytes Long integer: 4 bytes Single: 4 bytes Double: 8 bytes ReplicationID: 16 bytes Decimal: Size depends on Precision and Scale settings Number values to be used in calculations or sorted by numeric value. OLE Object Up to 2 GB, depending on available disk space. Objects from another source, such as graphics, sound, or a spreadsheet. Text Up to 255 characters. Values with combinations of letters and numbers. Yes/No 1 bit True or False values. TABLE QR- 6 Data Types Display Formats The Format field property determines the appearance of the data when displayed in a datasheet, form, or report. Access provides many built-in formats for Number, Currency, and Date/Time data types. Special formatting symbols are also available for creating custom display formats. Table QR-9 describes the predefined Number, AutoNumber, and Currency format settings. Table QR-10 describes the predefined settings for Date/Time fields. QR.indd 10 1/4/07 2:51:52 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Field Property Effect Allow Zero Length Differentiates between a blank field and a field containing an empty string of text ( ). If set to Yes, allows an empty string, even if the Required property is also set to Yes. Caption Displays an alternative name for the field in datasheets, forms, reports, and data access pages. Decimal Places Specifies the number of digits to the right of the decimal point in a numeric field. Choose Auto to let the Format property set the number of decimal places. Default Value Automatically inserts the specified value in the field. Default for a Text field is an empty string; for Number or Currency, 0. Field Size Specifies the number of characters allowed in the field. Default is 50 for Text fields, but may be up to 255 characters. Number field size depends on the Format setting. AutoNumber field size depends on the New Value property setting. Format Determines the display appearance. IME Mode Sets the Input Method Editor (IME) mode for a field when focus is moved to it. IME enters East Asian text into programs by converting keystrokes into complex East Asian characters. Default No Control. IME Sentence Mode Sets the type of IME sentence. Default is Phrase Predict. Indexed Indicates the table is indexed on the field. Input Mask Provides a template for data conforming to a pattern and adds literal characters, if desired. New Values Specifies the method of building the AutoNumber sequence. Choices are Increment or Random. Precision Specifies the total number of digits to store and the total of digits on both sides of the decimal point. Applies only to number fields with the Field Size property set to Decimal. Required Requires a value in the field. Default No. Scale Specifies the number of digits on the right side of the decimal point. Applies only to number fields with the Field Size property set to Decimal. Smart Tags Attaches a built-in or custom Smart Tag to the field that can perform specific actions. Text Align Specifies the alignment of the text in a control in a form or report: General (default), Left, Center, Right, Distribute. Unicode Compression Allows compression of Unicode character representation. Default Yes. Validation Rule Limits the field value to specific values or a range of values. Validation Text 11 Sets text to display when the field violates the validation rule. TABLE QR-7 Field Properties QR.indd 11 1/4/07 2:51:53 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 12 Microsoft Office Access 2007: The Complete Reference Property T M Allow Zero-Length X X Caption X X Default Value X X Field Size X Format X X IME Mode X X X X IME Sentence Mode X X X X Indexed X X Input Mask X Decimal Places N D/T C AN Y/N OLE X X X X X X ATT X X X X X X X X X X X X X X X X X X X X X X X X New Values X X X X Precision Required HL X X X Scale X X X X X X X Show DatePicker X Smart Tags X X X X X X Text Align X X X X X X Text Format X X X X X Unicode Compression X X X Validation Rule X X X X X X X Validation Text X X X X X X X TABLE QR- 8 Field Properties for Data Types Custom Formatting Symbols Special symbols can be used to create custom formats for many data types. Table QR-11 describes the global symbols that can be used with all data types. Table QR-12 describes the symbols used with Number and Currency fields. Table QR-13 lists the symbols used with Date/Time fields, and Table QR-14 lists those used with Text and Memo fields. Custom Format Strings Custom Number and Currency format strings can contain up to four sections, each applying to a different type of value. The sections are separated by semicolons. The first section specifies the format for positive numbers. The second section specifies the format for negative numbers. The third section specifies the format for zero values. The fourth section specifies the format for Null values. QR.indd 12 1/4/07 2:51:53 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference 13 For example, the custom currency format $#,##0.00[blue];($#,##0.00)[red];"Zero";"No value" displays positive values in blue, negative values in red and enclosed in parentheses, the word Zero for zero values, and No value for Null values. Both positive and negative values display at least one digit to the left of the decimal point and two digits to the right. Setting Effect General Number Displays number as entered. Default format for Number fields. Example: 1234.567 Currency Displays number with currency symbol and thousands separator with a default of two decimal places. Negative values appear in parentheses. Default format for Currency fields. Example: $1,234.56 Euro Same as Currency, except displays with Euro currency symbol. Example: !123.45 Fixed Displays at least one digit with a default of two decimal places. Example: 0.12 Standard Displays thousands separator with a default of two decimal places. Example: 1,234.56 Percent Displays value multiplied by 100 with added percent sign (%) and a default of two decimal places. Example: 12.34% Scientific Uses standard scientific notation with exponents. Example: 243 displays as 2.43E+02 TABLE QR- 9 Predefined Display Format Settings for Numbers, Currency, and AutoNumbers Setting Effect General Date Default format. Combination of Short Date and Long Time settings. If no time value, displays only the date; if no date value, displays only the time. Examples: 5/21/07 3:30:00 P.M. (US) 21/5/07 15:30:00 (UK) Long Date Uses Long Date setting. Examples: Monday, May 21, 2007 (US) Monday, 21 May 2007 (UK) Medium Date Uses Medium Date setting. Example: 21-May-07 Short Date Uses Short Date setting. Examples: 5/21/07 (US) 21/5/07 (UK) Long Time Uses Long Time setting. Example: 3:30:00 PM Medium Time Uses Medium Time setting. Example: 3:30 PM Short Time Uses Short Time setting. Example: 15:30 TABLE QR- 10 Display Settings for Date/Time Fields QR.indd 13 1/4/07 2:51:54 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 14 Microsoft Office Access 2007: The Complete Reference Date/Time format strings can also combine the special formatting symbols with text to produce custom display formats. Spaces, commas, and other literal characters must be enclosed in quotation marks. For example, the format string "Today is " dddd ", " mmm d ", " yyyy " in week number " ww applied to the value 11/2/07 displays Today is Friday, Nov 2, 2007 in week number 44 Symbol Effect ! Fills placeholders from left to right instead of right to left, forcing left alignment. (Space) Enters a space as a literal character when the SPACEBAR is pressed. xyz Displays the characters or symbols within the quotation marks. * Fills available space with the character that follows. \ Indicates that the following reserved symbol or character is to be treated as a literal character. [color] Displays the field data in the color contained within the brackets; you can use black, blue, green, cyan, red, magenta, yellow, or white. TABLE QR- 11 Global Symbols Symbol Effect . (period) Indicates the decimal point that separates the integer and fractional parts of a number. , (comma) Used as the thousands separator. 0 A digit placeholder that displays a digit if one is there or zero if none. # A digit placeholder that displays a digit if one is there or closes up the adjoining digits if none. $ Displays a dollar sign. % Multiplies the value by 100 and adds a percent sign. E or e Displays numbers in scientific notation with a minus ( ) sign before negative exponents, but no sign before positive exponents. E+ or e+ Same as the previous except displays either plus (+) or minus ( ) sign before the exponent. TABLE QR- 12 Number and Currency Field Symbols QR.indd 14 1/4/07 2:51:54 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Symbol Effect : (colon) Separates hour and minute values as set in the Windows Regional Settings Properties dialog box. / Separates date components. c Applies General Date predefined format as set in the Windows Regional Settings Properties dialog box. d Displays day of the month in one or two digits, as required (1 to 31). dd Displays day of the month in two digits (01 to 31). ddd Displays first three letters of the day of the week (Sun to Sat). dddd Displays full weekday name (Sunday through Saturday). ddddd Same as Short Date format. dddddd Same as Long Date format. w Displays day of the week by number (1 to 7). ww Displays week of the year by number (1 to 52). m Displays month in one or two digits, as needed (1 to 12). mm Displays month in two digits (01 to 12). mmm Displays first three letters of month name (Jan to Dec). mmmm Displays full name of the month (January to December). q Displays quarter of the year (1 to 4). y Displays number of the day in the year (1 to 366). yy Displays last two digits of year (01 to 99). yyyy Displays full year (0100 to 9999). h, n, s Displays hour, minute, or second in one or two digits, as needed (0 to 23, 1 to 59). hh, nn, ss Displays hour, minute, or second in two digits (01 to 23, 01 to 59). tttt Same as Long Time. AM/PM, am/pm Displays 12-hour clock with two-character uppercase or lowercase designators. A/P, a/p Displays 12-hour clock with one-character uppercase or lowercase designators. AMPM 15 Displays 12-hour clock with morning/afternoon designators, as specified in Windows Regional Settings Properties dialog box. TABLE QR- 13 Date/Time Field Symbols Input Masks Input masks display literal characters in the field with fill-in blanks before any data is entered. They ensure that the data entered can fit in the format you specify. Many predefined input masks are available for Text and Date fields by using the Input Mask Wizard, or you can QR.indd 15 1/4/07 2:51:54 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 16 Microsoft Office Access 2007: The Complete Reference Symbol Effect @@ Indicates a character or a space is required. & Indicates a character or a space is optional. < Converts all characters that follow to lowercase. > Converts all characters that follow to uppercase. TABLE QR- 14 Text and Memo Field Symbols Symbol Entry Entry Required? 0 Digit (0 through 9) with no + or sign. Blanks display as zeros. Yes 9 Digit with no + or sign. No # Same as 9, but allows +/ signs. Blanks display as spaces. No L Letter. Yes ? Letter. No A Letter or digit. Yes A Letter or digit. No & Any character or space. Yes C Any character or space. No .,:;-/ Decimal placeholder, thousands separator, date and time separators. N/A < Converts following letters to lowercase. N/A > Converts following letters to uppercase. N/A ! Input mask displays from right to left, rather than left to right. Characters typed into the mask fill it from left to right. Symbol can appear anywhere in the mask. N/A \ Next character is treated as a literal. N/A Password Creates a password entry box. Characters typed in the text box are stored as entered, but displayed as asterisks (*). N/A TABLE QR- 15 Input Mask Symbols create your own custom input mask. If you want an input mask for a Number or Currency field, you must enter the string directly in the field Input Mask property in the table design. The symbols used in an input mask can specify the type of character to be entered in that position and also indicate whether the entry is required. Table QR-15 describes the input mask symbols. Some of the symbols have the same usage as in a format string. QR.indd 16 1/4/07 2:51:54 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference 17 Input mask strings can contain up to three sections separated by semicolons: The first contains the mask itself. The second determines whether the literal characters will be stored with the data. Enter 0 in this section to store the literal characters with the data; enter 1 or leave it blank to store only the characters entered in the fill-in spaces. The third specifies the character to use as the blank fill-in spaces in the displayed mask. For example, type + to use plus signs in place of the default underline character. If you want to leave the fill-in spaces blank, type (with a space between the double quotation marks). For example, the following input mask string can be used in a ZIP code field to require the first five characters, provide for the four additional optional characters, store the hyphen with the data, and display plus signs in the fill-in spaces: 00000-9999;0; + User Interactions This section begins by describing the built-in ribbons. Subsequent paragraphs detail form and report specifications and properties, followed by detailed lists of control types and their properties. Ribbons and Tabs Access 2007 provides activity-related displays of commands in the new ribbon format. Each ribbon has tabs containing commands that can be used while working on the current task. For example, if you are creating a new form design, the Form Design Tools ribbon includes tabs for that purpose as well as the standard Home and other tabs. All ribbons contain four basic tabs: Home, Create, External Data, and Database Tools. Table QR-16 lists the groups of additional ribbon tabs that appear during the various Access database activities. The groups of commands on the tab depend on the object with which you are working. For example, the Report Design tab has different commands than the Macro Design or Form Design tab. Tab Group Design Data Sheet Arrange Table Tools X X Query Tools X Form Design Tools X X Report Design Tools X Formatting X Form Layout Tools X X Report Layout Tools X X Page Setup Macro Tools X PivotTable Tools X PivotChart Tools X X TABLE QR- 16 Ribbon Tab Groups QR.indd 17 1/4/07 2:51:55 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 18 Microsoft Office Access 2007: The Complete Reference Attribute Maximum Capacity Number of characters in a label 2048 characters Number of characters in a text box 65,535 characters Form or report width 22 inches (55.87 centimeters) Section height 22 inches (55.87 centimeters) Height of all sections, including section headers 200 inches (508 centimeters) Number of nested form or report levels 7 levels Number of fields or expressions sorted on or grouped by 10 fields or expressions Number of headers and footers in a report 1 report header/footer, 1 page header/footer, 10 group headers/footers Number of printed pages in a report 65,536 pages Total number of controls and sections added to a form or report 754 controls and sections Number of characters in an SQL statement used as the record source or as the row source for a control 32,750 characters TABLE QR- 17 Form and Report Attributes Form and Report Design Designing a form or report is a process of adding controls of various types to the design and setting properties that determine the behavior and appearance of the form or report itself, as well as the controls in the design. The following sections are devoted to describing the properties of forms and reports, of their individual sections, and of the controls in the forms and reports. The properties are grouped by the same categories used in the property sheets and are listed in alphabetical order within each group. Default values and settings, if any, appear in boldface. Form and Report Specifications Certain limitations apply to forms and reports, such as the number of characters in a label, the height of a section, and the number of printed pages in a report. Table QR-17 lists the form and report attributes together with their maximum capacities. Form and Report Format Properties Table QR-18 describes the Format properties of forms and reports that determine the object s appearance on the screen or when printed. (Default values appear in boldface.) Form and Report Data Properties The data properties of both forms and reports determine the source of the data to be used in the object and specify any filter or sort order. In addition, the form data properties specify the types of actions that the user may perform on the data in the form. Table QR-19 describes these properties. (Default values appear in boldface.) QR.indd 18 1/4/07 2:51:55 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Allowed Values Allow Datasheet View Permits Datasheet view Forms Yes/No Allow Form View Permits Form view Forms Yes/No Allow Layout View Allows switch to Layout view Forms and Reports Yes/No Allow PivotChart View Permits PivotChart view Forms Yes/No Allow PivotTable View Permits PivotTable view Forms Yes/No Allow Report View Permits switch to Report view Reports Yes/No Auto Center Centers the form automatically on screen Forms and Reports Yes/No Auto Resize Resizes form to display a complete record Forms and Reports Yes/No Border Style Sets the form window border style Forms and Reports None, Thin, Sizable, Dialog Caption Text that appears in title bar Forms and Reports Text optional Close Button Displays or hides the Close button (X) Forms and Reports Yes/No Control Box Displays or hides the control menu Forms and Reports Yes/No Default View Specifies the Default view Forms and Reports Single Form, Continuous Forms, Datasheet, PivotTable, PivotChart, SplitForm Report View, Print Preview Dividing Lines Displays or hides dividing lines between records in Form view Forms Yes/No FitToPage Expands report width to fit page Reports Yes/No Grid X Number of subdivisions per unit of measurement on the horizontal grid Forms and Reports Integer 1 through 64, default 24 Grid Y Number of subdivisions per unit of measurement on the vertical grid Forms and Reports Integer 1 through 64, default 24 Grp Keep Together Specifies whether to keep groups together by page or by column in a multiple column report Reports 19 Per Column, Per Page TABLE QR- 18 Form and Report Format Properties QR.indd 19 1/4/07 2:51:56 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 20 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Allowed Values Layout for Print Uses printer fonts in design Forms and Reports No default for forms, Yes default for reports Min Max Buttons Displays or hides one or both min/max buttons Forms and Reports None, Min Enabled, Max Enabled, Both Enabled Moveable Permits moving object about the screen Forms and Reports Yes/No Navigation Buttons Displays or hides navigation buttons Forms Yes/No Navigation Caption Specifies Navigation button caption Forms Blank Orientation Specified visual layout of text in objects and controls Forms and Reports Left-to-Right/ Right-to-Left Page Footer Determines which report pages show the page footer information Reports All Pages, Not with Rpt Hdr, Not with Rpt Ftr, Not with RptHdr/Ftr Page Header Determines which report pages show the page header information Reports Same as Page Footer Palette Source Chooses the palette of colors to use in the design Forms and Reports (Default), valid path, filename Picture Path or filename of picture to use as form or report background Forms and Reports (None), path, filename Picture Alignment Aligns the picture in the frame Forms and Reports Top Left, Top Right, Center, Bottom Left, Bottom Right, Form Center (forms only) Picture Pages Determines which pages show the background picture Reports All Pages, First Page, No Pages Picture Size Mode Controls how the contents of the object frame or graph are displayed Forms and reports Clip, Stretch H, Stretch V, Zoom Picture Tiling Tiles the picture in the frame Forms and Reports Yes/No TABLE QR- 18 Form and Report Format Properties (continued) QR.indd 20 1/4/07 2:51:56 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Allowed Values Picture Type Links or embeds the background picture Forms and Reports Embedded, Linked Record Selectors Displays or hides record selectors Forms Yes/No Save Splitter Bar Position Saves the current splitter bar location Forms Yes/No Scroll Bars Displays or hides scroll bars Forms and Reports Horizontal Only, Vertical Only, Both, Neither Show Page Margins Shows page margins in Report view Reports Yes/No Split Form Datasheet Allows editing datasheet in Split Form Forms Allow Edits, Read Only Split Form Orientation Positions the datasheet with the form Forms Datasheet on Top, Datasheet on Bottom, Datasheet on Left, Datasheet on Right Split Form Printing Allows printing both form and datasheet Forms Form Only, Datasheet Only Split Form Size Sets the size of the form part of the split form window Forms 0 Split Form Splitter Bar Displays a divider bar between the form and datasheet Forms Yes/No Subdatasheet Expanded Automatically displays all subdatasheets expanded Forms Yes/No Subdatasheet Height Specifies the default height of an expanded subdatasheet Forms Height in inches What s This Button Shows question mark button in title bar Forms Yes/No Width Width of all sections Forms and Reports 21 Width in unit of measure set in Regional Settings TABLE QR- 18 Form and Report Format Properties (continued) QR.indd 21 1/4/07 2:51:56 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 22 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Allowed Values Allow Additions Permits or prohibits adding new records Forms Yes/No Allow Deletions Permits or prohibits deleting records Forms Yes/No Allow Edits Permits or prohibits editing records Forms Yes/No Allow Filters Permits or prohibits filtering records Forms and Reports Yes/No Before ScreenTip Macro or function that r uns when a PivotTable or PivotChart triggers its Before ScreenTip event Forms and Reports None, [Event Procedure] Data Entry Opens form only for adding new records; existing records are not displayed Forms Yes/No Fetch Defaults Retrieves default values Forms Yes/No Filter Filter expression automatically loaded with form or report Forms and Reports Any valid filter expression Filter On Load Applies filter at startup Forms and Reports Yes/No Order By On Applies sort order at report startup Reports Yes/No Order By On Load Sort order automatically loaded with form or report Forms and Reports Valid field name Record Locks Determines whether and how records in the underlying record source are locked Forms No Locks, All Records, Edited Record Record Source Table, query, or SQL statement on which the form or report is based Forms and Reports Table or query name or valid SQL statement Recordset Type Determines which tables may be edited Forms Dynaset, Dynaset (Inconsistent Updates), Snapshot TABLE QR- 19 Form and Report Data Properties QR.indd 22 1/4/07 2:51:57 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference 23 Form and Report Event Properties All event properties relate to an event that can occur to the object or control. For example, the On Current event property setting determines the behavior of the form or report when it becomes active. Event properties specify the action to take when the event occurs. The action is in the form of an expression, a macro, or a Visual Basic event procedure. Event properties have no default or allowed values. Table QR-20 lists the event properties that apply to forms and reports and their sections. NOTE Additional event properties apply exclusively to forms in interactive PivotTable or PivotChart views. Property When Event Occurs Applies To After Del Confirm After responding Yes or No to the deletion confirmation message Forms After Insert After a new record has been added to the database Forms After Update After the data in a control or record is updated Forms Before Del Confirm After one or more records are deleted, but before the confirmation message appears Forms Before Insert When new data is typed into a new record, but before the record is added to the database Forms Before Update Before a control or record is updated with modified data Forms Key Preview Specifies the form-level, keyboard event procedures are invoked before the control-level keyboard event procedures Forms, Reports On Activate Object becomes active by opening or clicking it or by clicking a control on a form Forms, Reports On Apply Filter Applies a filter to or removes a filter from the data in the form by one of the filter methods Forms, Reports On Click Click the left mouse button on a record selector or outside a section or control on a form Forms, Form sections, Reports, Report sections On Close Form or report closes and no longer appears on the screen Forms, Reports On Current Focus moves to a record and it becomes current Forms, Reports TABLE QR- 20 Form and Report Event Properties QR.indd 23 1/4/07 2:51:57 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 24 Microsoft Office Access 2007: The Complete Reference Property When Event Occurs Applies To On Dbl Click Double-click the left mouse button on a record selector or outside a section or control on a form Forms, Form sections, Reports, Report sections On Deactivate A different Access window replaces the form or report as the active window, but before it becomes the active window Forms, Reports On Delete A record is deleted, but before the deletion is confirmed and actually carried out Forms On Dirty A record has been modified, but not yet saved Forms On Error A Microsoft Jet Database Engine error occurs Forms, Reports On Filter The Filter By Form or Advanced Filter/ Sort window opens Forms, Reports On Format Access determines what data goes in each report section, before the section is formatted for preview or print Report sections On Got Focus A control on the form receives focus, or a form with no active or enabled controls receives focus Forms, Reports On Key Down Press any key when the form has focus or send a keystroke with a macro or a VB procedure Forms, Reports On Key Press Press and release a key or key combination when the form has focus Forms, Reports On Key Up Release a pressed key when a form has focus Forms, Reports On Load A form is opened and records appear on the screen Forms, Reports On Lost Focus A form loses focus Forms, Reports On Mouse Down Click the left mouse button Forms, Form sections, Reports, Report sections On Mouse Move Move the mouse pointer over a form or form section Forms, Form sections, Reports, Report sections On Mouse Up Release the mouse button while the pointer is on a form or form section Forms, Form sections, Reports, Report sections On Mouse Wheel When the mouse wheel moves on the screen Forms, Reports On No Data After Access formats a report for preview or print and discovers the report is based on an empty recordset Reports TABLE QR- 20 Form and Report Event Properties (continued) QR.indd 24 1/4/07 2:51:57 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Property When Event Occurs Applies To On Open A form opens, but before the first record is displayed; a report opens, but before printing begins Forms, Reports On Page After a report is formatted, but before printing Reports On Paint Before a report section is painted Report sections On Print After section data is formatted, but before printing Report sections On Resize The size of a form changes or when the form is first displayed and expanded to its previously saved size Forms, Reports On Retreat Access has to back up one or more report sections to perform additional formatting passes Report sections except page header/footer On Timer Time interval specified in the Time Interval property has expired Forms, Reports On Undo All edits on the form are reversed Forms On Unload Form is closed and records are unloaded, but before it leaves the screen Forms, Reports Timer Interval Sets the length of time for the form s Timer event 25 Forms, Reports TABLE QR- 20 Form and Report Event Properties (continued) Form and Report Other Properties The properties listed on the Other tab of a form property sheet include settings that you can use to create pop-up forms and dialog boxes. Other properties specify custom menu bars, shortcut menus, or toolbars to be used with the form or report. Table QR-21 describes these properties. (Default values appear in boldface.) Form and Report Section Properties Each section of a form or report has specific properties that govern its appearance and behavior. Forms and reports both have a Detail section, Header and Footer sections, and Page Header and Footer sections. Reports can also have Group Header and Footer sections with some unique properties. Table QR-22 describes the section properties. Refer to Table QR-20 for a list of the form and report section event properties. (Default values appear in boldface.) Dialog Box and Pop-Up Form Property Settings By setting a certain combination of form properties, you can create a pop-up form to display information or a dialog box that can receive user input. Most of the form properties are set to remove any data manipulation capability, such as selecting or navigating through records. Because pop-up forms and dialog boxes don t contain record data, these properties are not necessary. QR.indd 25 1/4/07 2:51:58 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 26 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Allowed Values Cycle Specifies how the TAB key behaves when reaching the last control on the current record Forms and Reports All Records, Current Record, Current Page Date Grouping Specifies how to group date values Reports Use System Settings, U.S. Default Display on SharePoint Site Determines if this view maybe displayed on MSW SharePoint Services Forms and Reports Do Not Display, Follow Table Setting Fast Laser Printing Allows replacing lines and rectangles with text character lines Forms and Reports Yes/No Has Module Object has a Visual Basic class module Forms and Reports Yes/No Help Context ID Identifies Help topic within the custom Help file Forms and Reports Long integer value representing the context ID of the custom Help topic; 0 is default Help File Specifies custom Help file Forms and Reports Path and filename of a custom Help file Menu Bar Names a custom menu bar or menu macro Forms and Reports Name of custom menu bar Modal Keeps form in focus until closed Forms and Reports Yes/No Pop Up Defines the form as a pop-up form that remains on top of other windows Forms and Reports Yes/No Record Locks Locks records while the report is printing Reports No locks, All Records Ribbon Name Specifies name of loaded ribbon customization to apply on Open Forms and Reports Name of custom ribbon Shortcut Menu Allows display of shortcut menu in Browse mode by right-clicking Forms Yes/No TABLE QR- 21 Form and Report Other Properties QR.indd 26 1/4/07 2:51:58 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Allowed Values Shortcut Menu Bar Displays custom shortcut menu bar or menu-bar macro Forms and Reports Name of command bar whose Type property is Popup or the name of the macro Tag Any extra information about the form or report Forms and Reports (zero-length string), any string up to 2048 characters Toolbar Displays a custom toolbar when the form or report opens Forms and Reports Name of the custom toolbar Use Default Paper Size Returns to default paper size for printing Forms and Reports 27 No/Yes TABLE QR- 21 Form and Report Other Properties (continued) You also should not permit the user to maximize or minimize the form or to switch to Datasheet view. To require user input to close the box even if only to cancel the form remove the Close button (the one with the X). You can leave in the Control Menu box as an emergency exit, especially during form design. Setting the form s Border Style removes the Close button but retains the Control Menu. Table QR-23 lists the relevant properties and their settings. Controls and Control Properties Most of the control properties can be set in the control s property sheet. Others can be set only by using a macro or a Visual Basic procedure. This section describes the control properties that can be specified in the property sheet and indicates the controls to which they apply. NOTE If you want to know more about a property, click the property box on the property sheet and press F1. Several properties apply to all controls in both forms and reports: The Height and Width properties determine the dimensions of the control and apply to all controls except page breaks. The size is measured in inches or in the unit of measure specified in the Regional Settings Properties dialog box. The Left and Top properties determine the placement of the control in the form or report design relative to the upper-left corner of the window. This doesn t apply to page breaks, which use the same unit of measure as the Height and Width properties. All controls have the Name property, which specifies an identifier used by expressions, macros, and Visual Basic procedures. The default is the control name followed by an integer for example, Label4. QR.indd 27 1/4/07 2:51:58 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 28 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Allowed Values Back Color Sets the background color All sections Any standard or custom color number Can Grow Expands the section vertically when printed to accommodate entire record All sections except Page Header and Footer Yes/No Can Shrink Shrinks the section vertically when printed to eliminate blank lines All sections except Page Header and Footer Yes/No Display When Specifies when to show section Form Detail, Header, and Footer Always, Print Only, Screen Only Force New Page Specifies where to begin printing the section All sections except Page Header and Footer None, Before Section, After Section, Before & After Height Height of section in the design All sections Height in inches or in unit of measure set in Regional Settings Keep Together Keeps all information in the section on one page All sections except Page Header and Footer Yes/No Name Identifier name assigned to the section used by expressions, macros, and procedures All sections A string expression up to 64 characters that must conform to Access naming conventions; default is section name New Row Or Col Specifies where to start printing the section in a multiple-column layout All sections except Page Header and Footer None, Before Section, After Section, Before & After Repeat Section Repeats the section at the top of each page Report Group Header Yes/No Special Effect Gives section a 3-D appearance All sections Flat, Raised, Sunken Tag Contains extra information about the form or report All sections (zero-length string), any string up to 2048 characters Visible Displays or hides the section All sections Yes/No TABLE QR- 22 Form and Report Section Properties QR.indd 28 1/4/07 2:51:59 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Property Setting Purpose Allow Form View Yes Keeps the form in Form view. Sets the other Allow view properties to No. Auto Center Yes Centers the form automatically when it opens. If you want the form to appear in a special place, set to No. Border Style Dialog Form has a thick border and includes only a title bar with a Control Menu box. Caption Prompt for user input or other helpful text Displays text in title bar in Form view instead of form name. Control Box Yes Displays the Control Menu box in the title bar in Form view so the user can close the form. Dividing Lines No Removes horizontal lines from the form. MinMax Buttons None Prevents the user from resizing the form in Form view. Modal Yes Form retains focus until it s closed. Navigation Buttons No Removes navigation buttons from the form. PopUp Yes Form remains on top of other windows. Record Selectors No Removes record selectors from the form. Scroll Bars Neither 29 Removes scroll bars from the form. TABLE QR- 23 Dialog Box and Pop-Up Form Properties All controls have a Tag property that contains additional information about the control. The tag is a string expression containing up to 2048 characters. The default value is a zero-length string. All controls have gridline properties that set the style, color, and width of the left, right, top, or bottom gridlines. Styles are set to Transparent by default while the widths are set to 1 point. The default gridline color is white. The Padding properties specify the space added to the left, right, top, or bottom area next to the control. Defaults are all set as 0.0208 . The Visible property applies to all controls, except page breaks, and specifies whether the control is shown or hidden. The default is Yes. The Display When property applies to all of the controls on forms except tab pages. The property determines when the control is displayed: Always (default), Print Only, or Screen Only. Five event properties concerning mouse activities apply to all controls except subforms, subreports, and lines: On Click, On Dbl-Click, On Mouse Down, On Mouse Move, and On Mouse Up. The MouseDown event occurs when the left mouse button is pressed and the MouseUp event occurs when the button is released. QR.indd 29 1/4/07 2:51:59 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 30 Microsoft Office Access 2007: The Complete Reference The MouseMove event occurs when the mouse pointer is moved over a control. The two click events apply to standalone option buttons and check boxes only, not to controls in an option group. No event properties are associated with Page Break or Line controls. Some of the user-interaction properties can be applied only to controls in form designs, but not in report designs. Table QR-24 lists the remaining control properties and identifies the controls to which they apply. (Default values appear in boldface.) Property Description Applies To Add Colon1 Yes/No. Automatically adds a colon after the attached label text. Default property setting for all controls except labels, images, unbound object frames, tabs, rectangles, lines Allow AutoCorrect Yes/No. Entries are automatically corrected according to settings in the AutoCorrect dialog box. Text boxes, combo boxes Allow Value List Edits Yes/No. Permits editing a value in the list. Combo box Auto Activate Specifies how to activate an OLE Object: Manual, Double-Click, or Get Focus. Unbound object frames, bound object frames Auto Expand Yes/No. Automatically fills in value that matches characters typed in a combo box. Combo boxes Auto Label1 Yes/No. Labels are automatically created and attached to new controls. Default property setting for all controls except labels, images, unbound object frames, tabs, rectangles, lines Auto Repeat Yes/No. Runs an event procedure or macro repeatedly while the button is pressed in. Command buttons AutoTab Yes/No. Tabs to next tab stop when last allowable character in input mask is entered in a text box control. Text boxes (forms) Back Color Specifies the color of the interior or background of a control. Labels, text boxes, option groups, combo boxes, list boxes, images, unbound object frames, bound object frames, rectangles Back Style Normal/Transparent. Specifies whether a control is transparent or shows the color specified by Back Color. Normal is default for combo box, tabs, unbound and bound object frame controls. Transparent is default for label, text box, option group, image, and rectangle controls. Labels, text boxes, option groups, combo boxes, images, unbound object frames, bound object frames, tabs, rectangles TABLE QR- 24 The Remaining Control Properties QR.indd 30 1/4/07 2:51:59 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Border Color Numeric expression that specifies the color of the control s border. 0 is default. All controls except toggle buttons, command buttons Border Style Specifies the appearance of the control s border. Choices include Transparent, Solid, and a variety of dots and dashes. Transparent is the default for some controls, while Solid is the default for others. All controls except toggle buttons, command buttons Border Width Specifies the width of the control s border. Hairline or a choice of 1 to 6 points. All controls except toggle buttons, command buttons Bottom Margin Specifies bottom margin for text within a control. Labels, text boxes Bound Column In a multicolumn control, specifies which column is bound to the underlying field specified in the Control Source property. 1 is default. Combo boxes, list boxes Can Grow Yes/No. Expands vertically so all data can be printed or previewed. Text boxes, subforms/ subreports Can Shrink Yes/No. Shrinks vertically so data can be printed or previewed without blank lines. Text boxes, subforms/ subreports Cancel Yes/No. Specifies the button activated when the user presses ESC. Command buttons Caption Specifies text to display on the control. Default is control name followed by an index number. For example, Label1. Labels, toggle buttons, command buttons, pages Class Specifies the class of an embedded OLE Object. Unbound object frames, bound object frames Column Count Specifies the number of columns to display in a multicolumn control or to send to an OLE Object. Combo boxes, list boxes, unbound object frames Column Heads Yes/No. Displays field names from the underlying record source as the column heads. Combo boxes, list boxes, unbound object frames Column Widths Specifies width in inches of each column in a multicolumn control, separated by semicolons. 0 setting hides the column. Combo boxes, list boxes Control Source2 Specifies the data to appear in the control. Text boxes, option groups, toggle buttons, option buttons, check boxes, combo boxes, list boxes, bound object frames ControlTip Text Specifies text to display when the mouse pointer rests on a control. Applies only to controls in forms. All controls except tabs, subforms/subreports, lines, rectangles Datasheet Caption Caption to use as column header in Datasheet view. 31 Text boxes, combo boxes, list boxes, option groups, bound object frames TABLE QR- 24 The Remaining Control Properties (continued) QR.indd 31 1/4/07 2:52:00 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 32 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Decimal Places Auto. Specifies the number of digits to the right of the decimal point in numeric fields. Text boxes, combo boxes Default Yes/No. Specifies the button activated when the user presses ENTER. Command buttons Default Value2 Specifies the value to be assigned to the Control Source when a new record is added in Form view. Text boxes, option groups, toggle buttons, option buttons, check boxes, combo boxes, list boxes Display As Hyperlink If Hyperlink, Always, Screen Only. Text boxes Display Type Content/Icon. Specifies whether the OLE Object displays the contents or only an icon. Unbound object frames, bound object frames Enabled Yes/No. Specifies that a control can have focus. Applies only to controls on forms. All controls except labels, images, lines, rectangles, pages Enter Key Behavior Specifies behavior of ENTER key: Default (as specified under Move After Enter on the Keyboard tab of the Options dialog box) or New Line in Field, which adds a new line and stays in the text box. Text boxes on forms only Filter Lookup Never/Database Default/Always. Specifies whether values appear in control when using the Filter By Form window. Text boxes Font Italic Yes/No. Text in control is italicized. Labels, text boxes, toggle buttons, combo boxes, list boxes, command buttons, tabs Font Name Specifies font style for text in control. Labels, text boxes, toggle buttons, combo boxes, list boxes, command buttons, tabs Font Size Specifies font size for text in control. 10 default for command buttons; 8 default for all others. Labels, text boxes, toggle buttons, combo boxes, list boxes, command buttons, tabs Font Underline Yes/No. Text in control is underlined. Labels, text boxes, toggle buttons, combo boxes, list boxes, command buttons, tabs Font Weight Normal. Specifies thickness of text in control from Thin to Heavy. Labels, text boxes, toggle buttons, combo boxes, list boxes, command buttons, tabs TABLE QR- 24 The Remaining Control Properties (continued) QR.indd 32 1/4/07 2:52:00 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Fore Color Numeric value specifying the color of the text in control. 0 (black) is default. Labels, text boxes, toggle buttons, combo boxes, list boxes, command buttons Format Specifies format for displaying numbers, dates, times, and yes/no values. Text boxes, combo boxes Help Context ID Identifier for Help topic in a custom Help file. Numbers start with 0. Applies only to controls in forms. All controls except subforms/subreports, lines, rectangles Hide Duplicates No/Yes. Hides control is data is the same as previous record. Text boxes Horizontal Anchor Left/right. Anchors control to the side when parent section grows horizontally. ActiveX control Hyperlink Address Path or URL to target document. Command buttons, images Hyperlink Subaddress Location within Hyperlink Address target document. Command buttons, images IME Hold Yes/No. Keeps the setting of the IME mode for this field or control after the focus is moved away. Text boxes, list boxes, combo boxes IME Mode Sets the IME mode when focus is moved to the control or field. No Control, On, Off, Disable, and four other options. Text boxes, list boxes, combo boxes IME Sentence Mode Sets the IME sentence when focus moves to a control or field. Phrase Predict, Plural Clause, Conversation, None. Text boxes, list boxes, combo boxes Inherit Value List No/Yes. Gets value list from bound field. Combo boxes Input Mask Contains a pattern for data entered in the control. Text boxes, combo boxes Is Hyperlink Yes/No. Identifies control as hyperlink. Text boxes, combo boxes Keyboard Language Overrides the keyboard language currently in use. System, English, or one of the foreign character sets. Text boxes, combo boxes Label Align1 Aligns text within attached label of a new control. Options are General, Left, Center, Right, or Distribute. Default property setting for all controls except labels, images, unbound object frames, tabs, rectangles, lines Label X1 Sets the horizontal placement of the attached label with respect to the upper-left corner of a new control. 1 places the label 1 inch to the left of the control. 33 Default property setting for all controls except labels, images, unbound object frames, tabs, rectangles, lines TABLE QR- 24 The Remaining Control Properties (continued) QR.indd 33 1/4/07 2:52:00 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 34 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Label Y Sets the vertical placement of the attached label with respect to the upper-left corner of the control. 0 places the label on the same level as the new control. Default property setting for all controls except labels, images, unbound object frames, tabs, rectangles, lines Left Margin Specifies left margin for text within a control. Labels, text boxes Limit To List Yes/No. Prohibits (Yes) or allows (No) entered text that doesn t match a value in the list. Combo boxes Line Slant Slants line from upper-left to lower-right (\) or from upper-right to lower-left (/). Lines Line Spacing Specifies the line spacing for text within a control. Labels, text boxes Link Child Fields Specifies field name(s) in the subordinate object. Unbound object frames, subforms/subreports Link Master Fields Specifies field name(s) in the main form or report. Unbound object frames, subforms/subreports List Items Edit Form Name of the form to open to edit the values in the list. Combo boxes, list boxes List Rows Specifies the maximum number of rows to display in the drop-down list. Default is 8. Combo boxes List Width Auto. Specifies the width of the drop-down list part of the combo box. Combo boxes Locked Yes/No. Prevents (Yes) or allows (No) editing data in controls in Form view. All controls except labels, command buttons, images, lines, rectangles Multirow Yes/No. Allows tab control to display more than one row of tabs. If No, tabs are truncated to fit in a single row. Tabs Multiselect None/Simple/Extended. Specifies whether user can make more than one selection in a list box and how to do it. List boxes Numeral Shapes (Arabic only) Offers different representations of digit shapes. System, Arabic, National, Context. Labels, text boxes, list boxes, combo boxes OLE Class Automatically set to a description of the OLE Object contained in the frame. Unbound object frames OLE Type Determines whether the frame contains an OLE Object and whether it s embedded or linked. Unbound object frames OLE Type Allowed Specifies the type of OLE Object the frame can contain: Linked, Embedded, or Either. Unbound object frames, bound object frames 1 TABLE QR- 24 The Remaining Control Properties (continued) QR.indd 34 1/4/07 2:52:01 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Option Value Specifies the value to return when an option is selected. Applies only to controls in forms. Option buttons, check boxes (in option groups only) Page Index Specifies the page position in the page collection in a tab control. Pages Picture Path or filename for graphic to be loaded into control. Default is (none). Toggle buttons, command buttons, images, pages Picture Alignment Specifies the alignment of the image within the frame. Top Left, Top Right, Center, Bottom Left, Bottom Right. Images Picture Tiling Yes/No. Specifies whether picture is tiled across the entire image control. Images Picture Type Specifies graphic as embedded or linked. Toggle buttons, command buttons, images, pages Reading Order Determines the reading order of the control. Context, Left-to-Right, Right-to-Left. Labels, text boxes, command buttons, check boxes, list boxes, combo boxes, option buttons, toggle buttons Right Margin Specifies right margin for text within a control. Labels, text boxes Row Source Works with Row Source Type property to specify the table, query, SQL statement, field list, or value list as the row source. Combo boxes, list boxes, unbound object frames Row Source Type Specifies type of row source: Table/Query, Value List, or Field List. Combo boxes, list boxes, unbound object frames Running Sum No, Over Group, or Over All. Whether and how a text box displays a running total. Text boxes Scroll Bars None or Vertical. Displays or hides vertical scroll bar in Form view. Text boxes Scrollbar Align Places the vertical scrollbar on the right or left of the screen, based on the text orientation. System, Right, or Left. Text boxes, list boxes, combo boxes Shortcut Menu Bar Specifies the name of a global custom shortcut menu to display when the control is right-clicked. All controls except labels, subforms/subreports, lines, rectangles Show Only Row Source Value Yes/No. Shows only data value that matches the row source. Combo boxes Size Mode Controls how the contents of the object frame or the image are displayed. Clip, Stretch, Zoom. 35 Images, unbound object frames, bound object frames TABLE QR- 24 The Remaining Control Properties (continued) QR.indd 35 1/4/07 2:52:01 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 36 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Smart Tags Returns the names of the Smart Tags attached to the control. Combo boxes, labels, list boxes, text boxes Source Doc Specifies the file to create a link to or to embed in the frame. Unbound object frames, bound object frames Source Item Specifies the data within the Source Doc file to be linked when you create a linked OLE object. Unbound object frames, bound object frames Source Object Identifies the form or report that is the source of the subform or subreport. Subforms/subreports Special Effect Creates a 3-D appearance. Flat, Raised, Sunken, Etched, Shadowed, or Chiseled. Default depends on control type. All controls except toggle buttons, command buttons, pages Status Bar Text Specifies text to display in the status bar when the control is selected in Form view. All controls except labels, images, lines, rectangles Style Determines the tab style: Tabs, Buttons, or None. Tabs Subdatasheet Expanded Yes/No. Displays subdatasheets automatically expanded with datasheet. Standalone subforms, subreports Subdatasheet Height Specifies the maximum height for an expanded subdatasheet. Standalone subforms, subreports Tab Fixed Height Specifies tab height. Default 0 , which sizes tabs to fit contents. Tabs Tab Fixed Width Specifies tab width. Default 0 , which sizes tabs to fit contents. Tabs Tab Index2 Specifies control s place in the tab order on a form. Tab order begins with 0. All controls except labels, option buttons, check boxes, images, lines, rectangles, pages Tab Stop Yes/No. Determines whether you can press TAB to move focus to a control in Form view. All controls except labels, images, lines, rectangles, pages Text Align Determines placement of text in a control: General aligns text to the left, numbers and dates to the right. Left, Center, and Right options align all data types the same. Labels, text boxes, combo boxes Text Format Choose Plain Text to store only text. Choose Rich Text to store text as HTML and allow Rich formatting. Text boxes Top Margin Specifies right margin for text within a control. Labels, text boxes Transparent Yes/No. Specifies the command button as solid or transparent. Command buttons TABLE QR- 24 The Remaining Control Properties (continued) QR.indd 36 1/4/07 2:52:01 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Property Description Applies To Triple State Yes/No. Specifies how a check box, toggle button, or option button displays Null values. If Yes, Null values appear dimmed. If No, Null values appear as a No value. Toggle buttons, check boxes Update Options Specifies how a linked OLE object is updated: Automatic or Manual. Unbound object frames, bound object frames Validation Rule2 Specifies requirements for data entered into a control in Form view. If control is in an option group, the rule applies only to the group, not to individual options. Text boxes, option groups, toggle buttons, option buttons, check boxes, combo boxes, list boxes Validation Text2 Specifies text to display when the validation rule is violated in Form view. Text boxes, option groups, toggle buttons, option buttons, check boxes, combo boxes, list boxes Verb Specifies the index number of the operation to perform when an OLE object is activated. Unbound object frames, bound object frames Vertical Yes/No. Displays characters vertically from top to bottom and right to left in control. Labels, text boxes Vertical Anchor Anchors control to the top or bottom when the parent section grows vertically. Top is default. 37 ActiveX controls 2 1. 2. Can be set only as default control properties. Does not apply to controls within option groups, only to standalone controls. TABLE QR- 24 The Remaining Control Properties (continued) Table QR-25 lists the event properties that apply to form and report controls and describes when the events occur. Retrieving and Distributing Information The purpose of organizing and storing data is to enable you to retrieve only the information you want in the desired arrangement quickly and efficiently. After the data is filtered and sorted, it can be used as the basis for forms and reports to distribute outside the computer system. Filtering Records Filtering records limits the records in the datasheet, form, or report. Access provides four methods of filtering records: Filter By Selection leaves only the records with the same value as the one you selected in a field in one of the records. Filter Excluding Selection leaves only the records that don t include the same value as the one you selected in one of the records. QR.indd 37 1/4/07 2:52:02 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 38 Microsoft Office Access 2007: The Complete Reference Property When Event Occurs Applies To After the data in a control is updated Text boxes, option groups, toggle buttons, check boxes, combo boxes, list boxes, bound object frames Before Update* Before a control is updated with modified data Text boxes, option groups, toggle buttons, check boxes, combo boxes, list boxes, bound object frames On Change The contents of a control change Text boxes, combo boxes, tabs On Click User presses and releases the left mouse button All controls On Dbl Click User double-clicks the left mouse button All controls On Dirty A record has been modified, but not yet saved Text boxes, combo boxes On Enter* Before a control receives focus from another control or the first control in a newly opened form All controls except images, tabs, rectangles, pages On Exit* Before a control loses focus to another control on the same form All controls except images, tabs, rectangles, pages On Got Focus Control receives focus All controls except option groups, images, tabs, rectangles, pages, subforms/ subreports On Key Down Any key is pressed when the control has focus All controls except option groups, images, unbound object frames, subform/ subreports, tabs, rectangles, pages On Key Press A key or key combination is pressed and released when the control has focus All controls except option groups, images, unbound object frames, subform/ subreports, tabs, rectangles, pages On Key Up Key press is released when the control has focus All controls except option groups, images, unbound object frames, subform/ subreports, tabs, rectangles, pages On Lost Focus Control loses focus All controls except option groups, images, tabs, rectangles, subforms/subreports, pages On Mouse Down The mouse button is pressed All controls On Mouse Move The mouse moves All controls On Mouse Up The mouse button is released All controls On Not In List User types an entry that isn t in the combo box list of values Combo boxes On Undo All edits in the form have been reversed Text boxes, combo boxes On Updated An OLE object s data has been updated by the source program Unbound and bound object frames After * Update* Does not apply to controls within option groups, only to the groups themselves. TABLE QR- 25 Form and Report Control Event Properties QR.indd 38 1/4/07 2:52:02 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference 39 Filter By Form Screens records using the criteria that you entered into a table skeleton. Advanced Filter/Sort Gives you, in addition to filtering, the ability to sort the records by two or more fields using different orders: ascending or descending. Entering Filter Criteria When you choose to use the Filter By Form or Advanced Filter/Sort method, you enter a filter criteria, which is an expression defining the conditions under which the record is to be included when the filter is applied. If the expression evaluates to True, the record is included in the filtered recordset; if not, the record is excluded. Creating Expressions Expressions are used for many purposes in Access: to create calculated fields, to impose validation rules, to specify default values, and to provide criteria for filters and queries. Expressions can contain elements such as field name identifiers, functions, literals, constants, and operators. Identifiers are references to fields in expressions and may consist of more than one part, depending on the source of the field. If the field is in the active table, enclose the field name in brackets. If the field is in a related table, include the name of the table: [Alpha Entry]![DR]. The exclamation point (!) operator indicates that what follows in this case, the field named DR has been defined by the user. The period (.) operator indicates that what follows was named by Access. Built-in functions can be used in an expression to return a value. Access provides more than 200 built-in functions in 15 categories such as Database, Error Handling, Financial, Math, and Messages. All these functions are available in the Expression Builder. You can also use user-defined functions in expressions. Literals are values that Access uses exactly as you see them. For example, the string Welcome , the number 98, and the date #01-15-07# are all literals. String literals are enclosed in quotation marks and date literals are enclosed in the pound sign (#) date delimiters. Constants are string or numeric literals that don t change. True, False, and Null are examples of constants. Operators are special characters or keywords used to perform calculations or make comparisons. Arithmetic operators are used in mathematical expressions; comparison operators are used to compare one field value with another, a constant, or a literal; logical operators are used to compare two expressions or to negate a single expression. Arithmetic Operators The arithmetic operators include the familiar symbols from algebra, plus a few more. Table QR-26 describes the arithmetic operators. Comparison Operators The result of an expression using a comparison operator is True, False, or Null. If either of the expressions being compared evaluates to Null, the result of the comparison is Null. Table QR-27 describes the comparison operators used in expressions. The In and Between And operators are used in criteria expressions to specify a collection of values or a range of values. For example, the expression In("CA", "AZ", "NV") QR.indd 39 1/4/07 2:52:02 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 40 Microsoft Office Access 2007: The Complete Reference evaluates to True if the value in the corresponding field has any of the three values in the expression. Between And sets the upper and lower boundaries for values in the field. For example, using the criteria expression Between 145 And 250 includes all the records with a value from 145 to 250 in the corresponding field. The Like operator is used in criteria expressions to compare a Text or Date/Time field value to a string expression. The Like operator expression can make use of wildcards to perform an inexact search. Logical Operators Logical operators examine the result of two Boolean expressions. Boolean expressions evaluate to True, False, or Null. Logical operators can also be used to perform bitwise comparisons of numeric data. Table QR-28 describes the logical operators. Operator Result + Adds two numbers or numeric expressions. Finds the difference between two numbers or numeric expressions; if only one number or numeric expression, changes the sign. * Multiplies two numbers or numeric expressions. / Divides two numbers or numeric expressions and returns a floating point result. \ Divides two numbers or numeric expressions and returns only the integer portion of the quotient. Mod Divides two numbers or numeric expressions and returns only the remainder portion of the quotient. ^ Raises a number or numeric expression to an exponential power. TABLE QR- 26 Arithmetic Operators Operator Definition = Equal to < Less than <= Less than or equal to <> Not equal to > Greater than >= Greater than or equal to TABLE QR- 27 Comparison Operators QR.indd 40 1/4/07 2:52:03 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Operator Result And Performs a logical conjunction on two Boolean expressions. True only if both expressions are True. Eqv Performs a logical equivalence on two Boolean expressions. True only if both expressions evaluate to True or both to False. If either expression evaluates to Null, the result is Null. Imp Performs a logical implication on two expressions: Returns False only if Expr1 is True and Expr2 is False. Returns Null if both are Null, if Expr1 is True and Expr2 is Null, or if Expr1 is Null and Expr2 is False. Otherwise returns True. Not Returns the logical opposite of the expression or Null if the expression evaluates to Null. Or Performs a logical disjunction on two expressions. Returns True if either expression evaluates to True. Returns False only when both expressions evaluate to False. Returns Null if both are Null or one is Null and the other is False. Xor 41 Performs a logical exclusion on two expressions. If either expression evaluates to Null, the result is Null. Otherwise, evaluates to True if one and only one of the expressions evaluates to True. TABLE QR- 28 Logical Operators Queries An Access query is a set of explicit instructions that defines exactly what information you want and how you want it arranged. Several types of queries are available to retrieve information, perform calculations, display unmatched or duplicate records, update data, delete or append records, or create new tables from existing records. Queries fall into four major categories: select queries, special purpose queries, action queries, and SQL specific queries. The select queries include the following: Simple Select displays data from one or more tables sorted in a specific order. Find Duplicates displays all records in a table that have duplicate values in one or more specified fields. Find Unmatched displays records in one table that have no related records in another table. Special purpose queries include the following: Parameter displays a dialog box where you enter the criteria for retrieving data or a value to insert into a field. AutoLookup automatically fills in certain field values in a new record in one or more tables. Crosstab calculates a sum or count and group the results in a spreadsheet format that correlates the data with two types of information. QR.indd 41 1/4/07 2:52:03 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 42 Microsoft Office Access 2007: The Complete Reference Action queries include the following: Update makes global changes to a group of records in one or more tables. Append adds a group of records from one or more tables to the end of one or more other tables. Delete removes a specific group of records from one or more tables. Make-Table creates a new table out of data from one or more tables. Query Specifications Certain limits apply to queries such as the number of related tables, the number of criteria, and the size of recordset. Table QR-29 lists the query attributes together with their maximum capacities. Query Properties Several properties are common to most types of queries: Description contains a more informative description of the query and its purpose. Link Child Fields specifies the field name(s) in the subordinate object. Link Master Fields specifies the field name(s) in the main object. ODBC Timeout specifies the number of seconds to wait before a time-out error occurs. Attribute 32 tables* Number of enforced relationships 32 per table, minus the number of indexes on the table using fields or combinations of fields not involved in a relationship* Number of fields in the resulting recordset 255 fields Number of joins 16 Size of the resulting recordset 1 GB Character limit for sorting 255 characters in combined sort fields Number of nesting levels for queries 50 levels* Number of characters in a single cell in the query design grid 1024 characters Number of characters requested by a parameter query 255 characters Number of ANDs in a SQL WHERE or HAVING clause 99 ANDs* Number of characters in a SQL statement * Maximum Capacity Number of tables in a single query Approximately 64,000 characters* Maximum values may be lower if the query includes multivalued fields. TABLE QR- 29 Query Attributes QR.indd 42 1/4/07 2:52:03 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference 43 Orientation enables right-to-left features if using a language version of Office that reads visually from right to left. Record Lock specifies how records are locked when the query runs. No Locks (default), All Records, or Edited Records. Source Connect Str names the application used to create an external database. Source Database names the external host database. Subdatasheet Expanded displays the subdatasheet expanded with the main datasheet. Yes/No. Subdatasheet Height specifies the maximum height of the expanded subdatasheet: 2 . Subdatasheet Name specifies the associated subdatasheet. Table QR-30 describes the remaining query properties, together with the type of query to which each property applies and the settings they can assume. (Default values appear in boldface.) Aggregate Functions When you want to perform calculations with a query, you can add the Total row to the query design grid by right-clicking in the grid and choosing Totals from the shortcut menu. Or on the Design tab s Show/Hide group, click the Totals command. The Totals drop-down list includes 12 options, 7 of which are aggregate functions that perform calculations on the field specified in the Field row. To remove the Totals row, repeat one of the preceding actions. When used in a query, the aggregate functions don t include records with blank (Null) values in the calculations. Table QR-31 describes the aggregate functions and the data types with which they can be used. Five other options are included in the Total drop-down list: Group By defines the field group you want to summarize with an aggregate function. Expression enables you to enter an expression that creates a calculated field, which includes an aggregate function. Where specifies the criteria for a field that isn t used in a grouping. First returns a field value from the first record in the result set. Last returns a field value from the last record in the result set. Producing Reports As you work on a report design, you can switch to Layout view to see how it looks and make additional changes. When you switch to Print Preview, you can see how the report data is presented and how it will look on the printed page. Once the report is satisfactory, you need to set up the page margins and other options. Page Setup and Options With the report open in Layout view, the Page Setup tab contains many of the commands you need to set margins or choose page layout. The easiest way to do this is to open the Page QR.indd 43 1/4/07 2:52:04 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 44 Microsoft Office Access 2007: The Complete Reference Property Description Query Types Column Headings Specifies the order or limits the number of columns. Data values in quotation marks, separated by commas. Crosstab Default View Specifies the default view for the query result. Datasheet, PivotTable, PivotChart. Select, crosstab, union, parameter Dest Connect Str Specifies the name of the database to contain the new table or the table that is to receive the appended records. Append, make-table Destination DB Specifies the type of application used to create an external database. Current is default. Append, make-table Destination Table Name of table to hold query results. Append, make-table Fail On Error Specifies whether to terminate if an error occurs when running a query against an ODBC data source. Yes/No. Update, delete Filter Specifies a filter expression saved with the query. Select, parameter Filter On Load Applies filter when the query runs. Yes/ No. Select, parameter Max Records Sets limit on the number of records returned from an ODBC database. Select, parameter Order By Specifies a sort order saved with the query. Select, parameter Order By On Load Applies sort order when query runs. Yes/No. Select, parameter Output All Fields Includes all fields. Yes/No. Select, parameter, append, make-table Recordset Type Specifies the type of recordset made available to a form. Dynaset, Dynaset (Inconsistent), Snapshot. Select, parameter, crosstab Top Values Specifies number or percentage of records containing top values in a specific field. Select, parameter, make-table, append Unique Records Returns unique records based on all the fields in the underlying data source, not only those in the query. Yes/No. Select, parameter, update, append, delete, make-table Unique Values Omits records with duplicate field values. Values in all fields in query result must be unique. Yes/No. Select, parameter, append, make-table Use Transaction Runs query as a single transaction. Yes/ No. Update, append, delete, make-table TABLE QR- 30 Additional Query Properties QR.indd 44 1/4/07 2:52:04 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Function Result Applies to Data Types Sum Adds the values in the field. Number, Date/Time, Currency, AutoNumber Avg Computes the average of the values in the field. Number, Date/Time, Currency, AutoNumber Min Finds the lowest value in the field. Text, Number, Date/Time, Currency, AutoNumber Max Finds the highest value in the field. Text, Number, Date/Time, Currency, AutoNumber Count Counts the number of non-Null values in the field. Text, Memo, Number, Date/ Time, Currency, AutoNumber, Yes/No, OLE Object StDev Computes the standard deviation of the values in the field. Number, Date/Time, Currency, AutoNumber Var Computes the variance of the values in the field. 45 Number, Date/Time, Currency, AutoNumber TABLE QR- 31 Aggregate Functions and the Data to Which They Apply FIGURE QR - 5 The Page Setup dialog box Setup dialog box. In the Page Layout group s Page Setup tab, click the Page Setup command to open the Page Setup dialog box (see Figure QR-5). The dialog box has three tabs: Print Options set the four page margins, specify to print data only, or split the page. The margin settings are measured in inches or in the unit of measure specified in the Windows Regional Settings Properties dialog box. QR.indd 45 1/4/07 2:52:04 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 46 Microsoft Office Access 2007: The Complete Reference Page, Set the orientation and paper size and specify the printer to use. Columns, Set the number, size, and layout of columns. Click the Page tab to specify the page options (see Figure QR-6). The Paper Size drop-down list offers nine different page sizes. Your printer may determine which sizes you can use to print the report. Also, depending on your printer, you can choose paper trays, some automatic and others manual. If you have more than one printer in the system, one is designated as the default printer in the Control Panel Settings. If you want to choose another printer, click Use Specific Printer and click the Printer button to display a list of available printers. If you re printing a report that contains multiple columns, you can set the options in the Columns tab (see Figure QR-7). The Grid Settings group specifies the number of columns across the page and the space to leave between the rows and the columns. The Column Size group controls the size of the columns, again in inches or the unit of measure set in the Windows Regional Settings Properties. Type the width of the column in the Width box. Type the height of the row in the Height box. Choose Same As Detail if you want the values in the Width and Height boxes to match the width and height of the Detail section of the report. The final options are in the Column Layout group; these options determine the order in which the data is placed in the columns: Down, Then Across, fills one column vertically, and then starts filling the next column to the right. Across, Then Down, places data in one row across the page, and then starts filling the next row below. FIGURE QR.indd 46 QR - 6 Page speci cations 1/4/07 2:52:05 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference FIGURE QR -7 47 Column speci cations Controlling the Print Process Keeping data in a single record or group together on the same page may be important at times. At other times, you might want to start a new page for each record, group, or section. All of these can be accomplished by setting section properties for specific sections as follows. Keeping Data Together Several report section properties can be combined to ensure that data is printed together in the same page or column: To keep record data together on a page or in a column, open the Property Sheet and set the Detail section Keep Together property to Yes. If the record data won t fit on the page, the property setting is ignored. To print a group header, all the detail records, and the group footer together on one page, choose the Keep Whole Group On One Page in the Group, Sort, and Total Pane. To print the entire group together on one page, set the Grp Keep Together Report property to Per Page. To print the entire group together in a column or row, set the Grp Keep Together Report property to Per Column. Starting on a New Page If you want to start each record, group, or section on a new page, use the section s Force New Page property. This property is available to all sections, except page headers and footers. If you want to print every record on a separate page, set the Force New Page property for the Detail section. The property has the following settings: Set to None, the default, the section prints on the current page. Set to Before Section, the section begins printing at the top of a new page. QR.indd 47 1/4/07 2:52:05 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 48 Microsoft Office Access 2007: The Complete Reference Set to After Section, the following section begins printing at the top of a new page. Set to Before & After, the current section prints at the top of a new page and the following section also prints at the top of a new page. If you want to force a page break only under specific conditions, you can add a page break to the report, and then use a macro to decide whether to activate it by setting the control s Visible property. You actually need two macros: one attached to the On Format event of the report Page Header section that sets the page break Visible property to No, and a second attached to the On Format property of the section that contains the conditional page break. Add a condition to the second macro under which the page break Visible property is changed to Yes. Cancel Blank Reports If you have a report based on a query and the query produces no records, you don t want to waste time setting up a blank report. To avoid this, set the report On No Data event property to the name of a macro or event procedure that cancels previewing or printing the report. If you use a macro, you can display a message with the MsgBox action that explains that no records are in the report and the report has been canceled. Then add the Cancel Event action to stop previewing or printing the report. If you use an event procedure, use the MsgBox statement to display the explanatory message, and then set the Cancel argument of the Report_NoData Sub procedure to True. Hiding a Section You can prevent any section from printing or appearing in preview by setting the section s Visible property to No. If you want to hide the section only under specific conditions, attach a macro or event procedure to the section s On Format event property. Information Exchange One of the strengths of Access is the nearly seamless exchange of data and other objects with other databases and with applications of a different nature. This section describes the easily imported and exported file types, the tools Access 2007 provides, and the specifications you can set in Access to govern the import and export process. Supported Image File Formats Some of the tools you need to exchange text, graphics, and other objects with other programs are included in Office 2007. Attachment fields can render these files without needing additional software. Table QR-32 lists the supported file formats. Compatible File Formats In addition to being able to exchange Access objects with other Access databases, you can exchange Access objects with other programs. Access can import or link to objects in other formats, as well as save or export Access objects to many of the same formats listed. Table QR-33 lists the database and other types of file formats compatible with Access 2007. QR.indd 48 1/4/07 2:52:05 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Graphics Filter File Format Computer Graphics Metafile .cgm CorelDRAW .cdr Device Independent Bitmap .dib Encapsulated PostScript .eps Enhanced Metafile .emf Exchangeable File Format .exif Graphics Interchange Format .gif Icon .icon, .ico Joint Photographic Experts .jpg, .jpeg, .jpe Kodak Photo CD .pcd Macintosh PICT .pct Portable Network Graphics .png Run Length Encoded Bitmap .rle Tagged Image File Format .tif, .tiff Windows Bitmap .bmp WordPerfect Graphics Filters 49 .wpg TABLE QR- 32 Supported Image File Formats Application Format Comments dBASE 5, III, and IV Linking (read/write) with 7 requires Borland Database Engine 4.x or later. Delimited text files Windows (ANSII) and DOS All character sets. Excel 5.0, 7.0 /95, 8.0/97, 9.0/2000, 10/2002, and binary workbook Import, link, and export to all versions. Fixed-width text files Windows (ANSII) and DOS All character sets. HTML, IDC/HTX 1.0, 2.0, 3.x, and 4.x Format 1.0 if a list, 2.0, 3.x, and 4.x if table or list. Lotus 1-2-3 .wk1 and .wk3 Import, link, and export to all versions. Paradox 3.x, 4.x, 5.0, and 7-8.0 Linking (read/write) with 8.0 requires Borland Database Engine 4.x or later. Programs and databases that support ODBC protocol SQL tables and other formats See Microsoft Knowledge Base for a list of ODBC drivers. XML All versions. TABLE QR- 33 Other Supported File Formats QR.indd 49 1/4/07 2:52:06 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 50 Microsoft Office Access 2007: The Complete Reference Option Description Settings File Format Specifies the file as containing values all the same length or separated by a delimiter. Fixed Width or Delimited Field Delimiter Character used to separate the values, if delimited. Choose comma (,) usually, or semicolon (;), {tab}, or {space} from the drop-down list Text Qualifier Character that encloses the text characters. Double or single quotation marks or {none} Language Specifies language range. English (default) or All Code Page Specifies the type of page used for storing text characters. Western European (Windows) (default); many other codes in the drop-down list Date Order Specifies the order of date numbers in a date value. MDY (default), DMY, DYM, MYD, YDM, YMD Date Delimiter Character that separates parts of a date value. Default is / but can be any character Time Delimiter Separates hours, minutes, and seconds in time value. Default is colon (:) but can be any character Four Digit Years Imports or exports year data with four digits. Check for Yes, clear for No Leading Zeros in Dates Imports or exports single-digit date values with leading zeros. Check for Yes, clear for No Decimal Symbol Enter character to be used as decimal point in numbers and currency values. Default is period (.) TABLE QR- 34 File Import Specifications Import and Export Options and Specifications When you import objects from another Access database, several options control the import process. In the Import Objects dialog box, click Options to expand the dialog box and select from the following options: In the Import group, choose Relationships, Menus and Toolbars, Import/Export Specs, and Nav Pane Groups. In the Import Tables group, choose Definition and Data, or Definition Only. In the Import Queries group, choose to import the queries As Queries or As Tables. When you export to an external Access database, you can export only the table definitions or both the definitions and the data. When working with text files, you can set import and export specifications to indicate text formatting. To set the specifications for a text file that you re importing, click Advanced QR.indd 50 1/4/07 2:52:06 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference 51 in any of the Import Wizard dialog boxes. Similarly, to set the export specification, click Advanced in any of the Export Wizard dialog boxes. Table QR-34 lists the specifications that you can set in the dialog box. TIP Many of the import and export specifications are set in the Windows Regional Settings Properties dialog box, but they can be overridden in the Import or Export Specifications dialog box on a case-by-case basis. Changing the Access Environment Microsoft understands no two work environments are exactly alike, so Access provides methods of changing many aspects of the appearance and behavior of the environment. You may be satisfied with Access just as it s installed, or you can change a number of the options in several categories. Access Wizards, Builders, and Add-Ins With Access 2007 supplying so many helpful wizards, builders, and add-ins, little reason exists to start a new database, form, report, or query from scratch. Table QR-35 describes many of the built-in wizards and how to launch them. In addition to the fleet of wizards, Access 2007 offers the help of several builders that can make other jobs easier. The built-in builders include the following: Color Builder displays a palette of ready-mixed colors and provides the tools to create custom colors. Expression Builder gives you the elements for creating expressions for macros, queries, and property sheets. Query Builder makes sure the syntax in the query is correct. Three managers are used in the background while you are running Access. All three are available in the Database Tools group s Database Tools tab: Add-In Manager installs and uninstalls wizards, builders, and add-ins. Also used to create your own wizards. Linked Table Manager keeps track of the linkages to tables between databases. Switchboard Manager creates and manages switchboard forms for customized applications. Setting Default Options When you click the Microsoft Office button and choose Access Options, a dialog box opens with a list of 10 pages in the left pane. The first 6 pages contain groups of related default options. You can use these dialog boxes to change the default settings for all the Access databases or only the current database. These default settings can be overridden for any specific database or project. Most of the settings take effect after the database has been closed and reopened. Table QR-36 describes the contents of each of the Options dialog box tabs. QR.indd 51 1/4/07 2:52:06 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 52 Microsoft Office Access 2007: The Complete Reference Wizard Purpose Start By AutoDialer Adds a control to a form or datasheet, or a button to a toolbar, that dials a selected phone number. On the Design tab s Controls group, click the Command Button command. Select Miscellaneous in the Categories list and choose the AutoDialer action. AutoFormat Applies predefined styles to existing forms or reports. Also creates custom styles. On the Arrange tab, click the AutoFormat command. Controls Helps create controls in a form or report Design view, including combo box, command button, list box, option group, subform, or subreport controls. On the Design tab s Controls group, click the desired control command. Crosstab Query Creates a query that summarizes data in a compact, spreadsheet-like format. On the Create tab s Other group, click the Query Wizard command and choose from the New Query dialog box. Database Splitter Splits databases into a front-end/back-end application for a multiuser environment. On the Database Tools tab s Move Data group, click the Access Database command. Documenter Displays, prints, or saves the design characteristics of any or all types of objects in the database. On the Database Tools tab s Analyze group, click the Database Documenter command and choose the object types that you want to include in the dialog box. Export Exports data to a text file, enabling you to specify field delimiters, data types, and other details of the data. On the External Data tab s Export group, click the command for the destination data type. Find Duplicates Query Creates a query that finds records with duplicate field values in a single table or query. On the Create tab s Other group, click the Query Wizard command and choose from the New Query dialog box. Find Unmatched Query Creates a query that finds records in one table that have no related records in another table. On the Create tab s Other group, click the Query Wizard command and choose from the New Query dialog box. Form Creates a new form. On the Create tab s Forms group, click More and choose Form Wizard in the context menu. Graph (Chart) Adds a chart to a form or report based on the data in a table or query. On the Design tab s Controls group, click the Insert Chart command. Import Imports from another database, a spreadsheet, an Exchange or Outlook folder, a text file, an HTML document, an XML document, or an ODBC database into an Access table. On the External Data tab s Import group, click the command to select the file type. Input Mask Creates a custom input mask for a field. Click Build in the Input Mask property box in table Design view. TABLE QR- 35 Built-In Wizards QR.indd 52 1/4/07 2:52:06 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Wizard Purpose Start By Label Creates mailing labels in many standard sizes and arrangements. Also allows customizing a label layout. On the Create tab s Reports group, click the Labels command. Lookup Creates a lookup column in a table that displays a list of values from which the user can choose. In table Design view, choose Lookup Wizard from the list of data types. In table Datasheet view, on the Datasheet tab s Fields & Columns group, click the Lookup Column command. Microsoft Office Word Mail Merge Helps to merge table data from Access with a document stored in Word. On the External Data tab s Export group, click the More command and choose Merge It With Microsoft Office Word. Performance Analyzer Analyzes any or all aspects of a database for performance efficiency and produces a list of suggestions for improving its performance. On the Database Tools tab s Analyze group, click the Analyze Performance command. PivotTable Creates an Excel PivotTable on an Access form for summarizing large quantities of data. On the Create tab s Form group, click the More Forms command and choose PivotTable in the context menu. Print Relationships Prints the diagram displayed in the Relationships window. On the Design tab s Tools group, click the Relationship Report command. Report Creates a report based on one or more tables or queries. On the Create tab s Reports group, click the Report Wizard command. Simple Query Creates a select query from the fields you choose from one or more tables. On the Create tab s Queries group, click the Query Wizard command and choose in the New Query dialog box. Subform/Subreport Field Linker Links fields in a main form and a subform, or in a main report and a subreport. Automatically called if you used the Subform/Subreport Wizard. If not, start manually in the main form Design view by selecting the subform control and clicking Build in the subform s Link Child Fields or Link Master Fields property box. Table Analyzer Helps create an efficient database examines a table and suggests reducing duplicate data by splitting the table into related tables. 53 On the Database Tools tab s Analyze group, click the Analyze Table command. TABLE QR- 35 Built-In Wizards (continued) QR.indd 53 1/4/07 2:52:07 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 54 Microsoft Office Access 2007: The Complete Reference Option Page: Popular Category Options Settings Top Options Always Use ClearType Check box ScreenTip Style Choose from list Show Shortcut Keys in ScreenTips Check box Color Scheme Blue, silver, black Default File Format Access 2000, 2002 2003, 2007 Default Database Folder Name of path New Database Sort Order Drop-down list of available languages User Name Enter name Initials Enter initials Language Settings Choose from Editing Languages dialog box Creating Databases Personalize MS Office Option Page: Current Database Application Options Application Title Enter database name Application Icon Browse for image Use as Form and Report Icon Check box Display Form Choose startup form from list Display Status Bar Check box Document Window Options Overlapping Windows, Tabbed Documents, Display Document Tabs Use Access Special Keys Check box Compact on Close Compacts and repairs database if it would reduce by 256K or more; check box Remove Personal Information from File Properties on Save Check box Use Window-Themed Controls on Forms Check box Enable Layout View for This Database Check box Enable Design Changes for Tables in Datasheet View Check box Check for Truncated Number Fields Check box TABLE QR- 36 Access Default Option Settings QR.indd 54 1/4/07 2:52:07 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference 55 Option Page: Current Database (continued) Picture Property Storage Format Preserve source image format, convert all picture data to bitmaps Display Navigation Pane Check box Navigation Options Opens Navigation Options dialog box Ribbon Name, Shortcut Menu Bar Choose from drop-down lists Allow Full Menus Check box Allow Default Shortcut Menus Check box Track Name Autocorrect Info Check box Perform Name AutoCorrect Check box Log Name AutoCorrect Changes Check box Show List of Values in: (current database only) Local indexed fields, local nonindexed fields, ODBC fields Don t Display Lists Where More Than This Number of Records Read Enter maximum number of records Default Colors Font, Background, Alternate Background, Gridlines Choose from palette Gridlines and Cell Effects Default Gridlines Showing Horizontal, Vertical Default Cell Effect Flat, Raised, Sunken Default Column Width In inches Font, Size, Weight Choose from drop-down lists Underline, Italic Check boxes Navigation Ribbon and Toolbar Options Name AutoCorrect Filter Lookup Options Option Page: Datasheet Default Font Option Page: Object Designers Table Design Drop-down list of field types Default Text Field Size Choose from drop-down list Default Number Field Size Choose from drop-down list AutoIndex on Import/Create List of characters on which to create indexes automatically Show Property Update Options Button Check box Show Table Names, Output All Fields, Enable AutoJoin Check boxes Query Design Font Query Design Default Field Type Choose font and size TABLE QR- 36 Access Default Option Settings (continued) QR.indd 55 1/4/07 2:52:07 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 56 Microsoft Office Access 2007: The Complete Reference Option Page: Object Designers (continued) SQL Server Compatible Syntax (ANSI 92) Name of template Name of template Always use Event Procedures Check box Settings Enable error checking and select error indicator color Check for Errors Check boxes to set rules such as unassociated label and control, new unassociated labels, keyboard shortcut errors, invalid control properties, and common report errors AutoCorrect Options Correct TWo INitial CApital letters, capitalize first letter of sentences, capitalize names of days, correct accidental use of cAPS LOCK key, replace text as you type When Correcting Spelling Ignore word in UPPERCASE, ignore words that contain numbers, ignore Internet and file addresses, flag repeated words, enforce accented uppercase in French, suggest from main dictionary only Custom Dictionaries Choose from drop-down list French Modes Choose from drop-down list Dictionary Language Choose from drop-down list Move After Enter Don t move, next field, next record Behavior Entering Field Select entire field, go to start of field, go to end of field Arrow Key Behavior Next field, next character Cursor Stops at First/Last Field Check box Default Find/Replace Behavior Fast search, general search, start of field search Confirm Record changes, document deletions, action queries Default Direction Proofing Partially enclosed, fully enclosed Report Template Error Checking Selection Behavior Form Template Forms/Reports This database, default for new databases Left-to-right, right-to-left Option Page: Advanced Editing TABLE QR- 36 Access Default Option Settings (continued) QR.indd 56 1/4/07 2:52:08 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference 57 Option Page: Advanced (continued) General Alignment Interface mode, text mode Cursor Movement Logical, visual Datasheet IME Control Check box Use Hijri Calendar Check box Show this Number of Recent Documents Enter number Status Bar Check box Show Animations Check box Show Smart Tags on Datasheets Check box Show Smart Tags on Forms and Reports Check box Show in Macro Design Names column, conditions column Printing Left, Right, Top and Bottom Enter in inches General Show Add-in User Interface Errors Check box Provide Feedback with Sound Check box Use Four-Digit year formatting This database, All databases Web Options Opens Web Options dialog box Open Last Used Database When Access Starts Check box Default Open Mode Shared, Exclusive Default Record Locking No locks, all records, edited record Open Database by Using Record-Level Locking Check box OLE/DDE Timeout (sec) Enter number Refresh Interval (sec) Enter number Number of Update Retries Enter number ODBC Refresh Interval (sec) Enter number Update Retry Interval (msec) Enter number DDE Operations Ignore DDE requests, enable DDE refresh Command Line Arguments Enter text Display Advanced TABLE QR- 36 Access Default Option Settings (continued) QR.indd 57 1/4/07 2:52:08 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 58 Microsoft Office Access 2007: The Complete Reference Programming with Macros and SQL Three programming structures are used in Access applications: Macros, SQL statements, and Visual Basic code. This section of the Quick Reference lists the specifications imposed on Access macros and describes the macro actions individually. SQL syntax is also discussed with descriptions of using SQL to construct Access queries. Macro Specifications The following specifications apply to macros created in Access 2007: Up to 999 actions in a single macro Up to 255 characters in a condition Up to 255 characters in a comment Up to 255 characters in an action argument Macro Actions Table QR-37 contains an alphabetical list of macro actions with a description of the action and any required and optional arguments. Required arguments appear in boldface. All macro actions can be run conditionally by entering a condition expression in the Conditions column of the macro action row. When you first start a new macro, all the macro actions may not show in the list for security. Click the Show All Actions command to show the complete list. The actions that do not appear in the shortened list are marked in Table QR-37 with an asterisk. SQL Select Statement Syntax Structured Query Language (SQL) is used in querying, updating, and managing relational databases. SQL can be used to retrieve, sort, and filter specific data to be extracted from the database. You can use SQL SELECT statements anywhere that a table name, query name, or field name is accepted. For example, you can enter a SQL statement in the Record Source property of a form or report. Like any language, SQL has reserved words that are used to construct a command or specify an action. The SQL syntax includes statements, clauses, operations, predicates, declarations, and aggregate functions. The following paragraphs list and describe each of these SQL components and provides examples of their use. A SQL statement is an expression that defines a command, such as SELECT, UPDATE, or DELETE, and may include clauses, such as WHERE and ORDER BY or predicates, such as ALL or DISTINCT. Statements are most commonly used in queries, recordset objects, and aggregate functions, but they can also be used to create or modify a database structure. The SELECT statement is the most common SQL statement and is created behind the scenes when you create a select query. The statement returns information from a database in QR.indd 58 1/4/07 2:52:08 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Macro Action Description Arguments* AddMenu Creates a custom menu bar for a form or report; a custom shortcut menu for a form, form control, or report; a global menu bar or global shortcut menu to replace the built-ins. Menu Name (ignored for shortcut menus, but required for custom and global menu bars), Menu Macro Name, Status Bar Text (ignored for shortcut menus) ApplyFilter Applies a filter, a query, or a SQL WHERE clause to a table, form, or report to restrict or sort the records in the table, or the records from the underlying table or query of the form or report. Filter Name (must be blank when applying server filter), Where Condition (must use one or both), Filter Type (default Normal) Beep Sounds a beep. None CancelEvent Cancels the event that caused Access to run the macro containing this action. None ClearMacroError Clears last error in the MacroError object. None Close Closes a specified window or the active window if none is specified. Object Type, Object Name, Save (default prompt) Close Database Closes current database. None CopyDatabaseFile* Copies the current database. Database File Name, Overwrite Existing File (default Yes), Disconnect All Users (default No) CopyObject* Copies the specified database object to a different database or to the same database or project under a new name. Destination Database, New Name, Source Object Type, Source Object Name DeleteObject* Deletes a specified database object. Object Type, Object Name Echo* Turns echo on or off while the macro r uns. Echo On (default Yes), Status Bar Text FindNext Locates the next record that meets the criteria set by the previous FindRecord action or set in the Find In Field dialog box. None FindRecord Finds the first instance of data that meets the criteria specified by the FindRecord arguments. Find What, Match (default Whole Field), Match Case (default No), Search (default All), Search As Formatted (default No), Only Current Field (default Yes), Find First (default Yes) GoToControl Moves focus to a specified field or control in the current record in an open form or datasheet. Control Name TABLE QR- 37 QR.indd 59 59 Macro Actions 1/4/07 2:52:09 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 60 Microsoft Office Access 2007: The Complete Reference Macro Action Description Arguments* GoToPage Moves focus in a form to the first control on the specified page. Page Number (if blank, focus stays on current page), Right (horizontal position), Down (vertical position); Right and Down are used together GoToRecord Makes specified record the current record in an open table, form, or query result set. Object Type (Table, Query, Form, Server View, Stored Procedure, or Function), Object Name, Record (default Next), Offset Hourglass Changes the mouse pointer to an image of an hourglass while a macro is running. Hourglass On (default Yes) LockNavigationPane Locks or unlocks Navigation Pane. None Maximize Enlarges the active window, so it fills the Access window. None Minimize Reduces the active window to a small title bar at the bottom of the Access window. None MoveSize Moves or resizes the active window. Right, Down, Width, Height (must enter at least one argument) MsgBox Displays a message box containing a warning or other information. Message (up to 255 characters), Beep (default Yes), Type (default None), Title ( Microsoft Access ) NavigateTo Moves to specified Navigation Pane group and category. Category and group On Error Defines Error handling behavior. GoTo (Macro Name), Fail OpenDataAccessPage* Opens a data access page in Page view or Design view. Data Access Page Name, View (default Browse) OpenDiagram* Opens a database diagram in Design view. Diagram Name OpenForm Opens a form in Form view, Design view, Print Preview, Datasheet view, PivotTable, PivotChart, or Layout view. Form Name, View (default Form), Filter Name, Where Condition, Data Mode, Window Mode (default Normal) OpenFunction* Opens a function in Design view or Print Preview. Function name (required), View (default Datasheet), Data Mode (default Edit) OpenModule* Opens a specified Visual Basic module at a specified procedure. Module Name, Procedure Name (must enter valid name in either argument) TABLE QR- 37 QR.indd 60 Macro Actions (continued) 1/4/07 2:52:09 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Macro Action Description Arguments* OpenQuery Opens a select or crosstab query in Datasheet view, Design view, or Print Preview. Query Name, View (default Datasheet), Data Mode (default Edit) OpenReport Opens a report in Design view or Print Preview, or prints the report immediately. Report Name, View (default Print), Filter Name, Where Condition, Window Mode (default Normal) OpenStoredProcedure* Opens a stored procedure in Datasheet, Design, or Print Preview, PivotTable, or PivotChart view. Procedure Name, View (default Datasheet), Data Mode (default Edit) OpenTable Opens a table in Datasheet, Design, Print Preview, PivotTable, or PivotChart view. Table Name, View (default Datasheet), Data Mode (default Edit) OpenView* Opens a view in Datasheet, Design, Print Preview, PivotTable, or PivotChart view. View Name, View (default Datasheet), Data Mode (default Edit) OutputTo Outputs the data in the specified table, query, form, report, data access page, server view, stored procedure, or function to a file in Excel (*.xls), MS-DOS text (*.txt), rich-text (*.rtf), HTML (*.htm), IIS (*.htx or *.idc), Snapshot (*.snp), Active Server (*.asp) format, or stored procedure. Object Type (default Table), Object Name, Output Format, Output File, AutoStart (default No), Template File, Encoding PrintOut* Prints the active object in the open database. Print Range (default All), Page From, Page To, Print Quality (default High), Copies (default 1), Collate Copies (default Yes) Quit Exits Access. Options (default Save All) RemoveAllTempVars Removes all temporary variables. None RemoveTemp Removes temporary variable. Name Rename* Renames a specified database object. New Name, Object Type, Old Name RepaintObject Completes any pending screen updates for the active database object or a specified database object. Object Type (leave blank for active object), Object Name (leave blank if Object Type is blank) Requery Updates the data in a specified control on the active object by requerying the control source. Control Name Restore Restores a maximized or minimized window to its previous size. None TABLE QR- 37 QR.indd 61 61 Macro Actions (continued) 1/4/07 2:52:09 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 62 Microsoft Office Access 2007: The Complete Reference Macro Action Description Arguments* RunApp* Runs another application, such as Excel, Word, or PowerPoint, from within Access. Command Line RunCode Calls a Visual Basic Function procedure. Function Name RunCommand Runs a built-in menu command, that is appropriate for the current view. Command RunMacro Runs a macro. Macro Name, Repeat Count, Repeat Expression RunSavedImportExport* Runs a selected import or export specification. Saved Import Export name RunSQL* Runs an Access action query by using the corresponding SQL statement. SQL Statement (max 256 characters), Use Transaction (default Yes) Save* Saves the active object or a specified object. Object Type (blank to save active object), Object Name (required if select Object Type) SearchForRecord Searches an object for a record based on a criterion. Object Type, Object Name, Record [First], Where Criterion SelectObject Selects a specified database object. Object Type (default Table), Object Name (not required if In Database Window set to Yes), In Database Window (default No) SendKeys* Sends keystrokes directly to Access or to an active Windows-based application. Keystrokes, Wait (default No) SendObject Includes the specified Access datasheet, form, report, data access page, or module in an electronic mail message. Object Type, Object Name, Output Format, To, Cc, Bcc, Subject, Message Text, Edit Message (default No), Template File SetDisplayedCategories Sets categories to show in the Navigation Pane. Show Y/N, Category name, or leave blank to see all SetMenuItem Sets the state of menu items (enabled or disabled, checked or unchecked) on the custom menu bar or the global menu bar for the active window. Menu Index, Command Index, Subcommand Index, Flag (default Ungray) SetProperty Sets control property Control name, property, value SetTempVar Sets temporary variable with given value. Name, Expression TABLE QR- 37 QR.indd 62 Macro Actions (continued) 1/4/07 2:52:10 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference Macro Action Description Arguments* SetValue* Sets the value of an Access field, control, or property on a form, a form datasheet, or a report. Item, Expression SetWarnings* Turns system messages, which can stop the macro, on or off. Warnings On (default No) ShowAllRecords Removes any applied filter from the active table, query result set, or form, and displays all records. None ShowToolbar* Displays or hides a built-in toolbar or a custom toolbar. Toolbar Name, Show (default Yes) SingleStep Pauses macro execution and opens the Macro Single Step dialog box. None StopAllMacros Stops all currently running macros. None StopMacro Stops the currently running macro. None TransferDatabase* Imports or exports data between the current Access database or project and another database. Links Access database tables. Transfer Type (default Import), Database Type (default Microsoft Access), Database Name, Object Type (default Table), Source, Destination, Structure Only (default No) TransferSharePointList* Imports or links from SharePoint site. TransferType (default import), Site Address, List ID, ViewID, Table Name, Get Lookup Display Values (No) TransferSpreadsheet* Imports or exports data between the current Access database and a spreadsheet file. Transfer Type (default Import), Spreadsheet Type (default Excel Workbook), Table Name, File Name, Has Field Names (default No), Range TransferSQLDatabase* Transfers complete database from the current server to another server. Server, Database, Use Trusted Connection (default Yes), Login, Password, Transfer Copy Data (default Yes) TransferText* Imports or exports text between the current Access database and a text file. Also links data in a text file or an HTML file to the current Access database. Also exports data to a Word Mail Merge file. Transfer Type (default Import Delimited), Specification Name (required for Fixed Width text files), Table Name, File Name, Has Field Names (default No), HTML Table Name, Code Page TABLE QR- 37 QR.indd 63 63 Macro Actions (continued) 1/4/07 2:52:10 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 64 Microsoft Office Access 2007: The Complete Reference the form of a set of records, a recordset. SELECT statements don t change any of the data in the underlying database. The SELECT statement syntax is shown here: SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]} FROM tableexpression [, ...] [IN externaldatabase] [WHERE... ] [GROUP BY... ] [HAVING... ] [ORDER BY... ] [WITH OWNERACCESS OPTION] Part Description predicate Restricts the number of records returned: ALL (default) returns all records. DISTINCT omits records with duplicate values in the selected fields. DISTINCTROW omits completely duplicate records. TOP n [PERCENT] limits the records returned to a specific number or percentage of records from the top or bottom of the data range, depending on the sort order. * Includes all the fields from the specified table or tables. Table Name of the table from which to select the records. field1, field2 Names of the fields to include in the returned recordset. If more than one field, they are retrieved in the order listed. alias1, alias2 Substitute names to appear in the column headers in the recordset in place of the field names. tableexpression Name of the table or tables that contain the data to retrieve. externaldatabase Name of the database that contains the tables from which to retrieve data, if not current database. The SELECT Statement Clauses The SELECT statement has several clauses that further refine the statement to retrieve only the data you want and in the arrangement you want to see. The FROM clause is the only clause required with a SELECT statement. FROM specifies the tables or queries that contain the data to be retrieved. You can use the IN clause with the externaldatabase part to specify tables or queries in other than the current database. FROM tableexpression [IN externaldatabase] The WHERE clause specifies the criteria to be used to select the records to return. A criteria expression follows the WHERE keyword. The WHERE clause is also used with the UPDATE and DELETE statements. WHERE criteria The GROUP BY clause combines records with the same values in the specified field list into a single record. The field list can contain up to 10 fields, the order of which QR.indd 64 1/4/07 2:52:10 PM CompRef8 / Microsoft Office Access 2007: The Complete Reference / Andersen / 350-4 Microsoft Office Access 2007: The Complete Reference 65 determines the grouping levels from the highest to the lowest. You can also add an aggregate function to summarize the values. GROUP BY groupfieldlist The HAVING clause specifies conditions that the grouped records must meet to be displayed, for example, to display records for customers whose total purchase exceeds $100. HAVING groupcriteria The ORDER BY clause specifies the sort order of the resulting recordset based on values in a specified field or fields. ASC and DESC are keywords used to specify whether the records are sorted in ascending or descending order. ORDER BY field1,[ASC | DESC][,field2[ASC | DESC]][, ]]] The last part of a SELECT statement is the optional WITH OWNERACCESS OPTION declaration, which gives the user who runs the query the same permissions as the owner of the query. The Join Operations When you use the FROM clause to retrieve records from more than one table, you must specify how the tables are related. For this, you need to add one of the following JOIN operations to the FROM clause. The INNER JOIN operation combines records from two tables if the matching values are in a field common to both tables. The INNER JOIN syntax is FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2 Part Description table1,table2 Names of the tables containing the records to be combined field1,field2 Names of the fields common to the two tables compopr The comparison operator: =, <, >, >=, <=, <> You can link with several ON clauses by combining the clauses with the logical AND and OR operators. The LEFT JOIN and RIGHT JOIN operations are also used with the FROM clause to create an outer join type. The parts and the syntax are the same as for the INNER JOIN operation. A left join or a right join may be nested within an inner join but an inner join may not be nested within a left join or a right join. QR.indd 65 1/4/07 2:52:11 PM

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

Formatting page ...

 

  Print intermediate debugging step

Show debugging info


 

 


© 2010 - 2026 ResPaper. Terms of ServiceContact Us Advertise with us

 

davinder84 chat