List of Figures

Chapter 1: What Is Microsoft Access?

Figure 1–1: Choose a date using the ActiveX calendar control.

Figure 1–2: The Attachment data type displays a picture in a form.

Figure 1–3: This query will retrieve information about products owned by contacts in the Conrad Systems Contacts sample application.

Figure 1–4: Here is the project file version of a query to retrieve information about products owned by contacts.

Figure 1–5: The query returns a list of contacts and the products they own.

Chapter 2: Exploring the New Look of Access 2007

Figure 2–1: You can choose privacy options when you first launch Access 2007.

Figure 2–2: You can create a database from a template, create a new blank database, or search for a database file to open on the Getting Started screen in Access 2007.

Figure 2–3: You can use the Open dialog box to find and open any existing database file.

Figure 2–4: When you open the Issues Sample database, you can see the new user interface for Access 2007.

Figure 2–5: You can view many commands by clicking the Microsoft Office Button.

Figure 2–6: The default Quick Access Toolbar contains the Save, Undo, and Redo commands for the current object, and the command to customize the toolbar.

Figure 2–7: You can add or remove commands on the Quick Access Toolbar and change their sequence using the Customize category in the Access Options dialog box.

Figure 2–8: You can add or remove commands on the Quick Access Toolbar for the current database by selecting your database from the Customize Quick Access Toolbar list.

Figure 2–9: Add a command to the Quick Access Toolbar by selecting it in the list on the left and then clicking the Add button.

Figure 2–10: Add a saved macro object to the Quick Access Toolbar by selecting it in the list on the left and then clicking the Add button.

Figure 2–11: You can change the button face and the display name in the Modify Button dialog box.

Figure 2–12: You can change the order of the commands on your Quick Access Toolbar by clicking the Move Up and Move Down arrow buttons.

Figure 2–13: Your two additional commands now appear on the Quick Access Toolbar for this database.

Figure 2–14: Access asks you to confirm resetting the Quick Access Toolbar back to the default commands.

Figure 2–15: The Message Bar alerts you if Access has disabled certain content.

Figure 2–16: You can enable blocked content from the Microsoft Office Security Options dialog box.

Figure 2–17: The Trust Center dialog box displays various categories in which you can select trust and privacy options.

Figure 2–18: The Trusted Locations category in the Trust Center dialog box shows you locations that are currently trusted.

Figure 2–19: Creating a new trusted location from the Microsoft Office Trusted Location dialog box.

Figure 2–20: The new Ribbon interface replaces menu bars and toolbars.

Figure 2–21: The Home tab provides common commands for editing, filtering, and sorting data.

Figure 2–22: The Create tab provides commands for creating all the various types of database objects.

Figure 2–23: The External Data tab provides commands for working with external data sources.

Figure 2–24: The Database Tools tab gives you access to miscellaneous tools and wizards.

Figure 2–25: The new Navigation Pane replaces the Database window from previous Access versions.

Figure 2–26: You can collapse the Navigation Pane to give yourself more room to work on open objects.

Figure 2–27: You can change the display in the Navigation Pane by selecting a different category or filter from the Navigation Pane menu.

Figure 2–28: You can display only the Forms group of objects in Object Type view by applying a filter in the Navigation Pane menu.

Figure 2–29: The Tables And Related Views category on the Navigation Pane menu offers a different way to view your database objects.

Figure 2–30: The Tables And Related Views category in the Navigation Pane groups objects under a table.

Figure 2–31: You can filter Tables And Related Views to show only the database objects dependent on one table.

Figure 2–32: The Created Date and Modified Date categories display objects in the order you created or last modified them.

Figure 2–33: Both Custom and Issues Navigation are custom categories available in the Issues Sample database.

Figure 2–34: The Issues Navigation category displays a custom view of the various database objects.

Figure 2–35: Right-click the top of the Navigation Pane and click Navigation Options to open the Navigation Options dialog box.

Figure 2–36: The Navigation Options dialog box lets you create and edit grouping and display options.

Figure 2–37: Four groups have been defined in the Issues Navigation category.

Figure 2–38: Click the Rename Item button when Custom is selected to rename that category.

Figure 2–39: You can rename the Custom group by typing a new name in the field.

Figure 2–40: Click the Rename Group button when Custom Group 1 is selected to rename that group.

Figure 2–41: When you click the Add Group button, Access creates another Custom Group 1 group.

Figure 2–42: The completed Issues Database Objects category now contains five groups.

Figure 2–43: After you select the new Issues Database Objects category, the Navigation Pane displays the custom groups you defined.

Figure 2–44: Access initially places all objects into the Unassigned Objects group after you create a custom category.

Figure 2–45: You can move several objects to your custom group at the same time by selecting them and clicking Add To Group from the shortcut menu.

Figure 2–46: After you move your objects to the first custom group, Access creates a shortcut to each object.

Figure 2–47: Group all your Issues reports together under Issues Reports by selecting them and clicking Add To Group from the shortcut menu.

Figure 2–48: All the form and report objects now have shortcuts in custom groups in the Navigation Pane.

Figure 2–49: Clear the check box next to Unassigned Objects to hide this group in the Navigation Pane.

Figure 2–50: The completed changes to the Navigation Pane now display only form and report object shortcuts in four custom groups.

Figure 2–51: To hide an object in a specific group, right-click it and click Hide In This Group from the shortcut menu.

Figure 2–52: You can hide a database object or an object shortcut from view in the Navigation Pane by selecting the Hidden check box in the Properties dialog box.

Figure 2–53: To rename an object shortcut in the Navigation Pane, right-click it and click Rename Shortcut.

Figure 2–54: After you click Rename Shortcut, Access unlocks the object shortcut name so that you can change it.

Figure 2–55: The customized Navigation Pane category and groups now display only form and report shortcuts.

Figure 2–56: Selecting the Show Hidden Objects check box causes Access to display any hidden object shortcuts in the Navigation Pane.

Figure 2–57: Access displays any hidden shortcuts, objects, or groups in the Navigation Pane when you select the Show Hidden Objects check box.

Figure 2–58: You can unhide a database object or an object shortcut from view in the Navigation Pane by clearing the Hidden check box in the Properties dialog box for the object or shortcut.

Figure 2–59: Clear the Show Hidden Objects check box to have Access hide any hidden object shortcuts, objects, or groups in the Navigation Pane.

Figure 2–60: The Sort By submenu on the Navigation Pane menu allows for further Navigation Pane sorting.

Figure 2–61: The View By submenu lists commands to view the Navigation Pane objects by Details, Icon, or List.

Figure 2–62: The Details view displays more information about each object in the Navigation Pane than Icon or List view.

Figure 2–63: Click the Remove Automatic Sorts command to manually sort your object list in the Navigation Pane.

Figure 2–64: Click and drag your form shortcut into a new position within the Issues Forms category.

Figure 2–65: Click the Search Bar command on the Display Options menu to display the Search Bar.

Figure 2–66: Select the Show Search Bar check box in the Navigation Options dialog box to display the Search Bar.

Figure 2–67: The Search Bar in the Navigation Pane helps you find specific database objects.

Figure 2–68: The Search Bar collapses any groups if it does not find any objects in that group that meet your search criterion.

Figure 2–69: Select Issues Forms from the Navigation Pane menu to show only that group in the Navigation Pane.

Figure 2–70: Access might not be able to find any objects that meet your criterion if your chosen display view is too restrictive.

Figure 2–71: You can limit your search to form objects by selecting the Object Type category and Forms group from the Navigation Pane menu.

Figure 2–72: After restricting the Navigation Pane to show only forms, text you enter in the Search Bar searches only in the Forms group.

Figure 2–73: All open objects appear in their own separate window when using the multiple-document interface.

Figure 2–74: All open objects appear on their own tabs when using the single-document interface.

Figure 2–75: The Document Window Options section in the Current Database category of the Access Options dialog box controls the interface mode.

Figure 2–76: With Tabbed Documents selected and the Display Document Tabs check box cleared, no tabs for open objects appear at the top of the object window.

Figure 2–77: Click the Microsoft Office Button and then click Access Options to open the Access Options dialog box.

Figure 2–78: The Popular category has general settings for your Microsoft Office system applications.

Figure 2–79: The Current Database category has general settings for the database currently open.

Figure 2–80: The Datasheet category has general settings to control the look of datasheets.

Figure 2–81: The Object Designers category has settings for working with database objects.

Figure 2–82: The Proofing category has settings for checking spelling and AutoCorrect.

Figure 2–83: The Advanced category has options for controlling editing, display, and printing.

Figure 2–84: The Customize category allows you to customize the Quick Access Toolbar.

Figure 2–85: The Add-Ins category lists any installed Access add-ins and COM add-ins.

Figure 2–86: The Trust Center category has links to privacy and security information and the Trust Center Settings button to view more options.

Figure 2–87: The Resources category has options for contacting Microsoft and utilities to repair problems with your 2007 Microsoft Office system applications.

Chapter 3: Microsoft Office Access 2007 Overview

Figure 3–1: In an Access application, you can design queries to extract data from or update data in tables; you can build forms or reports on tables or queries, and you can write code in macros or modules to automate your application.

Figure 3–2: Access 2007 displays the Getting Started window every time you start the program.

Figure 3–3: Use the Open dialog box to locate the database that you want to open.

Figure 3–4: The Navigation Pane displays the objects defined in the Housing Reservations sample database.

Figure 3–5: Select Object Type under Navigate To Category and then All Access Objects under Filter By Group to see all objects organized in groups by object type.

Figure 3–6: After filtering the Object Type category in the Navigation Pane, you can see only the tables in the Housing Reservations database.

Figure 3–7: You can access many commands from the shortcut menu for a table in the Navigation Pane.

Figure 3–8: Open a table in Design view to change its structure.

Figure 3–9: The Data Type list box shows you the available data types.

Figure 3–10: Use the Views button on the Ribbon or the individual view buttons on the status bar to switch from Design to Datasheet view.

Figure 3–11: When you filter object types by queries in the Navigation Pane, Access displays a list of only the queries in the Housing Reservations database.

Figure 3–12: The qryFacilityReservations query in Design view shows data from three tables being linked.

Figure 3–13: The Datasheet view of the qryFacilityReservations query shows you fields from three related tables.

Figure 3–14: When you filter Object Type by Forms, Access displays a list of only the forms in the Housing Reservations database.

Figure 3–15: When you open the frmEmployeesPlain form in Design view, you can modify its design.

Figure 3–16: The property sheet lets you set individual properties for a form, form sections, or controls on the form.

Figure 3–17: Layout view lets you see your data and also modify the design of the form.

Figure 3–18: You can move a control within a group in Layout view, and Access keeps them perfectly aligned.

Figure 3–19: The frmEmployeesPlain form in Form view lets you view and edit employee data.

Figure 3–20: You can filter the Navigation Pane to show only a list of the reports in your database.

