FunctionX Logo

The US Senate Database: Forms

Accessory Forms

Introduction

In a classic database, you usually need to create corresponding forms and probably equivalent reports or Data Access Pages to the accessories tables. Because such tables are sometimes hardly used, they can be neglected. For example, we create a table for senators genders. Since there are only male and female senators, you would think that there is no need to create a corresponding form because there never be any need to add values to it. After all, we don't expect to have an hermaphrodite senator, at least as long as no one claims to be one. On the other hand, for statistic reasons, you may want to get a list of male, then a list of female senators in your database. For this and many other reasons, you should create forms for (most of) your access tables.

Automatic Forms

Accessory forms are particularly easy to create, regardless of the version of MS Access you are using. The main difference is that MS Access >= 2000 creates subforms when you use AutoForm. Therefore, AutoForm is a valuable feature to quickly create forms. In MS Access >= 2000, the automatic subform can be annoying. Fortunately, you can remove them very easily. In fact, we will not use them for our database.

  1. To quickly create a form, on the main menu, click Insert -> Form
  2. In the New Form dialog box, click AutoForm: Columnar. In the bottom combo box, select CMStatus
     
  3. Click OK
  4. To save the form, press Ctrl + S. Accept the suggested name of the form as CMStatus and click OK
  5. In the same way, use AutoForm: Columnar from the New Form dialog box to create a form for the following tables: Committees, Genders, MaritalStatus, Parties, Religions, SCMStatus, States, and Subcommittees
  6. Save each form with the same name as its corresponding table

Relations-Based Forms

Relations-Based Controls

Relationships on a database are particularly important as they allow information to flow from one object to another, thereby reducing duplicate information. One of the prime controls used in such relationships is the combo box.

If you create a foreign key on a table using the Lookup Wizard, the field would become a combo box, also called lookup list. This conveniently allows the user to select the value of the field from a list instead of typing it. When creating the corresponding form, report, or Data Access Pages for the table, the field for that foreign key would be created as a combo box. If you created the field only as a numeric type, you would need to create its relationship with the primary key of its parent table. When creating the table, you can then use either the Combo Box Wizard or create a SQL statement you would assign to the combo box to use as the list or items.

  1. To modify a form, in the Database window, click Forms. Right-click Subcommittees and click Design View
  2. Click the CommitteeID text box and press Delete to remove it with its label
  3. On the Toolbox, make sure the Control Wizard button is down . Click the Combo Box control and, on the form, click the area where the CommitteeID text box was
  4. In the first page of the wizard, make sure the first radio button is selected and click Next
  5. In the list of tables, click Committees and click Next
  6. In the Available Tables list, double-click Committee and click Next twice
  7. Click the arrow of the Store That Value In This Field combo box and select CommitteeID
     
  8. Click Next
  9. Change the label to Committee and click Finish

Intermediary Forms

You may have noticed that we create some tables made of only fields from other tables. These tables are the solution to a Many-To-Many relationship. The reason is that they allow you to put together, in one list, values that are stored in different tables but cannot professionally be created in the same  table. Therefore, this table, also called junction table is used as intermediary to create a complex list.

To create a form that uses a junction table, you should(must) first make sure that the relationships have been established between the junction tables and those that are supposed to provide it with necessary values. We took care of that by creating and managing the relationships.

When creating the table, if you use the Lookup Wizard, each table would be added as a combo box. When creating the form, you can simply drag the item from the Field List and it would be added as a combo box. When creating the table, if you used the classic approach as done on SQL Server and other database environment, which consists of using a numeric data type for each foreign key, the field can only be added as a regular text box. To make it a combo box so it would display it real list, you can either use the Combo Box Wizard or create a SQL expression to be assigned to the combo box.

  1. To create a new form, on the main menu, click Insert -> Form
  2. In the New Form dialog box, click Design View. In the bottom combo box, select CommitteesMemberships, and click OK
  3. From the Field List, drag CommitteeMembershipID and drop it in the top section of the form
  4. Save the form as CommitteesMemberships
  5. To create a new combo box, on the Toolbox, make sure the Control Wizard button is down . Click the Combo Box control and, on the form, click the area under the CommitteeMembershipID text box
  6. In the first page of the wizard, make sure the first radio button is selected and click Next
  7. In the list of tables, click Committees and click Next
  8. In the Available Tables list, double-click Committee and click Next twice
  9. Click the arrow of the Store That Value In This Field combo box and select CommitteeID
     
  10. Click Next
  11. Change the label to Committee and click Finish
  12. To create a new combo box, on the Toolbox, click the Combo Box control and, on the form, click the area under the CommitteeID box
  13. In the first page of the wizard, click Next
  14. In the second page of the wizard, click the Queries radio button. In the list of Queries, click SenatorsPartyAndState and click Next
  15. In the Available Tables list, double-click Senator, Party, and State
  16. Click Next twice
  17. Accept that Senator will display as the value of the column and click Next
  18. Click the arrow of the Store That Value In This Field combo box and select SenatorID 
  19. Click Next
  20. Change the label to Senator and click Finish
  21. To create a new combo box, on the Toolbox, click the Combo Box control and, on the form, click the area under the SenatorID combo box
  22. In the first page of the wizard, click Next
  23. In the second page of the wizard, in the list of tables, click CMStatus and click Next
  24. In the Available Tables list, double-click CMStatus and click Next twice
  25. Accept that Senator will display as the value of the column and click Next
  26. Click the arrow of the Store That Value In This Field combo box and select CMStatusID
  27. Click Next
  28. Change the label to Committee Membership Status and click Finish
  29. Reposition and resize the form to make it look acceptable as you judge necessary
  30. Close the form
  31. Using the same techniques as above, create a form based on the SubcommitteesMemberships table and save it as SubcommitteesMemberships
  32. Close the form
Previous Home