access

COMPREHENSIVE

Access Tutorial 6

Using Form Tools and Creating Custom Forms

XP

New Perspectives on Microsoft Office Access 2007 2

Objectives

• Change a lookup field to a Text field• View and print database documentation• Create datasheet, multiple items, and split forms• Modify a form and anchor form controls in Layout view• Plan, design, and create a custom form in Design view

and in Layout view• Select, move, align, resize, delete, and rename controls

in a form

XP


Objectives

• Add a combo box to a form• Add form headers and footers to a form• Add a combo box to a form to find records• Add a subform to a form• Add calculated controls to a form and a subform• Change the tab order in a form• Improve the appearance of a form

XPDesigning Forms

• To create a custom form, you can modify an existing form in Layout view or in Design view, or you can design and create a form from scratch in Layout view or in Design view

• A combo box is a control that provides the features of a text box and a list box; it lets you choose a value from the list or type an entry


XPChanging the Data Type of the CustomerID Field• Open the tblContract table in the Panaroma database.• Click the CustomerID Field Name box and then click the

Lookup tab in the Field Properties pane. The Field Properties pane now displays the lookup properties for the CustomerID field, as shown in Figure 6-1.

• Click the right side of the Display Control property box, and then click Text Box. All the lookup properties in the Field Properties pane disappear, and the CustomerID field changes back to a Text field without lookup properties.


XPChanging the Data Type of the CustomerID Field


XPChanging the Data Type of the CustomerID Field• Click the General tab in the Field Properties pane and

notice that the properties for a Text field still apply to the CustomerID field.

• Save the tblContract table, switch to Datasheet view, resize the CustomerID column to its best fit, and then click one of the CustomerID text boxes. An arrow does not appear in the CustomerID text box because it is no longer a lookup field.

• Save the table and then close it.


XPRe-Establish the Relationship Between the tblCustomer and tblContract Tables• On the Ribbon, click the Database Tools tab, and then in the

Show/Hide group, click the Relationships button to open the Relationships window, as shown in Figure 6-2.

• Click CustomerID in either the tblCustomer or tblContract table and drag to the other table, and then release the mouse button to open the Edit Relationships dialog box.

• Click the Enforce Referential Integrity check box, click the Cascade Update Related Fields check box, and then click the Create button to define the one-to-many relationship between the two tables and close the dialog box.

• The join line connecting the two tables can be seen in the second slide.


XPNo Relationship Between the tblCustomer and tblContract Tables - Figure 6-2


XPThe Relationship Between the tblCustomer and tblContract Tables


XPUsing the Documenter

• In the Analyze group on the Database Tools tab, click the Database Documenter button

• Select the object(s) you want to document• If necessary, click the Options button to select specific

documentation options for the selected object(s), then click the OK button

• Click the OK button, print the documentation, then close the Object Definition window


XPPrinting the Relationships Window and Using the Documenter• On the Ribbon, click the Database Tools tab, and then in

the Show/Hide group, click the Relationships button to open the Relationships window.

• In the Tools group on the Design tab, click the Relationship Report button to open the Relationships for the Panorama report in Print Preview, as shown in Figure 6-3 in the next slide.

• In the Print group on the Print Preview tab, click the Print button, select the printer in the Name text box (if necessary), and then click OK. Access prints the Relationships for Panorama report.


XPPrinting the Relationships Window and Using the Documenter – Figure 6-3


XPPrinting the Relationships Window and Using the Documenter• Click the Close ‘Relationships for Panorama’ button X

on the Relationships for Panorama tab to close the window. A dialog box opens and asks if you want to save the report. Because you can easily create the report at any time, click the No button to close the report without saving the changes, and then close the Relationships window.

• On the Ribbon, click the Database Tools tab. In the Analyze group, click the Database Documentator button, and then click the Tables tab (if necessary) in the Documenter dialog box, as shown in Figure 6-4 in the next slide.


XPUsing the Documenter – Figure 6-4


XPUsing the Documenter• In the Documenter dialog box, click the tblContract check

box, and then click the Options button. The Print Table Definition dialog box opens, as shown in Figure 6-5 in the next slide.

• Make sure all check boxes are checked in the Include for Table section, click the Names, Data Types, and Sizes option button in the Include for Fields section (if necessary), click the Names, Fields, and Properties option button in the Include for Indexes section (if necessary), click the OK button, and then click the OK button. The Documenter dialog box closes and the Object Definition reports opens in Print Preview, as shown in Figure 6-6.


XPUsing the Documenter – Figure 6-5



• In the Zoom group of the Print Preview tab, click the arrow on the Zoom button and then click Zoom 100%.

