Locked lesson.

Upgrade

About this lesson

Create a form and combo box for easy data entry and storing numbers for foreign keys.

Exercise files

Download this lesson’s related exercise files.

Services_03_Start.accdb
564 KB Services_03_End.accdb
592 KB

Quick reference

Create a Form and Combo Box

Application Terminology

Builder Button

The Builder Button is 3 dots  to the right of a property.  Clicking it launches a wizard to help fill the value for the property.

Change Control Type to a Combo Box

To change the control type to a combo box, right-click on it in Design View and choose Change to > Combo box.

Collapse or expand the Navigation Pane

Collapse or expand the Navigation Pane using the Shutter Button.

Collapse or expand the Ribbon

Double-click a ribbon name to collapse or expand the ribbon.

Column Count

The property specifying how many columns a combo box or listbox will have.

Column Widths

The property specifying individual widths of each column in a combo box or listbox.  Separate column widths with  semi-colon.

Combo Box

A combo box is a control that is a combination of a textbox that allows typing and a listbox showing a list of choices to pick from.  Combo boxes provide a great way to show text for easy data entry while storing numbers behind the scenes for foreign keys.

The main properties to set for a combo box are:

  1. RowSource
  2. Column Count
  3. Column Widths
  4. List Width

Control

An object on a form or report such as a label, a line, a textbox, or a combo box.

Create a new form

To create a new form, select the table you want to base it on in the Navigation Pane.  Click the Create ribbon and choose the form type you desire from the Forms group.  To build a Multiple Items Form, click the More Forms down arrow.

Fieldlist

A list of fields in a table or query.

Foreign Key         

A field (or combination of fields) in a table that relates to a primary key in another table. Foreign Keys are often single fields using a Long Integer data type that relate to an autonumber field in the main table.

List Width

The property specifying how wide a list will be when it drops down for a combo box.  Add the column widths plus 0.2 inches (0.4cm) to allow for a scrollbar.

Multiple Items     

A Multiple Items form shows several records per screen.  Also called a continuous form.

Navigation Pane Shutter Button

The shutter button is an icon in the upper right of the Navigation Pane that you can use to collapse  (<<) or expand  (>>) the Navigation Pane.

Primary Key

A field (or combination of fields) in a table that has a unique value for every record.  Primary Keys are often AutoNumber fields. AutoNumber is a special form of Long Integer that gets its value automatically.

Property

A characteristic of an object such as Name, Size, and Color.

Property Sheet    

The Property Sheet shows detailed information about whatever is selected.  In the design view of forms and reports, the property sheet is organized into categories for Format, Data, Event, Other, and All.

QBE grid

The Query By Example grid allows you to visually choose which fields you want to see, their order, and how they will be sorted and filtered.

Query

A query allows you to choose which fields you want to see from one or more tables and how they will be sorted.

Row Source

The property telling Access where a list will come from for a combo box or listbox.

Setting Properties for a Combo Box

The choices to display in the combo box are set in the Row Source property on the Data tab of the Property Sheet. Click the down arrow to choose a table or query name.  Click the Builder button   (…) for the Query Builder where you can choose fields, sort, and filter.

In the Query Builder, double-click fields from the fieldlist to put them on the grid.  Sort data by the first field that shows.  Click the Datasheet View icon to see the data.  While looking at the data, determine column widths.  When the builder is closed, the Row Source property of the combo box is updated with the SQL statement that defines where data will come from.

For a combo box, also set properties on the Format tab of the Property Sheet: Column Count, Column widths, and List Width.  Press TAB to move to the next property. Use ; (semi-colon) to delimit numbers in the Column Widths property.  The List Width is usually the sum of the column widths + 0.2 inches to allow for a scrollbar.

SQL

SQL is short for Structured Query Language. An SQL statement to show information starts with the word SELECT and tells Access which fields to get and how to sort them.

Subform

A subform is a special use of a form when it is contained on another form.

Views

To switch between Form View, Layout View, Design View, or to Save or Close, right-click on the form title bar.  To maximize the form, double-click on its title bar.

You can also choose a view from the Views command on the HOME ribbon.

Steps

Create New Multiple Items Form

  1. To create a new form, select the table you want to base the form on in the Navigation Pane, and click on the Create ribbon.
  2. In the Forms group, click on the dropdown for More Forms and choose Multiple Items.

Change Text Box Control to Combo Box

  1. Right-click on the textbox control
  2. From the shortcut menu, choose Change To  Combo Box

Change Row Source for Combo Box

  1. Select Combo Box Control
  2. On the Data Tab of the Property Sheet, click Row Source
  3. Click the Builder Button
  4. Make desired changes, Save, and Close

Basic Combo Box Properties

  1. Name
  2. Row Source
  3. Column Count
  4. Column Widths
  5. List Width
