Advanced Module – Databases (Access)

Lesson 6 – Advanced Forms and Controls

This lesson explains how to build more advanced forms in Access, including subforms, combo boxes, calculated controls, command buttons and navigation forms. These tools help users enter, view and manage data efficiently.

1. Creating a form from a table or query

The quickest way to build a simple bound form is to base it on a table or query.

1. In the Navigation Pane, click once on a table or query (e.g. Customers).
2. On the ribbon, select Create > Form.
3. Access generates a basic form showing all fields.
4. Use Layout View for quick visual changes.
5. Use Design View for advanced modifications, such as control properties, formatting and adding new controls.

2. Controls on a form

Forms are built from controls. Controls may be bound (linked to a field) or unbound (standalone).

• Text box – displays or accepts text, numbers or dates (bound/unbound).
• Label – displays fixed text such as headings.
• Combo box – provides a dropdown list of values, often from another table.
• Check box – used for Yes/No or Boolean fields.
• Command button – performs built-in actions (navigate, open forms, run commands).

3. Creating a combo box (lookup list)

Combo boxes allow users to select values from a list instead of typing them manually.

Example: Choose a Customer from a dropdown instead of typing a CustomerID.

1. Open the form in Design View.
2. Select Combo Box from the Design tab.
3. Click on the form to place the control.
4. In the Combo Box Wizard, select “I want the combo box to get the values from another table or query.”
5. Select the table containing lookup values (e.g. Customers).
6. Choose fields to display, such as CustomerID and FullName.
7. Sort the list (e.g. by FullName).
8. Hide the key field (CustomerID) if you want to display names only.
9. Choose to store the key field (CustomerID) in the bound field of the underlying table.
10. Finish and save the form.

4. Creating a form with a subform (master–detail)

Subforms display related child records. This is essential for one-to-many relationships (e.g. Customers → Orders).

1. On the ribbon, select Create > Form Wizard.
2. Choose the main table (e.g. Customers) and select fields.
3. Choose the related table (e.g. Orders) and select fields.
4. Access detects the relationship and offers Form with Subform.
5. Select subform layout: Datasheet or Tabular.
6. Finish and open the form.
7. Navigate through customers and confirm that each customer’s orders appear in the subform.

5. Adding a calculated control to a form

Calculated controls display results based on other fields without storing the result in a table.

Example: Display a Full Name from FirstName and LastName.

1. Open the form in Design View.
2. Insert a Text Box control.
3. Select the text box → open the Property Sheet.
4. Set Control Source to:
   = [FirstName] & ” ” & [LastName]
5. Edit the label to “Full Name”.
6. Switch to Form View to test the calculation.

6. Adding command buttons

Command buttons automate actions such as opening forms, navigating records or printing.

1. Open the form in Design View.
2. Select Button on the Design tab.
3. Click to place the button.
4. In the Wizard, choose an action category (e.g. Record Operations or Form Operations).
5. Select an action such as Go To Next Record, Close Form, or Open Form.
6. Choose text or icon display.
7. Finish and save the form.

7. Navigation forms

Navigation forms provide a user-friendly menu for opening forms and reports.

1. On the ribbon, click Create.
2. Select Navigation and choose a layout (e.g. Horizontal Tabs).
3. The navigation form opens in Layout View.
4. Drag forms or reports from the Navigation Pane into tab areas.
5. Rename tabs (e.g. Customers, Orders).
6. Save as frmMainMenu or another clear name.

8. Practical Activity

• Create a form for a main table (e.g. Customers) using the Form command.
• Create a form based on a related table (e.g. Orders).
• Add a combo box to the Orders form that selects a Customer from a dropdown list.
• Use Form Wizard to build a Customer form with an Orders subform.
• Add a calculated text box showing full name (FirstName + LastName).
• Add a command button that closes the form.
• Create a navigation form that links to your main forms and reports.