ACCESS LNC

week_8_pdf4.pdf

Home >Computer Science homework help >ACCESS LNC

Week 8 - What We'll Be Working On This Week

This week's lessons will introduce you to forms and reports in Access. We'll start by talking about the design characteristics of both object types, and use the Form Design window to orient ourselves to the elements that we find in both forms and reports. Then, when we look at reports in next week's lessons, much of what you have learned this week should help make much of that information easier to understand. We'll take a look at the different types of forms that you can create using the Form Wizard, and we'll use the Wizard to create forms based on one table, multiple tables, and a query. We'll add headers and footers to the forms, add a subform, and then create a form without the Wizard so you can see the difference. Once we've created our forms, we'll modify them by moving and resizing controls, and we'll add graphic elements, page breaks, and date and time variables. In addition, we'll see how to change the Tab Order in order to facilitate the data entry process.

Goals:

 Observing the design window of forms page 2

 Adding a header and footer data to a form page 16

 A look at the house of cards page 19

 Creating a Tabular form based on one table page 25

 Using the Form Wizard to create a form based on multiple related tables page 30

 Moving the subform object and hiding a field in the subform page 38

 Adding a subform manually page 42

 Starting with a blank form page 51

 Moving objects on a form page 56

 Resizing objects on the form page 63

 Changing field labels page 65

 Creating a rectangle unbound control on the form page 68

 Aligning objects on the form page 71

 Grouping objects on the form page 75

 Inserting a date variable on the form page 77

 Adding a page break to the form page 81

 Changing the Tab Order page 84

This is what's in store for this week, so let's get to it!

Goals for this section:

 Observing the design window of forms

 Adding a header and footer data to a form

 A look at the house of cards

Form and Report Design So far, we've created a few Forms in order to view our data from a different perspective. We used both tables and queries as our record source, but what we haven't addressed is the issue of when to use forms instead of tables and queries, and which object or objects are most appropriate for printing the data in your database. Datasheets are useful for viewing and changing data in your database, but they are not particularly attractive (nothing more than a grid of columns and rows) or simple to use. If you want to format your data in a special way or automate how your data is used and updated, you need to use a form. Forms provide a number of important capabilities. You can control and enhance the way your data looks on the screen. For example you can:

 add color and shading

 add number formats

 add controls such as list boxes and check boxes

 display ActiveX objects such as pictures and graphs directly on the form

 calculate and display values based on data in a table or a query

 perform extensive editing of data using macros or Visual Basic procedures

 link multiple forms or reports by using macros or Visual Basic procedures that are run from buttons on a form.

When we created our forms earlier, we noticed that Access automatically displayed all of the available fields vertically in the object window, and didn't give us the opportunity to specify a different configuration or design. However, once the form was created we could have easily gone into Design View and changed the size, location and properties of the fields, thereby customizing the display. This is one of the fundamental differences between forms and tables: The latter provides a very basic means of data entry and can never be displayed as anything but a grid of columns and rows, whereas the layout of the form can be custom-designed to make the data entry screen more attractive and user-friendly. In addition, if you base a form on a query, which in turn was based on multiple tables, then when you enter data into the form, you'll be entering data into multiple tables simultaneously. Herein lays the beauty of Access: If you design your table relationships, queries and forms well, you can populate multiple tables as you go about doing your data entry. What about the issue of printing the data in your database? Tables and queries can be printed, but what you'll get is a functional, relatively unattractive "dump" of the data. Will this work for you? Maybe, but you need to be aware of the fact that there are other options. As we're about to see, there are several different layouts that can be used for forms:

 Single form creates a single form based on the active object. The fields are listed vertically, and if appropriate, a subdatasheet is displayed if the table is in just one relationship. If a table has multiple relationships, a subdatasheet is not created in the form. Switching to Design View for this form you will see that Access creates a text box with a caption, and aligns all the controls. Access also creates a bitmap picture logo and a label for the form's title in the Header section.

This form could still use some modifications, such as entering a different form title and resizing some controls, but overall Access has completed a lot of the hard work of creating the form.

 Multiple Items Form is a continuous form based on all the fields in the active object.

More than 1 record at a time is displayed.

 Split Form allows you to simultaneously display a record in a regular form view and also

see a list of records in Datasheet View. In the figure below, notice that the upper half of the form

displays the details about one specific customer and the lower half of the form displays the list of

other customers. You can use the Record: number box and the buttons in the lower-left corner of

the form to move through the records. The record that you view in the top half becomes the

highlighted record in the bottom half of the form.

 Columnar Form (created from the Form Wizard) is similar to the form created by clicking on the

Form icon. A record's fields are vertically configured on the page and one record at a time is

displayed. Notice that there is no default subdatasheet.

 Tabular Form (created from the Form Wizard) is similar to tables and queries in that they present

the data in columns and rows. However, the fields are boxed in a way that makes the data more

attractive.

 Datasheet Forms are identical to table datasheets. This form can be created either through the

Form Wizard or Create / Forms / More Forms group. The benefit of using the datasheet form

over a table, is that the form can be based on a query which in turn can be based on multiple

tables. Therefore, as you fill in the form you are actually populating multiple tables. Datasheet

forms are excellent for subforms that are embedded in another form, as you'll see soon.

 Justified Forms (created through the Form Wizard) initially will display the fields next to one

another. However, you can then move or resize the fields to suit your needs, and add such things

as page breaks, headers and footers.

OK, this explains the differences between forms and tables or queries in terms of display issues, but what

about printing, and how do reports figure into the picture? Basically, you should think of forms as primarily

a data entry and display tool. When it comes to printing your data, on the other hand, reports are the best

option. If you use the Report Wizard to generate your reports they'll be easy to create, and they will have

a much nicer appearance than tables or queries.

The main difference between forms and reports is that forms can be used for data entry and editing,

whereas reports are read-only. What this means is that when you have a report displayed on screen and

you find a mistake in the data, you'll have to go back to the record source (that is, the table or query on

which the report was based) and make the change there. If you find a data mistake in a form, on the other

hand, you can make the change directly on the form.

Forms and reports share many things in common:

 Both objects must have a record source, which may be either tables or queries.

 Once you specify the record source for the form or report, the fields from that record source become controls in the form or report design.

 If sort orders or filters have been saved in a table or query, they are automatically inherited by the form or report that is based on that table or query.

 The Design window of both forms and reports contains different sections: o The Form Header or the Report Header section contains all the information you want to

appear at the top of the first page of the printed form or report. In a Tabular form, the Form Header section will also contain the field names.

o The Page Header section contains all the information you want to appear at the top of every page of the report. In a Columnar form or report, the Page Header section will also display the field names.

o The Detail section is where the field controls will reside on the form or report.  The Page Footer section of the report contains all the information you want to

appear at the bottom of each printed page.  The Form Footer or the Report Footer section holds all the information you

want to appear at the end of the last page of the printed form or report.

Views

There are three different views when working with Forms.

 Design View - When you want to change the definition of a form (the structure or design, as opposed to the data represented in the form), you generally must open the form in Design view. The large window in the center is the form design window where you create the form's design. The two contextual tabs, Design and Arrange, on the ribbon are the action centers of form design. You will use the tools here to add and arrange the design elements of your form.

When the form is displayed in Design View, you can see either the Property Sheet or the Field List for

this form.

Property Sheet: Properties are named attributes of controls, fields, or database objects that are

used to modify the characteristics of a control, field, or object. Examples of these attributes are

the size, color, appearance, or name of an object. A property can also modify the behavior of a

control, determining, for example, whether the control is read-only or editable and visible or not

visible. Properties are used extensively in forms and reports to change the characteristics of

controls. Each control on the form has properties. The form itself also has properties, as does

each of its sections. These properties are displayed in a Property Sheet.

Format tab - determines how a label or value looks: font, size, color, special effects, borders, and scroll bars. Data tab - affects how a value is displayed and the data source it is bound to: control source, input masks, validation, default value, and other data type properties. Event tab - these are named events such as clicking a mouse button, adding a record, pressing a key for which you can define a response. Other tab - shows additional characteristics of the control such as the name of the control or the description that displays the status bar. Field List: The Field List displays a list of fields from the table or query the form is based on. You can add a bound control to the form by dragging fields from the Field List onto the form. Select

and drag them one at a time or select multiple fields by using the Ctrl or Shift key. Click the Add

Existing Fields icon on the Design / Tools .