• Double-click the Print Preview tab on the Ribbon to minimize the Ribbon, and then scroll down the report so you can see the date at the top of the window, as shown in Figure 6-6.

• Scroll down the Object Definition report to view the remaining information in the documentation, and then close the Object Definition report. Notice that the Navigation Pane is closed and the Ribbon is minimized.


XPCreating a Form Using the Datasheet Tool

• Click tblContract in the Navigation Pane.• Click the Create tab to display its Ribbon.• In the Forms group, click the More Forms button and then click

Datasheet. The Datasheet tool creates a form, as shown in Figure 6-7 on the next page, that shows every field, every record in the dblContract table, and in the same format as a table or query recordset displayed in Datasheet view.

• Click the Home tab on the Ribbon.• In the Views group, click the arrow on the View button. Form

view is dimmed and the Layout view is not an option in the list. The only options are Datasheet view and Design view.


XPCreating Forms Using Form Tools

• The Datasheet tool creates a form in a datasheet format that contains all the fields in the source table or query


XPCreating a Form Using the Datasheet Tool

• There is no need for a form with a datasheet view, so I won’t save it.

• Click the View button to close the menu, and then close the form without saving it.


XPCreating a Form Using the Multiple Items Tool• Select the tblContract table, and then click the Create tab on the

Ribbon.• In the Forms group, click the Multiple Items button. The Multiple

Items tool creates a form that shows every field in the tblContract table and opens the form in Layout view, as shown in Figure 6-9 on the next page.

• The new form displays all records and fields from the tblContract table in a format similar to a datasheet, but the row height for every record is increased.

• Click the View button in the Views group. Form view, Layout view, and Design view are available views for this form.

• Click the arrow on the View button to close the menu, and t hen close this form without saving it.


XPCreating Forms Using Form Tools

• The Multiple Items tool creates a customizable form that displays multiple records from a source table or query in a datasheet format


XPCreating a Form Using the Split Form Tool

• Select the tblContract table, and then click the Create tab on the Ribbon.

• In the Forms group, click the Split Form button. The Split Form tool creates a split form that opens in Layout view and displays a form with the contents of the first record in the tblContract table on the top and a datasheet of the first several records from the table on the bottom.

• In the Layout view, you can make layout and design changes to the form and layout changes to the datasheet at the bottom.

• Click the Format tab on the Ribbon to modify the format and appearance of a split form and all other form types.


XPCreating Forms Using Form

• The Split Form tool creates a customizable form that displays the data in a form in both Form view and Datasheet view at the same time


XPModifying the Split Form in Layout View• For a split form, some options on the Format tab apply to

the form or the datasheet, other options apply only to the form, and the Add Existing Fields in the Control group apply to both the form and the datasheet at the same time. These options are described in Figure 6-13 on page 274.

• Click the Arrange tab on the Ribbon. The form’s label and text box controls for the fields from the tblContract table are grouped together in a form or report. A Control Layout is a set of controls grouped together so that you can manipulate the set as a single control.

• All text boxes in the control layout are the same width. If you increase or reduce the width of one text box, you increase or reduce the width of all text boxes.


XPModifying the Split Form in Layout View• Click the layout selector, which is located at the top-left

corner of the Contract Num label, to select the entire control layout. An orange outline, which identifies the controls selected, appears around the labels and text boxes in the form, as shown in Figure 6-14 on the next slide.

• Click the ContractNum text box, which contains the value 3011, to deselect the control layout and select the ContractNum text box, hold down the Shift key, click each of the five text boxes below the ContractNum text box, and then release the Shift key to select all six text boxes in the control layout.


XPModifying the Split Form in Layout View – Figure 6-14


XPModifying the Split Form in Layout View

• Position the mouse pointer on the right edge of the SigningDate text box until the pointer changes to a <-> shape, click and drag to the left until the right edge is just to the right of the SigningDate field value, and then release the mouse button. All six text boxes were resized, as shown in Figure 6-15 in the next slide.

• Click the ContractType text box, and then click the Remove button in the Control Layout group.

• Select the ContractType text box and its label and then drag the two controls up and to the right until their tops are aligned with the top of the ContractNum controls.


XPModifying the Split Form in Layout View

• Click the ContractType text box so that it is the only selected control, and then drag the right edge of the control to the right and the bottom edge of the control down to the positions shown in Figure 6-16 on the next slide.

• Click one of the controls in the stacked layout, click the layout selector to select all controls in the stacked layout, click the Control Margins button in the Control Layout group, and then click Medium. The text inside the stacked layout controls moves down and to the right.


XPModifying the Split Form in Layout View• Click the Control Margins button, click Wide and

