FIT 100

Lab 16: More Microsoft Access and Relational Databases

Creating Forms for Data Entry

Spring 2001

Reading to be done for Labs 15 and 16:

· Chapter 15 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:

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.
To become familiar with MS Access, one of many different Relational Database Applications.
To create forms that add and retrieve data from tables for view by a user.

TO DO:

1. Open Microsoft Access

Programs>Microsoft Access

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

3. Last lab you created two tables. This lab we will add a table called Major to hold the list of majors that a student can declare. For our database, a student will only be allowed to declare one major. This will maintain a one – to - many relationship between the Student and Major entities.

4. Create a table in design view. This table will be called Major. The table should have the following attributes: MajorID as the Primary Key and MajorName as a text attribute.

5. Set the primary key for the Major table and save it as Major.

6. Close the table.

7. Since we are going to use the data in this table and associate it with the Student table, we need to remove one attribute and add another attribute to the Student table. What attribute will we add to the Student table? What will we remove?

8. Open the Student table in Design view. Do this by clicking once on the Student table object and then click the Design icon above,

9. We will no longer use the attribute Major as a text data type. Since you already have some data in that column, it will be easier to delete the attribute at this point and then create another new field called Major again, but this time with a number data type.

Select the field Major, right click the mouse. From the menu that pops up, select Delete Rows,

When asked if you want to “permanently Delete the selected fields….”, click Yes.

10. Now the old attribute Major and all the corresponding data in your table have been removed. Insert a new row right above the field Year.

Select the Year row, right click and select Insert Rows,

A new row will be inserted between FName and Year.

11. Add a new field (attribute) to the table Student where you inserted the row.

Name it Major.
Make the data type a number.
This is where you will store the number that corresponds with the name of the Major (from the Major table) that the student has declared. (It’s the same as the way you have added the attribute AdvisorID as a number data type)

12. Save and close the table.

13. You now have the table structures created to hold instances of the entities Student, Advisor and Major. Let’s move to the Forms tab and create a form where we can add information to the Advisor, Major and Student tables.

Creating Forms in Access

Practice creating Forms using the wizard. After you create a couple forms with the wizard, we’ll create combo boxes on one of them in order to see data from two different tables as we add data to a third.

14. Create a form to add Advisors. Go to the Forms window and create a form for the Advisor table using the form wizard.

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

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

Click Next
Choose a style
Click Next.

16. In the last window, give a name to your form. Let’s call it frmAdvisor to distinguish it from the table Advisor. Click finish and the form will open up, ready for you to add data.

17. Using frmAdvisor, add the following advisors:

Mel Oyler from INFO

Alan Borning from CSE

John Cusak from ART

Vitoli Demetrio from HIST

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

18. Create a form to add data to the Major table. Use the same process as steps 14-16, but select from Table: Major. Name it frmMajor.

19. 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. Look at the records you just added through the form.

20. Do you notice where another location where we could use the number from the Major table instead of typing out the same names over and over?

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

22. Go through the same steps, 14-16 using the Table:Student to add all the fields.

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

24. Now, switch to the design view by clicking the icon in the upper left corner:

25. In design view, extend the borders of the form and the form footer to give you some layout room.

26. You will now 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.

27. From the tool box on your screen, select the combo box object. 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:

Have the combo box look up values from a table

Select the Major table for the table that will provide values for the combo box.

Move all available field over to the selected fields window.

Make sure the “Hide key column” box is checked

Store the value in the “Major” field of the Student table (those are the options in the drop down menu)

Name the combo box cboMajor and click Finish.

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

28. Your form now looks similar to this:

29. Switch to Form view:

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

30. 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 Advisor table for 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 “Advisor” field of the Student table (those are the options in the drop down menu)
Name the combo box cboAdvisor and click Finish.

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

31. 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.

32. 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.

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

34. When you have added 5 or 6, save the form layout and close the form.

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

***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 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.

36. Show your Student form to your TA.

37. FTP your Lab15.mdb to your Dante account if you don’t already have it on disk. We will use it again for Lab 17.

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