Business 245 ilab 7 – must be sufficient in Microsoft visio and Microsoft access
DeVryUniversity
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 1 of 20
A. Lab # : BSBA BIS245A-7
B. Lab 7 of 7 : Database Navigation
C. Lab Overview – Scenario / Summary:
TCOs:
8. Given a database application containing forms, queries and reports, automate tasks
and create a menu system which allows for efficient navigation and operation of the
database’s functionality.
Scenario:
The purpose of this lab is to show the student how to create navigation systems for an Access
application. Students will create a main navigation form which links to additional navigation forms
called Enter Data and View Reports. Additionally, students will create forms to automate printing
reports and Closing/Exiting the Database. The Enter Data form allows you to open Customer and City
forms automatically. The View Reports form allows the user to automatically open the Customer List
and Customers by City reports. The Print Reports form will allow users to automatically print the
reports. The Exit Database form allows users to close the database and exit the application entirely.
The navigation forms can provide some measure of security to the database as they may prevent
users unfamiliar with Access from accessing database objects directly; they make using Access more
intuitive to use, and can prevent new users from inadvertently damaging your database objects.
D. Deliverables:
Upload the modified Lab7_Start.accdb file that you saved as YourName_Lab7.accdb to the
Week 7 iLab Dropbox.
Step Deliverable Points
Part A Develop the Enter Data, View Reports, Print Reports, and Customer
Database Navigation Forms
Part B Create the Close Database Form and add it to the Main Database
Navigation Form. Set the Database Options.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 2 of 20
E. Lab Steps:
Preparation:
a. Download the Database from Doc Sharing:
a. The Northwind database file Lab7_Start.accdb, available in Doc Sharing.
b. Save the file onto “c:\” directory (your local drive)
b. Using Citrix for MS Visio and / or MS Access
a. If you are using the Citrix remote lab, follow the login instructions located in the
iLab tab in Course Home.
b. You will have to upload the Lab7_Start.accdb file to your Citrix folder. Follow the
instructions located on the iLab Tab in Course Home.
c. Start MS Access:
a. If you are using Citrix, click on Microsoft Office Applications folder
b. If you are using Access on a local computer, select Microsoft Office from your Program Menu
MS Access Switchboards
In the past, users navigated database applications using a menu system based on a hierarchy similar
to that illustrated below.
Customer
Database (Main)
Switchboard
Enter Data
Switchboard
Run Reports
Switchboard
Enter Customer
Information Item
(Opens Customer
Information Form)
Enter City Data
Item
(Opens Maintain
City Data Form)
Run Customer List
(Opens the
Customer List
Report)
Run Customer List
by City
(Opens the
Customer By City
Report)
Print Reports
Print Customer
List
Print Customer by
City List
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 3 of 20
While this is a logical approach, it was not always efficient or user friendly. Access 2010 allows for
development of navigation screens facilitating more efficient movement from one function to another
within the database. In this Lab you’ll create navigation forms that allow users to move through the
various objects in a simple Access application. The Access application has two forms (Maintain
Customers and Maintain City), and two Reports (Customer List, and Customers By City). The
navigation system accesses these objects using this structure:
Notice the Customer Database Navigation Form with the tabbed options of Enter Data, View
Reports, Print Reports, and Exit Database. It facilitates easy movement from one object to
another, but (in this case) does not allow access to the underlying tables. The Enter Data tab
is currently selected, and it presents two additional tabs to either Maintain Customers or to
Maintain Cities.
The user can easily go to the database reports by simply clicking the View Reports tab in the
Customer Database Navigation Form. This opens an additional navigation form showing the
two reports CustomerList and CustomersByCity.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 4 of 20
Lab:
Part A: Step-by-step instructions to create navigation forms
Step 1: Create the Navigation Forms
a. Open your Lab7_Start.accdb Access Database file and become familiar with the contents.
b. Go to the Create tab and select Navigation, Horizontal Tabs, from the Forms group
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 5 of 20
You will see the screen below, which we will use to create the Enter Data navigation form. Notice that
the new navigation form opens in Layout view.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 6 of 20
c. Click the tab reading Add New, and enter Maintain Customers. Then press Enter. Notice the
Maintain Customer form appears in the in the body of the navigation form. Click to Select the
Maintain Customers tab and drag the right border to widen it so that the complete title shows.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 7 of 20
d. Now repeat this process to add the MaintainCities Form. Notice that in the database objects
list, the MaintainCities form name does not include a space between the two words. Be sure
you type it exactly the same way in the tab as it is shows in the database objects list.
Otherwise, your navigation form will not be able to associate the tab with the existing form.
Once the new form is added, your navigation form should look similar to the next illustration.
You will need to change the title at the top of the form. Click to select the current title,
Navigation Form, and replace it with the new title, Enter Data.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 8 of 20
e. Click the Form view option. If you are satisfied with your new form, you may close it using the
object close box circled in the diagram below. When the dialog box appears, click Yes. Name
the form Enter Data.
f. The new navigation form now appears in the objects list as shown below.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 9 of 20
Step 2: Create Navigation form to View Reports
Repeat the process used in Step 1 to create the View Reports navigation form. You will want to add
the two reports (CustomerList and CustomersByCity) shown in the objects list. Be sure to change the
report heading and to save the form as View Reports.
When finished, the form should look as follows in the Form view.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 10 of 20
Step 3: Create Form to Print Reports
a. This form will be a little different from those you have created before as you will use form
controls rather than database objects. Begin by opening a new Blank form.
b. Click to display the Design tabribbon. Click the drop down arrow to the right of the Controls
group, and be sure that Use Control Wizards is selected. See below.
c. You will then add command buttons and the label to the form. First, click on the Labels control
and then move your cursor to the body of the form. Your cursor will appear as a plus sign and
a capital letter A. Click in the form, and enter the label, Click the buttons below to select and
print the desired form. Click and drag to position the label.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 11 of 20
d. With the label selected, click on the Property Sheet option. Change the Text Align property to
Center. Set the Font Weight to Bold, and the Font Size to 14. See illustration below.
e. Now you will add command buttons to print each of the reports. Notice that when you add the
command button to the form, it automatically moves to the top left corner. That’s fine. We will
move it later. At the same time, the Command Button Wizard opens. In the wizard, select the
category Report Operations, and the action Print Report. Click the Next button.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 12 of 20
f. Notice the next view in the wizard shows the existing reports found in the database. Make sure
the first report, CustomerList, is selected, and Click the Next button.
g. Select the options to display text on the command button, and enter Print Customer List as
the text to display. Click the Next button.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 13 of 20
h. In the next view, rename the command button as cmdPrintCustList, and click the Finish
button. The wizard will close, and you will see the command button on the form. The default is
that it will be “attached” to the label previously created. In order to drag and drop it into the
desired position, right click on the button to bring up the shortcut menu. Select the Layout ->
Remove Layout option. Position the command button so that your form looks similar to the
next illustration.
i. Repeat the process to add the second command button to print the Customer by City report.
When completed, your form should look similar to the one shown below.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 14 of 20
j. Before closing the form, change the caption at the top from Form to Print Reports. To do this,
select Form from the drop down list at the top of the Property Sheet. See the diagram below.
Once it is selected, enter Print Reports in the Caption property and press Enter.
k. See changed caption property below.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 15 of 20
l. Press the close object button to close the form. When asked whether to save, say Yes, and
save the form at Print Reports. It will take a few seconds to save, but the form will then show
in your database object list.
Step 4: Create the Customer Database Navigation Form, and Add Items.
a. Create a new navigations form. Refer back to earlier steps in the lab if you need more detailed
instructions.
b. Name the Add New tab as Enter Data. It will take a few seconds, but you will then see the Enter
Data navigation form appear as a subform. See the illustration below.
c. Add the View Reports and Print Reports form in the same way.
d. When the three forms have been added, Change the heading (caption) of the main form to
Customer Database.
e. Click the close button to close the form. When prompted to save, click Yes, and name the form
Customer Database. Reopen the form to see it in Form view. It should look similar to the one
below. Navigate through the various options to be sure the form works as desired.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 16 of 20
End of Part A
Part B: Create a Form to Exit the Database and add to the Customer Database Navigation
Form. Set the Database Options.
Step 1: Create the Form to Exit the Database and add to the Customer Navigation Form.
a. Referring to the instructions in the previous Step, create a new blank form to exit and close the
database. Note that you will not be using Report Operations in the control wizard. Look at the
various options to locate the selections that will meet your needs.
b. When completed, the new addition should appear similar to that shown below.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 17 of 20
Step 2: Set the OPTIONS
Now you need to set up the Access environment so new users don’t see the objects in the Navigation
Pane. Further, you want to set the Customer Database Navigation Form to show up automatically
when the user starts the database application.
a. Go to the File tab and press the Options button at the bottom left.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 18 of 20
b. You should get this screen:
c. Select the Current Database item in the left-most pane, and in that pane, (see next illustration)
select the Display Form drop down list. Then select the Customer Database Form, and uncheck
the Display Navigation Pane checkbox.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 19 of 20
d. Press the OK button to get this message:
e. Press OK, and then close and reopen your database. When it opens, the Customer Database
Navigations form should appear, and the Navigation Pane should be gone:
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
Copyright © 2010 by DeVry Educational Development Corporation.
All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical,
including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry
Educational Development Corporation.
BIS245_W7_iLab_Instructions x Page 20 of 20
Step 3: Testing your Navigation Forms
Test your Navigation forms to make sure they work. If you’ve forgotten something or need to modify
something, open the forms which needs changing in either layout or design view.
Step 4: Save and Submit
Save your YourName_Lab7.accdb database. Submit your iLab 7 database to the Weekly iLab
Dropbox.
End of Part B
End of Lab 7
City | CityName | |||||||
---|---|---|---|---|---|---|---|---|
1 | Orlando | |||||||
2 | Toronto | |||||||
3 | Calgary | |||||||
4 | Atlanta |
CustomerID | LastName | FirstName | |
---|---|---|---|
Ward | Bert | ||
Feldman | Robin | ||
Smith | Jay | ||
Russell | Stephanie | ||
5 | Krause | Tim | |
6 | Pinesi | Veronica | |
7 | Garrett | Jana | |
8 | Beecher | Rick | |
9 | Romney | Steve | |
10 | Gardner | JP | |
11 | Tate | Heather | |
12 | Kim | ||
13 | Carrabas | Ron | |
14 | Crawford | Jake | |
15 | Brown | Sheri |
SELECT [City].[CityID], [City].[CityName]
FROM City;
SELECT DISTINCTROW *
FROM Customer;
SELECT DISTINCTROW *
FROM City;
SELECT DISTINCTROW *
FROM Customer;
SELECT [Customer].[CustomerID], [Customer].[LastName], [Customer].[FirstName], [City].[CityName]
FROM City INNER JOIN Customer ON City.CityID=Customer.City;
City | CityName | |||||||
---|---|---|---|---|---|---|---|---|
1 | Orlando | |||||||
2 | Toronto | |||||||
3 | Calgary | |||||||
4 | Atlanta |
CustomerID | LastName | FirstName | |
---|---|---|---|
Ward | Bert | ||
Feldman | Robin | ||
Smith | Jay | ||
Russell | Stephanie | ||
5 | Krause | Tim | |
6 | Pinesi | Veronica | |
7 | Garrett | Jana | |
8 | Beecher | Rick | |
9 | Romney | Steve | |
10 | Gardner | JP | |
11 | Tate | Heather | |
12 | Kim | ||
13 | Carrabas | Ron | |
14 | Crawford | Jake | |
15 | Brown | Sheri |
SELECT [City].[CityID], [City].[CityName]
FROM City;
SELECT DISTINCTROW *
FROM Customer;
SELECT DISTINCTROW *
FROM City;
SELECT DISTINCTROW *
FROM Customer;
SELECT [Customer].[CustomerID], [Customer].[LastName], [Customer].[FirstName], [City].[CityName]
FROM City INNER JOIN Customer ON City.CityID=Customer.City;