observe the effect on the text inside the controls, click the Control Margins button, click None and observe the effect of this setting, click the Control Margins button, and then click Narrow. Narrow is the default setting for the Control Margins property. It is also the default setting for the Control Padding property.

• In the Control Layout group, click the Control Padding button, click Medium and observe the change to the spacing around the controls, and then repeat for the other settings of this property, making sure you set the property to Narrow as the final step.


XPAnchoring Controls in a Form• Save the form as frmContractSplit.• Use Shift + Click to select the SigningDate and StartDate text

boxes, and then click on the Remove button in the Control Layout group to remove these two controls and their labels from the stacked layout.

• In the Position group on the Arrange tab, click the Anchoring button to open the Anchoring gallery, as shown in Figure 6-17 in the next slide.

• Click Bottom Left in the Anchoring gallery.• Click the ContractType text box, click the Anchoring button, and

then click Top Right.• The SigningDate and StartDate controls shifted down, and the

ContractType controls shifted to the right.New Perspectives on Microsoft Office Access 2007 35

XPAnchoring Controls in a Form

• Open the Navigation Pane. The two sets of controls on the left shift to the right because the horizontal dimensions of the form decreased from the left, and these two sets of controls are anchored to the left in the form. The ContractType controls remain in their same position in the form.

• Position the pointer on the border between the form and the datasheet until the mouse pointer changes to a plus/minus shape, and then drag down until you see only the column headings and the first row of the datasheet. The bottom set of controls shifts down, because it is anchored to the bottom, and the two sets of controls at the top remain in the same position, as shown in Figure 6-18 on the next slide.


XPAnchoring Controls in a Form – Figure 6-18


XPAnchoring Controls in a Form• Click the ContractType text box, click the Anchoring button,

and then click Stretch Down and Right. Because the ContractType text box is already anchored to the top right, it can’t stretch any more to the right, but it does stretch down to increase the height of the text box.

• Position the mouse pointer on the border between the form and the datasheet until the pointer changes to a plus/minus shape, and then drag up until you can see several rows in the datasheet. The bottom set of controls shifts up, and the bottom edge of the ContractType text box shifts up, reducing its height.

• Close the frmContractSplit form without saving your design changes.


XPPlanning and Designing a Custom Form


XPCreating a Form in Design View

• Click the Create tab on the Ribbon• In the Forms group, click the Blank Form button• Click the Design View button on the status bar. The Blank

Form appears, as shown in Figure 6-20 on the next slide.• Make sure the Field List pane is open, then add the

required fields to the form, as shown in Figure 6-21.• Add other required controls to the form• Modify the size, position, and other properties as

necessary for the fields and other controls in the form• Save the form


XPTypes of Controls in a Form

• Bound controls – Controls are connected, or bound, to a field in the database. You use bound controls to display and maintain table field values.

• Unbound controls – Controls are not connected to a field in the database. You use unbound controls to display to column (field) names, text, lines, rectangles, graphics, pictures and other objects. An unbound control that displays text is called a label.

• Calculated controls display values that are the result of expressions.


XPAdding Fields to a Form• Double-click ContractNum in the Field list pane. Access adds a

bound control to the Detail section of the form, removes the tblCustomer and tblInvoice tables from the “Fields available for this view” section of the Field list pane, and places the two tables in the “Fields available in related tables” section of the Field list pane.

• Repeat the previous step for the ContractAmt, SigningDate, StartDate, CustomerID, and ContractType fields, in this order, as shown in Figure 6-21.

• Click the Save button on the Quick Access toolbar, type frmContractsAndInvoices as the name of the form in the Save As dialog box, and then press the Enter key.

• Click the Add Existing Fields button in the Tools group of the Design tab to close the Field List pane.


XPSelecting and Moving Controls

• Click the control to select it. To select several controls at once, press and hold down the Shift key while clicking each control. Handles appear around all selected controls

• To move a single selected control, drag the control’s move handle (located in the upper-left corner) to its new position

• To move a group of selected controls, point to any selected control until the pointer changes to a move pointer, then drag the group of selected controls to its new position

• To move selected controls in small increments, press the appropriate arrow key

• To move selected controls to the next nearest grid dot, hold down the Ctrl key and press the appropriate arrow key



• If necessary, click the ContractType text box to select it, as shown in Figure 6-22 on the next slide.

• Position the mouse pointer on one of the edges of the ContractType text box, but not on a move handle or sizing handle. What the pointer changes to a plus/minus shape, drag the control to the right until the left edge of the highlight on the horizontal ruler is at the 3-inch mark and the top of the highlight is just below the top of the SigningDate bound control, and then release the mouse button, as shown in Figure 6-23 on the second slide.