By default the Field List appears docked on the right of the Access window as shown above. The window is movable and resizable and displays a vertical scroll bar if it contains more fields than can fit in the window. Dragging a field from the Field List adds a bound text box to the Form Design. If you drag a Yes/No field from the Field List, you add a check box. If you drag a field that has a Lookup property, you add a List Box control or Combo Box control.

Note: When you drag fields from the Field List window, the first control is placed where you release the mouse button. Make sure that you have enough space to the left of the control for the label. If you don't have sufficient space, the labels slide under the control.

Advantages of dragging a field:

 The control is automatically bound to the field that you dragged it from

 Field properties inherit table-level formats, status-bar text, and data - validation rules and messages.

 The label control and label text are created with the field name as the caption

 The label control is attached to the field control so they move together.

 Form View: This view is where you actually view and modify data. Working with data in Form View is similar to working with data in a table or query's Datasheet View. Form View presents the data in a user-friendly format, which you create and design.

 Layout View: This view is a cross between Form View and Design View: The form (or report) displays live data, but you can add and delete controls, move controls, and change properties as if you were in Design View. This view gives the developer a fast and easy way to create and modify form designs. Layout view enables you to work with various control elements and form sections using existing live data. For example, if you need to resize a text box to fit the available data, you do not have to continually switch back and forth between Form and Design view to see if your size change works effectively - you actually see data in the text box while resizing the control. In certain situations, Access displays a message telling you that you must switch to Design view to make a particular change.

Note: Layout view is useful for developers at design time, but you might not want your users to stumble upon it after your application is deployed. You can turn it off for the entire database by clearing the Enable Layout View for this database check box on the Current Database page of the Access Options dialog box.

Controls A control is a graphical object on a form or report that is used for several things:

 To display data (fields and records)

 To perform an action (for example, a command button that triggers a macro)

 To change the appearance of (or decorate) the form or report (for example, border lines, boxes, and labels)

All of the information shown on a form or report is represented by controls. There are three types of controls, and it is very important that you understand the difference so that you don't use the wrong tool to create or change the control on a form or report.

 Bound control: The word bound is significant here, because this type of control is tied directly to the field in the record source. As the data in the table or query change, the data in the bound control on the form or report will also change. A bound control gets its value from a field in the table or query. The most common type of bound control on a form or report is fields. When a form has a record source, it is bound to the records in that record source. Most of the controls that let you enter information can be bound. Controls can be bound to most data types, including text, dates, numbers, Yes/No, pictures, and memo fields. When you add a bound control, you need to specify the linked field so that Access knows what to display. You can most easily add a bound control by dragging a field from the Field List pane and letting Access create the control for you.

 Unbound control: This type of control refers to the object on a form or report that have no tie to the record source. For example, lines and boxes can be created on a form or report without automatically changing the tables or queries on which the forms and reports are based. Field labels can be changed on the form or report without impacting the tables or queries. All superficial, decorative objects on a form or report are unbound.

 Calculated control: This control is based on expressions such as functions or calculations. This type of control is wonderful because it will enable you to include formulas to generate new data on the form or report. An example of a calculated control is =[curSalePrice]-[curCost]. This control calculates the total of two table fields for display on a form but is not bound to any table field. The value shown in the calculated control changes as the values in the underlying fields change. You cannot directly edit a calculated control. Calculated controls are unbound because they do not update table fields

Hands-On Activity: Observing the design window of forms

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. On the Navigation Panel, activate the Tables object group.

2. Double-click the tblCustomers, to display the table in Datasheet View.

3. On the Create / Forms group, select the Form icon.

4. Save the form as frmCustomerData.

5. Notice the change of the label in the Title Bar. By default, the label assumes the name of the form.

6. The Title Bar label can be changed by selecting Design View followed by Design / Tools / Property Sheet. On the Format tab of the Form's property sheet, change the Caption field to Customer Data.

7. The change is reflected when you view the form in either Form View or Layout view.

8. Select Design View from the View icon. Observe the Design View. When you have a form displayed in Design View, either the Field List for the record source or the Property Sheet may be displayed.

If the Field List is not currently displayed, click the Add Existing Fields icon in the Design / Tools group. The default display will just show the fields for the table that were used to create the form. With the field list displayed, you can drag a field from the field list onto the form. If you want to see other tables in the database and perhaps use fields from those tables, click on the Show all tables.

 By default the available controls are also displayed when the form is in Design View

9. By default, a form created by the wizard from a single table or query has a Detail section, Form Header, and Form Footer sections. All information that you place in these sections will be displayed at the beginning of the first page and at the end of the last page of the form when printed.

10. The Form Header and Form Footer sections can be expanded or contracted by placing the cursor on

the Detail bar. When the cursor changes to the area can be expanded or contracted by dragging the bar.

11. Let’s remove the information in the Form Header by selecting the form's Design View icon. Click in the Form Header area so that the text is selected and then hit the Delete key.

12. Click on the image and hit the Delete key.

13. Now let's add some text to the Form Header Section by using the Label tool in the Design /

Controls group. The Label tool is used to display static text that typically doesn't change. Once you have clicked the Label tool, the mouse pointer will change to a plus sign with the letter A next to it when you move the mouse to the Form Header section. Click in the Form Header section where you want to add the label, and then type Customer Data as the desired label. After you hit Enter, you can then change the characteristics of the font by using the Home / Text Formatting group or Format. Select Segoe UI as the font and 18 as the size.

If the full label is not displayed, , place the cursor in the upper right corner and expand the text box until the full label is displayed. If the lower portion of the label is not shown, expand the Form Header section by placing the cursor at the top of the Detail section and drag it down thus enlarging the Form.

14. Switch to Form View to see the results.

Note: The Controls are only available when the Form is displayed in Design View.

Adding Header and Footer data to the form

You can use the Label tool in the Design / Controls group to add text to these sections. Once you have clicked the Label tool, the mouse pointer will change to a plus sign with the letter A next to it. Click in the Form section where you want to add the label, and then type the desired label. To add the current date and/or time to a header or footer section of the form, select the Date & Time icon in the Design / Header/Footer group:

On the Date and Time dialog box you can specify the format you want to use for the date and time.

The Date and Time variable will be inserted into the Form Header section, so that the information will

appear at the top of the first page of the printout. The object may be moved to wherever you want it in the

same section, or it may be placed in a different section by cutting and pasting it.

Hands-On Activity: Adding Header and Footer data to a form

Before beginning: The frmCustomerData form is displayed in Form View.

1. Change frmCustomerData to Design View.

2. In the Design / Control group, click the Label tool.

3. Click in the Customer Data label of the Form Header section of the Form, to place the cursor.

4. Change Customer Data to Home Tech Repair Customers

5. Press Enter. Selection handles appear around the text object.

6. Select the Date & Time icon in the Design / Header/Footer group. The Date & Time dialog box is

displayed.

7. In the Include Date section of the dialog box, choose the mm/dd/yyyy option.

8. In the Include Time section of the dialog box, choose hr:min

9. Click OK.

10. By default the date/time will be placed in whatever section of the form that is active (Form Header, Detail or Form Footer). The object can be copied and pasted in which ever section you want.

11. Click on the form Footer bar to activate it. When the appears, drag it down to open the Form Footer section. Copy and paste the date & time objects into the Form Footer section.

12. From the File tab, select Print / Print Preview to see what this form would look like if it were printed. Whatever is found in the Form Footer will appear on the last page of the form.

13. Click the Navigation button at the bottom of the window a number of times, to see the different records.

Note: At this point you may be seeing an overflow page for each page as we have not set up the width of the form.

14. Observe the form:

 The Home Tech Repair Customers heading (Form Header) appears at the top of the first page (but not on any other pages).

 Some records are split between pages. We'll need to add a page break so that this doesn't happen.

15. Close the Print Preview window. [Click the Close Print Preview icon, not the Close Box for the window.]

16. Re-save and close the form.

17. Close the tblCustomers table.

Taking another Look at the Relationship between Objects Now that you've created a form based on the tblCustomers table, let's see how this form relates to the other objects in your database, not only to the table but also to the other forms that were based on the same table.

I've mentioned this many times and you're probably tired of hearing it, but I've got to keep talking about it because of its importance. As you go about creating the different objects for your database, you have to continuously think about how those objects are going to relate to other objects in the database, and what impact a change to one of the objects will have on the others. For example, in our database we just created the frmCustomerData form based on the tblCustomers table. You already know, because the form is based on the table as its record source, that any changes made to the data in the form will be reflected in the table. But what about the other objects that are also related to that same table? Let's take a look.

Hands-On Activity: A look at the house of cards

Before beginning: The Home Tech Repair database file is open, and there are no objects currently open.

1. Open the Forms object group on the Navigation Panel.

2. Double-click the frmCustomerData form object, to display its contents in Form View.

3. Observe record 1 in the form: Perhaps only a portion of the subform at the bottom of the main form is visible. For example, the Material Cost field may be truncated. Whenever you build a form in Access, you need to take a careful look at things like this and make the necessary adjustments whenever necessary. Don't assume that Access will take care of everything. While the program certainly does a tremendous amount for us, it won't do it all.

4. Notice the fact that there are two sets of Navigation buttons at the bottom of the screen. The upper set of buttons controls the subform, and the lower set controls the main form. It will be important to clearly distinguish between these so that you don't end up adding a new record in the wrong place.

5. On the Navigation Bar at the bottom of the screen, click the button to add a new record to the main form.

6. Enter the following information into the form for this customer:

Field Data

ID 1071

Company Lowell Construction Company

First Name Steven

MI S

Last Name Smith

Job Title Mr.

Address 1 University Place

City Lowell

State/Province MA

Zipcode 01854

Phone 978-458-1000

Notes (leave blank)

7. In the sub-form at the bottom of the screen, enter Lowell Construction Co. work order information:

Field Data

Work Order # 016

Bid Number 98-126

Start Date (Today)

Completion Date (1 week from today)

Supervisor Lookup 12

Supervisor 12

Helper Chu, Mary

Material Cost 1200

Labor Cost 859

Description Driveway

Drawing (leave blank)

8. Move off the focus to save the new record. Click the plus sign next to work order number 016 in the

subform, to display another subform. Because the tblBidData table is related to tblWorkOrders, which in turn is joined to tblCustomerData, you have the ability to enter records into all three locations at the same time.

9. In the subform for the bid information, enter the following data:

Field Data

Bid # 1011

Customer ID 171

Bid Date 3/1/2005

Expires 12/31/2012

Definition Roofing

10. Move off the focus to save the new record. Lowell Construction Company information should look as

follows:

11. Close the form.

12. Open tblCustomer and observe record 40: Lowell Construction Company record, of course, appears in the table.

13. Close the table.

14. Open the tblWorkOrders table, and notice record 16: This is the record we just entered for Lowell Construction Company work order #016. When we entered the work order information into the subform, it was automatically entered into the record source for that subform, which is tblWorkOrders.

15. Close tblWorkOrders.

16. Open the tblBidData table and observe record 1: This is the record we just entered in the subform for Lowell Construction Company.

17. Close the table.

Goals for this section:

 Creating a Tabular form based on one table

 Using the Form Wizard to create a form based on multiple related tables

 Moving the subform object and hiding a field in the subform

 Adding a subform manually

Creating Forms with the Form Wizard

Although using the Create / Forms is an easy way to create forms for your database, you have no control over the layout and display of the fields because all of the settings are set for you by default and all decisions regarding the layout of the form are pre-determined. If you'd like a different layout for your form - for instance, a Tabular rather than Columnar layout - or if you would like to choose the fields and background style for the form, you should use the Form Wizard. The Form Wizard virtually walks you through a series of questions about the form that you want to create and then creates it for you automatically. The Form Wizard lets you select which fields you want on the form, the layout (Columnar, Tabular, Datasheet, and Justified) of the form, the style, and the title on the form.

Note: When you select a style in the Form Wizard, the new style becomes the default for new forms you create using the wizard until you change the style setting again, either in the Form

Wizard or with the AutoFormat commands.

The Form Wizard can help save you time vs. starting with a blank form, but it doesn't provide many layout options and therefore is somewhat limited. You cannot, for instance, use the Wizard to create a free form layout (where your fields are placed around the screen in a configuration that makes sense for data entry). However, you can get very close to the desired layout you want, and then modify it. When you use the Form Wizard to create a form, you can select one or more tables or queries as the record source for the form. As you are going to see, the Form Wizard will enable you to create four main types of forms:

 Columnar

 Tabular

 Datasheet

 Justified

Let's create a series of forms with the Form Wizard so you can see what options are available to you.

Once we've done that, we'll create a form in Design View, so you can see the advantages of using the

Wizard.

Hands-On Activity: Creating a Tabular form based on one table

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. On the Create / Forms group, choose the Form Wizard option.

2. Observe the Tables/Queries drop down where the object's data comes from:

When you use the Form Wizard to create a form, you have to specify the record source for the form so the Wizard knows which fields to make available to you.

3. From the drop-down menu, choose tblCustomers.

4. Once tblCustomers has been selected as the data source, the Wizard is able to provide you with a list of the available fields.

5. Click the button to add all of the fields to the form.

Note: This is extremely important. If you're planning to use a form for data entry, make sure you've added all of the critical fields to the form. If you omit some fields, some of the data won't be entered into the table and you'll end up with data gaps.

6. Click the Next> button to move to the next Wizard screen.

Here, you're given the opportunity to select the layout for the fields in the form.

7. Select Tabular as the field layout, and click Next>.

8. Click Next> to move to the final Wizard screen. Notice that the Wizard proposes the name of the table as the data source and the name of this form.

9. Enter frmCustomerDataEntry as the name of the form.

10. Click Finish.

11. Observe the form:

 The form is created with a tabular layout, with the fields' Caption names at the top of the each column.

 Each record is represented in a separate row.

 Because the Notes field was defined as a Memo field, it takes up more room vertically than the other fields on the form. As a result, there's a gap between records.

Note: We'll be able to change the size and shape of the Notes field so there isn't such a large space between records.

 The Title field has a drop-down list because it inherited the value list from the table on which the form was based.

 Despite the fact that tblCustomers is related to tblWorkOrders, no subform has been created

for this form. When you create a Form using the option, the subform is automatically added based on the existing relationships between the tables in the database, but the Wizard will only do it if you base the form on multiple objects.

12. Switch to Design View and observe what the Form Wizard has done:

 Because this is a Tabular form, it has placed the Caption field labels in the Form Header section of the form. As such, these field labels will just appear at the top of page 1 when the form is printed. Instead of the labels appearing at just the top of the first page, you can put the labels in the Details section of the form. The drawback here is that the labels will appear above each record. You can insert a Page Header and put the labels in the Page Header and the labels will appear at the top of every page when the form is printed. To add a Page Header to your form, right click anywhere on the form, right click and select Page Header/Footer.

 The field controls appear in the Detail section of the form. This will display the different records together in that section. Field controls are always placed in the Detail section of a form.

 Because the Form Header section was added to the form, its corresponding Form Footer section has been added as well. However, it doesn't contain any information and is therefore closed. To expand a section of the form, simply place the mouse pointer on the bottom edge of the section and drag down.

13. Since Access 2010, there are built-in themes that you can use to change the color of your form. The themes are found under File / Themes.

14. Close the frmCustomerDataEntry form.

Using the Form Wizard to Create a Form Based on Multiple Tables This is where you're going to derive the maximum benefit from using forms in Access. When you base a form on multiple tables that have a One-to-Many relationship, the Wizard will enable you to represent the fields from one of the tables in the main form and the fields from the other table in the subform. Then, you will be able to enter data into a record on the main form and also enter the corresponding data into the related table at the same time. By so doing, you will be populating two different tables from a single form.

Hands-On Activity: Using the Form Wizard to create a form based on multiple

related tables

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

Our objective is to create a form that lists all of our Customer information in the main form, with the corresponding work order information in the subform.

1. From the Create / Forms group, select the Form Wizard option.

2. From the Tables/Queries where the object's data comes from, select tblCustomers from the drop down menu. This will be the data that we'll display in the main form.

BY THE WAY: This is one time when having used the Leszynski Naming Convention is going to help you distinguish your queries from your tables. Without that prefix, it would be virtually impossible to tell them apart on this list.

3. Click the button, to add all of the available fields to the Selected Fields window.

Note: Do not click Next> to move to the next Wizard screen. If you want a subform to appear with the main form, you need to change the record source and select additional fields for the subform right now on this Form Wizard screen. If you have inadvertently clicked the Next> button, just click the <Back button once to return to the first Form Wizard screen.

4. From the Tables/Queries drop-down list, choose Table:tblWorkOrders. This will be the record

source for the data that will appear in the subform.

At this point you are still seeing the fields from the tblCustomers table, and now you can add to that list the fields from tblWorkOrders.

5. Click the >> button to add all of the fields from tblWorkOrders to the Selected Fields list.

