Illustrated Access 2016 | Module 3: SAM Project 1a

Illustrated Access 2016 | Module 3: SAM Project 1a

Trips Database

USING FORMS

“Detailed Teaching Instructions” have been prepared for NGU students to enhance the learning experience. Red, and italic text are additional details to the SAM instruction documents designed to “teach” as you work. The italic and red words are the words your instructor would likely speak while demonstrating the skill in a classroom setting.

There is usually more than one way to complete a task. Your instructions will indicate one method; however, you should never be afraid to experience with other shortcuts to accomplish the same task. When in doubt, just google for answers!

PC users will find many additional commands and options from the dialog box launchers that appear in the lower right corner of many ribbon groups. Click the button for a dialog box to reveal additional choices.

There is not an Access version for the MAC, therefore, MAC users will need to use a PC for the Access unit of study. There are PC computers in the computer labs and library on campus for your use.

FOLLOW THE GETTING STARTED INSTRUCTIONS AND SAVETHE FILE BEFORE MOVING FORWARD!

GETTING STARTED

  • Open the file IL_AC16_3a_FirstLastName_1.accdb, available for download from the SAM website.
  • Save the file as IL_AC16_3a_FirstLastName_2.accdb by changing the “1” to a “2”.
  • Hint: If you do not see the .accdb file extension in the Save As file dialog box, do not type it. Access will add the file extension for you automatically.
  • To complete this Project, you will also need to download and save the following support file from the SAM website:
  • Support_IL_AC16_3a_House.jpg
  • Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

*If you did not save the project and change the 1 to a 2, do it now. In Access, you will have trouble with saving and with the Compact and Repair tool at the end of the project if you have not savedproperly at the start of the project.

  1. Use the Form Wizard to create a form based on the Customers table with the following options:
  2. Include all fields from the Customers table in the form.
  3. Select the Columnar layout for the form.
  4. Set the title of the form to Customer Entry Form, then open it in Design View.

Instructor Explanation: A form in Access is a database object that you can use to create a user interface for a database application. A "bound" form is one that is directly connected to a data source such as a table or query, and can be used to enter, edit, or display data from that data source. Forms make it much easier to enter and view the data into a table.

Here’s How:

On the Create Ribbon, Forms Group, click the Form Wizard Button

Under Tables/Queries click the drop-down arrow and select Table: Customers

Click the double arrow to add all table fields to the form

Click Next and select Columnar

Click Next and for the title type: Customer Entry Form

Click Finish

On the Home Ribbon, Views Group, change the view to Design View

Close the Field List task pane if it opens

  1. With the Customer Entry Form open in Design View, change the Default value property of the State text box to CO. Save the form, then switch to Form View.

Instructor Explanation: When you switch to Design View, the Property Sheet may open. If not, on the Design Ribbon, Tools Group, click the Property Sheet Button

Here’s How:

Click to select the State Text Box (the one to the right)

In the Property Sheet, click the Data Tab

In the space to the right of Default Value type: CO

On the Home Ribbon, Views Group, click the Views Button and switch to Form View

Any new entry you enter through the form will automatically default to CO for the state. You can still change the entry if the State is not CO.

  1. Use the Customer Entry Form to navigate to the record with the CustNo field value 4. Change the Phone field value of this record to 555-111-7878.

Here’s How:

Locate the record buttons in the lower left of the form screen

Click in the space that shows the record number

Type: 4

Press Enter

Click into the Phone space of the form and select the phone number

Type: 555-111-7878

Notice the input field properties that added the parentheses and hyphen automatically.

  1. Use the Customer Entry Form to add a new record to the Customers table with the field values shown in Figure 1 below. (Hint: The CustNo field is an AutoNumber field.)

Here’s How:

Locate the record buttons in the lower left of the form screen

Click the button with the yellow starburst to add a new record

Notice that the CustNo is an automatic field and you will not enter text in that field

Click in the FName space and type: John

Notice the automatic number field has changed to 50

Enter the remaining text from the table below

Figure 1: Customer Entry Form

  1. Switch to Layout View for the Customer Entry Form, then change the font color of the Customer Entry Form label in the Form Header section to Automatic (black) and bold. Save and close the Customer Entry Form.