Figure 3–21: Open the rptEmployeesPlain report in Design view to modify its design.

Figure 3–22: The property sheet lets you set individual properties for a report, report sections, or controls on the report.

Figure 3–23: When you open a report in Print Preview, Access shows you how the report will look when you print it.

Figure 3–24: Click the Two Pages button to see two pages side-by-side in Print Preview.

Figure 3–25: Similar to Layout view for forms, Layout view in reports lets you adjust design elements while looking at the data from your database.

Figure 3–26: Access makes it easy to move controls around within a group in Layout view.

Figure 3–27: When a report is in Report view, you can program controls to respond to mouse clicks to open a related form.

Figure 3–28: You can filter the Navigation Pane to show the Macros list in the Housing Reservations database.

Figure 3–29: Open the SampleMacro macro object in the Housing Reservations database in Design view to examine and modify its definition.

Figure 3–30: You can filter the Navigation Pane to display only the Visual Basic modules in the Housing Reservations database.

Figure 3–31: The Visual Basic Editor window displays the IsFormLoaded function in the modUtility module.

Figure 3–32: The Navigation Pane in a project file shows the tables in the database on SQL Server.

Figure 3–33: When you open a table in Design view in an Access project, you’re editing the table in the database on SQL Server.

Figure 3–34: The list of queries in an Access project shows the views, functions, and stored procedures saved in the database on SQL Server.

Figure 3–35: When you open a query in the query designer in an Access project, you’re editing the view, function, or stored procedure stored in the server database.

Figure 3–36: Although Access is primarily a desktop database system, you can use Access to build client/server applications.

Chapter 4: Creating Your Database and Tables

Figure 4–1: When you first start Access 2007, you see the Getting Started screen.

Figure 4–2: You access templates from Microsoft Office Online by selecting one of the categories to see a list of database templates for that category.

Figure 4–3: Choosing one of the database templates in the center of the screen shows you more information in the right task pane.

Figure 4–4: Use the File New Database dialog box to select a folder for saving the new database.

Figure 4–5: When you ask to download a template, Access verifies that you have a genuine copy of the 2007 Office release.

Figure 4–6: After you create the Contacts database from a template, Access opens the database and displays the Contact List form.

Figure 4–7: From the Getting Started screen, click Blank Database in the center to open the Blank Database task pane on the right.

Figure 4–8: When you create a new blank database, Access 2007 opens a new table in Datasheet view for you.

Figure 4–9: You can create the wedding invitee list table by entering data.

Figure 4–10: Double-click the column heading or click Rename in the Fields & Columns group on the Ribbon to rename a column in Datasheet view.

Figure 4–11: Access 2007 displays the Save As dialog box when you save a new table so that you can specify a table name.

Figure 4–12: The five types of table templates help you create common types of tables.

Figure 4–13: The Table Templates command builds a complete table with appropriate field types.

Figure 4–14: You can double-click a column heading in table Design view to change the name of the field.

Figure 4–15: The Table Design command opens a new table in Design view.

Figure 4–16: You can choose the data type of a field from a list of data type options.

Figure 4–17: Your fields in the Companies table should look like this. You’ll learn how to define validation rules in the next section.

Figure 4–18: You can choose from several built-in input masks in the Input Mask Wizard.

Figure 4–19: You can choose the placeholder character in the Input Mask Wizard.

Figure 4–20: You can choose to store formatting characters.

Figure 4–21: The wizard stores the input mask for PhoneNumber based on the criteria you selected.

Figure 4–22: You can easily define the primary key for the Companies table by selecting the field in Design view and clicking the Primary Key button on the Ribbon.

Figure 4–23: You can define a table validation rule in the property sheet for the table.

Figure 4–24: The datasheet for the tblDepartments table in the Housing Reservations sample database shows an expanded subdatasheet.

Figure 4–25: Access displays the Show Table dialog box when you open the Relationships window for the first time.

Figure 4–26: Drag the linking field from the “one” table (Companies) to the “many” table (CompanyContacts) to define the relationship between the tables.

Figure 4–27: The Edit Relationships dialog box lets you specify the linking fields in two tables.

Figure 4–28: Select multiple fields in the Edit Relationships dialog box to define a relationship between two tables using more than one field.

Figure 4–29: The Relationships window shows a graphical representation of all the main tables in your Contact Tracking database.

Figure 4–30: You can use the Indexed property box to set an index on a single field.

Figure 4–31: The FullName index includes the Last Name and First Name fields.

Figure 4–32: You can find settings that affect table design in the General section in the Advanced category of the Access Options dialog box.

Figure 4–33: You can set Name AutoCorrect options in the Current Database category of the Access Options dialog box.

Figure 4–34: You can find settings that affect table design in the Object Designers category of the Access Options dialog box.

Figure 4–35: You can select your default database file format in the Creating Databases section of the Popular category in the Access Options dialog box.

Figure 4–36: The Blank Database task pane appears on the right when you click the Blank Database command.

Figure 4–37: Save the Blank.accdb file in the correct subfolder in the Microsoft Office folder.

Figure 4–38: After you enter the correct name and select the correct location, you’re ready to create your new database template.

Figure 4–39: The Blank.accdb file must be located in the same folder as the local database templates.

Figure 4–40: You can select the objects you want to document in the Documenter dialog box.

Figure 4–41: The Database Documenter previews its reports on your screen.

Chapter 5: Modifying Your Table Design

Figure 5–1: The Back Up Database command creates a backup of your entire database file.

Figure 5–2: Click Object Type and Tables on the Navigation Pane menu to display only the tables in your database.

Figure 5–3: Click the Copy command to copy a table from the Tables list.

Figure 5–4: Enter the new name for the copied table in the Paste Table As dialog box.

Figure 5–5: The Object Dependencies feature tells you it needs to turn on Track Name AutoCorrect Info and examine all objects in your database.

Figure 5–6: The Object Dependencies pane shows you the list of objects that depend on the object you selected in the Navigation Pane.

Figure 5–7: This dialog box gives you the option of canceling the deletion of a table.

Figure 5–8: After clicking Rename on the shortcut menu, you can rename a table in the Navigation Pane.

Figure 5–9: This dialog box asks whether you want to replace an existing table with the same name.

Figure 5–10: You can change a field name, a field data type, and a field caption in Design view.

Figure 5–11: After renaming the fields in the Contacts table created from the template, it is beginning to look more like the table in the Conrad Systems Contacts sample database.

Figure 5–12: You can drag the EmailName field to a new position between the WorkCountry and Website fields.

Figure 5–13: The EmailName field is now correctly placed.

Figure 5–14: After moving several fields, the sequence of fields in your Contacts table is similar to that in tblContacts.

Figure 5–15: The Insert Rows command inserts a new row above a selected row or above the row in which the insertion point is located.

Figure 5–16: The Contacts table with additional fields inserted and descriptions defined.

Figure 5–17: Select the WorkAddress field and click the Copy command on the Home tab of the Ribbon to copy the field to the Clipboard.

Figure 5–18: You can paste the copied WorkAddress field into a new blank row.

Figure 5–19: The Contacts field list is now almost identical to tblContacts.

Figure 5–20: This dialog box asks you to confirm a field deletion.

Figure 5–21: Access 2007 can convert the Text data type to Hyperlink, but will get it right only if the text contains a recognizable protocol string.

Figure 5–22: This dialog box informs you of possible data truncation problems.

Figure 5–23: This dialog box informs you of conversion errors.

Figure 5–24: This dialog box appears if you decide not to save a modified table definition.

Figure 5–25: When you change a field description, you see a smart tag offering property update options.

Figure 5–26: This dialog box gives you the option of reversing unsaved changes to a table.

Figure 5–27: The opening page of the Table Analyzer Wizard informs you about the problems it is designed to correct.

Figure 5–28: Select the table you want to analyze in the Table Analyzer Wizard.

Figure 5–29: The Table Analyzer Wizard examines the data in your table and makes an initial recommendation.

Figure 5–30: After adjusting what the wizard proposed, you’re ready to create the new tables.

Figure 5–31: The Table Analyzer Wizard gives you the opportunity to fix potentially duplicate lookup values.

Figure 5–32: The final page of the Table Analyzer Wizard lets you decide whether you want a query to duplicate the original unnormalized data structure.

Figure 5–33: The Table Analyzer Wizard automatically separates your old data into the new table structure.

Figure 5–34: The DepartmentID field in tblEmployees in the Housing Reservations sample database has Lookup properties defined.

Figure 5–35: The Lookup tab settings show you a combo box in Datasheet view.

Figure 5–36: The table Lookup tab properties were inherited by the combo box on frmEmployeesPlain.

Figure 5–37: A Multi-Value Lookup Field control allows you to select more than one value for that field.

Figure 5–38: Access also provides a Multi-Value Lookup Field control in the frmContactsPlain form of the Conrad Systems Contacts database.

Figure 5–39: Set the Allow Multiple Values property to Yes to enable this field as a Multi-Value Lookup Field.

Figure 5–40: Select the new field that will become the primary key and then click Primary Key on the Design tab to define the key.

Figure 5–41: Click the Microsoft Office Button, Manage, and then Compact And Repair Database to open the dialog box for specifying a database to compact.

Chapter 6: Importing and Linking Data

Figure 6–1: The Microsoft ODBC architecture allows any ODBC-enabled application to link to any SQL database for which you have a driver.

Figure 6–2: Access can import every data type supported in a dBASE file.

Figure 6–3: The data in the first row of this Excel spreadsheet can be used as field names when you import the spreadsheet into a new Access table.

Figure 6–4: The Zip field entry contains data that can’t be stored in numeric format.

Figure 6–5: Access displays this error message when it encounters a problem with your primary key values.

Figure 6–6: Access displays this message at the top of the Save Import Steps page of the Get External Data-Excel Spreadsheet dialog box if it encounters data conversion errors while importing a spreadsheet.

Figure 6–7: Here is the import errors table that results from importing the spreadsheet shown in Figure 6–3.

Figure 6–8: After importing the spreadsheet shown in Figure 6–3, one row is missing a postal code entry, and there is a duplicate value in the ID column.

Figure 6–9: A comma-separated and double-quote-delimited text file uses commas between the field values and surrounds text values in double quotations marks.

Figure 6–10: A tab-separated text file uses tab characters to separate the fields.

Figure 6–11: A fixed-width text file contains data in fixed-width columns.

Figure 6–12: In the Advanced section of the Advanced category in the Access Options dialog box, you can set options that affect the performance of linked tables.

Figure 6–13: Access displays a message to confirm that you want to unlink a table.

Figure 6–14: You can use the Linked Table Manager to correct links to files that have moved.

Figure 6–15: You can begin the process of collecting data via e-mail by using the table’s shortcut menu or the Create E-Mail button on the Ribbon.

Figure 6–16: The first page of the e-mail collection wizard is an introductory page.

Figure 6–17: The second page of the wizard asks you to choose a type of form.