6. Click the Next > button. Observe the contents of the Form Wizard screen that is displayed: Notice what Access is doing now with regard to the display of the fields in the Selected Fields list: Now that two different tables are represented in this field list, it uses the table name as a prefix for those fields that come from tblCustomers, to distinguish them from those fields that come from the currently selected table, which is tblWorkOrders.

 Because you specified two different data sources for the form, the Wizard needs to know which of the two tables will serve as the contents of the main form and which one will be displayed in the subform.

 The data source that you select in response to the "How do you want to view your data?" question will determine which source object will be displayed in the main form.

 The Wizard has selected the Form with subform(s) option, and it provides a preview of how the data will be configured on the form

7. In the How do you want to view your data? section of the Wizard screen, leave by tblCustomers selected. We want this information to be located in the main form.

8. Click the Next> button. The next Form Wizard screen asks you to select a layout for the subform. Datasheet is selected by default because it is a common (and easy to read) layout for subform data.

9. Leave the Datasheet option selected, and click the Next> button.

10. The next Form Wizard screen enables you to name both the form and the subform. Notice that the Wizard proposes the same name as the data source for the form, which could get confusing.

Note: The Wizard is asking for a name for the subform because it's about to create a new object for the subform data. As with all Access objects, you should take the time to give it a meaningful name so that you won't get confused down the road. Also, it's a good idea to include the word "Subform" in the name of the subform object, so that you will know that it's an embedded object in another form of the database.

11. Enter frmCustomer as the name of the form and frmWorkOrderSubform as the name of the

subform.

12. Click Finish. The form is created, and once again we'll have to adjust the field size and position to make the form more attractive and readable.

13. Switch to Design View and observe the form design that has been created by the Wizard:

 All of the field controls and their corresponding labels have been placed in the Detail section of the form. This would be an easy form to turn into a customized layout.

 The subform has also been placed in the Detail section of the form. This is an important placement. You want work orders to be displayed in the subform for each record that's displayed in the main form. The only way to do this is by placing the subform in the Detail section.

 In addition, the Form Wizard has added a Form Header with the form name as the header. The Form Footer section has also been added to the form design, but does not contain any data and therefore is closed.

 The Form Wizard created the form with a default Control layout. Control layouts help you align and position controls on the form. Access has two kinds of control layout and you can mix them on your forms:

o Stacked control layout arranges controls in a vertical list on the left side. When you widen or narrow one control in a column, you change the width of any other controls in that column. You can have multiple sets of stacked controls within a section. Access no longer automatically builds a stacked control form when you create a form using the wizard.

If in fact you do not want all of the fields in the Control to be the same size, and you want to move the fields in the control independently (which is what we are going to be doing in class), you can remove the control by clicking on the Remove icon in the Arrange / Control Layout group.

 Tabular control layout places bound controls horizontally with labels along the top as column headings like a datasheet.

Modifying the Subform

Once a subform has been embedded in a form through the Form Wizard, you can make changes to its contents and appearance. Some changes can be made directly in Form View, while others can only be made on the Form Design window. The changes you can make to the subform in Form View are the following:

 Sort the contents of the subform

 Apply filters to the contents of the subform

 Hide or freeze columns

 Change the column width

 Cut, copy or paste the data in records If you want to move or resize the subform object, or change the general properties of the subform, you have to make those changes on the Form Design View window of the subform itself.

Hands-On Activity: Moving the subform object and hiding a field in the subform

Before beginning: The frmCustomer form is displayed in Design View.

1. Position the mouse pointer on the top edge of the Form Footer section of the form. When it is positioned correctly, the mouse pointer will change to a vertical double-edged arrow with a horizontal line running through it.

2. Drag down until the top edge of the Form Footer section is aligned with the 2" mark on the vertical ruler (located along the left edge of the window).

Note: You'll see the vertical ruler turning black as you drag down.

3. When you release the mouse, the size of the Detail section should be larger.

4. Click anywhere on the subform object, to select it. Selection handles should appear around the periphery of the object.

5. Place the mouse pointer on the large square selection handle in the upper left corner of the selected object. The mouse pointer should change to a

Note: Whenever you have this image displayed, you can drag with the mouse, the selected object will be moved.

6. With the mouse pointer appearing as the crossed arrows, drag the subform object down, so it's below

the main form. The top edge of the subform object should be aligned at the 5" mark on the vertical ruler. (This will free up some space so we can move the other controls around on the main form.)

7. Switch to Form View and observe the subform: It now appears lower on the screen, and as a result it will be easier to distinguish the main form data from the subform information.

8. Place the mouse pointer on the heading of the Customer ID field in the subform. Click Home / Records / More. Select Hide Fields. Since this field is already in the main form, it is redundant in the subform and thus we really don't need to see it and could therefore hide the column. Everything is interrelated in a relational database.

9. Right clicking on the field will display a different shortcut menu. You have the ability to sort records, copy data, and filter data.

Note: Here's some very good news. If the Customer ID column is hidden in the subform, you add a new record to the main form, and you fill in the Customer ID data in the main form, the ID number will automatically be entered into the hidden field of the subform. Consequently, you don't need to worry about unhiding columns before adding new records to the form.

10. Re-save and close the form. If you are asked whether to save the subform or not, click Yes.

One More Note on Hiding and Unhiding Columns in a Subform Once you have hidden columns in a subform, the only way to unhide them is by doing the following:

 Display the form in Form View.

 Click in any cell of the subform.

 Choose the Home / Records / More / Unhide Fields option.

 Select the checkbox next to the field(s) you want to unhide.

 Close the Unhide Columns dialog box.

 Or you can place the cursor in any column heading in the subform and click Home / Records / More and click the Unhide Field option and select the checkbox next to the field(s) you want to unhide.

Adding a Subform to a Form Manually

What we've seen thus far, is that the Form Wizard automatically added a subform to our form when we based our form on multiple tables. This occurred because the relationship between the two tables was One-to-Many. On the other hand, if you base a form on two tables that have a One-to-One relationship, the Form Wizard does not do this. Instead, it places all of the selected fields on the same main form, which in this case would not be the desired result. Suppose you want one of the tables in a One-to-One relationship to be embedded in a form that's based on the other table in that relationship. For example, you might want to enter records into your tblEmployeeHRData table through a form, and at the same time enter the employee's address information into a subform. In order to do this, you'll have to do the following:

1. Create the form for the table you want in the main form.

2. Create a form for the table you want as the subform.

3. Display the main form in Design View.

4. Expand the Detail section of the form, to make enough room for the subform.

5. In the Design / Controls group, make sure the Control Wizards button is selected. This is found

when you scroll down the Design / Controls area and then click

. The Control Wizard should be highlighted

Note: This is an important step, because this button will cause the Wizard to start automatically when you click the Subform / Subreport button. Adding a subform will be much easier with the Wizard.

6. Click the Subform / Subreport button.

The mouse pointer changes to a plus sign with a Form View icon attached to it.

7. Click on the Form where you want the subform to be located.

8. Select the name of the form to be used as the subform.

9. Click Next> to move to the next screen of the Wizard.

10. Name the subform.

11. Click Finish.

Hands-On Activity: Adding a Subform manually Before beginning: The Home Tech Repair database file is open, and there are no objects currently open.

Our objective is to create a form for the tblEmployeeHRData table with a subform for entering the employee personal information.

1. In the Create / Forms select the Form Wizard option to create a new form.

2. Select tblEmployeeHRData as the table on which the form will be based.

3. Add all the fields from the table to the Selected Fields list. (Click the button.)

4. Click Next>.

5. Leave Columnar selected as the layout.

6. Click Next>.

7. Name the form frmEmployeeHRData.

8. Click Finish. The form is created. As always, many of the fields will need to be resized.

9. Close the frmEmployeeHRData form.

10. Return to Create / Forms group, select the Form Wizard option to create a new form.

11. Select tblEmployeePersonal as the record source for the subform.

12. Click the button to include all of the fields in the subform.

13. Click Next>.

14. Select Datasheet as the layout.

15. Click Next>.

16. Enter frmEmployeePersonal as the name of the subform.

17. Click Finish.

18. Close the subform.

19. Open the frmEmployeeHRData form in Design View.

20. Place the mouse pointer on the top edge of the Form Footer heading. It should change to