XPSelecting and Moving Controls – Figure 6-23


XP


Aligning the ContractType and SigningDate Bound Controls – Figure 6-24• Select the ContractType bound control, hold the Shift

key down, click the Contract Type label, click the SigningDate text box, click the Signing Date label, and release the Shift key. The action selects the four controls; each selected control has an orange border.

• Right-click one of the selected controls, click on Align in the shortcut menu, and then click Top. The four selected controls are aligned on their top edges, as shown in Figure 6-24 on the next slide.

• Save your form design changes and the switch to Form view.

XP


Aligning the ContractType and SigningDate Bound Controls – Figure 6-24

XP

• Click the control to select it and display the sizing handles

• Place the pointer over the sizing handle you want (i.e., ContractType), and then drag the edge of the control until it is the size you want

• To resize selected controls in small increments, hold down the Shift key and press the appropriate arrow key. This technique applies resizing to the right edge and the bottom edge of the control


Resizing a Control

XPDeleting a Bound Control and Resizing a Control• Switch to Design view, click an unused portion of the grid to

deselect all controls, and then click the CustomerID text box to select it.

• Right-click the CustomerID text box to open the shortcut menu, and then click Delete. The label and the text box are deleted.

• Click the ContractType text box to select it.• Place the pointer on the middle-right handle of the text box.

When the pointer changes to a <-> shape, drag the right border to the 6-inch mark on the horizontal ruler.

• Place the pointer on the middle-bottom handle of the ContractType text box. When the pointer changes to a shape, drag the bottom border down to the 1.75-inch mark on the vertical ruler, as shown in Figure 6-26 on the next page.


XPResizing a Control



• Switch to Layout view, and then click the ContractNum text box to select it.

• Position the pointer on the right edge of the ContractNum text box. When the pointer changes to a <-> shape, drag the right border horizontally to the left until the text box is slightly wideer than the field value it contains, as shown in Figure 6-27 in the next slide.

• The sizes of the ContractAmt, SigningDate, and StartDate text boxes will look fine if you reduce the to have the same width. Select all three text boxes and resize them by dragging the right border to the left until the SigningDate and StartDate text boxes are slightly wider than the values they contain, as shown in Figure 6-27.



• Navigate through the first several records to make sure that the five text boxes are sized properly to display the full field values. If any text box is too small, select the text box and increase its width appropriately.

• Save your form design changes, switch to Design view, and then deselect all controls by clicking in an unused portion of the grid.


XP Adding a Combo Box to a Form

• In the Controls group on the Design tab, click on the Use Control Wizards button.

• In the Controls group of the Design tab, click the Combo Box (Form Control).

• When you move the mouse pointer over the form, the pointer changes to a shape with a plus symbol in its upper-left corner.

• Position the + portion of the pointer three grid dots from the top of the grid and at the 4” mark on the horizontal ruler, and then click the mouse button. Access places a combo box control in the form and opens the first Combo Box Wizard dialog box.


XPAdding a Combo Box to a Form

• Click the I want the combo box to look up the values in a table or query option button (if necessary), click the Next button to open the next dialog box, click the Queries option button in the View group, Click Query: qryCustomersByName, and then click the Next button. Access opens the third (and next) dialog box.

• The third dialog box lets you select fields from the query to appear as columns in the combo box. You’ll select the first two fields.

• Double-click Customer to move this field to the Selected Fields list box, double-click CustomerID, and then click the Next button. The next dialog box lets you choose a sort order for the combo box entries.


XPAdding a Combo Box to a Form• Click the arrow for the first list box, click Customer to select it as

the field for sorting, and then click the Next button to open the next Combo Box Wizard dialog box.

• Resize the columns to their best fit, scrolling down the columns to make sure that all values are visible and resizing them if they’re not, and then click the Next button.

• In this dialog box, you select the foreign key, which is the CustomerID field. Click CustomerID and then click the Next button.

• Click the Store that value in this field option button, click its arrow, click CustomerID, and then click the Next button.

• Type CustomerID and then click the Finish button. The completed CustomerID combo box appears in the form, as shown on the next page.


XPAdding a Combo Box to a Form


XPChanging a Label’s Caption

• Right-click the label to select it and to display the shortcut menu, and then click Properties to display the property sheet

• If necessary, click the All tab to display the All page in the property sheet

• Edit the existing text in the Caption text box; or click the Caption text box and press the F2 key to select the current value, then type a new caption

• In the Tools group on the Design tab, click the Property Sheet button to close the property sheet



