FIT 100

Lab 17:  More Microsoft Access and Relational Databases

Creating Forms for Data Entry

Autumn 2001

Reading for Lab:

·         Chapter 16 of Fluency with Information Technology:  Foundations and Fundamentals of Information Technology

 

Introduction:

In today’s lab we will look at the presentation layer:  creating forms for users to input data and receive data output. (See Lab 15 for a brief description of all three levels involved in a complete Relational Database Management System)

 

Presentation Services

The interfaces created to allow users of the Database system the ability to enter data and to view output of data.  A Form is the typical interface used for input and output of data that is retrieved by a Query.  There are other interfaces in Access such as Reports and Data Access Pages, but you will only be dealing with Forms for the first part of Project 4.

 

Objectives:

  1. To connect the abstract concepts of Entities, Attributes and Rows (Tuples) in the Relational Database Model with the corresponding objects in Access that are concrete representations of those concepts.
  2. To become familiar with MS Access, one of many different Relational Database Applications.
  3. To create forms that will allow a user to add and retrieve data from tables and display it in a pleasing manner.

 

TO DO: 

1.      Download a copy of your Lab 15 database to the desktop from Dante.

If your database was not closed when you tried to ftp it after the last lab, you will have an empty file.  If this happened, you will need to work through Lab 15 to recreate it.

 

2.      Open Microsoft Access

Programs>Microsoft Access

 

3.      Click the “Open an existing database” radio button and navigate to the database you created for Lab 15: Lab15.mdb

 

Creating Forms in Access

 

Start lab by creating Forms using the wizard.  After creating a couple forms, you will create combo boxes on one of them in design view in order to see data from two different tables as we add data to a third.

4.      Create a form to add Major instances (rows of data) to the Major table.  Go to the Forms window and create a form for the Major table using the form wizard.

 

Select Table: tblMajor from the available fields and move all the attributes over to the right to the selected fields.

 

 

5.      Choose a layout (Usually Columnar or Justified is the nicest looking)

  • Click Next
  • Choose a style
  • Click Next

6.      In the last window, give a name to the form.  Call it frmMajor to distinguish it from the table Major.  Click finish and the form will open up, ready for you to add data.

7.      Using frmMajor, add the following Majors:

 

INFO

CSE

BIOL

CHEM

PHYS

SPAN

ART

HIST

 

 

Save the form layout and close the form.  Go to the Major table.  Are the records you just added through the form in the table?  Good!

8.      Create a form to add data to the Advisor table.  Use the same process as steps 3-5, but select from Table: tblAdvisor.  Name it frmAdvisor.

9.      Using frmAdvisor, add the following Advisor Names and Department:

Mel Oyler from Informatics

Go to the Advisor table.  Look at the records you just added through the form.

10.  Now we’ll do something a little different:  create a form that adds data to the Student table, but it will display data from Major and Advisor tables.

11.  Go through the same steps, 3-5 using the Table: tblStudent to add all the fields. 

 

12.  Save the form as frmStudent.  You now will have a form that may look different from this, but all the attributes listed should be the same:

 



13.  Switch to the design view by clicking the icon in the upper left corner:

 

 

14.  In design view, extend the borders of the form and the form footer to give you some layout room.  Notice the form has some similarities to the form setup from Visual Basic.  Access is powered by subset of Visual Basic called VBA and works with the same type of Integrated Development Environment (IDE).

 

 

 

15.  Add two combo buttons to the form.  One combo button will be used to display the contents of the Major table.  The other will display the contents of the Advisor table.

 

16.  From the tool box on your screen, select the combo box object (also known as a control).  Add the first combo box to the form here in the same way you added controls to your forms in VB.

 

Once you place the combo box control on your form, a combo box wizard window will come up to walk you through the process.

 

Select the following options from each screen and then click Next:

 

  1. Have the combo box look up values from a table
  2. Select the Major table for the table that will provide values for the combo box.
  3. Move all available fields over to the selected fields window.
  4. Make sure the “Hide key column” box is checked
  5. Store the value in the “MajorID” field of the Student table (those are the options in the drop down menu)
  6. Leave the name the combo box is given and click Finish.

 

You will return to design view.  There is now a combo box and label in the form.  The label can be deleted (the combo box should line up across from the other Major label and text box).
 

 

 

 

17.  Your form now looks similar to this:

 

 

 

18.  Switch to Form view:

 

 

and look at the values that are in the combo box.  Tres cool, no?

 

         

19.  Return to Design View.  Add another combo box just across from the AdvisorID text box.

Select the following options from each screen and then click Next:

 

  • Have the combo box look up values from a table
  • Select the tblAdvisor as the table that will provide values for the combo box.
  • Move ONLY the AdvisorID, Lname and Department from the available fields over to the selected fields window.
  • Make sure the “Hide key column” box is checked
  • Store the value in the “AdvisorID” field of the Student table (those are the options in the drop down menu)
  • Ignore the name option for the combo box and click Finish.

 

You will return to design view.  There is now a combo box and label in the form.  Delete the label.  (Ask your Instructor if you aren’t sure)     

 

20.  Return to Form view again.  Start moving through the records you have already created.  You should see the number value of the Advisor in the text box for Advisor ID and the name and department of the person that goes with that number appears in the combo box on the right. 

 

We have set up the combo box so that it displays the text values for us to see, but it will store the corresponding key value in the Student table in the attribute AdvisorID.  That stored value will be referred to as a foreign key in the Student table.

 

 

21.  Since we deleted the original Major attribute and that held text earlier in the lab, let’s now add a major for the students in the table.  Use the combo box to select a Major. Notice the corresponding key value is stored to the left.

 

22.  Add Student records using the form.  Give the students a major and an advisor.

 

23.  Once you have added 5 or 6, go to design view and let’s add a command button to our form that will close it when a user is done adding data.  Select a command button control and place it on the form in the upper right hand corner.

 

 

24.  When the Command Button Wizard comes up, select Form Operations from the Categories column and Close Form from the Actions column and click next:

 

 

Choose either a picture or text for display on the button and click next:

 

 

Name the button cmdExit and Finish.

 

25.  Save your form.  Switch to Form View and press the exit button.  Does the form close?

Remember all the various operations that are available in the Command Button Wizard.  You will use some of them for Project 4.

 

26.  Go to the Student table and open it.  Do you see the records you just added? 

 

27.  Show your Student form to your Instructor.

***A quick note about file size:***

 

As you start working on your project, you will create forms.  You will make mistakes and decide to delete forms and other objects from the database.

 

In other applications, this would usually mean your file gets a little smaller.  It is NOT that way in Access.

 

Access “remembers” all the different forms and command buttons, etc that you create - even after you delete them.  If you don’t occasionally do a little “house cleaning”, your database file will grow immense!

 

To clean up your database and decrease the file size after you have been working in it for a while, compact and repair the database:

 

Tools>Database Utilities>Compact and Repair Database…

 

Do this to keep the database size manageable.  ESubmit will only accept files up to 1 MB in size, so you must keep your database under control for Project 4.
 
 


 

 

 

 

 

 

28.  Close your database and FTP the Lab15.mdb (remember, you created it in Lab 15) to your Dante account if you don’t already have it on disk.  We will use it again for the next lab.

 

WHEW!!!! You’ve made it through another lab!  Only 2 more to go!!!!!