21. Drag down, to expand the Detail section of the form. [Expand the section down to the 7" mark on the vertical ruler.]

22. In the Design / Controls group, make sure the Use Control Wizards button is selected.

Note: When selected, this button will cause the Wizard to start automatically when you click the Subform / Subreport button.

23. In the Design / Controls group, click the Subform / Subreport icon.

24. Click around the 6" mark on the vertical ruler (located along the left edge of the screen), to place the subform there. An Unbound object with the name of Child number is added to the form, and the Subform Wizard is started.

Note: When you add objects in this way to a form or report, Access numbers the objects sequentially. As a result, the number of the Child object you're seeing on your own screen will reflect how many other Unbound controls you have added to your form, and it probably won't agree with the number you're seeing in the above graphic. But this doesn't matter. The only thing you should be noticing here is that an Unbound object has been created, with a number. You should train yourself not to be concerned with the number.

25. Choose the Use an existing form option.

26. In the list of available forms, choose frmEmployeePersonal as the data source for the subform.

27. Click Next>. The Subform Wizard gives you the option of specifying the linking field for the two tables.

Note: In order for the Subform to accurately reflect the data, these linking fields must be correct.

28. Choose the Define my own option on the Subform Wizard window. When you do, the Wizard opens a different screen for you so that you can specify the field in both the main form and in the subform that are to be linked. This is important. If you don't choose two related fields here, the form and subform won't work properly.

29. In the Form/report fields: cell, display the drop-down list and select strEmployeeID field. This is the primary key field of the tblEmployeeHRData table.

30. In the Subform/subreport fields: cell, display the drop-down list and select the strEmployeeID field. This is the primary key field of the tblEmployeePersonal table, as well.

31. Click Next>.

32. Leave frmEmployeePersonal indicated as the subform name.

33. Click Finish. The subform is embedded in the form.

34. Switch to Form View and observe the subform:

 Now you're seeing the employee's personal information (address and phone number) in the subform while you're seeing the HR data in the main form.

 The subform needs to be resized both vertically and horizontally so that all the fields are visible.

 The Emp ID field could potentially be hidden in the subform, since it's already in the main form and would be superfluous here.

 The caption (frmEmployeePersonal) in the upper left corner of the subform should be changed, so that it's less "techie."

 The fields in the main form need to be resized and moved. [Leave the form as it is for the time being.]

35. Re-save and close the form.

Goals for this section:

 Starting with a blank form

 Moving objects on a form

 Resizing objects on the form

 Changing field labels

Creating a Form in Design View Before we move on to talking about modifying forms, I'd like you to see what it would be like to create a form without the Wizard. Once you're accustomed to creating forms in Access you may prefer to do it this way, because you'll have a lot more control over the end result. Until you get to that point, though, you'll probably find the Wizard easiest.

Hands-On Activity: Starting with a blank form

Before beginning: The Home Tech Repair database file is open and there are no objects currently open.

1. On the Create / Forms group click the Form Design icon to create a new form.

2. Select the Property Sheet icon in the Design / Tools group.

3. If the Field list is displayed on the right side of the Design window, the Property Sheet will replace it.

4. Be sure that your Selection type is Form (from the drop down menu), and Data tab is active. On the Record Source field, click the and select qryCustomersAndWorkOrders as the data source.

5. Observe the screen.

 A blank form is created, with just a Detail section.

 Clicking on the Add Existing Fields icon in the Tools group, replaces the Property Sheet with the Field List from the selected data source (in this instance qryCustomersAndWorkOrders). Clicking on Show all tables displays the fields available for the view, fields from related tables as well as fields from the tables in your database.

6. For now we are only interested in the fields from the query specified in the Record source from step 4. At the top of the Field List, click on the Show only fields in the current record source. Only the fields used in the qryCustomersAndWorkOrders are displayed.

7. The fields you now have available are:

8. From the Design / Header/Footer group, choose the Title icon to add a Form Header and Form Footer to the form.

9. Drag the strCustomerID field from the qryCustomersAndWorkOrders Field List to the Detail section of the form.

10. If you placed the field at the left margin, place the cursor on the upper right corner of the strCustomerID field. When the cursor changes to the crossed arrows, drag the field to the right so that you have some room to 'neaten' this field up. Click on any empty space in the form area to deselect your previous selection.

11. Now click on the ID label (not the field name). When the cursor changes to the crossed arrows, move the label to the right.

12. Drag the other fields to the Detail section of the form, so that the form looks similar to the following (you may have to change the width of some of the fields). Do not worry about alignment at this time.

13. Make sure no objects are selected on the form.

14. Leave the form displayed in Design View, and choose the Auto Format icon.

Note: The AutoFormats for forms and reports are no longer on the default ribbon. You can still use the AutoFormats, but you will need to create an entry point. On the Quick Access Toolbar (QAT) or Customize Ribbon category of the Access Options dialog box, select More Commands. Click on the AutoFormat option on the left side and then click Add>> to add the command to the Quick Access Toolbar or Custom ribbon group. Click OK to save your changes. You can then use the AutoFormat for your forms and reports.

15. Select a format. Color is then added to the objects of the form.

Note: This is how you can change the style of a form that was created with the Form Wizard, as well.

16. Save the form as frmCustomersAndWorkOrdersQuery.

17. Close the form.

Modifying Forms As we have seen, getting a form created is the easy part, especially if you use the Form Wizard or create

a form using . What will take hours to do is making the design changes - moving and resizing fields, adding header and footer information, aligning objects on the form, changing the Tab order and properties, adding calculated controls... The list goes on and on. All of it is important, because the form is probably what your end-user (or you yourself) will use for data entry, and it needs to be as user-friendly and attractive as possible. The interesting thing about form design is that you look at the basic form, decide how you want it to look, and say to yourself, "Oh, I can have this done in an hour." Then five hours later, when you're still working on it, you wonder why it's taking so long.

Form design is a black hole that will suck hours out of your day.

I am mentioning this to you so you don't get frustrated with how long things are taking. Whenever I am working on an Access database I estimate the amount of time it's going to take, and then add 20%. We have a lot of changes to make to the forms we've just created. Let's get to it.

Moving Objects on a Form As you saw when you moved the subform in frmCustomer, once an object is selected and you place the mouse pointer on the upper left corner handle of the object, the mouse pointer changes to crossed arrows. Dragging at this point will move the selected object. But there's more to moving objects that you need to be aware of. Here's the information you need to know, in a nutshell. We'll do all of these things in a moment. 1. If you place the crossed arrows on the large square selection handle in the upper left corner of an

object and then drag, you will move only that individual object. This means that if a field control and its corresponding label are selected and you place the crossed arrows pointer in the upper left corner of the field control and drag, only that object will be moved, without the label.

2. If you place the mouse pointer on one of the edges of a selected field control (being careful not to be on one of the selection handles), the mouse pointer will change to the crossed arrows. Dragging with the crossed arrows will move the selected object along with its related object.

3. If you wish to move multiple objects together, select all of the objects to be moved using one of the following methods:

 Click on the first object to be moved, hold down the Shift key, and click on each of the other objects. As long as the Shift key remains depressed, the other objects will be selected without losing the selection handles on the previously selected objects.

Note: I refer to this method as "Shift-Clicking." It's useful in all Windows applications for selecting multiple objects, whether it be paragraphs in Word or cells in Excel. You'll be using it a lot in Access.

 Use the "Lasso technique" for selecting multiple contiguous objects. To do this, position the mouse pointer outside the objects to be selected, and drag on the form to create a box around the objects. When you release the mouse, all of the objects that were touched by your lasso will be selected.

4. An alternative to dragging an object with the mouse is using the keyboard, a technique I call "micro spacing." Once one or more objects have been selected, pressing one of the cursor arrow keys on the keyboard will nudge the object(s) in the direction of the arrow.

Note: I find I use this technique a lot, since it's often necessary to move objects just a small amount on forms and reports. You'll find that sometimes dragging with the mouse moves objects too much, whereas micro spacing with the keyboard will give you more control.

The other advantage of micro spacing is that if you move an object vertically its horizontal position doesn't change, and, conversely, if you move the object horizontally its vertical position doesn't change.

Hands-On Activity: Moving objects on a form

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. Open the frmCustomer form in Design View. This is the form in which we embedded a subform to

display the work order information for each customer.

Our goal is to move and ultimately resize the objects so that the form design looks as follows (Your color and fonts may be different depending on how creative you attempt to be as well as your date and time variables will be different.):

2. Position the mouse pointer in the gray area of the form, below and to the right of all field controls on the Details section of the form.

3. Hold down the left mouse button and drag left and up, to create a box around all of the objects in the Details section of the form.

4. Once the selection box has surrounded all of the objects, release the mouse. All of the lassoed objects should be selected.

5. Press the key on the keyboard a few times. As you do, all of the selected objects (including the subform) should be moving down together. Notice that their horizontal position on the form doesn't

change when you use the to micro space the objects.

6. Continue moving the selected objects down until the top edge of the field controls is aligned at the 1" mark on the vertical ruler (along the left edge of the window).

7. Click anywhere in the grey area of the form, to deselect the objects.

8. Hold down the Shift key and click on the ID label and the strCustomerID field control. (This is the white box with black text, not the blue label).

9. Position the mouse pointer on any edge of the field control, so that the mouse pointer changes to the crossed arrows.

Note: Make sure you don't have the mouse pointer on one of the selection handles, which will cause the mouse pointer to change to a double-edged arrow. If you drag from one of the handles of an object, you'll resize (rather than move) the object.

10. With the mouse still appearing as the crossed arrows, drag the object up on the form so that it's

positioned around the half-inch mark on the vertical ruler.

11. Hold down the Shift key and click on the strCompanyName field control object to select it and its label.

12. Press the key repeatedly until the two objects are positioned under the Customer ID field.

Remember that micro-spacing objects with the keyboard is an excellent way to move them a short distance. If you're moving objects from one section of a form to another, on the other hand, you will need to use Cut and Paste.

13. Select the subform label (frmWorkOrder), and press the down arrow key on the keyboard once or

twice. Notice that moving the subform label automatically causes its associated subform to move as well.

Note: This is where you'll have to drag from the upper left corner handle instead of micro- spacing.

14. Position the mouse pointer on the left sizing handle subform's label control and drag it until it is over

the subform.

15. Lasso the subform label field and the subform and align with the left side of the Customer ID, Company fields. Once you have it aligned on the left, move the subform and label down to about the 5 1/2 inch mark.

16. Hold down the Shift key and click on the strContactTitle field control, to select it and its label.

17. Move the 2 objects so that they are above the First Name label and field control.

18. Click on the Zipcode label to select it.

19. Hold down the Shift key and click on the sngZipCode field control.

20. Using the down-pointing cursor arrow, micro space the Zipcode label and object to the right of the State label and strState.

21. Hold down the Shift key and click on the strContactPhone field control and its label. Selection handles should appear around the two objects.

22. Continue holding down the Shift key and click on the memNotes field control object and its label. You should now have selection handles around all 4 objects:

23. Using the down-pointing cursor arrow key, micro space the selected objects down so that they're below the sngZipCode field.

This will move them out of the way so we have room to move the other fields into position on the form. Depending upon where your fields are, you may have to unselect them and move them around individually. Don't be concerned about the alignment (or misalignment) of the objects now. We'll fix that soon.

24. Re-save the frmCustomer form. [Leave it open.]

Resizing Objects Once an object has been selected, if you position the mouse pointer on one of the selection handles (except the large square selection handle in the upper left corner of the object) the mouse pointer will change to a double-edged arrow. Dragging with the double-edged arrow will cause the object to be resized. In general, you'll get more bang for your buck, so to speak, if you resize an object from a corner because two sides of the object will be resized simultaneously. Be careful when sizing an object horizontally. The most common mistake that's made is dragging down while dragging to the right, which causes the object to be heightened as well as widened. If you don't have experience working with graphic objects, you may have trouble with this until you've done it a few times. But if this happens there's not much to worry about. As long as you haven't allowed yourself to slip into a semi-conscious state and you realize what just happened, all you need to do is click Undo and then try it again.

Note: We all slip into that semi-conscious state from time to time, when we're tired, distracted, overextended, etc. In Access, this can have terrible consequences because there are things that cannot be undone. So stay awake!

Hands-On Activity: Resizing objects on the form

Before beginning: The frmCustomer form is displayed in Design View.

1. Click on the strCompanyName field control (the white box, not the blue label.), to select it.

2. Place the mouse pointer on the right center handle of the object. The mouse pointer should change to

3. Drag to the 3" mark on the horizontal ruler.

4. Release the mouse. The field control is resized.

5. Select the strBillingAddress field control and resize that object so it's the same size as the Company Name field.

6. Re-size the subform object so that its right edge is at the 5" mark on the horizontal ruler.

7. Move the Form Footer closer to the bottom edge of the subform. This tightens up the form.

8. Re-save the form.

Changing Labels on the Form Once the form has been designed and fields have been added, moved and resized, you may decide that the field labels are unnecessarily long and that they need to be changed. Here's where you need to be very alert and very careful. Field labels are completely superficial elements and they may be changed at any time without impacting the record source at all. However, if you change the name of a field control object on the form, Access will no longer be able to find the data for the field and as a result you'll get error messages. To edit the contents of a field label on the form, click on the object to select it and then click in the object to place the cursor there. Once the cursor is in the object, if you double-click on a word the entire word will be selected, as in Word and Excel.

Hands-On Activity: Changing field labels

Before beginning: The frmCustomer form is displayed in Design View.

1. Click on the ID field label, to select it. (This is the label to the left of the field control.)

2. Position the mouse pointer in the selected object (so the mouse pointer appears as an I-beam) and

double-click on the word Customer. The entire word is selected, exactly as in Microsoft Word when we double-click on a word in a document. Note: Your Caption may already be ID.

3. Press the Delete key on the keyboard, to delete the selected word.

4. Press Delete one more time, to delete the space.

5. In the Billing Address field label, click to place the cursor to the right of the word Billing.

6. Press the Backspace key 8 times, to delete the word Billing.

7. Click anywhere off the selected label, to remove the highlighting.

8. Using the same techniques, change the following labels on the form:

Current label name Change to...

Contact Title Title

Contact First Name First Name

Contact MI MI

Contact Last Name Last Name

Contact Phone Phone

Company Name Company

9. Finally, change the label directly above the Subform from frmWorkOrder to Company Work Orders.

10. Re-save and close the form.

In Form View with record 1 displayed, the finished form should look similar to the following:

Goals for this section:

 Creating a rectangle unbound control on the form

 Aligning objects on the form

 Grouping objects on the form

 Inserting a date variable on the form

 Adding a page break to the form

 Changing the Tab Order

Adding Graphics as Unbound Controls on the Form Another thing you can do to your forms is to separate out specific pieces of information with lines and boxes. Remember that you're designing the form to facilitate the data-entry process, and anything you can do to help the end-user find things easily, the better. To draw lines and boxes on the form, use the Line and Rectangle tools in the Design / Controls group.

To draw perfectly straight lines, hold down the Shift key as you drag with the mouse.

Hands-On Activity: Creating a rectangle unbound control on the form

Before beginning: The Home Tech Repair database is open, and there are no objects open.

Our first objective is: To draw a box around the Contact information fields (Title, First Name, MI, Last Name and Phone), so that the contact information stands out on the form.

1. Open frmCustomer in Design View.

2. In the Design / Controls group, click the Rectangle tool.

3. Place the mouse pointer (which appears as a white box with a plus sign next to it) where you want to start drawing the box.

4. Drag to create the box around the 4 Contact field controls and their corresponding labels.

5. Leave the box selected. (If you deselect the box inadvertently, just click on one of the sides of the box again to select it.)

6. Select the Property Sheet on the Design / Tools group.

7. On the Rectangle Properties window, select the Format tab.

8. Observe the Border Width option. Hairline is the default line width for all graphic objects.

9. Click in the Border Width text box and click the to display the drop-down list of options.

10. From the drop-down list, choose 2 pt.

The thickness of the box should change as soon as you make the selection on the Properties window. Remember that the Properties window works differently than normal dialog boxes, where the selections don't take effect until you click OK. On the Properties window, all selections take

effect immediately.

11. Close the Properties window.

12. Re-save the form. [Leave it open.]

Aligning Objects on the Form As you go about moving objects on a form, you may find it difficult to get objects aligned with one another vertically or horizontally, even when you micro space the objects. Because your forms need to look good, you'll need to make sure the objects aren't placed helter-skelter around the window and that they're in alignment with other objects. The way to do this most easily is by selecting the objects to be aligned with one another and then choosing the desired option from the Arrange / Sizing & Ordering / Align group.

Note: If you have not selected any objects on the form, the Control Alignment options are not available. If you have only selected a single object on the form, only the To Grid sub-option will be available to you. If you have selected at least two objects, all five of the sub-options will be available.

In my opinion, the Left and Bottom alignment options are the most useful for forms. In a free form like frmCustomer, for example, the Left alignment option would enable you to align the left edges of the ID, Name, Address, and City labels in the upper portion of the form, and the left edges of the Title, First Name, and Phone labels in the boxed area of the form. The Bottom alignment option would enable you to align the bottom edges of the Contact First Name, MI, and Last Name field controls and labels, so that they have the same baseline. A word of clarification before we continue. These alignment options are not synonymous with the three alignment buttons on the Home / Text Formatting group.

The Align Left, Center, and Align Right buttons on the Font toolbar will enable you to align the data within the control, whereas the Arrange / Sizing & Ordering / Align options enable you to align one object to one or more other objects.

If you're aligning the left edges of multiple objects that are way out of alignment with one another, Access always chooses the furthest object on the left and aligns all of the other objects to it. In anticipation of the fact that it's going to do this, you might want to give this some thought and position one of the objects where you want the others to be aligned to before selecting and aligning the objects.

Hands-On Activity: Aligning objects on the form

Before beginning: The frmCustomer form is displayed in Design View.

Our next objectives are: First, we'll align the left edges of the labels in the upper portion of the form. Then we'll align the bottom edges of the Contact Name field controls and labels in the boxed area of the form.

1. Click on the ID field label (the blue label, not the white box).

2. Hold down the Shift key and click the labels for the Company, Address, and City fields.

3. From the Arrange / Sizing & Ordering / Align group, choose Left. The left edges of the objects are aligned with one another.

4. Move MI (label), strM (field control), Last Name (label), strLastName (field control) so that they are all on the same horizontal line as First Name (label) and strFirstName (field control).

5. Shorten the width of strFirstName, and strLastName if they are off the form.

6. Select the rectangle and readjust it so that it now includes all six fields.

7. Click on the First Name field control label.

8. Hold down the Shift key and click on the other objects on the same line: strFirstName (field control) MI (label) strMI (field control) Last Name (label) strLastName (field control) [A total of six objects should be selected.]

9. From the Arrange / Sizing & Ordering / Align group, choose Bottom, to align the bottom edges of

all 6 objects.

10. Re-save the form.

On Your Own On the frmCustomer form, make the following additional changes: 1. Align the left edges of the strCustomerID, strCompanyName, strBillingAddress, and strCity field

controls.

2. Move the Phone label and its field control under the First Name.

3. Move the memNotes field so that it's located below the Zip Code field, close to being left-aligned with the sngZIPCode field control. Then align the left edges of the memNotes and sngZIPCode field controls.

Note: Move the memNotes field first, and then do the alignment. If you need the form background to be wider, place the mouse pointer on the right edge of the grey dotted background, and drag out slightly to the right. (Just don't widen it too much, or when the form is printed there will be extra pages printing with the spillover of the background. Unless you change the margins first, 7½" is the widest point you can expand the background horizontally without any spillover.)

4. Resize the box around the Contact Name fields so that it extends to the 7" mark on the horizontal

ruler. Do the same with the subform object. You may have to make other adjustments to keep the fields within the box.

5. In Form View, since the Supervisor Lookup column in the subform duplicates the information in the Supervisor column, delete it. In Form View, the finished form with record 1 displayed should look as follows:

6. Save the form. Display the form in Design View.

Grouping Objects on the Form

What can easily happen, after you've spent time meticulously aligning objects on the form, is that your hand jerks the mouse and one of the objects becomes misaligned. Or you simply decide you want to move a set of objects, and then need to take the time to shift-click or lasso the objects in order to be able to move them at the same time. These issues can easily be handled by grouping a set of related objects so that they're considered to be a single object with a single set of selection handles. This way, no single item in the group can inadvertently be moved into misalignment with the others in the group, and you'll only have to click on one object in order to select all of the items in the group.

Hands-On Activity: Grouping objects on the form

Before beginning: The frmCustomer form is displayed in Design View. The six Contact Name fields (First Name, MI, and Last Name) and their corresponding labels have had their bottom edges aligned.

Our next objective is this: To group the three Contact Name fields (First Name, MI, and Last Name) and their corresponding labels, so that the entire group may be moved as a single object and their position relative to one another cannot be inadvertently changed.

1. Select the three Contact Name field controls and their corresponding labels. (A total of six objects

should be selected.)

2. From the Arrange / Sizing & Ordering / Size/Space group, select the Group icon.

3. Press the key on the keyboard once, and notice that the entire group of objects moves together.

4. Using the cursor arrow keys, move the grouped object to an appropriate position on your form, between the Title and Phone fields.

5. Re-save the form. Once a set of objects has been grouped, if you want to make a change to any item in the group - for example, to resize the control or change its font size - it will be necessary to first ungroup the controls with the Arrange / Sizing & Ordering / Size/Space.

Note: The Ungroup option will only be available if a grouped object is selected.

Adding a Date Variable to the Form If you want the current date and/or time to be reflected on the form, for example in the Form Header section so that it appears at the top of the form, you should use appropriate icon from the Design /

Controls group. This will insert a date and/or time variable into the currently selected section of the form, so that the information in this variable will change whenever the form is opened. Once the Date and/or Time variables have been inserted, they can be moved or resized on the form, and their font and alignment may be changed. If you increase the font size of a Date or Time object once it has been added to the form, the size of the object will not automatically be adjusted to accommodate the larger font size, and as a result part of the label may be truncated. To fix this, select the object and then choose the icon from the Arrange / Sizing & Ordering / Size/Space group.

Another thing you need to be aware of is the fact that inserting the time as a variable will not keep continuous track of the time as you continue to view the form on screen. To update the time, press the F9 key on the keyboard.

Hands-On Activity: Inserting a date variable on the form

Before beginning: The frmCustomer form is displayed in Design View.

1. The Form Header has put frmCustomer in as the default value when we created this form. We want

to rename it.

2. Select the frmCustomer label and hit the Delete key.

3. In the Design / Control group, select the Label icon.

4. Click somewhere in the center of the Form Header section, to place the cursor there. (It doesn't matter where you place the cursor, because you'll be able to move the label after it's been created.)

5. Type Customers and Work Orders as the label for the Header.

6. Click anywhere off the label, to see it.

Note: If you selected a dark theme, you may not be able to see it. Click on the File / Themes icon, and pick a lighter format, or change the font color in the Design / Font group.

If you should ever see he exclamation icon , it is an indication that there may be something wrong with the field.

7. Observe the black heading of the Form Header section: Its color indicates that this section is active, which means that when you choose the command to insert date and time variables, they will be inserted into this section of the form. When working with forms, always pay attention to which section is active before choosing a command.

8. From the Design / Header / Footer group, choose the Date and Time icon .

9. Observe the Date and Time dialog box:

Both the Include Date and the Include Time options are selected, with default formats selected, as well. If you insert both the Date and Time, they'll be added to the active section of the form as two separate objects.

10. Leave the Date format selection as it is, but change the Time format so that it reflects just the hours

and minutes with AM or PM.

11. Click OK. You now see that =Date() and =Time() variables have been added to the upper right corner of the Form Header section of the form.

12. Select the =Date() object, by clicking on it.

13. Place the mouse pointer on the lower edge of the object so that the mouse pointer changes to the crossed arrows.

14. Drag the object so that it's placed below the Customers and Work Orders label.

15. Move the =Time() object to below the date. (You might need to expand the Form Header section vertically in order to have enough room to do this.)

16. Re-save the form.

17. Select both the =Date() and the =Time() objects on the form.

18. On the Home / Text Formatting group, click the Center alignment button . The contents of both of these objects will be centered within the objects on the form. To accurately center an object on a form, you must extend the object to the boundaries of the form.

19. Switch to Form View and observe the date and time information in the Form Header: The time is centered below the date. However, we still need to change the appearance of the Customers and Work Orders label so it's larger and stands out more on the form.

20. Switch back to Design View.

21. Select the Customers and Work Orders label.

22. Observe the default font size in which labels are created: 10 points is a bit small for headings.

23. On the Home / Text Formatting group, click the in the Font Size box to display the drop-down list of font sizes.

24. Choose 14 as the font size.

25. Notice what has happened to the label:

Access did not automatically resize the object to accommodate the format change. But there's an easy way to do this.

26. With the Customers and Work Orders label still selected, click the Arrange / Sizing & Ordering /Size/Space tab and choose the Size to Fit icon.

The size of the object is automatically adjusted to accommodate its contents.

27. Move the Customers and Work Orders label so that it's centered above the date and time variables. The best way to do this is as follows: Expand the size of the label object so that it extends from the left edge of the form background all the way across to the right. Then center-align the label in the object. Once you have centered the label, do the same with the Date and Time objects so that those items are perfectly centered horizontally across the form.

28. Switch to Form View to see the finished product.

Adding Page Breaks to a Form If you're only planning to use your forms for data-entry but not for printing, then you don't need to be concerned about the division of data on the page. But what if you are planning to print the form? You don't want all of one record and part of the next to appear on the same page, which is what usually happens. The solution is adding a page break, which you insert into the Detail section below the last set of objects that you want to appear on a page. The procedure is as follows: 1. Display the form in Design View.

2. In the Design / Controls group, click the Page Break button.

3. Click on the form where you want the page break to occur. Access will insert a . . . . . symbol to reflect the location of the page break.

To delete a page break, simply click on the.... to select it and press the Delete key on the keyboard.

Hands-On Activity: Adding a page break to the form

Before beginning: The frmCustomers form is displayed in Form View

1. Preview the form. (Click the Print Preview icon from the File / Print option.)

2. Although the first page seems fine, keep hitting the next page at the bottom of the screen:

Observe the layout of the pages 2:

The data for record 3 (Rogers & Rogers) appears on page 2, along with some of the data for record 1 (David Jones).

Note: Your form may not be like mine….but scroll through all the pages as you will see the change between records.

3. Close the Print Preview window. (Don't close the form.)

4. Switch to Design View.

5. Expand the Detail section of the form from its bottom edge, so there's room to insert a page break

below the subform. (You only need ¼" to ½" of free space.)

6. In the Design / Controls group, click the Page Break icon . The mouse pointer changes to a page icon with a plus sign attached to it.

7. Click in the Detail section below the subform, to place the page break there. Regardless of where you click (horizontally) on the form, Access places the page break along the left edge of the section in which you have clicked, and represents it as a series of dots.

8. Click anywhere off the page break, to see it.

9. Preview the form and notice the change: Now each record will print on a separate page, with its corresponding subform at the bottom of the page. If there is still some data on another page due to several items in the subform, you can tighten up the form by moving the subform up a bit. Keep in mind that you will be using an 8 1/2 x 11 sheet of paper.

10. Close the Print Preview window.

11. Hopefully, you noticed that there is some overflow widthwise. When creating Forms or Reports and printing in Portrait mode, the width of the page in Design View should be no wider than 7 1/2 inches. Before you try to make the Design page 7 1/2 inches, you must make sure your right most objects are no wider than 7 1/2 inches. Go back and edit your form accordingly in Design Mode.

12. Preview the form now.

13. Close the Print Preview window.

14. Re-save the form. [Leave it open.]

Changing the Tab Order As you've seen, pressing the Tab key moves the cursor from field to field in tables, and it does the same thing in forms. The order in which the cursor moves from field to field, which is referred to as the Tab Order, is determined in accordance with the order in which you added the fields to the form. What you'll frequently find, however, is that once you've moved fields around on the Form Design View window, the default Tab Order will no longer be appropriate and you'll want to change it. The last thing you'll want to do is drive the data-entry person insane as the cursor jumps around the screen. (In addition, it will waste a lot of valuable data-entry time.) To keep the data-entry person from cursing you every time they need to add a new record to the form, you should change the Tab Order so the cursor will move in a logical progression when the Tab key is pressed. The procedure for changing the Tab Order is as follows: 1. Display the form in Design View.

2. Click in the Detail section of the form, to activate that section.

3. From the Design / Tools group, choose the Tab Order icon.

The Tab Order dialog box will display all of the controls for the currently active section, and give you

access to selecting a different section, as well.

4. Click the row selector of the field to be moved to a different position in the order, being sure to release

the mouse.

5. Position the mouse pointer on the row selector of the field to be moved, and drag the field to the desired position in the list of fields.

6. Once all of the controls appear in the desired order, click OK to record the change. The Auto Order button on the Tab Order dialog box can save you a lot of time. If you simply want the cursor to start with the first field and then move from left to right and top to bottom through the fields as they're currently displayed on the firm, clicking this button will do it for you. My strategy is always to click the Auto order button first, and then make individual changes to the Tab Order as needed.

Hands-On Activity: Changing the Tab Order

Before beginning: The frmCustomer form is displayed in Design View.

1. Switch to Form View. The selection is in the ID field at the top of the form.

2. Press the Tab key repeatedly and observe the current Tab order: If you were to leave it as it is, you

would be entering the Customer ID and Name information first, then the Contact information, then the Company Address information (address, city, state, and Zip), then the Phone number, followed by notes on the customer. The data-entry process would be chaotic, to say the least. By changing the Tab Order, you can control the order in which the highlight moves from field to field, thereby making the data-entry process more logical (and a lot less frustrating).

3. Switch to Design View and click anywhere in the Detail section, to activate that section.

4. From the Design / Tools group, choose the Tab Order icon. The Tab order dialog box is displayed.

5. Click the Auto Order button at the bottom of the dialog box, to reorder the fields.

Notice that Access follows the physical position of the field controls on the form, ordering them from left to right and top to bottom. This means that the selection would move to the Notes field after you enter the Customer ID, which may not be exactly what you want. Suppose, for example, you want to enter comments about the customer after you enter the name, address, and contact information.

6. Click the row selector to the left of the memNotes field in the Custom Order list, to select that field.

7. Position the mouse pointer on the row selector to the left of the memNotes field, and hold down the left mouse button.

8. Drag the field down in the list, to below the strContactPhone field. [Leave all other items as they are in the list.

9. Once the fields have been moved into the desired Tab order, click OK.

10. Switch to Form View and test the new Tab Order by pressing the Tab key repeatedly.

11. Save and close the form.

Assignment for Week 8

1. Read chapters 17, 18 & 19in the textbook

2. Do Final Database Project Assignment #5. Due by midnight Eastern Standard Time on Sunday

March 13, 2016.

In this assignment, you're going to create a series of queries that go beyond Select queries. Review the

information in chapter 4 of the textbook. All fields should fully display the contents, currency fields should

be formatted, all quantities should have a comma if appropriate (1,234).

Here's the assignment:

Query #5

1. Create a query with an aggregate function that lists customers and the total amount ordered by

each customer. [Use the Discounted Cost calculation that you created in query #4, because this

calculation multiplied the quantity by the base cost.]

There should be only 2 fields in the query: One for the Customer Name, and the other for the total

amount that they have ordered.

Each customer should be listed only once in the recordset.

2. Display the Total Amount Ordered with dollar signs and 2 decimal places.

3. Sort in Ascending order by Customer Name.

4. Change the heading of the Total Order column to read Total Order.

5. Name the query qry#5CustomerTotalOrder.

Query #6

1. Create a query that lists the following fields, in the order in which they appear below:

 Territory Name

 Sales Rep First and Last Names, displayed as follows

Last Name, First Name

 Customer Name

 Contact First and Last Names, displayed as follows:

Last Name, First Name

Make sure a comma and a space separate the last and first names in each set of concatenated

names.

Display Rep and Contact as the headings in the two columns of concatenated field data.

2. Sort in Ascending order by Territory Name, then by Customer Name.

3. Name the query qry#6ConcatenatedNames.

Note: There will be a lot of redundancy in the data in this query. In order to eliminate the redundancy, you could convert this query to a report in which each item is mentioned only once. Do not create a report for this assignment.

Query #7

1. List all customers who ordered a particular product (e.g., Utility Pine or Ungraded lumber) within a

range of dates. Design the query so that, when the query is run, the user has the opportunity to

specify the product description as well as the Start and End dates of the range within which the

product was ordered.

2. Include the following fields in the query, in the order in which they appear below:

 Customer ID

 Customer Name

 Product Description

 Quantity Ordered

 Date of Order

3. Sort by Date of Order, in Ascending order.

4. Add an explanation on the Navigation Panel to tell the user that this is a Parameter query and

that they will need to enter the product description and the start and end dates of the range when

the product was ordered.

5. Name the query qry#7ProductAndDateParameter.

Query #8

1. Make a backup copy of the fields and records of your Customers table. Call the backup

tblCustomerBackup.

2. Using the backup copy of the table, create a query that includes all of the fields, locates all

inactive customer records, and appends them to an archive table named tblArchiveInactive.

3. Name the query qry#8aAppend. Make sure you run the query.

4. Create another query to delete the archived records from the backup table. Name the query

qry#8Delete. [Make sure you include all fields in both queries, and that you run both the Append

and the Delete queries.]

Query #9

1. Create a query based on tblOrders that locates all incomplete orders, and makes a new table out

of the recordset. Your objective is for the new table to be created when you run the query.

2. Name the new table tblIncompleteOrders.

3. Name the query qry#9IncompleteOrdersNewTable.

4. Include all fields in the query.

[Note: Do not delete the incomplete order records from the tblOrders table!]

Note: Make sure all queries have been run.