• Right-click the Customer ID label to select it and to display the shortcut menu, and then click Properties on the shortcut menu. The property sheet for the Customer ID label opens.

• If necessary, click the All tab to display all properties for the label.

• Click before the “ID” in the Caption text box, press the spacebar, press the End key, type a colon, and then press the Tab key to move to the next property. The label for the CustomerID bound control now displays Customer ID:, as shown in Figure 6-28 on the next slide.

• Close the property sheet and save your design changes.


XPModifying a Combo Box in Layout View

• Switch to Layout view to view the form, and then click the CustomerID combo box, hold down the Shift key, click the Customer ID label, and then release the Shift key.

• Drag the selected controls to the right until the Customer ID label is approximately one inch to the right of the Contract Type label, making sure that the two controls are above the ContractNum bound control.

• Select the Customer ID label and the Contract Type label, click the Arrange tab on the Ribbon, and then click the Left button in the Control Alignment group.

• Similarly, align the CustomerID combo box and the ContractType text boxes on their left edges.



• Select the Customer Num label, the CustomerNum text box, the Customer ID label, and the CustomerID combo box, and then click the Bottom button in Control Alignment group. The four selected controls are aligned at their bottoms.

• Switch to Form view, and then click the CustomerID arrow to open the control’s list box. You need to widen the CustomerID combo box, so that the widest customer value in the list is displayed in the combo box.

• Switch to Layout view, and then navigate to record 16. This is the widest value that is displayed in the combo box. Widen the combo box so that the value in record 16 is completely visible, as shown in Figure 6-30 on the next slide.


XPAdding and Removing Form Header and Form Footer Sections• In Design view, click the Form Header/Footer button in

the Show/Hide group on the Arrange tabor• In Layout view or Design view, click a button in the

Controls group to add a logo, title, page numbers, or date and time to the form

• To remove a Form Header or Form Footer section, drag its bottom edge up until the section area disappears or set the section’s Visible property to No


XPAdding a Title to a Form

• In the Controls group on the Design tab, click the Title button. Access adds the title to the form, displaying it in the upper-left corner of the form and using the form name as the title.

• Edit the title by pressing the Home key to move to the start of the title, press the Delete key three times to delete the first three characters, click before the word “And,” press the spacebar, type the letter a, press the Delete key, press the -> key twice, press the spacebar, and then press the Enter key.

• Select the Title, and then click the Bold button in the Font group of the Format tab.

• Switch to Design view, and then save your design changes. The title is displayed in the Form Header section, as shown in Figure 6-32 on the next slide.


XPAdding and Removing Form Header and Form Footer Sections


XPAdding a Combo Box to Find Records• Open the property sheet for the form in Design view,

make sure the record source is a table or query, then close the property sheet

• In the Controls section on the Design tab, click the Combo Box button, and then click the position in the form where you want to place the control

• Click the third option button (Find a record on my form based on the value I selected in my combo box) in the first Combo Box Wizard dialog box, then complete the remaining Combo Box Wizard dialog boxes


XPAdding a Combo Box to Find Records• Open the frmContractsAndInvoices form, if necessary.• Open the property sheet by clicking the form selector

(located near the left of the horizontal ruler) and then by either clicking the Property Sheet in the Tools group of the Design tab or by right-clicking and selecting the Properties command in the shortcut menu, and then clicking the All tab. The property sheet displays the properties for the form, as shown in Figure 6-33 on the next slide.

• Click the Record Source text box, press F2 to select the entire property setting, type tblContract, and then close the property sheet.


XPAdding a Combo Box to Find Records – Figure 6-33


XPAdding a Combo Box to Find Records• Position the Combo Box at the top of the Form Header

section and at the 5-inch mark on the horizontal ruler, and then click the mouse pointer, as shown in Figure 6-34 on the next slide.

• Access places a dialog box on the form and opens the first Combo Box Wizard dialog box. In the dialog box, click the Find a record on my form based on the value I selected in my combo box option button, and then click Next.

• In the next dialog box, double-click ContractNum to move this field to the Selected Fields list box, and then click the Next button.


XPAdding a Combo Box to Find Records


XPAdding a Combo Box to Find Records• The column is already sized correctly in the next dialog box,

so click Next.• In the next dialog box, type Select Contract and then click

the Finish button. The completed unbound combo box is displayed in the form, as shown in the previous slide.

• Deselect all controls, select the two combo boxes (one in the Form Header section and the other in the Detail section), right-click one of the selected controls, point to Align, and click Right.

• Click the Select Contract label, point to the label’s move handle, and then drag the label to the right until it is two grid dots to the left of the combo box.


XPAdding a Combo Box to Find Records• Select the combo box in the Form Header section, the Select