Figure 6–18: You can choose to collect new information or update existing records on this page of the wizard.

Figure 6–19: Select which fields to include on the form, in what order to display them, and what labels to display on the form.

Figure 6–20: Change the display order by moving the LastName field down two positions and type a more descriptive label for the FirstName field.

Figure 6–21: Choose whether to have the replies automatically processed or to manually process them yourself.

Figure 6–22: You can set options for processing e-mail forms in the Collecting Data Using E-Mail Options dialog box.

Figure 6–23: Access asks for the source of the e-mail addresses on this page of the wizard.

Figure 6–24: You can choose a field in the current table or query or in an associated table from which to obtain the e-mail addresses.

Figure 6–25: Select the associated table and field that contain the e-mail addresses.

Figure 6–26: Enter a descriptive subject line and introduction on this page of the wizard and specify where to place the e-mail address in the sent messages.

Figure 6–27: Access is now ready to create your e-mail form.

Figure 6–28: You can select the people to whom you want to send the message on the final page of the wizard.

Figure 6–29: You can preview your message if you choose to manually enter the e-mail addresses.

Figure 6–30: An e-mail data collection form has arrived in the Inbox.

Figure 6–31: After clicking Reply, you can fill in the fields on the form.

Figure 6–32: Access includes the lookup values at the bottom of the HTML form to help you enter the correct values in the form fields.

Figure 6–33: Access automatically processes the message when you receive it.

Figure 6–34: Our new contact information has now been added to the tblContacts table.

Figure 6–35: The first page of the wizard outlines the steps you take to collect data through e-mail.

Figure 6–36: Select the second option to create an InfoPath form.

Figure 6–37: You can choose to collect new information or update existing records on this page of the wizard.

Figure 6–38: Move all the fields in the Tasks table to the Fields To Include In E-Mail Message list.

Figure 6–39: Leave these check boxes cleared to manually process the replies.

Figure 6–40: You can choose a field in the current table or query or in an associated table from which to obtain the e-mail addresses.

Figure 6–41: Select E-mail Address from the field list for the Contacts table.

Figure 6–42: Enter a descriptive subject line and introduction on this page of the wizard and specify where to place the e-mail address in the sent messages.

Figure 6–43: Access displays potential issues that might cause problems with the data collection process.

Figure 6–44: You can select the people to whom you want to send the message on the final page of the wizard.

Figure 6–45: InfoPath forms allow you to use combo box controls for data entry.

Figure 6–46: Click Insert A Row on an InfoPath form to add additional records.

Figure 6–47: The recipient of the e-mail can add new records in the InfoPath form if you selected this option in the wizard.

Figure 6–48: Fill out the blank form to add a new record to the Tasks table.

Figure 6–49: Click Remove to delete a record in the InfoPath form.

Figure 6–50: The Data Collection Status column in Outlook indicates that the message has not been processed.

Figure 6–51: Use the message’s context-sensitive menu within Outlook to export the data to Access.

Figure 6–52: You can review the data to be exported to Access in this dialog box.

Figure 6–53: The updated and new data is now added to the Tasks table.

Figure 6–54: You can review the status of sent messages in the Manage Data Collection Messages dialog box.

Figure 6–55: The resend process takes you back to this data collection wizard page.

Chapter 7: Creating and Working with Simple Queries

Figure 7–1: The Navigation Pane has been filtered to show all the queries in the Conrad Systems Contacts database.

Figure 7–2: A query open in Design view shows the tables and field lists.

Figure 7–3: The Show Table dialog box allows you to select one or more tables or queries to build a new query.

Figure 7–4: The Query window in Design view for a new query on tblContacts shows the table with its list of fields in the top part of the window.

Figure 7–5: You can drag a field from the table field list to a column in the design grid.

Figure 7–6: In the property sheet, you can set properties for the BirthDate field.

Figure 7–7: The BirthDate field is now displayed with new property settings.

Figure 7–8: When you specify “CA” as the selection criterion in the design grid, Access returns only records with a WorkStateOrProvince equal to California.

Figure 7–9: When you specify the logical AND operator between two tests, the result is true only if both tests are true.

Figure 7–10: When you specify the logical OR operator between two tests, the result is true if either or both of the tests is true.

Figure 7–11: You can specify multiple AND and OR selection criteria in the design grid with additional OR lines.

Figure 7–12: The recordset of the query shown in Figure 7–11 shows only the records that match your criteria.

Figure 7–13: You can also restrict records by using Between, In, and Like all in the same design grid.

Figure 7–14: The recordset of the query shown in Figure 7–13 shows only the records that match your criteria.

Figure 7–15: If you use the Zoom window to enter an expression, you can see more of the expression and select a different font.

Figure 7–16: Here is a query result with concatenated text fields.

Figure 7–17: Use an expression to calculate the amount owed based on the daily rate.

Figure 7–18: Access displays the results of your calculated expression in Datasheet view.

Figure 7–19: This expression and criterion finds the rows that are different.

Figure 7–20: The datasheet now shows only the rows where the calculation is different than the stored value.

Figure 7–21: The Expression Builder dialog box helps you build simple and complex expressions.

Figure 7–22: Create a calculation using table field names in the Expression Builder dialog box.

Figure 7–23: Your completed expression in the Expression Builder dialog box should match this figure.

Figure 7–24: Switch to Datasheet view to see the result of your complex calculation expression.

Figure 7–25: You can change the Exprl and Expr2field names shown in Figure 7–16 to display more meaningful field names.

Figure 7–26: Access sorts the query results on the NewTotalCharge field in descending order.

Figure 7–27: Datasheet view shows the recordset of the query shown in Figure 7–25 sorted on the NewTotalCharge field.

Figure 7–28: This example sorts on two fields while maintaining the original field sequence in the query output.

Figure 7–29: Create a new query to test a proposed new field validation rule.

Figure 7–30: You can create a query to test a new table validation rule.

Figure 7–31: Open the Datasheet view of the qryContactsDatasheet query to begin learning about moving around and editing in a datasheet.

Figure 7–32: You can navigate through the datasheet records using the navigation buttons and Record Number box.

Figure 7–33: Click the plus sign to view the contact event details for the second contact in a subdatasheet.

Figure 7–34: The property sheet for the qryContactsDatasheet query displays the subdatasheet properties.

Figure 7–35: The Subdatasheet menu allows you to easily expand all subdatasheets, collapse all subdatasheets, or remove the currently displayed subdatasheet.

Figure 7–36: You can choose a different table to display other related information in a subdatasheet from the Insert Subdatasheet dialog box.

Figure 7–37: You can review all product information for a contact from the subdatasheet by expanding it.

Figure 7–38: You can select the old data by clicking the left side of the column.

Figure 7–39: You can then replace the old data with new data by typing the new information.

Figure 7–40: The Find And Replace dialog box allows you to quickly replace data in more than one record.

Figure 7–41: This message box asks whether you want to proceed with a paste operation.

Figure 7–42: This message box appears when you delete rows.

Figure 7–43: Place your mouse pointer over a hyperlink field in Datasheet view to show the hyperlink or the ScreenTip.

Figure 7–44: Here is the result of clicking a Web site link in the tblContacts table.

Figure 7–45: The dialog box used to insert a hyperlink shows you a list of files in the current folder.

Figure 7–46: You can choose a Web site address from a list of recently visited Web sites.

Figure 7–47: You can edit the text that defines a hyperlink directly in a datasheet.

Figure 7–48: You can sort contacts by birth date by using the sort buttons on the Ribbon.

Figure 7–49: After applying the second sort, the records are now sorted by state or province ascending and then by birth date descending within state or province.

Figure 7–50: Select the fields you want to sort in the Advanced Filter Design window.

Figure 7–51: After defining your sorts and clicking the Toggle Filter button, you can see the results of your sorting contact records by state or province, city, and then last name.

Figure 7–52: You can use the Find And Replace dialog box to search for data.

Figure 7–53: Here is the list of contacts in Pennsylvania, compiled using the Selection filter option.

Figure 7–54: Access displays a ScreenTip on the filter icon in the column header to show you what filter is applied.

Figure 7–55: The Filter window for date/time fields displays filter criteria based on the dates entered in the field.

Figure 7–56: Date Filters presents built-in date filters for periods and months.

Figure 7–57: Four contacts in the table have birthdays in the month of December.

Figure 7–58: Use Filter By Form to search for one of several states.

Figure 7–59: The contacts with names containing son in the states of OR and PA.

Chapter 8: Building Complex Queries

Figure 8–1: This query selects information from the tblFacilities and tblReservations tables.

Figure 8–2: Here you can see the recordset of the query shown in Figure 8–1. The facility information in the drop-down list comes from the Lookup properties defined in the tblReservations table.

Figure 8–3: In this example we are creating a complex query using four tables.

Figure 8–4: In Datasheet view you can see the recordset of the query shown in Figure 8–3.

Figure 8–5: This query is an attempt to find out which contacts have purchased both BO$$ Single User and the BO$$ Multi-User upgrade.

Figure 8–6: Here you are solving the “contacts who own two products” problem the right way by building a query on queries.

Figure 8–7: You can now correctly see the four contacts who purchased a BO$$ Single User edition and later upgraded.

Figure 8–8: The Join Properties dialog box allows you to change the join properties for the query.

Figure 8–9: This query design finds employees who have no reservations.

Figure 8–10: This recordset shows the two employees who have no reservations.

Figure 8–11: You can add a filter to your query to list reservation data for particular months.

Figure 8–12: An outer join query searches for employees not booked in June and July 2007.

Figure 8–13: These employees have no bookings in June and July 2007.

Figure 8–14: This query lists all the rooms and their room types in Facility 1.

Figure 8–15: This query determines which room types are not in Facility 1.

Figure 8–16: Select a query wizard in the New Query dialog box.

Figure 8–17: You can select tables or queries on the first page of the Find Unmatched Query Wizard.

Figure 8–18: This page is where you define the unmatched link.

Figure 8–19: On this page you select the fields to be displayed in a query.

Figure 8–20: The query wizard has helped you build a query that finds contacts who have purchased no products.

Figure 8–21: The Total row in the design grid allows you to define aggregate functions.

Figure 8–22: This query design explores many different aggregate functions.

Figure 8–23: Running the query in Figure 8–22 returns total revenue, average revenue, smallest revenue per reservation, largest revenue per reservation, and count of reservations by facility and month.

Figure 8–24: In this figure we are adding an expression and defining custom field names in a totals query.

Figure 8–25: This is the result in Datasheet view of the query shown in Figure 8–24.

Figure 8–26: Use the Department field to select the rows that will be included in groups.

Figure 8–27: This figure displays the recordset of the query shown in Figure 8–26.

Figure 8–28: Enter a Criteria setting for the TotCharge field to limit the records to months with more than $1,000 in total charges.

Figure 8–29: A spreadsheet mockup shows the result you want in your crosstab query.

Figure 8–30: This is a crosstab query in Design view.

