Access Advanced Training Course

Forms can be built with Access based on multiple tables provided there are links between the tables.  These forms can be useful for displaying data from different sources in one place.  They can also help with data entry by automatically populating fields from the related tables.

To show a simple example create a query in the Advanced_form database based on the 3 tables; Products, Retailer and Transaction as shown below.  Access automatically adds join links between the tables.

Add the fields in the order shown above then save the query as qryTransaction_Details.  Close the query then while selecting qryTransaction_Details from the Navigation pane click Create, Form. 

Now add a new record, enter today’s date for the transaction_date and P003 for the Product_ID.

After pressing enter or tab the fields Productname, Unit_Price and Qty_available automatically fill in. Enter a Qty_sold and a Retailer_Code (eg R004) and the Retailer_name and Phone_no are automatically filled in as well.

Save the form as frmTransaction_Details.   

Another way to automate data entry is to create calculated fields with built in functions. For example, when entering an employee date of birth, a calculated field can be created to work out the employee’s age.

To insert a calculated field in a form (for example, the frmEmployee form in the Orderingdb database):

1.     In form design view, select the Ab textbox tool.

2.     Place the control next to the DateOfBirth field.

3.     Type the following expression

= INT((Date()-[DateOfBirth])/365.25)

The calculated field works out the number of years between today’s date and the employee date of birth. The INT function removes the decimal part resulting in an integer value for the age. (Preventing rounding up).

Change the caption of the calculated field to Age.

Another example of a calculated field is the Total of Order in the subform frmOrdersub

Use the Ab tool again to enter a calculated field into the form footer:

=Sum([TotalCost])

Forms that hold a large amount of information can be designed for easier use by inserting tab pages. For example, the form below contains a tab for General details and a tab for order details.

More tabs can be added, reordered or deleted if required.

To create this form start by selecting tblOrder (in the Orderingdb database) and choose Create, Form Design

This creates a blank form in design view based on tblOrder.

Now select the Tab Control and draw a page filling the Detail section.

To add further pages, right click and choose Insert Page.

Select Page Order to change the sequence of tabs and Delete Page to remove a page.

To rename a page right click the default name Page 1 and choose Properties.

Change the first page name to General and the second to Order details.

To add fields to a tab page, click the option Add Existing Fields. Choose the table containing the fields then drag the first field to the page.  Use the Shift key to add a further group of fields.

In this example add all the fields from tblOrder.

 Select Design, Form View to display the form.

As the OrderID field is automatically updated it does not need to be selected. Change its field property to:

Locked          Yes

Enabled        No

Border style   Transparent

Also in design view change the CustomerID and EmployeeID to combobox fields.

As well as having tabbed forms it is possible to display a form within a form.  This is useful in a relational database where there are one to many relationships. The main form displays the primary record (one) and an embedded subform displays the related records (many).

The subform can be either a columnar, tabular form or a datasheet.

The following Retailer details form contains a subform showing the transaction details for that retailer.

Click next retailer record to view a different set of transactions.

To insert a subform:

1.     First create and save the subform.

2.     From design view of the main form for frmOrder select the second tab page Order Detail.

3.     Click the Subform/Subreport control and draw out the shape for the subform.

4.     Select the subform, frmOrderSub.

5.     Choose the option to link the main form with the subform based on OrderID then click Finish.

 View the form and select the Order Details tab to see the subform data.

Note that the ProductId field has been changed to a combo box to display the Product Name field.