Login to download
  • 00:05 This is lesson 3 of Access 2013.
  • 00:09 In the last lesson, you saw how to add records to tables.
  • 00:14 In this lesson, we will create a form for entering data.
  • 00:19 Hi this is Crystal.
  • 00:21 We will make a form to enter data into the Services table.
  • 00:25 The Services table has 2 Foreign Keys --
  • 00:29 one for the service type and one for the time type.
  • 00:33 A Foreign Key is a Primary Key somewhere else.
  • 00:37 This relationships diagram shows how the Services
  • 00:40 and ServTypes tables are related
  • 00:43 using the primary and foreign keys.
  • 00:47 In the service types table, ServTyID is a Primary Key.
  • 00:51 In the services table, ServTyID is a Foreign Key.
  • 00:57 A Service Type of 1 means Lawn Care, which can be
  • 01:00 looked up in the Service Types table
  • 01:04 Once the form is made, we will see how to use a
  • 01:07 combobox to show a list of service types to pick from.
  • 01:12 To create a new form, select the table you want to
  • 01:15 base the form on in the Navigation Pane,
  • 01:19 and click on the Create ribbon.
  • 01:22 In the Forms group, click on the dropdown
  • 01:24 for More forms and choose Multiple Items.
  • 01:28 Very quickly, Access makes a form that can be changed.
  • 01:32 Right-click on the title bar (or tab) of the new form
  • 01:36 and choose Design View from the shortcut menu.
  • 01:40 To make more space for designing the form,
  • 01:42 I will collapse the Navigation Pane by clicking its shutter button.
  • 01:47 If the Property Sheet is not showing,
  • 01:50 click the Property Sheet icon on the Design ribbon.
  • 01:54 The Property Sheet shows detailed information
  • 01:56 about whatever is selected.
  • 01:58 Everything on this form is in what is called a control.
  • 02:03 Controls can be labels such as the title of the form
  • 02:07 and the labels across the top,
  • 02:09 or controls can show data such as
  • 02:12 the controls in the detail section.
  • 02:15 We will talk more about controls in later lessons.
  • 02:19 Currently, the ServTyID is in a textbox control.
  • 02:24 Instead of entering a number for the service type,
  • 02:27 I would like to pick the value from a list.
  • 02:31 Right-click on the textbox control and
  • 02:33 change the control type to a combobox.
  • 02:37 Look at the Property Sheet.
  • 02:39 It is organized into categories.
  • 02:42 Across the top you see tabs for
  • 02:44 Format, Data, Event, Other, and All.
  • 02:49 On the Data tab of the Property Sheet for the new combobox,
  • 02:53 set the RowSource, which is where the list of
  • 02:56 values will come from, to be the ServTypes table.
  • 03:01 Now click the Builder Button, the 3 dots ... off to the right.
  • 03:05 Access asks if you want to invoke the Query Builder and you do.
  • 03:10 Queries provide a way to pick what we want to see
  • 03:13 and choose how it will be sorted.
  • 03:16 The box with the list of fields from the ServTypes table
  • 03:19 is called a fieldlist.
  • 03:21 Double-click on the first 2 fields in the fieldlist
  • 03:24 to put them on the grid.
  • 03:26 The Query By Example grid allows you to visually choose
  • 03:29 which fields you want to see,
  • 03:31 their order, and how they will be sorted and filtered.
  • 03:36 Notice the table has fields I am not picking.
  • 03:40 In the sort cell under the service type, choose Ascending
  • 03:43 so they will be in alphabetical order
  • 03:47 The first icon on the Home ribbon allows you to switch Views.
  • 03:51 To see how the list will look, click on the Datasheet View icon.
  • 03:56 While you are looking at the data,
  • 03:59 decide how wide you want the columns to be
  • 04:01 and count them. There are 2 columns.
  • 04:05 The first column will be hidden so its width will be 0.
  • 04:09 The second column needs to be about 1.5 inches,
  • 04:12 which is about 4 cm.
  • 04:16 Click on the Save icon and then
  • 04:18 Yes to save changes made to the SQL statement.
  • 04:22 SQL means Structured Query Language.
  • 04:26 SQL is what Access stores when you make a query.
  • 04:30 The SQL statement starts with the word SELECT
  • 04:34 and tells Access which fields to get and how to sort them.
  • 04:38 The Format tab is where we specify
  • 04:40 that the Column Count is 2,
  • 04:43 and also how wide the columns will be.
  • 04:46 Separate each of the ColumnWidths
  • 04:48 with a semi-colon so enter 0;1.5
  • 04:54 The ListWidth is the sum of the column widths
  • 04:57 plus 0.2 inches (0.4cm) to allow for a scrollbar
  • 05:01 so the ListWidth will be 1.7 inches.
  • 05:04 Save the form and call it frm_Services_sub.
  • 05:11 Later on, this form will be used as a subform,
  • 05:14 which is why "sub" is being added to the end of the name.
  • 05:19 A subform is a special use of a form where
  • 05:22 it is contained on another form.
  • 05:24 Click the Form View icon on the Home ribbon
  • 05:26 to see what the form looks like.
  • 05:29 Now instead of looking up that Lawn Care is service type ID 1,
  • 05:34 there is a list with easy-to-read text fields.
  • 05:38 Comboboxes provide a great way
  • 05:40 to show text for easy data entry
  • 05:43 while storing numbers behind the scenes for foreign keys.
  • 05:48 Seeing the word Lawn Care in the combobox is
  • 05:51 much easier to understand than numbers.
  • 05:54 Behind the scenes, the number is still being stored;
  • 05:57 it is just hidden from sight.
  • 06:00 A combobox is a combination of a textbox that allows typing
  • 06:04 and a listbox showing a list of choices to pick from.
  • 06:09 Each record in the table is a row on the form.
  • 06:13 A multiple items form shows more than
  • 06:16 one record on the screen at a time.
  • 06:19 We have just scratched the surface of forms.
  • 06:22 In the next lesson, we will explore
  • 06:24 more things we can do with forms.

Lesson notes are only available for subscribers.

Gift this course