Figure 8–31: This is the recordset of the crosstab query you’re building.

Figure 8–32: These entries in the property sheet fix the order of column headings for the query shown in Figure 8–31.

Figure 8–33: This crosstab query recordset has custom headings and custom column order, as defined in Figure 8–32.

Figure 8–34: Your crosstab query now shows a grand total on each row as an additional row heading, and all empty cells are filled with zero values.

Figure 8–35: This crosstab query uses partitioned values.

Figure 8–36: Run the crosstab query shown in Figure 8–34 to see the result of partitioning sales totals on ranges of room rates.

Figure 8–37: You can use query parameters to accept criteria for a range of reservation dates.

Figure 8–39: The Enter Parameter Value dialog box asks for the query parameter value.

Figure 8–38: Use the Query Parameters dialog box to assign data types for query parameters.

Figure 8–40: This figure displays the recordset of the query shown in Figure 8–37 when you reply with May 1, 2007, and May 12, 2007, to the parameter prompts.

Figure 8–41: The property sheet for select queries lets you customize the way the query works.

Figure 8–42: You can build a query that demonstrates setting both Unique Values and Unique Records to No when you’re using two tables.

Figure 8–43: Run your sample query to see the result of retrieving all rows across a join even though the output columns are from only one of the tables.

Figure 8–44: Even though your query uses two tables, when you set the Unique Records property to Yes, your query returns records that are unique in the one table that provides output columns.

Figure 8–45: When you set the Unique Values property to Yes, Access removes all the duplicate records.

Figure 8–46: To modify a query to use it as a subdatasheet that displays contacts, include the appropriate linking field.

Figure 8–47: Select the qryContactsDatasheetCOID query to provide the subdatasheet for this query.

Figure 8–48: This query shows company information with its contact subdatasheet information expanded.

Figure 8–49: You can expand the subdatasheet of the subdatasheet to see contact event information.

Figure 8–50: You can switch to Datasheet view to verify that you have correctly built the first part of a union query to display names and addresses.

Figure 8–51: The second part of a union query to display names and addresses displays the home addresses for persons who are not the primary contact for each company.

Figure 8–52: You’re going to copy the first part of your union query from the first query’s SQL view.

Figure 8–53: You can assemble a union query by copying and pasting the SQL from two other queries.

Figure 8–54: The union query displays the company address for all primary contacts and the home address for all other contacts.

Figure 8–55: This complex query generates the data you need for a PivotTable.

Figure 8–56: The reservations for June, July, and August are expanded into one row per day.

Figure 8–57: You can design PivotTables using the PivotTable design window.

Figure 8–58: This PivotTable shows fields added to all drop zones.

Figure 8–59: The PivotTable now shows two totals calculations and we are hiding all the details.

Figure 8–60: You can change a field’s caption in the Properties window for a field in a PivotTable.

Figure 8–61: You can look at the data in a PivotTable another way by “pivoting” the rows and columns and displaying totals only.

Figure 8–62: We are beginning to design a PivotChart on a query.

Figure 8–63: You can create totals and display them using a PivotChart.

Figure 8–64: In the Properties window you can add a title and legend to the PivotChart workspace.

Figure 8–65: On the Series Groups tab you can add an axis to your PivotChart.

Figure 8–66: Use the Add Data Label button to display labels on data points on your PivotChart.

Figure 8–67: The completed PivotChart shows revenue totals by month and department.

Chapter 9: Modifying Data with Action Queries

Figure 9–1: This select query finds weekly rates that will fail the new table validation rule.

Figure 9–2: This is the recordset of the select query shown in Figure 9–1.

Figure 9–3: The Query Type group on the Design contextual tab below Query Tools contains commands for the four types of action queries.

Figure 9–4: An update query shows an Update To row in the design grid.

Figure 9–5: This dialog box reports the number of rows that will be changed by an update query.

Figure 9–6: You can now see the updated data in the tblFacilityRooms table.

Figure 9–7: This dialog box asks you to confirm an action query.

Figure 9–8: This query finds daily rates greater than $90.

Figure 9–9: This query finds daily rates greater than $90.

Figure 9–10: This query finds contact events since July 1, 2007.

Figure 9–11: This query finds contacts with no contact events since July 1, 2007.

Figure 9–12: You can use the property sheet to assign an alias name to a field list in a query.

Figure 9–13: You can design a complex query to gather together many details about reservations.

Figure 9–14: Build a complex parameter query to expand reservation details over a specified time span.

Figure 9–15: This is the recordset of the select query shown in Figure 9–14, for the second quarter of 2007.

Figure 9–16: In the Make Table dialog box, type a name for your summary table.

Figure 9–17: This dialog box asks you to confirm the preliminary results of a make-table query.

Figure 9–18: The new table is the result of running the qxmplReservationDetailsMakeTable query.

Figure 9–19: In Design view, you can modify the design of the table created by the qxmplReservationDetailsMakeTablequery.

Figure 9–20: This query finds old contact events to archive.

Figure 9–21: After you click the Append button on the Ribbon, specify the target table of an append query.

Figure 9–22: In the Append To row you can specify the target fields in an append query.

Figure 9–23: This dialog box asks you to confirm the appending of rows.

Figure 9–24: This query uses a date parameter to select old contact events.

Figure 9–25: Enter the query date parameter when Access prompts you.

Figure 9–26: When you run the select query, you can verify the rows to delete.

Figure 9–27: Click the Delete button in the Query Type group on the Ribbon to convert your query to a delete query.

Figure 9–28: This dialog box asks you to confirm the deletion of rows.

Figure 9–29: This query allows you to safely delete archived rows.

Figure 9–30: This dialog box alerts you to action query errors.

Figure 9–31: You can design an append query to avoid duplicate row errors.

Chapter 10: Using Forms

Figure 10–1: The frmContactSummary form has a header, a detail section, and a footer.

Figure 10–2: This is the first page of a record in the multiple-page frmContactsPages form.

Figure 10–3: Here is the second page of the same record shown in Figure 10–2.

Figure 10–4: The frmLkpContactEventTypes form in the Conrad Systems Contacts database is a continuous form.

Figure 10–5: The frmProducts form in the Conrad Systems Contacts database is a split form.

Figure 10–6: The frmCompanies form has an embedded subform that shows the related contacts.

Figure 10–7: The frmAbout pop-up form “floats” on top of frmCompanies, which has the focus.

Figure 10–8: The fdlgContactSearch form in the Conrad Systems Contacts database is a modal form that opens as a Windows dialog box.

Figure 10–9: You can see option groups on the fdlgProductPrintOptions form.

Figure 10–10: A list box on the frmContactList form allows you to choose multiple contacts to edit.

Figure 10–11: When you click the arrow on a combo box, you can see a list of options.

Figure 10–12: The Contact Type field on the frmContactsPlain form is a Multi-Value Lookup Field control.

Figure 10–13: When you first open the frmContactsPlain form, you see information on the Contact Info tab.

Figure 10–14: When you click another tab in a complex form you can see different data.

Figure 10–15: Right-click on an attachment control to see a shortcut menu with a Manage Attachments command.

Figure 10–16: You can add and delete different data files bound to an Attachment data type using the Attachments dialog box.

Figure 10–17: Jeff’s document has now been added to the attachment field in the current record.

Figure 10–18: When you store multiple files in an attachment field, you can use the Forward and Back commands on the shortcut menu to view the files.

Figure 10–19: You can select a picture and then edit it by selecting Bitmap Image Object on the shortcut menu and then selecting Edit on the submenu.

Figure 10–20: The Photo OLE object field from Figure 10–19 is being edited “in place” with its host application.

Figure 10–21: The command buttons on the frmMain switchboard form take the user to various parts of the application.

Figure 10–22: The ptContactProducts form is designed to open in PivotTable view.

Figure 10–23: This form to edit product data also has an embedded subform in PivotChart view to show related sales information.

Figure 10–24: You can use the frmContactsPlain form in the Conrad Systems Contacts database to explore moving around on a form.

Figure 10–25: You can display the fsubContactEventsPlain subform in Datasheet view on the Events tab of frmContactsPlain.

Figure 10–26: The frmContactAdd form opens in Data Entry mode.

Figure 10–27: When you click the Add New button in the header of the frmContactsPlain form, Access displays the form in Data Entry mode.

Figure 10–28: The Microsoft Office Access dialog box allows you to enter data into an OLE object field.

Figure 10–29: You can insert an object from a file using the Create From File option in the Microsoft Office Access dialog box.

Figure 10–30: The Insert Hyperlink dialog box shows a link to the AccessJunkie.com Web site.

Figure 10–31: You can add a new contact event record on the Events tab in the frmContactsPlain form.

Figure 10–32: The Conrad Systems Contacts application shows you a custom error message when you attempt to delete a contact that has dependent records in other tables.

Figure 10–33: You can use the Find And Replace dialog box to search your records for specific information.

Figure 10–34: You can also use the Search box to search through your form records.

Figure 10–35: Enter filter criteria for the frmContactsPlain form in the Filter By Form window.

Figure 10–36: In the Advanced Filter/Sort window for the frmContactsPlain form, you can see criteria previously entered using the Filter By Form command.

Figure 10–37: When you view the frmContactSummaryXmpl form in Print Preview, you see a different set of headers and footers.

Figure 10–38: The Page tab of the Page Setup dialog box for forms includes several page options.

Figure 10–39: The Columns tab of the Page Setup dialog box for forms lets you define grid and column settings.

Chapter 11: Building a Form

Figure 11–1: An Access form can contain other objects, including other forms, and you can set some of its properties to define procedures that respond to events.

Figure 11–2: Some of the automated processes for the frmContacts form include opening a dialog box to choose a contacts report and automatically filling in the city and state when you enter a postal code.

Figure 11–3: When you click the Blank Form command on the Ribbon, Access opens a new Form window in Layout view.

Figure 11–4: When you open a form in Design view you can use the form grid and tools to create your form elements.

Figure 11–5: Select a record source to specify which table or query to use for the data on your form.

Figure 11–6: You can use the various commands on the two contextual tabs under Form Design Tools to create and edit your forms.

Figure 11–7: The field list shows the names of the fields in the bound table or query, any related tables, and fields from all other tables in the current database.

Figure 11–8: You can view the properties of form controls and sections using the property sheet.

Figure 11–9: If you click the Data tab on the form property sheet, Access displays only the data properties.

Figure 11–10: You can use the Query Builder to create a query for the form’s Record Source property.

Figure 11–11: Select all the fields from the table to include them in the query for the Record Source property of the form.

Figure 11–12: You can drag the fields from the qryCompaniesSortedByName field list to place these text box controls on the form design grid.

Figure 11–13: You can drag a corner handle of a selected control to change the control’s width or height or both.

Figure 11–14: You can drag the edge of a selected control to move the control.

Figure 11–15: You can drag the large handle of a selected control to move the control independently of its label.

Figure 11–16: The Font group provides you with tools to change the appearance of form controls.

