Access Project 7

1
Access Project 7

• Advanced Report and Form Techniques

2
Objectives

• Create a report in Design view
• Create queries for reports
• Add fields to a report
• Add a subreport to a report

3
Objectives

• Modify a subreport
• Add a date
• Add a page number
• Create and print mailing labels

4
Objectives

• Add command buttons to forms
• Modify VBA code associated with a command button
• Add a combo box to a form
• Modify properties of a combo box

5
Creating Queries for the Report

• Open Microsoft Office Access 2003 and open the
  database, according to the instructions on pages
  AC 404 AC 405
• If necessary, in the Database window, click
  Tables on the Objects bar, and then click
  Trainer. Click the New Object button arrow on the
  Database window toolbar, and then click Query. Be
  sure Design View is selected, and then click the
  OK button. Maximize the window
• Right-click any open area in the upper pane,
  click Show Table on the shortcut menu, click the
  Client table, click the Add button, and then
  click the Close button in the Show Table dialog
  box. Resize the upper and lower panes of the
  window as well as the field lists so all fields
  appear

6
Creating Queries for the Report

• Double-click the Trainer Number, First Name, and
  Last Name fields from the Trainer table.
  Double-click the Client Number, Name, Address,
  City, State, Zip Code, Client Type, Amount Paid,
  and Current Due fields from the Client table
• Close the query by clicking its Close Window
  button, and then click the Yes button to save the
  query. Type Trainers and Clients as the name of
  the query, and then click the OK button
• Click Course and then click the New Object button
  arrow on the Database window toolbar. Click
  Query. Be sure Design View is selected, and then
  click the OK button

7
Creating Queries for the Report

• Right-click any open area in the upper pane,
  click Show Table on the shortcut menu, click the
  Course Offerings table, click the Add button, and
  then click the Close button in the Show Table
  dialog box. Resize the upper and lower panes of
  the window as well as the field lists so all
  fields appear. Double-click the Client Number and
  Course Number fields from the Course Offerings
  table. Double-click the Course Description field
  from the Course table, and then double-click the
  Total Hours and Hours Spent fields from the
  Course Offerings table
• Close the query by clicking its Close Window
  button, and then click the Yes button to save the
  query. Type Course Offerings and Courses as the
  name of the query, and then click the OK button

8
Creating an Initial Report

• If necessary, in the Database window, click
  Queries on the Objects bar, and then click
  Trainers and Clients
• Click the New Object button arrow, and then click
  Report
• Be sure Design View is selected, and then click
  the OK button
• If necessary, maximize the window and dock the
  toolbox at the bottom of the screen

9
Creating an Initial Report

• Be sure the field list appears. If it does not,
  click the Field List button on the Report Design
  toolbar
• Drag the bottom boundary of the field list down
  so all fields appear, and then move the field
  list to the lower-right corner of the screen by
  dragging its title bar
• Right-click any open area of the Detail section
  of the report
• Click Sorting and Grouping on the shortcut menu,
  and then click the down arrow in the
  Field/Expression box

10
Creating an Initial Report

• Click Trainer Number, click the Group Header
  property box, click the Group Header property box
  arrow, and then click Yes
• Click the Keep Together property, click the Keep
  Together property box arrow, and then click Whole
  Group in the list of available values for the
  Keep Together property
• Click the Field/Expression box on the second row
  (the row under Trainer Number), click the down
  arrow that displays, and then click Client Number
  in the list of fields that appears
• Close the Sorting and Grouping dialog box by
  clicking its Close button

11
Creating an Initial Report
12
Adding Fields to the Report

• Drag the Trainer Number field to the approximate
  position shown on the next slide
• Release the left mouse button to place the field
• Use the same techniques to place the First Name
  and Last Name fields in the approximate positions
  shown on the next slide
• Adjust the positions of the labels to those shown
  in the figure. If any field is not in the correct
  position, drag it to its correct location. To
  move the control or the attached label
  separately, drag the large handle in the
  upper-left corner of the control or label
• Place the remaining fields in the positions shown
  on the next slide

13
Adding Fields to the Report
14
Saving the Report

• Click the Save button on the Report Design
  toolbar, and then type Trainer Master List as the
  report name
• Click the OK button

15
Adding a Subreport

• Close the field list by clicking its Close button
• Be sure the Control Wizards tool is selected,
  click the Subform/Subreport tool, and then move
  the pointer, which has changed to a plus sign
  with a subreport, to the approximate position
  shown in Figure 7-10 on page AC 410
• Click the position shown in Figure 7-10. Be sure
  the Use existing Tables and Queries option button
  is selected
• Click the Next button. Click the Tables/Queries
  box arrow

16
Adding a Subreport

• Click Query Course Offerings and Courses, and
  then click the Add All Fields button
• Click the Next button and then ensure the Choose
  from a list option button is selected
• Click the Next button and then type Course
  Offerings by Client as the name of the subreport
• Click the Finish button

17
Adding a Subreport

• If necessary, maximize the window
• If the field list appears, click its Close button
• Drag the subreport to the approximate position
  shown on the next slide
• Close the report design by clicking its Close
  Window button. Click the Yes button to save the
  changes

18
Adding a Subreport
19
Modifying the Subreport

• Be sure the Reports object is selected,
  right-click Course Offerings by Client, and then
  click Design View on the shortcut menu that
  appears
• If necessary, maximize the window
• Drag the lower boundary of the Report Header
  section to the approximate position shown in
  Figure 7-17 on page AC 413
• Delete the Client Number controls from both the
  Report Header and Detail sections
• Change the labels in the Report Header section to
  match those shown in Figure 7-18 on page AC 414.
  (To extend a heading over two lines, press
  SHIFTENTER)

20
Modifying the Subreport

• Right-click the ruler in the position under the
  section selector for the detail section to select
  all the controls in the Detail section and to
  display the shortcut menu
• Click Properties on the shortcut menu
• Click the Can Grow property, click the arrow that
  appears, and then click Yes
• Click the Close button in the Multiple selection
  property sheet, and then close the subreport by
  clicking its Close Window button
• Click the Yes button to save the changes

21
Modifying the Subreport
22
Adding a Date

• Be sure the Reports object is selected,
  right-click Trainer Master List, and then click
  Design View on the shortcut menu
• Click Insert on the menu bar and then click Date
  and Time on the Insert menu
• Be sure that Include Date is checked and that
  Include Time is not checked. Be sure the date
  format selected is the first of the three options
• Click the OK button to add the date. Click the
  newly added Date control and point to the
  boundary but away from any of the handles. The
  pointer shape changes to a hand
• Drag the Date control to the position shown on
  the following slide

23
Adding a Date
24
Adding a Page Number

• Click Insert on the menu bar and then click Page
  Numbers on the Insert menu
• Be sure Page N, Bottom of Page Footer, Right
  Alignment, and Show Number on First Page are
  selected
• Click the OK button to add a page number

25
Creating Labels

• If necessary, in the Database window, click
  Tables on the Objects bar, and then click Client.
  Click the New Object button arrow on the Database
  window toolbar and then click Report
• Click Label Wizard and then click the OK button
• Ensure that English is selected as the Unit of
  Measure and that Avery is selected in the Filter
  by manufacturer box
• Click C2163 in the Product number list

26
Creating Labels

• Click the Next button
• Click the Next button to accept the default
  settings
• Click the Name field and then click the Add Field
  button
• Click the second line in the label, and then add
  the Address field

27
Creating Labels

• Click the third line of the label
• Add the City field, type , (a comma), press the
  SPACEBAR, add the State field, press the
  SPACEBAR, and then add the Zip Code field
• Click the Next button
• Select the Zip Code field as the field to sort
  by, and then click the Add Field button

28
Creating Labels

• Click the Next button
• Ensure the name for the report (that is, the
  labels) is Labels Client
• Click the Finish button
• Close the window containing the labels by
  clicking its Close button

29
Creating Labels
30
Printing Labels

• If necessary, in the Database window, click the
  Reports object. Right-click Labels Client
• Click Print on the shortcut menu. If a warning
  message appears, click the OK button

31
Adding Command Buttons to a Form

• Click Forms on the Objects bar, and then
  right-click Client Update Form
• Click Design View on the shortcut menu, and
  ensure the toolbox appears and is docked at the
  bottom of the screen. (If it does not appear,
  click the Toolbox button on the toolbar. If it is
  not docked at the bottom of the screen, drag it
  to the bottom of the screen to dock it there)
• If a field list appears, remove it by clicking
  its Close button
• Make sure the Control Wizards tool is selected
  and the window is maximized
• Click the Command Button tool and move the mouse
  pointer, whose shape has changed to a plus sign
  with a picture of a button, to the position shown
  in Figure 7-40 on page AC 424

32
Adding Command Buttons to a Form

• Click the position shown in Figure 7-40 on page
  AC 424
• With Record Navigation selected in the Categories
  box, click Go To Next Record in the Actions box
• Click the Next button
• Click the Text option button
• Next Record is the desired text and does not need
  to be changed, so click the Next button

33
Adding Command Buttons to a Form

• Type Next Record as the name of the button
• Click the Finish button
• Use the techniques in Steps 3 through 7 on pages
  AC 424 through AC 426 to place the Previous
  Record button directly to the right of the Next
  Record button
• Click Go To Previous Record in the Actions box,
  and then type Previous Record as the name of the
  button
• Use the techniques in Steps 3 through 7 to place
  a button directly to the right of the Previous
  Record button

34
Adding Command Buttons to a Form

• Click Record Operations in the Categories box.
  Add New Record is the desired action
• Click the Next button and then click Text to
  indicate that the button is to contain text. Add
  Record is the desired text
• Click the Next button, type Add Record as the
  name of the button, and then click the Finish
  button
• Use the techniques in Steps 3 through 7 to place
  the Delete Record and Close Form buttons in the
  positions shown on the following slide
• For the Delete Record button, the category is
  Record Operations and the action is Delete Record
• For the Close Form button, the category is Form
  Operations and the action is Close Form

35
Adding Command Buttons to a Form
36
Adding a Combo Box to a Form

• Make sure the Control Wizards tool is selected,
  click the Combo Box tool and then move the mouse
  pointer, whose shape has changed to a small plus
  sign with a combo box, to the position shown in
  Figure 7-46 on page AC 427
• Click the position shown in Figure 7-46 on the
  previous page to place a combo box. Click the
  Find a record on my form based on the value I
  selected in my combo box option button
• Click the Next button, click the Name field, and
  then click the Add Field button to add Name as a
  field in the combo box
• Click the Next button

37
Adding a Combo Box to a Form

• Point to the right boundary of the column heading
• Double-click the right boundary of the column
  heading to resize the column to best fit the
  data, click the Next button, and then type Name
  to Find as the label for the combo box
• Click the Finish button. Position the control and
  label in the position shown on the following slide

38
Adding a Combo Box to a Form
39
Modifying the Add Record Button

• Click the View button on the toolbar to return to
  Design view
• Right-click the control for the Client Number
  field (the white space, not the label), and then
  click Properties on the shortcut menu
• Ensure the Name property is selected
• Use the DELETE or BACKSPACE key to erase the
  current value, and then type Client_Number as the
  new name

40
Modifying the Add Record Button

• Click the Close button to close the Text Box
  Client Number property sheet
• Right-click the Add Record button
• Click Build Event on the shortcut menu
• Make sure the window is maximized

41
Modifying the Add Record Button

• Press the down arrow key four times, press the
  TAB key, and then type Client_Number.SetFocus as
  the additional statement
• Press the ENTER key
• Close the Microsoft Visual Basic - Ashton James
  College - Form_Client Update Form (Code) window
• Click the View button arrow on the toolbar, click
  Form View, and then click the Add Record button

42
Modifying the Add Record Button
43
Modifying the Combo Box

• Click the View button on the toolbar to return to
  Design view
• Right-click the Name to Find combo box (the white
  space, not the label), and then click Properties
  on the shortcut menu
• Note the number of your combo box, which may be
  different from the one shown in Figure 7-64 on
  page AC 437, because it will be important later
• Click the Row Source property
• Click the Build button

44
Modifying the Combo Box

• Click the Sort row in the Name field, click the
  box arrow that appears, and then click Ascending
• Close the SQL Statement Query Builder window by
  clicking its Close Window button
• Click the Yes button to change the property, and
  then close the Combo Box Combo29 property sheet
• Right-click the form selector (the box in the
  upper- left corner of the form)
• Click Properties on the shortcut menu

45
Modifying the Combo Box

• Click the down scroll arrow on the Form property
  sheet until the On Current property appears, and
  then click the On Current property
• Click the Build button and then click Code
  Builder
• Click the OK button
• Press the TAB key and then type Combo29
  Client_Number 'Update the combo box in the
  position shown in Figure 7-72 on page AC 440

46
Modifying the Combo Box

• Click the Close button for the Microsoft Visual
  Basic Ashton James College - Form_Client
  Update Form (Code) window, and then close the
  Form property sheet
• Right-click the Name to Find combo box and then
  click Properties on the shortcut menu
• Click the down scroll arrow until the Tab Stop
  property appears, click the Tab Stop property,
  and then click the Tab Stop property box arrow
• Click No, and then close the Combo Box Combo29
  property sheet

47
Modifying the Combo Box
48
Summary

• Create a report in Design view
• Create queries for reports
• Add fields to a report
• Add a subreport to a report

49
Summary

• Modify a subreport
• Add a date
• Add a page number
• Create and print mailing labels

50
Summary

• Add command buttons to forms
• Modify VBA code associated with a command button
• Add a combo box to a form
• Modify properties of a combo box

51
Access Project 7 Complete