Contract label, and the title, right-click one of the selected controls, point to Align, and click Bottom. Deselect the three controls by clicking the Select Contract label. The three controls are aligned at their bottom edges, as shown in Figure 6-35 on the next slide.

• Save the form design changes, and then switch to Form view.• Click the Select Contract combo box arrow to open the

combo box’s list, as shown in Figure 6-36 on the second slide.• Scroll down the list, and then click 3073. The current record

changes from record 1 to record 41, which is the record for contract number 3073.


XPAdding a Combo Box to Find Records – Figure 6-35


XPAdding a Combo Box to Find Records


XPAdding a Subform to a Form

• You use the Subform/Subreport tool in Design view to add a subform to a form

• In the Controls group on the Design tab, make sure the Use Control Wizards tool is selected

• In the Controls group on the Design tab, click the Subform/Subreport button



• Switch to Design view.• Place the pointer on the bottom edge of the Detail section. When

the pointer changes to a plus/minus shape, drag the section’s edge down until it is at the 4-inch mark on the vertical ruler.

• In the Controls group on the Design tab, make sure the Use Control Wizards tool is selected (colored highlight).

• In the Controls group on the Design tab, click the Subform/Subreport button.

• Position the + portion of the pointer in the Detail section at the 2.5 inch mark on the vertical ruler and the 1-inch mark on the horizontal ruler, and then click the mouse button. Access places a subform control in the form’s detail section and opens the firs Subform Wizard dialog box.


XPAdding a Subform to a Form• Select the Use existing Tables and Queries option button,

and then click the Next button.• In this dialog box, click the Tables/Queries arrow to display

a list of tables and queries in the Panorama database, scroll to the top of the list box, and then click Table: tblInvoice. The Available Fields list box shows the fields from the tblInvoice table.

• Click the >> button to move all available fields to the Selected Fields list box, click the ContractNum field in the Selected Fields list box, click the < button (since it is already placed in the Detail section from the tblContract table), and then click the Next button to open the next Subform Wizard dialog box, as shown in Figure 6-37 on the next slide.


XPAdding a Subform to a Form – Figure 6-37



• In this next dialog box, make sure that the Choose from a list option is selected and that the first link is highlighted, and the click the Next button.

• Type frmInvoiceSubform and then click the Finish button. Access increases the height and width of the subform in the form.

• Deselect all controls, save your form changes, and switch to Form view, as shown in Figure 6-38 on the next slide. The subform displays the two invoices related to the first contract record for contract number 3011.


XPAdding a Subform to a Form – Figure 6-38


XPModifying the Form’s Design

• Switch to Design view. • Deselect all controls, right-click the subform label to open

the shortcut menu, and then click Cut.• Move the pointer to the edge of the subform border, click

the border’s edge to select the subform, position the pointer on the border and right-click the border when the pointer changes to a plus/minus shape, and then click Subform in New Window on the shortcut menu. The subform opens in Design view.

• Switch to Datasheet view, resize all columns to their best fit, scrolling down the datasheet to resize the Invoice Item column again, as necessary.


XPModifying the Form’s Design

• Save your design changes, and then close the frmInvoiceSubform form.

• Switch to Layout view (main form), click the edge of the subform to select it, hold down the Shift key, click the Start Date label, and then release the Shift key. The subform and the Start Date label are selected.

• Click the Arrange tab on the Ribbon, and then in the Control Alignment group on the Arrange tab, click Left. The two controls are aligned on their left edges.

• Hold down the Shift key, click the Start Date label to deselect it, release the Shift key, leaving only the subform selected, and then drag the right edge of the subform to the right until are five datasheet columns are fully visible, as shown in Figure 6-39.


XPDisplaying a Subform’s Calculated Controls in the Main Form• The Count function determines the number of

occurrences of an expression, and its general format as a control in a form or report is =Count(expression)

• The Sum function calculates the total of an expression, and its general format as a control in a form or report is =Sum(expression)


XPDisplaying a Subform’s Calculated Controls in the Main Form• Save your form’s design changes, switch to Design view, click the

subform’s border to select it, right-click the border, and then click the Subform in New Window on the shortcut menu. The subform opens in Design view.

• Place the mouse pointer at the bottom edge of the Form Footer section bar. When the pointer changes to a plus/minus shape, drag the bottom edge of the section down to the .5-inch mark on the vertical ruler.

• In the Controls group on the Design tab, click the Text Box button and place it at the top of the Form Footer section and at the 1-inch mark on the horizontal ruler.