Figure 11–17: You can use commands in the Controls group on the Design tab to customize the borders of your controls.

Figure 11–18: You can set the Enabled and Locked properties of the CompanyID text box control so that users cannot click into that control.

Figure 11–19: This is the property sheet for the CompanyID label control.

Figure 11–20: You can use the Caption property on the Format tab of the property sheet for the form to define a title for the form.

Figure 11–21: You can add contrast to the Companies/Organizations form by using the Fill/Back Color button.

Figure 11–22: Switch to Form view to see how the Companies/Organizations form looks so far.

Figure 11–23: Access can save you time by creating a single form using all the fields in the selected table.

Figure 11–24: When you click the Split Form command, Access creates a new split form based on your table.

Figure 11–25: Use the Multiple Items command to create a continuous form.

Figure 11–26: This datasheet form was created using the Datasheet command on the More Forms menu.

Figure 11–27: The first page of the Form Wizard displays fields you can select to include in your form.

Figure 11–28: You can select a style for your form on the third page of the Form Wizard.

Figure 11–29: The Form Wizard creates a form in a columnar format using the Solstice style that is very similar to the form produced with the quick create commands.

Figure 11–30: This Products form is in a tabular format using the Northwind style.

Figure 11–31: This is the Products form in a justified format using the Office style.

Figure 11–32: If you or Access has applied a control layout to the form controls, a box with a crosshair appears next to the controls.

Figure 11–33: You can now see the modified Products form in Design view with the changes you applied.

Figure 11–34: When you switch to Form view, you can see how the modified Products form looks at this point.

Figure 11–35: After you drop the CategoryDescription field onto the form grid, Access opens the first page of the Combo Box Wizard.

Figure 11–36: The Combo Box Wizard set these properties for the CategoryDescription field.

Figure 11–37: A combo box for the CategoryDescription field makes it much easier for the user to select a correct value.

Figure 11–38: The Products form now contains a check box control to display the TrialVersion field.

Figure 11–39: Your Products form now has both a combo box control and a check box control to simplify data entry.

Chapter 12: Customizing a Form

Figure 12–1: Start to build a form in Design view to display and edit employee data.

Figure 12–2: You can see in the Property Sheet windows for the EmployeeNumber and FirstName text box controls that the two controls are not aligned vertically.

Figure 12–3: With the bold font, the label controls are no longer large enough to fit the caption text.

Figure 12–4: After clicking the Size To Fit command, you can see all the text in the labels.

Figure 12–5: The form for employee data has controls sized to better fit the data.

Figure 12–6: The employees form has the controls arranged into columns that make sense.

Figure 12–7: This is how your employees form should look after you “snap” the controls to the grid.

Figure 12–8: The employees form has a column of labels selected.

Figure 12–9: The labels from Figure 12–8 are right-aligned.

Figure 12–10: The controls and labels are aligned horizontally and vertically.

Figure 12–11: This is your employees form with controls aligned and sized.

Figure 12–12: Access positions the EmployeeNumber control in the upper-left corner no matter where you drop it on the form grid.

Figure 12–13: Use the anchoring options to select different anchoring positions for your controls.

Figure 12–14: The Stretch Across Top anchoring option produces a very wide control.

Figure 12–15: Drag and drop the FirstName field below the EmployeeNumber field.

Figure 12–16: Access sizes all the same types of controls to the same height and width when you use a control layout.

Figure 12–17: You can quickly move a group of fields from the field list into a control layout in Layout view.

Figure 12–18: Drag the MiddleName label and text box controls into their correct position.

Figure 12–19: In a control layout, Access repositions controls when you move them around the form grid.

Figure 12–20: All the controls now match the field list display order.

Figure 12–21: Select all the labels by resting your mouse pointer on the top edge of the Employee Number label.

Figure 12–22: When you resize one control in a control layout, all other controls in the same column are also resized.

Figure 12–23: Click the Remove button to remove the control layout applied to the form controls.

Figure 12–24: Drag the selected controls into a new column.

Figure 12–25: The controls are now separated into two columns, but you still need to move and align them.

Figure 12–26: Click the Stacked button to apply a control layout to the controls on the left side of the form grid.

Figure 12–27: Access aligns all the controls on the left side of the form after you apply control layout.

Figure 12–28: Each column of controls is now within its own stacked control layout.

Figure 12–29: Use the commands in the Control Alignment group to align both control layouts together.

Figure 12–30: Change the control padding from Narrow to Medium to increase the space between the controls.

Figure 12–31: You now have more space between the controls after increasing the control padding.

Figure 12–32: Resize the EmployeeNumber, DepartmentID, and StateOrProvince text boxes to a smaller width.

Figure 12–33: You now have a good, basic employees form created entirely in Layout view.

Figure 12–34: You can group a set of controls and then move them together.

Figure 12–35: Use the Line tool to draw a line on a form; use the Border Thickness button to adjust the line width.

Figure 12–36: Use the Rectangle tool to place a rectangle with a default etched look on the employees form.

Figure 12–37: The employees form in Form view has a line and a solid rectangle added.

Figure 12–38: The rectangle behind the controls appears raised above the surface of the form background.

Figure 12–39: The first light gray rectangle appears to float on the form using special effects.

Figure 12–40: This is a partial list of fonts available to you in the Font Name combo box.

Figure 12–41: Adjust the size of the label to fit the form header title.

Figure 12–42: The employees form now has a title and some different fonts for variety.

Figure 12–43: You can select a format from the list of format settings for the BirthDate control, which uses the Date/Time data type.

Figure 12–44: When you click in the Notes text box, Access displays a scroll bar.

Figure 12–45: You can change the tab order on the form by using the Tab Order dialog box.

Figure 12–46: You can define smart tags for your controls using the Smart Tags dialog box.

Figure 12–47: You can select a smart tag action from the menu that appears.

Figure 12–48: The employees form now has views restricted and does not have a record selector or scroll bars.

Figure 12–49: The employees form as a pop-up form floats on top of the Navigation Pane and the Ribbon.

Figure 12–50: You can create your own custom AutoFormat definitions.

Figure 12–51: This is the zsfrmTemplate sample template form in the HousingDataCopy.accdb sample database.

Chapter 13: Advanced Form Design

Figure 13–1: The qryXmplEmployeesDepartmentManager query serves as the record source for your form.

Figure 13–2: Select a form style on the third page of the Form Wizard.

Figure 13–3: The Employees form is a many-to-one form to display data from multiple tables.

Figure 13–4: The DepartmentID control displays the related department name after you changed it to a combo box.

Figure 13–5: If you change the Department field for the employee, new related information is displayed automatically on this many-to-one form.

Figure 13–6: The Relationships window in the Conrad Systems Contacts application shows the relationships between companies, contacts, and products.

Figure 13–7: You can use this query to update the tblContactProducts table from a subform while displaying related information from the tblProducts table.

Figure 13–8: You can use this query to update the tblCompanyContacts table from a subform while displaying related information from the tblCompanies table.

Figure 13–9: When you use the Form Wizard to build a form on a query using two tables, the wizard offers data layout choices.

Figure 13–10: The Form Wizard created a continuous form to edit contact product information.

Figure 13–11: Here is your subform to edit contact products in Design view.

Figure 13–12: This is your contact products subform displayed in Continuous Forms view.

Figure 13–13: Your form to display company contact information is now beginning to take shape.

Figure 13–14: You can drag and drop one form from the Navigation Pane onto the Design view of another form to create a subform.

Figure 13–15: The contact products subform, embedded in your form, displays the products owned by a company contact.

Figure 13–16: Set the link field properties of the subform control to tell Access how the data in the outer form is related to the data in the inner form.

Figure 13–17: You now have a form to display company contact information with a subform that displays the related products owned.

Figure 13–18: This query sorts the contact records to be used in a form.

Figure 13–19: This is the start of your main form with space for a subform.

Figure 13–20: The new subform is embedded in the form to edit contacts.

Figure 13–21: You now have a form to edit contacts in a main form and products owned by the contact in subforms.

Figure 13–22: The contact products subform was changed to be displayed in Datasheet view.

Figure 13–23: Your form now displays company contact information in Datasheet view with a subdatasheet to display products.

Figure 13–24: Your modified form now allows you to edit contacts in a main form and products owned by the contact in subforms displayed in Datasheet view.

Figure 13–25: You can use this form to edit contact name and address information.

Figure 13–26: Add two option button controls inside an option group control.

Figure 13–27: You can use an option group to set the default address for the contacts.

Figure 13–28: Define conditional formatting for the ProductName field using the Conditional Formatting dialog box.

Figure 13–29: You can now see the effect of defining conditional formatting for the ProductName field.

Figure 13–30: You can also define conditional formatting for a group of controls.

Figure 13–31: The default address fields are highlighted and underlined in the contacts form based on the value of the DefaultAddress field.

Figure 13–32: The tab control allows you to place multiple subforms and controls on a tab page, such as this tab to edit contact events.

Figure 13–33: Your completed tab control in Design view shows three tabs with various controls.

Figure 13–34: The frmXmplContactsPages form includes a page break control that splits the Detail section exactly in half.

Figure 13–35: This is the first page of the frmXmplContactsPages form.

Figure 13–36: When you press Page Down, you can see the second page of the frmXmplContactsPages form.

Figure 13–37: You can use this basic form to edit contact events using standard controls.

Figure 13–38: The Insert ActiveX Control dialog box displays all ActiveX controls that are registered on your computer.

Figure 13–39: You can set custom properties for the Calendar ActiveX control in the Property Sheet window and in the control’s Custom Properties dialog box.

Figure 13–40: You can see the ActiveX Calendar control in action.

Figure 13–41: This sample query selects product sales data by product or by company.

Figure 13–42: The chart you’re building displays product sales by product and month.

Figure 13–43: This form displays product information with a sales chart in a subform.

Chapter 14: Using Reports

Figure 14–1: You can use the object shortcut menu to open a report from the Navigation Pane.

Figure 14–2: The rptContactProducts report in Print Preview shows sales data gathered from several tables.

Figure 14–3: The rptContactProducts report has a subtotal for each contact.

Figure 14–4: The rptProductSalesByProduct report’s grand total calculation is in the report footer.

Figure 14–5: Select Design View from the shortcut menu to open rsubCompanyProducts in Design view.

Figure 14–6: This is the Report window for the rsubCompanyProducts report in Design view.

Figure 14–7: Switch to Print Preview for the rsubCompanyProducts report to see a complex list of sales history.

Figure 14–8: The rptCompanyProducts report has an embedded subreport to display each company’s purchase history.

Figure 14–9: The rptInvoices report has an unbound bitmap image object (the Conrad Systems logo) embedded in the report header.

Figure 14–10: The Photo field in the rptContacts report is a bitmap image object stored in an attachment field.

Figure 14–11: When you click Open on the shortcut menu for the rptEmployeesPlain report, Access opens it in Report view.