Here’s How:

Right-click the Customer Entry Form name in the Navigation Pane and select Layout View

Locate the title box with the text “Customer Entry Form” in the blue area toward the top and select the text

On the Format Ribbon, Font Group, click the Bold Button

On the Format Ribbon, Font Group, click the drop-down arrow on the Font color Button and select Automatic (Black)

Click the X close button for the form and answer Yes for saving

  1. Create a split form based on the Trips table, then save the form as Trips Split Form.

Instructor Explanation: A split form is a feature in Microsoft Office Access that gives you two views of your data at the same time— a Form view and a Datasheet view. The two views are connected to the same data source and are synchronized with each other at all times. You can add, edit, or delete data from either part.

Here’s How:

In the Navigation Pane, click to select the Trips Table

On the Create Ribbon, Forms Group, click the More Forms drop-down arrow and select Split Form

Right-click the form tab in the upper left of the form and select Save

Name the Form: Trips Split Form

Click OK

  1. Switch to Form View for the TripsSplit Form. Navigate to the record with the TripNo field value 7. Change the TripName field value for this record to Aspen Development Project. Close the TripsSplit Form.

Here’s How:

On the Home ribbon, Views Group, click the View Button and select Form View

This will activitate the form view located in the top portion of the screen

Locate the Records buttons in the lower left of the screen (below the datasheet view of the table) and navigate to TripNo field 7

Observe how the form is synchronized to the table

Change the TripName field to: Aspen Development Project

Press Enter

Observe that the field has been updated in both views

Click the X close button to close the form

Answer Yes to saving

Use Figure 2 on the next page as a reference when modifying the Trip Schedule form
in Steps 8–18.

Figure 2: Trip Schedule Form

  1. Open the Trip Schedule form in Design View, then change the Trip Schedule label in the Form Header section to read Trip and Schedule Form.

Here’s How:

In the Navigation Pane, Forms Section, right-click the Trip Schedule form and select Design View

Locate the title box with the text “Trip Schedule”

Change the text to: Trip and Schedule Form

  1. With the Trip Schedule form still open in Design View, delete the InsertObservation House label in the Form Header section.

Here’s How:

Locate the title box with the text “Insert Observation House”

Click the border of the text box to select the box

Press the Delete key on the keyboard

  1. With the Trip Schedule form still open in Design View, do the following:
  2. Insert the file Support_IL_AC16_3a_House.jpg, available for download from the SAM website, at the top of the Form Header section, at the 3" mark on the horizontal ruler.
  3. Resize the image to position it at the top of the Form Header section, between the 3" and 5" marks on the horizontal ruler, and between the 0" and 1.5" marks on the vertical ruler.

Here’s How:

On the Design Ribbon, Controls Group, click the Insert Image Button

Browse to the location where you stored the House.jpg support file

Select the file

In the blue header section, position the mouse pointer at about the 3” gridline near the top of the title area and click

Drag the borders to move the image or the corners to resize the image and place it in an area where it is at the top and about 2” wide x 1.5” tall

  1. In the Trip Schedule form, drag the right edge of the form to the left, so that the form is only 5" wide.

Here’s How:

Position the mouse point over the right edge of the grid area of the form until you see a double arrow

Drag the edge left to end at the 5” mark on the ruler

  1. In the Trip Schedule form, drag the top edge of the Detail section up, so that the Form Header section is 1.5" tall.

Here’s How:

Position the mouse point over the top edge of the detail banner until you see a double arrow (the detail banner is a white bar with an arrow and the word “Detail”)

Drag up to make the height of the header area 1.5”

  1. In the Trip Schedule form, select all 10 labels in the Detail section of the Trip Schedule form, then right-align the text within the labels. (Hint: Do not right align the controls in the form.)

Here’s How:

Select the Trip No label (the box on the left)

Hold down the Shift key and select all other label boxes underneath

On the Format Ribbon, Font Group, click the Align Right Button

  1. In the Trip Schedule form, do the following:
  2. Delete the Price label
  3. Move the Price text box to the right of the TripNo text box.
  4. Position the Price text box so that the left edge is at the 3" mark on the horizontal ruler and the top edge is aligned with the top edge of the TripNo text box.