• Open the property sheet for the text box, click the All tab, select the entry in the Name text box, type txtInvoiceAmtSum in the Name text box, press the Tab key, type =Sum(InvoiceAmt) in the control source text box, and press the tab key, as shown in Figure 6-41.


XPDisplaying a Subform’s Calculated Controls in the Main Form


XPDisplaying a Subform’s Calculated Controls in the Main Form• In the Controls group on the Design tab, click the Text Box button

and place the text box at the top of the Form Footer section and at the 4-inch mark on the horizontal ruler.

• Set the Name property value to txtContractNumCount and the Control Source property value to =Count(ContractNum).

• Close the property sheet, save your subform changes, and then close the subform. You will return to Design view for the main form.


XPAdding a Calculated Control to the Main Form’s Detail Section• In the Controls group on the Design tab, click the Text Box button,

and then place the text box and its attached label at the 2-inch mark on the horizontal ruler and at the 2-inch mark on the vertical ruler.

• Select the label, open the property sheet, set its Caption property to Number of Invoices:, right click one of the edges of the label to open the shortcut menu, point to Size, and then click To Fit.

• Click the text box to select it, click the Control Source text box in the property sheet, and then click the property’s Build button to open the Expression Builder.

• Double-click frmContractsAndInvoices in the left column, click frmInvoiceSubform in the left column, scroll down the middle column, click txtInvoiceNumCount in the middle column, and click the Paste button.


XPAdding a Calculated Control to the Main Form’s Detail Section• Press the Home key, click the = button in the row of operators to

the left of the Paste button, and then click the OK button. Access closes the Expression Builder dialog box and sets the Control Source property.

• In the Controls group on the Design tab, click the Text Box button, and then place the text box and its attached label at the 5-inch mark on the horizontal ruler and at the 2-inch mark on the vertical ruler.

• Select the label, open the property sheet, set its Caption property to Invoice Amount Total:, right click one of the edges of the label to open the shortcut menu, point to Size, and then click To Fit.

• Click the text box to select it, click the Control Source text box in the property sheet, and then click the property’s Build button to open the Expression Builder.


XPAdding a Calculated Control to the Main Form’s Detail Section• With the Expression Builder dialog box open for the new text

box, click the = button to insert an equal sign in the large text box, double-click frmContractsAndInvoices in the left column, click frmInvoiceSubform in the left column, and then click txtInvoiceAmtSum in the middle column, and then click the Paste button.

• Close the Expression Builder dialog box, close the property sheet, save your form changes, and then switch to layout view, as shown in Figure 6-43.


XPAfter Adding Two Calculated Controls – Figure 6-43


XPResizing, Moving, and Formatting Calculated Controls• Switch to Design view, right-click the rightmost calculated

control, click Properties on the Shortcut menu to open the property sheet, click the All tab, set the Format property to Currency, and then close the property sheet.

• Reduce the widths of the two calculated controls, as shown in Figure 6-44 on the next slide.

• Use Shift + Click to select the Start Date label and the Number of Invoices label, click the Arrange tab on the Ribbon, click the Left button in the Control Alignment group, press the Shift key, click the Start Date label to deselect it, release the Shift key, and the move the Number of Invoices label up to the position shown in Figure 6-45 on the second slide.


XPResizing, Moving, and Formatting Calculated Controls – Figure 6-44


XPResizing, Moving, and Formatting Calculated Controls – Figure 6-45


XPResizing, Moving, and Formatting Calculated Controls• Select the Number of Invoices label and its related

calculated control, and then in the Control Alignment group, click the Top button.

• Use Shift + Click to select the Start Date label and the Invoice Amount Total label, click the Arrange tab on the Ribbon, click the Left button in the Control Alignment group, press the Shift key, click the Start Date label to deselect it, release the Shift key, and the move the Invoice Amount Total label up to the position shown in Figure 6-45 on the previous slide.

• Select the Invoice Amount Total label and its related calculated control, and then in the Control Alignment group, click the Top button.


XPResizing, Moving, and Formatting Calculated Controls• Move the bottom calculated control to the left to the

position shown in Figure 6-45, align the two calculated controls on their left edges, and then deselect all controls.

• Right-click the bottom calculated control, click Properties on the shortcut menu, click the Other tab in the property sheet, set the Tab Stop property to No, and then set the ControlTip text property to Calculated invoice total for this contract.

• Click the Number of Invoices text box, position the pointer on the Number of Invoices text box to display its ScreenTip, click the Invoice Amount Total text box, and then position the pointer on the Invoice Amount Total text box to display its ScreenTip, as shown in Figure 6 -46 on the next slide.


XPChanging the Tab Order in a Form

• Focus refers to the control that is currently active and awaiting user action; focus also refers to the object and record that is currently active