Figure 14–12: The rptEmployeesPlain report is set to open in the new Report view.

Figure 14–13: In Report view you can filter the records to show just the ones you want to print.

Figure 14–14: After you apply the filter shown in Figure 4–13, Access shows only the three employees in the Finance department.

Figure 14–15: You can use Report view to respond to control events such as opening data entry forms.

Figure 14–16: Click the Two Pages button on the Ribbon to display a two-page view of the rptContacts report in Print Preview.

Figure 14–17: You can adjust the margins in the Page Setup dialog box.

Figure 14–18: You can set page orientation options on the Page tab of the Page Setup dialog box.

Figure 14–19: You can define properties for a specific printer to be used with your report.

Figure 14–20: You can set report column properties on the Columns tab of the Page Setup dialog box.

Figure 14–21: Print Preview now displays the rptContacts report in landscape orientation and in two columns.

Chapter 15: Constructing a Report

Figure 15–1: This query selects contact and contact event data for your report.

Figure 15–2: Click the Report Design button to start creating your report.

Figure 15–3: When you open a new Report window in Design view, Access displays all the tools you need to create the report.

Figure 15–4: You can create groups and specify their sort order in the Group, Sort, And Total pane.

Figure 15–5: After you click Add A Group in the Group, Sort And Total Pane, Access creates a new grouping specification and opens a field list to let you select the field that defines the group.

Figure 15–6: After you add a group in the Group, Sort, And Total pane, Access creates a new group level on the grid.

Figure 15–7: Click More to expand the list of grouping and sorting options.

Figure 15–8: The group interval displays different options based on the field’s data type.

Figure 15–9: You can ask Access to calculate and display totals in the Totals list.

Figure 15–10: Access displays the Zoom dialog box when you want to add a title to a group header.

Figure 15–11: You can choose to have Access create a group header for you.

Figure 15–12: Select the With A Footer Section option to include a footer section for the ContactID group on the report.

Figure 15–13: You can choose from among several options to control how the report will look when printed.

Figure 15–14: Access will now sort the contact event records for your report in descending order.

Figure 15–15: This is the completed Contact Events report that you will create in Design view.

Figure 15–16: This is how your completed Contact Events report looks in Print Preview.

Figure 15–17: Click the Report command to let Access build a report using the qryContacts query.

Figure 15–18: With one click, Access creates an entire formatted report for your convenience.

Figure 15–19: This report is easier to understand than the one created on a more complex query.

Figure 15–20: Select fields to include in the report on the first page of the Report Wizard.

Figure 15–21: Make sure to verify the primary grouping criteria on the second page of the Report Wizard.

Figure 15–22: You can set grouping intervals on the grouping fields in the Report Wizard.

Figure 15–23: Select ContactDateTime on the fourth page of the Report Wizard to sort on that field.

Figure 15–24: Click the Summary Options button on the fourth page of the Report Wizard to select additional summary options.

Figure 15–25: Choose a layout style and page orientation on this page of the Report Wizard.

Figure 15–26: You can specify a report title on the last page of the Report Wizard.

Figure 15–27: This is the first page of the Contact Events report created using the Report Wizard.

Figure 15–28: Open the Contact Events report in Layout view to begin making changes.

Figure 15–29: Drag the ContactDateTime label control to the left to resize the entire column.

Figure 15–30: When you make the ContactEventTypeDescription field wider, Access moves the other columns to the right.

Figure 15–31: Access resizes the control for you in Layout view when you enter a new caption for a label.

Figure 15–32: You can easily drag and drop controls into new positions using Layout view.

Figure 15–33: Change the properties of the Sum Of ContactFollowUp control in order to display an integer instead of a Yes or No value.

Figure 15–34: Click the left side of the report to highlight all the detail records.

Figure 15–35: You can select an alternating background color to provide more contrast to your detail records.

Figure 15–36: Your completed report now includes all the changes you made in Layout view.

Figure 15–37: Access always opens in Layout view when you click the Blank Report button.

Figure 15–38: Assign the qryRptContactEvents query as the new report’s record source.

Figure 15–39: After you add ContactID as a group level, Access adds that field to the report grid.

Figure 15–40: In Design view, you can see where Access has placed the various controls in the report sections.

Figure 15–41: Access adds the ContactDateTime field to the Detail section below the ContactID field.

Figure 15–42: Drag the Contact field and drop it below the ContactID field.

Figure 15–43: Access moves the other controls up after you delete the ContactID text box and label.

Figure 15–44: Access resizes the Contact and Phone fields together.

Figure 15–45: Drop the ContactEventTypeDescription field below the first ContactDateTime field.

Figure 15–46: Access places the ContactEventTypeDescription field into the stacked control layout with the ContactDateTime field.

Figure 15–47: You can remove control layouts by clicking the Remove button on the Arrange tab.

Figure 15–48: The tabular control layout arranges the controls with labels horizontally across the report.

Figure 15–49: Expand the width of the ContactEventTypeDescription field so that the data fits on one line.

Figure 15–50: Use the vertical l-bar to help you position the ContactNotes field.

Figure 15–51: After you drop the ContactNotes field on the report, Access adds a label and text box control to the appropriate report sections.

Figure 15–52: Your report is beginning to take shape with all the fields now in place.

Figure 15–53: Click the Count Values command to create a control to total the follow-ups for each contact.

Figure 15–54: Access creates an expression to count the number of True values for the ContactFollowUp field.

Figure 15–55: Click the Count Values option to create a control to total the event records.

Figure 15–56: Access now correctly displays a total of events for each contact.

Figure 15–57: Select one of the AutoFormats to give your report a more professional look.

Figure 15–58: Access makes several visual changes to your report when you select an AutoFormat.

Chapter 16: Advanced Report Design

Figure 16–1: The qryXmplRptReservationsByDay query for the Facility Occupancy By Date report returns one row per day in each reservation.

Figure 16–2: This is the initial Facility Occupancy By Date report created by the Report Wizard.

Figure 16–3: Your Facility Occupancy By Date report should look like this after you adjust what the wizard built.

Figure 16–4: Set your grouping and sorting criteria for the Facility Occupancy By Date report in the Group, Sort, And Total pane.

Figure 16–5: The Facility Occupancy By Date report has new footer sections after you define the grouping and sorting criteria.

Figure 16–6: This is a property sheet for a report section.

Figure 16–7: The property sheet for a report displays many properties that you can customize for the report object.

Figure 16–8: Use the Date function in an unbound control to add the date to a report.

Figure 16–9: You can now see the current date displayed in the report in Print Preview.

Figure 16–10: Use the Date And Time dialog box to assist you in building a report date control.

Figure 16–11: Use the Page and Pages properties to add page numbers to a report.

Figure 16–12: Use the Page Numbers dialog box to assist you in building a page number control.

Figure 16–13: Add an expression to the Detail section to calculate daily revenue.

Figure 16–14: The calculated detail line values within a group now appear in Print Preview.

Figure 16–15: Add summaries by facility, by month, and by date into the three footer sections.

Figure 16–16: Use the Report Footer section to create a grand total control for all records.

Figure 16–17: You can see the various totals displayed in the report in Print Preview.

Figure 16–18: Set the Hide Duplicates property to Yes to eliminate redundant values in each group.

Figure 16–19: A text constant and a string derived from a field in the record source are concatenated as a “label” in a text box.

Figure 16–20: The total lines now have descriptive captions using data from the record source.

Figure 16–21: You can add a calculation in the group footer for a percentage of a grand total.

Figure 16–22: At the end of the report, you can see percentage calculations for two groups in Print Preview.

Figure 16–23: For the Running Sum property of the new calculated text box, select Over Group to add a running sum calculation on the charge.

Figure 16–24: Use the Running Sum property to generate a line number.

Figure 16–25: You can see the result of using Running Sum to produce a cumulative total for each group and a line number for each detail line.

Figure 16–26: Set conditional formatting for the Sum text box in the first DateValue Footer section.

Figure 16–27: Change all the page margins to 1 inch.

Figure 16–28: Here is the result of setting conditional formatting for the two Sum text boxes.

Figure 16–29: This report displays departments with related employees in a subreport.

Figure 16–30: The top of the fourth page of the departments and employees report displays the header information.

Figure 16–31: The top of the fifth page of the departments and employees report has missing headers.

Figure 16–32: This design of a report and subreport handles the page overflow problem.

Figure 16–33: The top of the fifth page of the departments and employees report in this sample has headers correctly repeated.

Figure 16–34: This query for the subreport calculates revenue by facility and month.

Figure 16–35: This is your subreport to summarize revenue by month.

Figure 16–36: The design of your report now includes a subreport.

Figure 16–37: Your report now displays facility information with monthly revenue in a subreport.

Figure 16–38: This PivotChart form displays facility revenue by month.

Figure 16–39: Your report now includes an embedded PivotChart form as a subreport.

Figure 16–40: Here is your completed report with an embedded subreport and PivotChart in Print Preview.

Chapter 17: Understanding Event Processing

Figure 17–1: Applications running in Windows send messages and respond to events.

Figure 17–2: Use the WeddingEvents form to study event sequence.

Figure 17–3: Many events occur behind the scenes when you edit data on a bound form.

Chapter 18: Automating Your Application with Macros

Figure 18–1: A new Macro window displays columns where you can define your macro.

Figure 18–2: The list of macro actions displays 70 actions you can use in Access 2007.

Figure 18–3: Enter arguments for the MsgBox action to display a greeting message.

Figure 18–4: Enter a name for this test macro in the Save As dialog box.

Figure 18–5: The Macro Single Step dialog box allows you to test each action in your macro.

Figure 18–6: Access displays the dialog box you created by using the MsgBox action in the TestGreeting macro.

Figure 18–7: The AutoexecXmpl macro defines multiple actions that Access executes when you run the macro.

Figure 18–8: The two command buttons on the PrintOptions form run macros.

Figure 18–9: The DoReport macro group includes nine individual macros.

Figure 18–10: You can see that Access lists all macro objects and named macros in the various event properties.

Figure 18–11: In the Zoom window, you can see we added a condition in the DoReport macro group.

Figure 18–12: The property sheet lists any embedded macros attached to events.

Figure 18–13: The macro design window shows the embedded macro we created to respond to the Click event of the cmdPrint button on the fdlgNotTrusted form.

Figure 18–14: The Close button on the fdlgNotTrusted form executes an embedded macro to close the form.

Figure 18–15: Select Macro Builder in the Choose Builder dialog box to create an embedded macro.

Figure 18–16: The MsgBox action displays a message box in Access.

Figure 18–17: Your embedded macro now displays a message box before the form opens.

Figure 18–18: The AskEdit macro in the ValidateCitySetStateAndZip macro uses a temporary variable to indicate that the CityInformation form has been opened in Data Entry mode.

Figure 18–19: The RefreshCityList macro in the ValidateCitySetStateAndZip macro tests and sets temporary variables.