Here’s How:

Click away from the selection to deselect

Select the Price label (the one on the left)

Press the Delete key on the keyboard

Position the mouse pointer over the border of the Price text box

Drag to position the Price text box to the right of the TripNo text box and allow the left edge to be positioned at the 3” gridline

  1. In the Trip Schedule form, resize the TripStartDate text box to be approximately the same width as the calculated date for the trip end date. (Hint: The trip end date is a calculated value in the text box just below the Duration text box.)

Here’s How:

Locate the TripStartDate text box (the one on the right) and drag the left side border left until the box is about the same width as the TripStartDate text box

  1. In the Trip Schedule form, change the Tab Stop property for the TripNo text box to No.

Here’s How:

Select the TripNo text box (The box on the right) *You will see an orange border around the box when it is selected

If necessary, turn on the Property Sheet by clicking the Property Sheet Button from the Design Ribbon

Click to select the “Other” tab

Locate the Tap Stop row in the Property Sheet and observe the setting is Yes

Click the drop-down arrow to the right of Yes and select No

  1. In the Trip Schedule form, change the tab order for the form so that the Price field is first.

Here’s How:

On the Design Ribbon, Tools Group, click the Tab Order button to open the task pane

Select Detail in the Section area

Obseve the list under Custom Order (This is the order in which pressing the tab key will cycle you through the text boxes)

Locate the Price field in the Custom Order

Position the mouse in the small gray box to the left of the name “Price” and drag to position the Price field in the first position

Click OK

  1. In the Trip Schedule form, add a new text box to the Detail section of the form, to the right of the Duration text box. Modify the new text box and its associated label using the following options:
  2. Change the new label text to Cost/Night.
  3. Enter the expression =[Price]/[Duration] in the text box.
  4. Change the Format property for the new text box to Currency.
  5. Switch to Layout View, then move and resize the new label and text box as shown in Figure 2 on the previous page.
  6. Save the Trip Schedule form.

Here’s How:

On the Design Ribbon, Controls Group, click the Text Box Button

In the “Detail” area, position the mouse point at about the 4” gridline from the horizontal ruler and about the 1” gridline from the verital ruler and click to create a new label box and text box

Click in the label box (the box to the left) and replace the existing text with: Cost/Night

Click in the text box (the box to the right) and replace the existing text with: =[Price]/[Duration]

Select the text box you just typed in and go to the Format tab of the Property sheet.

On the Format line click the drop-down arrow to the right side and slelct Currency

The next step is a little tricky. You need to move the text box over the the left, but if you drag the text box the label box will move equally to the left. Instead, you resize the text box and in doing so you will be able to position it to the left.

Position the mouse pointer over the left side border of the text box (the box on the right)

Look for the double arrow and drag to the left until the box is almost touching the label box

Position the mouse pointer over the right side border of the text box (the box on the right)

Look for the double arrow and drag to the left until the box is about lined up on the right side with the other text boxes in the form

If it is necessary to move the boxes up or down, click away to deselect then click back on the text box to select and use the up or down arrow key on the keyboard to move the boxes up or down

  1. Switch to Form View for the Trip Schedule form, then navigate to the last record in the form. (Hint: The TripNo field value for this record is 55.) Change the Duration field value for this record to 4.

Here’s How:

On the Home Ribbon, Views Group, click the View Button and select Form View

In the lower section of the screen, locate the navigation arrows and click the arrow pointing right with a line to move to the last record (Trip No 55)

In the duration field, change the 5 to a 4

Press Enter

  1. Using the Trip Schedule form, navigate to the second record in the form. (Hint: The TripNo field value for this record is 9.) Change the Trip Name value for this record to Biking for Ecology.

Here’s How:

Using the navigation arrow buttons, move to record number 2 (The TripNo field value for this record is 9)

Change the Trip name to: Biking for Ecology

  1. In the Trip Schedule form, use the Find feature to find the trip record where the City field value equals Islamorada. Change the Duration field value for this record to 5. Close the Trip Schedule form.

Here’s How: