Access07: Hire Co : HR - Forms and sub-forms

Create Staff x Timesheet form
1. Create > More Forms > Form Wizard

2. Table: tblStaff, use [>>] to select all fields

3. Table: tblTSheet, use [>>] to select all fields

4. [Next]

5. Notice that the data view is outer form tblStaff, inner form Timesheet. Select [Next]

6. Leave subform as a datasheet, [Next]

7. Choose any style (for the example I used Windows Vista), [Next]

8. Titles for forms:


 * Form: frmStaffTS
 * SubForm : sfrStaffTS

9. Use your forms design skills to change the form as follows



Woops..All the fields in the subform are not showing .. so drag the column heading margins until all the fields show.

Summing fields in a subform
Now we would like to add up the hours in the sub form so that they can be transferred to the main (staff form)

1. In Design view, enlarge the subform area to show the Form Footer.

2. From the bottom line of the form footer drag the mouse and a footer area should apppear

3. In this area drag a text box.

4. In the text box enter =SUM([TsHrsWorked]) Note the field name must be the same as the one you created for hours worked in the subform!

5. Right click on the text box and change the Name to txtTotalHrsWorked

Your display should look like ..



Now we need to add the text box to show the total hours worked

6. Now save the form (this is important or the text box you created in the subform will not appear)

7. Add a text box above the sub form

8. Right click and choose Build Event

9. Choose Expression builder

10. Enter an = sign into the box, then navigate Forms > Loaded Forms > frmStaffTS > sftStaffTS

11. In the center column you should see txtTotalHrsWorked, double click this and it should put the field name into the expression builder paste area.



12. Highlight the expression created and [Ctrl]+[C] (copy), then [OK]

13. Now back at the form design paste the expression into the text box, using [Ctrl]+[V]

.. your form should look like this ..



14. And when you change to form view display the subform should total on the main form, as:



Adding tabs
When forms contain too much information it can be useful to divide it up into tabbed forms. In this case the staff details can be on one tab while the timesheet information can be on another

In design view ...

1. Move the subform down to make room for the tab area

2. Click on the tab button, and drag a tab area below the Total Hours Worked text box



3. Now click on the sub form and use cut (ctrl + X) to remove from the main form

4. Now click on the second tab and Paste the subform into it (Crtl + V). You may want to remove the subform label at this point.

5. Double click on the tab and rename it (use Name property) to Timesheet

6. Drag the bottom of the Detail part of the form up to reduce the size of the form.



7. Now select the fields Address, TownCity, Phone and start date. (Use the shift button and click on each one).

8. Now cut these out (Ctrl + X)

9. Click on the front tab

10. Then Paste (Ctrl+V) to the tab area.

11. Change the Tab to Information.

Change to display view and your form should now be able to display tabs