Figure 18–20: The ErrorTrapExample macro demonstrates error handling in Access 2007.

Figure 18–21: When you run the ErrorTrapExample macro, it first asks you whether you want to trap the error.

Figure 18–22: Access cannot divide a number by zero, so it displays an application error message.

Figure 18–23: Access displays an Action Failed dialog box if it encounters an unhandled error.

Figure 18–24: By trapping an error in a macro, you can display a helpful message to the user.

Figure 18–25: The DoReport macro uses the OnError action to handle the possibility that no records are returned in the report.

Figure 18–26: The error handling in the DoReport macro presents an informative message if the report contains no records.

Figure 18–27: Macro actions that are not trusted display an exclamation mark in the left column of the macro design window.

Figure 18–28: When triggered from an event on the WeddingList form, this macro opens the CityInformation form filtered on the city name.

Figure 18–29: Select the macro you created for the DblClick event of the City combo box control.

Figure 18–30: The CityInformation form displays a matching city in the WeddingList form.

Figure 18–31: You’ll create these conditions, actions, and comments for the SyncWeddingAndCity macro.

Figure 18–32: Associate the SyncWeddingAndCity macro with the On Current event property of the WeddingList form.

Figure 18–33: This figure shows the Macro window for the first two macros in the ValidateCitySetStateAndZip macro group.

Figure 18–34: The conditional expression in the TestCity macro uses the DLookup function to try to find the city in the CityNames table.

Figure 18–35: The RefreshCityList macro sets a temporary variable to indicate a requery is needed.

Figure 18–36: The SetStateAndZip macro uses SetValue actions to automatically fill in the State and Zip controls.

Figure 18–37: The Before Update and After Update event properties for the City control on the WeddingList form are set to run macros in the ValidateCitySetStateAndZip macro.

Figure 18–38: The ValidateCitySetStateAndZip.RefreshCityList macro executes when the AfterInsert event of the CityInformation form occurs.

Figure 18–39: The AskEdit macro displays a message box if you enter a new city.

Figure 18–40: The AskEdit macro then opens the CityInformation form where you can enter the details of the new city.

Figure 18–41: Access includes a command to convert a form’s macros to Visual Basic.

Figure 18–42: Access converted the macro from the Current event of the WeddingList form to Visual Basic.

Chapter 19: Understanding Visual Basic Fundamentals

Figure 19–1: To see all the modules in your database, click Modules under Filter By Group on the Navigation Pane menu when you have Navigate To Category set to Object Type. On the Create tab, in the Other group, click the arrow under the Macro command and then click Module to create a new standard module.

Figure 19–2: Use the Visual Basic Editor to view and edit all Visual Basic code in your database.

Figure 19–3: You can customize the Visual Basic Editor by using the settings on the Editor tab in the Options dialog box.

Figure 19–4: You can modify settings to help you debug your code on the General tab in the Options dialog box.

Figure 19–5: You can set a breakpoint in a Visual Basic module to help you debug your code.

Figure 19–6: You can execute a module function from the Immediate window.

Figure 19–7: When your Visual Basic code stops at a breakpoint, you can use the Locals window to examine variable and object values.

Figure 19–8: You can set a watch for when a variable’s value changes.

Figure 19–9: Visual Basic code halts immediately after a watch variable has changed.

Figure 19–10: The gintDontShowContactList variable is set to the value of a form control.

Figure 19–11: When your code is halted, you can see the chain of code executed to the point of the halt in the Call Stack dialog box.

Figure 19–12: You can explore objects in the Access application architecture from the Application object.

Figure 19–13: The Data Access Objects (DAO) model is specifically designed to manipulate data objects in an Access desktop database.

Figure 19–14: The ActiveX Data Objects (ADODB) and ActiveX Data Objects Extensions for DDL and Security (ADOX) models provide another way to work with the data and objects in your database.

Figure 19–15: The zfrmLoadData form in the Conrad Systems Contacts sample database makes it easy to load sample data.

Figure 19–16: This query returns person names in a random sequence.

Chapter 20: Automating Your Application with Visual Basic

Figure 20–1: Selling a product to a contact involves filling in the price and the default company.

Figure 20–2: The qlkpProductsForContacts query is the row source for the Product combo box on fsubContactProducts.

Figure 20–3: When you enter a product that isn’t defined in the database, the application asks if you want to add the new product.

Figure 20–4: The frmProductAdd form lets you define the details for the new product.

Figure 20–5: Click the calendar command button next to the ContactDateTime control on the Events tab of the frmContacts form to open a graphical form to select the date and enter the time.

Figure 20–6: The image control loads the photo on the Employees form from a picture path.

Figure 20–7: The application warns you about a potentially duplicate name in the contacts table.

Figure 20–8: Special business rule code won’t let you sell a product with a missing prerequisite.

Figure 20–9: The tblCompanyContacts table defines the many-to-many relationship between companies and contacts.

Figure 20–10: The Housing Reservations application displays a warning when you attempt to save an overlapping reservation request.

Figure 20–11: The form page doesn’t align correctly when you back-tab from the Home Address field in frmXmplContactsPages.

Figure 20–12: You can select multiple contact records to edit in the frmContactList form.

Figure 20–13: After you select the records you want to edit in the frmContactList form, the application opens the frmContacts form displaying only those records.

Figure 20–14: The multiple-selection list box on the frmContactList form has its Multi Select property set to Extended.

Figure 20–15: You can design a custom Query By Form to perform a complex search.

Figure 20–16: When you look at the fdlgContactSearch form in Design view, you can see that it has no record source.

Figure 20–17: This message box appears when the cmdSearch_Click procedure returns more than five rows.

Figure 20–18: You can select a specific contact from the search summary form.

Figure 20–19: You can solve a filtered combo box display problem by overlaying text boxes.

Figure 20–20: The qryContactProducts query provides the necessary ProductName and CategoryDescription fields from a related table so that you can display the values.

Figure 20–21: You can provide a link from the Companies / Organizations form to details about a particular contact.

Figure 20–22: You can ask to print only the current invoice in the Conrad Systems Contacts database.

Figure 20–23: The CityInformation form pops open over the main WeddingList form to display additional information about the invitee’s home city.

Figure 20–24: Logging a product sale event on the Events tab automatically sells the product to the contact.

Figure 20–25: The Unbooked Requests form lets administrators view pending requests and start the booking process.

Figure 20–26: The fdlgAvailableRooms form shows a list of available rooms matching the selected reservation request.

Figure 20–27: You can request mailing labels and specify that some labels have already been used on the first page.

Figure 20–28: The labels print and avoid the used ones.

Figure 20–29: This report uses a border around the data, but one of the text boxes isn’t large enough to display all the text.

Figure 20–30: Code in the rptContactsExpandNotes report draws a custom rectangle around expanded text.

Figure 20–31: A parameter dialog box opens from the report that you asked to view.

Figure 20–32: The Facility Occupancy report uses a shared filter dialog box to let you specify a date range.

Chapter 21: Publishing Data on the Web

Figure 21–1: A simple Web page is displayed in Internet Explorer.

Figure 21–2: A more complex Web page is shown with a hyperlink behind the graphic image.

Figure 21–3: The HTML view in Expression Web allows you to see the results of your code while editing the HTML.

Figure 21–4: You can load, edit, and save XML files using Access 2007.

Figure 21–5: On the World Wide Web, Web browsers connect to Web servers using TCP/IP.

Figure 21–6: You can create a simple HTML menu page easily using Expression Web.

Figure 21–7: This simple Web site displays data exported from an Access table.

Figure 21–8: You can export the data in an Access table as simple HTML using the Export-HTML Document dialog box.

Figure 21–9: This is data from the tblDepartments table exported to HTML using a simple format.

Figure 21–10: You can change settings that affect all datasheets using the options in the Access Options dialog box.

Figure 21–11: You can customize the format of an individual query in Datasheet view.

Figure 21–12: To see additional formatting options, click the Datasheet Formatting Dialog Box Launcher button.

Figure 21–13: Use the Datasheet Formatting dialog box to specify settings for a specific datasheet.

Figure 21–14: You can export a query to HTML and preserve its formatting.

Figure 21–15: You can choose to use an existing HTML template in the HTML Output Options dialog box.

Figure 21–16: The HTML page shows the formatted query you exported.

Figure 21–17: You can specify to use a custom template when you export data to 1–11 ML.

Figure 21–18: The HTML page displays the formatted query using the template specifications.

Figure 21–19: When you export a report to HTML, Access automatically keeps any formatting.

Figure 21–20: When you export the report to HTML with a template applied, you can see the template elements with the report formatting.

Figure 21–21: The completed Web page created by a Visual Basic procedure shows the current phone numbers of all employees.

Figure 21–22: A high-level schematic shows the delivery of database queries dynamically.

Figure 21–23: An Office Live Web site uses Windows SharePoint Services.

Chapter 22: Working with Windows SharePoint Services

Figure 22–1: A Windows SharePoint Services Web site allows you to collaborate and share information through a Web browser.

Figure 22–2: Click Common Tasks to create, edit, and customize the various elements of your Windows SharePoint Services Web site.

Figure 22–3: A list displayed on a SharePoint site resembles Datasheet view of a table in Access.

Figure 22–4: Click the New Item command to add a new record.

Figure 22–5: You can add new records via a view of the list that looks like a form.

Figure 22–6: You can see John’s record details being added to the form.

Figure 22–7: Windows SharePoint Services supports Rich Text Format for text fields.

Figure 22–8: John’s record has been added to the Employees list.

Figure 22–9: You can create new views of your lists within Windows SharePoint Services.

Figure 22–10: Windows SharePoint Services offers several built-in views for your lists.

Figure 22–11: On this page, you can choose options to customize your new Datasheet view.

Figure 22–12: You can now see your new Datasheet view of the Employees list.

Figure 22–13: The Create Column command adds a new column to the list.

Figure 22–14: On this page you can set properties for the new column.

Figure 22–15: To display the new Middle Name column, you have to add it to the Employee Datasheet view.

Figure 22–16: You can adjust the display positions for the columns on the Edit Datasheet View page

Figure 22–17: The new Middle Name column now appears in the Employee Datasheet view.

Figure 22–18: Windows SharePoint Services includes a Recycle Bin so you can recover deleted items.

Figure 22–19: John’s record can be restored from the Recycle Bin.

Figure 22–20: John’s record has now been completely restored.

Figure 22–21: Enter some contact records before exporting the Contacts table to a SharePoint list.

Figure 22–22: Click the SharePoint List button to start the Export-SharePoint Site wizard.

Figure 22–23: The Export-SharePoint Site wizard helps you export a table to a SharePoint list.

Figure 22–24: Your new Contacts table is now displayed as a list on the SharePoint site.

Figure 22–25: Access confirms whether the table export was successful on the last page of the wizard.

Figure 22–26: Click the SharePoint List button to start the import process.

Figure 22–27: You can import or link to Windows SharePoint Services lists using this wizard.