• The order in which you move from control to control, or change the focus, in a form when you press the Tab key is called the tab order

• Switch to Design view, click the Arrange tab on the Ribbon, and then in the Control Layout group on the Arrange tab, click the Tab Order button



• Select 2 in the Number of Invoices text box, and then press the 8 key. The Number of Invoices value remains at 2 and a warning message is displayed on the status bar.

• Click the Invoice Amount Total text box, and then press any key. The value remains unchanged, and a warning message displays on the status bar.

• Select 3011 in the ContractNum text box, press the Tab key to advance to the ContractAmt text box, and then press the Tab key five more times to advance to the SigningDate, StartDate, ContractType, CustomerID text boxes, in order, and then to the subform.


XPChanging the Tab Order in a Form• Switch to Design view, click the Arrange tab on the Ribbon, and then

in the Control Layout group on the Arrange tab, click the Tab Order button.

• Because I did not set the Name property for the combo box control and the calculated controls, Access assigned their names as follows, as shown in Figure 6-48 on the next slide: Combo box control – Combo6 Number of invoices calculated control – Text14 Invoice Amount Total calculated control – Text16

• Click the row selector to the left of Combo6, and then drag the row selector above the ContractType entry. The entries are now correct in the correct tab order.

• Click the OK button, save your form design changes, switch to Form View, and then tab through the controls.


XPAdding a Line to the Form

• Display the form or report in Design view• In the Controls group on the Design tab, click the Line button• Position the pointer’s plus symbol (+) at the left edge of the Form

Header section and at the .75-inch mark on the vertical ruler (i.e., where you want the line to begin).

• Hold down the Shift key, drag a horizontal line from left to right, so that the line aligns with the right edge of the grid in the Form Header section, release the mouse button, and then release the Shift key, as shown in Figure 6-49 on the next slide.

• Drag up the bottom of the Form Header section to just below the line at the .75-inch mark.

• Save your form design changes.


XPAdding a Line to the Form – Figure 6-49


XPAdding a Rectangle to the Form

• Display the form in Design view (if necessary).• In the Controls group on the Design tab, click the Rectangle

button.• Position the pointer’s plus symbol (+) approximately two grid dots

above and two grid dots to the left of the Number of Invoices label.

• Drag the rectangle down and to the right until all four sides of the rectangle are approximately two grid dots from the two calculated controls and their labels, as shown in Figure 6-50 on the next slide.

• In the Controls group on the Design tab, click the Line Thickness button, click the line with the ScreenTip 2 pt in the list, and then deselect the control.


XPAdding a Rectangle to the Form


XPAdding a Rectangle to a Form or Report


XPAdding Color and Visual Effects to the Controls in the Detail and Form Header Sections• Select the Number of Invoices label and the Invoice Amount

Total label. In the Font group on the Design tab, click the arrow for the Font Color button, click the Blue color (row 7, column 8) in the Standard Colors palette, and then in the Font group, click the Bold button (B). The labels’ captions use a bold, blue font.

• Select the Select Contract label in the Form Header section, set the label’s color to Red (row 7, column 2), and then set the font style to Bold.

• In the Controls group on the Design tab, click the arrow for the Special Effect button, and then click Special Effect: Raised. The label now has a raised special effects, and the label’s caption now uses a bold, red font.


XPAdding Color and Visual Effects to the Controls in the Detail and Form Header Sections• Select the Contract Num label, Contract Amt label, Signing Date

label, Start Date label, Customer ID label, and Contract Type label, set the controls’ Special Effect property to Special Effect: Sunken, and then deselect all controls.

• Click the Form Header’s section bar, and in the Font group on the Design tab, click the arrow for the Fill/Back Color button, and then click the Access Theme 2 color (row 2, column 2) in the Access Theme Color palette. The Form Header’s background color changes to the Access Theme 2 color.

• Click the Detail section’s section bar, and in the Font group on the Design tab, click the arrow for the Fill/Back Color button to change the Detail section’s background color to the Access Theme 2 color.


XPAdding Color and Visual Effects to the Controls in the Detail and Form Header Sections• Select the Select Contract combo box, Number of Invoices

text box, and the Invoice Amount Total text box, set the selected controls’ background color to the Access Theme 2 color, and then deselect all controls by clicking on an empty area of the form’s grid, as shown in Figure 6-51 on the next page.

• You have now completed the custom form. Save the form design changes, close the form, close the Panorama database, open the Panorama database, compact and repair the database, close the database again, and then exit Access.


access

Documents

form new perspectives

form xp new perspectives

relationships button

microsoft office access

form tools

lookup field

text field view

relationships window