14.2 Multiple Table Forms

Using Queries for a Form

First, a query needs to be designed to build a relational database for the fleet vehicle program. In this example, you have two tables of information, as seen in Figure 14.17: tblVehicle and tblMaintenance.

Figure 14.17 Forms are used in a variety of ways in organizations and using Access collects the data automatically into a table. (Used with permission from Microsoft)

The tblVehicle table represents each unique car/truck the company owns. The tblMaintenance table represents every maintenance record (oil changes, new tires, chipped glass repair, inspections, etc.) on all vehicles. In this section, you will see how to design a form that can add information to both tables simultaneously.

Remember that the ultimate goal of the new form is to add a new vehicle to the fleet and to include that vehicle’s initial service information (oil change, safety inspection, cleaning, company decals, etc.) so that WorldCorp does not lose track or misuse valued assets. The new form will serve as a quick form for all new vehicles. For the query to be effective, a clear relationship needs to be established between the two tables.

In this example, VehicleID is the primary key of tblVehicle and is listed as a required field of tblMaintenance. Recall from the chapter Understanding and Using Databases that a primary key is a field or set of fields with values that are unique throughout a table. Values of the primary key can be used to refer to entire records because each record has a different value for the key, as shown in Figure 14.18.

Figure 14.18 Similar to defining the number type in Excel, when you create fields you can determine the type of information in that field. (Used with permission from Microsoft)

Access provides different data types for formatting field names. Rather than setting the VehicleID field in the tblMaintenance to short text, autonumber, or number, you can use the Lookup Wizard to build a relationship between tables. The example, shown in a series of screenshots (Figure 14.19, Figure 14.20, Figure 14.21, Figure 14.22, Figure 14.23, and Figure 14.24) will walk through the use of the Lookup Wizard steps that are used to create a relationship.

Figure 14.19 The Lookup Wizard can be used to create fields in a table in a step by step fashion either from an existing table or you can create a new table. (Used with permission from Microsoft)

Figure 14.20 If you choose to pull fields from an existing table in the database, you will need to identify the specific table. (Used with permission from Microsoft)

Figure 14.21 Only fields contained in the table you select will be listed. (Used with permission from Microsoft)

Figure 14.22 Sort the information using a logical field such as the specific car details. (Used with permission from Microsoft)

Figure 14.23 Recall the primary key is the field that ties the tables together. (Used with permission from Microsoft)

Figure 14.24 Enabling data integrity is a good idea if you are collaborating with others in the same database file. (Used with permission from Microsoft)

Now that you have created a relationship between the tables, build a query that joins the fields from the two tables together. To start, go to Query Design and bring in all the fields from both tables. In this example, the query will be saved as qryNewVehicles, as it will be the driver for the New Vehicle form to use in the vehicle fleet program (see Figure 14.25).

Figure 14.25 (a) In the query view you can see the primary keys identified in each table. (b) Define the relationship between the two tables and which records are created in the new table created from the form. (Used with permission from Microsoft)

Select qryNewVehicle from the list of All Access Objects to create the form “fromNewVehicle” (see Figure 14.26). Access will create the form automatically. This form allows the user to enter both the new vehicle that is part of the fleet and the initial check-in service performed on the vehicle.

Figure 14.26 Because relationships were created previously between the two tables using the Wizard, when data is entered into the form, both tables are populated with the information. (Used with permission from Microsoft)

Understanding How Subforms and Relationships Work

Using subforms is an effective way to create one-to-many relationships and offers unlimited options for business use. In this type of association between two tables, the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.

For example, if you want to display an order with all of its details, you will use a subform. The order information would display on the “main” form (one detail), and the order details would display on the subform (many details). The data in the CustomerOrder table in Figure 14.27 is the “one” side of the relationship in this example. It creates a Document number for the transaction, identifies the Customer, provides room for Notes (delivery instructions, special handling, etc.), and date-stamps the transaction. In contrast, the data in the OrderDetails table is the “many” side of the relationship, showing an unlimited list of items purchased in a transaction.

Figure 14.27 The main form provides the lookup values to determine what to populate in the subform, which lists the items ordered. (Used with permission from Microsoft)

The main form and subform are linked so that the subform displays only records that are related to the current record in the main form. For example, when the main form displays a Customer Order by Arnold on 1/20/22, as shown, the subform then displays items that were included on Arnold’s #2 order only. If the form and subform were unlinked, the subform would display all the items that Arnold ever ordered, not just those ordered on 1/20/22, on Document #2.