Figure 22–28: Select which lists to import to Access on the second page of the wizard.

Figure 22–29: The last page of the wizard asks if you want to save the import steps.

Figure 22–30: The Contacts list from the SharePoint site has now been imported as a local table into Access.

Figure 22–31: Click the SharePoint List button to start the Get External Data-SharePoint Site wizard.

Figure 22–32: Select the link option on the first page of the wizard to link to a list.

Figure 22–33: Select the list you want to link to on this wizard page.

Figure 22–34: Access now has a link to the Contacts list on the SharePoint site.

Figure 22–35: You can update a linked SharePoint list just like local tables or tables linked to other data sources.

Figure 22–36: You can interact directly with the Windows SharePoint Services interface from within Access by using commands on the shortcut menu of a linked list.

Figure 22–37: You can create new lists on a SharePoint site from within Access.

Figure 22–38: To create a new list, specify a name for the list and the location of the SharePoint site.

Figure 22–39: Access created a new list on a SharePoint site and a table linked to the list.

Figure 22–40: By default, the new Vacation Calendar list is displayed in Calendar view.

Figure 22–41: Enter a vacation record into the table to see it displayed on the SharePoint site.

Figure 22–42: John’s vacation schedule now appears as a block of time in the Calendar view.

Figure 22–43: The single-list view shows the details of John’s schedule.

Figure 22–44: Click the Custom option to create a custom list on the SharePoint site.

Figure 22–45: Enter a name for the custom list and the location of the SharePoint site.

Figure 22–46: By default, the new list includes three columns.

Figure 22–47: The new custom list shows up in the browser window.

Figure 22–48: Access displays all the hidden columns of the list in the table’s Design view.

Figure 22–49: The right task pane on the Getting Started screen includes an option to create links to a SharePoint site when you create the database.

Figure 22–50: Click Document Management Server to begin publishing your database.

Figure 22–51: Enter the address to your SharePoint site in the Publish To Web Server dialog box.

Figure 22–52: Select the Shared Documents library to publish the Contacts database.

Figure 22–53: The published database does not have active links to any list on the SharePoint site.

Figure 22–54: The Move To SharePoint button facilitates the process of moving your database to a SharePoint site.

Figure 22–55: On the first page of the wizard, enter the address of your SharePoint site and decide whether you want to upload a copy of the database.

Figure 22–56: You might need to log on to your SharePoint site before proceeding.

Figure 22–57: Select the document library where you want to save the database.

Figure 22–58: After selecting the document library in the workspace, click OK to save the database in that location.

Figure 22–59: The wizard displays the location on the SharePoint site where the migrated database will be saved.

Figure 22–60: Select the Show Details check box to see any issues Access encountered during the migration process.

Figure 22–61: The database on your computer is now linked to the SharePoint site.

Figure 22–62: Access creates a log table for any problems it encounters when moving your tables to Windows SharePoint Services lists.

Figure 22–63: The Issues Sample database has now been migrated to the SharePoint site.

Figure 22–64: Add some records to the Contacts table for this example.

Figure 22–65: Change the label caption in the Form Header section.

Figure 22–66: Navigate to the folder on the SharePoint site to republish the database changes.

Figure 22–67: When you open a published Access 2007 database using Edit In Microsoft Office Access, your browser downloads and opens a read-only copy of the database.

Figure 22–68: Internet Explorer asks if you want to open the database or save a copy for editing.

Figure 22–69: Select a folder to save a local copy of the database.

Figure 22–70: Use the Save option to download a local copy of the database for editing.

Figure 22–71: Reopen the Issues Sample database and verify that you are working online with the SharePoint site.

Figure 22–72: Click the Work Offline button to disconnect from the SharePoint site.

Figure 22–73: Your database is now disconnected from the Windows SharePoint Services lists, but you can still edit the data.

Figure 22–74: You can edit and add records while working offline.

Figure 22–75: Access displays a pencil icon next to records that you changed while offline.

Figure 22–76: The data on the SharePoint site changed while you were offline.

Figure 22–77: Click the Work Online button to reestablish links to the Windows SharePoint Services lists.

Figure 22–78: Access displays the Resolve Conflicts dialog box whenever data conflicts occur.

Figure 22–79: Access relinks the tables when you go back online.

Figure 22–80: The Issues list on the SharePoint site now includes all the changes we made while working offline.

Chapter 23: Using XML

Figure 23–1: The tblFacilities.htm file displayed in Windows Internet Explorer shows the data and the fields in the tblFacilities table.

Figure 23–2: Specify a destination folder and name for your exported XML document on the first page of the Export-XML File wizard.

Figure 23–3: Access displays the Export XML dialog box when you export a table to an XML file.

Figure 23–4: Click the More Options button shown in Figure 23–3 to display additional XML export customizing options.

Figure 23–5: You can select options to export the table definition on the Schema tab of the Export XML dialog box.

Figure 23–6: Select the Export Presentation check box on the Presentation tab in the Export XML dialog box to create an HTML file.

Figure 23–7: The Data tab options when exporting a form as XML are the same as when exporting a table.

Figure 23–8: The Presentation tab of the Export-XML dialog box lets you set options to include images when you export a form as XML.

Figure 23–9: The frmDepartments form exported as XML is shown here displayed in a Web page.

Figure 23–10: The rptDepartments report in the Housing Reservations database is exported as XML and displayed in a Web page.

Figure 23–11: Select the location and name of the XML file to import on the first page of the Get External Data-XML File wizard.

Figure 23–12: The Import XML dialog box displays options for importing XML files.

Figure 23–13: The two tables imported into Access from an XML file contain all the correct data and field properties.

Figure 23–14: The frmXMLExample sample form allows you to import XML data, edit the data, and then export the data when you have finished making your changes.

Figure 23–15: After you click the Load XML button, an XML file is loaded into a window in the form so that you can edit the data.

Figure 23–16: The original Contacts.accfl file includes schema information for 18 fields.

Figure 23–17: Access creates the MiddleInitial field because you added XML schema information.

Figure 23–18: The FirstName field now has no spaces when you use the revised table template.

Figure 23–19: Select the Show System Objects check box to display the USysRibbons table.

Figure 23–20: Access looks for a table called USysRibbons during startup to load custom Ribbons.

Figure 23–21: The Conrad Systems Contacts database includes three custom Ribbons.

Figure 23–22: You’ll have an easier time editing your XML for the USysRibbons table if you use a form.

Figure 23–23: Create a test form on the tblContacts table to use for your Ribbon testing.

Figure 23–24: Create a new record in the USysRibbons table for your test Ribbon by using the zfrmChangeRibbonXML form.

Figure 23–25: The simple XML you created earlier completely hides the Ribbon.

Figure 23–26: You can create custom tabs for your Ribbons.

Figure 23–27: The built-in Records group now appears on your custom Ribbon.

Figure 23–28: The custom Ribbon you created now includes buttons and commands from three built-in groups.

Chapter 24: The Finishing Touches

Figure 24–1: The standard Ribbon (top) displays many commands and tabs your end users won’t need, compared to the custom form Ribbon (bottom) from the Conrad Systems Contacts sample database.

Figure 24–2: In the Current Database category in the Access Options dialog box, you can select a specific custom Ribbon to load each time you open the database.

Figure 24–3: The main Ribbon in the Conrad Systems Contacts database displays custom controls.

Figure 24–4: You can load images from attachment fields onto custom controls in your Ribbons.

Figure 24–5: You now have limited options available when you click the Microsoft Office Button.

Figure 24–6: Use the TabSetFormReportExtensibility element to set focus to a specific tab.

Figure 24–7: You can select which objects to analyze from the eight tabs of Performance Analyzer.

Figure 24–8: Performance Analyzer displays recommendations to improve your application.

Figure 24–9: You can disable the ability to view objects in Layout view in the Access Options dialog box.

Figure 24–10: The main switchboard form for the Conrad Systems Contacts database has command buttons to guide users through the application.

Figure 24–11: This message box appears if the Switchboard Manager does not find a valid switchboard form and Switchboard Items table in your database.

Figure 24–12: To add a switchboard page to the main switchboard form, click the New button, and give your page a name.

Figure 24–13: Click the New button to create a new action on a switchboard page.

Figure 24–14: Make sure you create an action to return to the main switchboard form from another switchboard page.

Figure 24–15: The main switchboard form of our example shows options to other areas of the application.

Figure 24–16: You can set startup properties for your database in the Current Database category of the Access Options dialog box.

Figure 24–17: The design of this AutoKeys macro intercepts the Ctrl+F4 key combination.

Figure 24–18: Choose the Debug, Compile project-name command to compile all the Visual Basic procedures in your database.

Chapter 25: Distributing Your Application

Figure 25–1: The Database Splitter wizard helps you move the tables into a separate database.

Figure 25–2: You can’t edit any modules in the Contacts.accde database file.

Figure 25–3: You can modify the Target setting for a Windows shortcut in the shortcut’s Properties dialog box.

Figure 25–4: You must open your database in exclusive mode to encrypt the database with a password.

Figure 25–5: Enter your password in the Set Database Password dialog box.

Figure 25–6: Select the digital certificate you want to use to sign the package.

Figure 25–7: Enter a file name and location for your packaged database.

Figure 25–8: Click Open if you trust the publisher and want to open the database.

Figure 25–9: Select a location to extract the packaged database.

Appendix: Installing Your Software

Figure A-1: Click Install Now to install the default Office Ultimate 2007 programs.

Figure A-2: The Installation Options tab allows you to choose which programs and options to install

Figure A-3: Choose Run All From My Computer to install Access 2007 components.

Figure A-4: Select an installation folder on the File Location tab.

Figure A-5: Enter your personal information on the User Information tab.

Figure A-6: The setup program displays this message when the installation process completes.

Figure A-7: When you have previous versions of Microsoft Office programs installed, you can choose either Upgrade or Customize.

Figure A-8: You can choose to keep or remove existing Microsoft Office programs on the Upgrade tab.

Figure A-9: Click Install to install the prerequisite components needed for SQL Server 2005 Express Edition.

Figure A-10: Click Next to begin the installation process for SQL Server 2005 Express Edition.

Figure A-11: If your computer meets all the prerequisites for installation, you can click Next to proceed.

Figure A-12: Enter your name and company on this page of the setup wizard.

Figure A-13: Select the components you want to install on the Feature Selection page.

Figure A-14: Select an authentication mode to use with SQL Server 2005 Express Edition.

Figure A-15: Select user and administrator options on the Configuration Options page.

Figure A-16: These two options allow you to report errors and usage data to either Microsoft or your company’s IT department.

Figure A-17: Click Install to complete the installation process.

Figure A-18: The setup wizard displays a status message next to each installed component.

Figure A-19: You can review any errors that might have occurred during the installation on the last page of the setup wizard.

Figure A-20: The SQL Server Configuration Manager displays the status of your SQL Server services.