Adding a Subform through the Subform Wizard

There are two common ways to add a subform to a main form. The quickest way is to simply open a form in Design View and then drag and drop a form (from your list of completed forms) that you want to be the “many” as your subform. However, using the Form Wizard can ensure that the correct relationships are established when creating the advanced form. The following example will walk you through the use of the Form/Subform Wizard to track your time away from the WorldCorp office on the golf course.

Start by designing a query that helps ensure that relationships between the tables are created. To help you think through the query design, you might consider sketching it out on paper or in another app with boxes and arrows to show how fields are connected. Then you can take this plan and use it as your work through the queries when designing a form. Using the Form/Subform Wizard can also make designing the queries and the form easier. In this example, shown in Figure 14.28, you will design a golfing relational database that keeps track of individual scores and rounds. It may sound like a complicated task to create for a weekend activity, but with the wizard, it can take just a few minutes to have a great application in place for your out-of-office recreation.

First, create four tables to help keep track of (1) the different courses (tblCourseID), (2) the different players (tblPlayer), (3) each round played (tblRound), and (4) the score shot on each hole played per round (TblScore). Then, create a query that will bring together all four tables with all the fields from each table using the skills covered earlier in the chapter. The goal is to make tblPlayer the main form; with tblRound and tblScore listed as subforms.

Figure 14.28 Four tables are joined in this query to create the main form and two subforms. (Used with permission from Microsoft)

Now that you have created a query, save and close it. The next step, shown in Figure 14.29, is to select the Form Wizard from the Create tab in Access.

Figure 14.29 The query established the relationships between the tables and the Form Wizard found on the Create tab is used to create the form from the query. (Used with permission from Microsoft)

The following sequence of figures walks through the different options in the wizard. Once you have used the wizard a few times, you will find it easier to plan future forms and subforms for data management (see Figure 14.30, Figure 14.31, Figure 14.32, and Figure 14.33). Remember that we also used the Form Wizard in Understanding and Using Databases.

Figure 14.30 The Form Wizard begins with determining the fields that will be used in the form and from which tables those fields come from. (Used with permission from Microsoft)

Figure 14.31 Rather than creating a form with a subform, you can instead create two separate forms and link them together. (Used with permission from Microsoft)

Figure 14.32 Each subform can be displayed differently on the main form. (Used with permission from Microsoft)

Figure 14.33 Choose Modify to preview the form before using it to collect data. (Used with permission from Microsoft)

After you select Finish in the wizard, the following form is created Figure 14.34. This database, built with four tables, one query, and three forms, will provide a way to keep track of weekend golf performance for years to come.

Figure 14.34 This form was designed as a training aid to demonstrate how the Form Wizard can be used to create subforms. The subform to collect the scores is displayed in a tabular form. (Used with permission from Microsoft)

To review, the Form Wizard created three forms, as shown in the All Access Objects section: Player, Round, and Score. When you open the Player form, it will display all three forms. In this example, Larry is the selected player on the main form—PlayerID #1. The subforms will display information related to only Larry. He is currently playing his first round at Twin Oaks on 5/24/22. We know this because the information displayed within the Round subform is clearly highlighted in blue. Round is set to be a subform of Player. Additionally, Larry has just entered his score on hole #3 of his first round. His score was a 3, and he also marked that the hole was listed as a par 3; Larry shot par!

Score is a subform of Round, so only scores for the selected round will be stored. As Larry continues his round, he can simply add additional rows of data from each hole he plays on the Score subform. These scores will both be saved under RoundID #1 and kept as Larry’s unique scores. If Larry is playing against another player, you would navigate to the correct player on the main form and enter their round/score as well.

Larry can use this simple yet powerful form to keep track of unlimited rounds of golf. In the office, he might apply these steps to developing a way to keep track of a variety of business tasks or projects. For example, each of his employees could have an assigned project with numerous unique details concerning each project. The employee would be the main form, while the project names and project details could be the subforms.

Adjusting a Subform in Design View

Subforms are difficult to change and update within Design View of the main form that they are sitting on. A much easier way to alter a subform is to open that form separately (first making sure the main form has been closed). Using the previous golf example, if you want to add details to the Round subform (for example, add a place for notes on weather or course conditions), it is best to open up the Round form by itself and make the changes. Once the changes have been made, you can save and close the form, then reopen the main form (in this example, Player) to see whether you need to adjust the size. After making any changes, always make sure the subform displays the new information within the main form.