Week 5: July 28-August 1

TUES   THURS   TO DO

WEEK'S OBJECTIVE

MS Access 2013 Forms. Book: Lesson 4. On-Your-Own Tasks: Forms 1, Forms 2.

MIC ASSIGN 5, due Sunday August 3.

Tuesday

GAMEPLAN

  • Forms
  • Form Wizards
  • Form Layout
  • Form Tools

DISCUSSION

Forms

Forms make it easier to enter, display, and print information from Access databases. While you can add information to tables directly, complex databases with several users can experience more redundancies and incoorect information if table input is used.

  • A form can be a friendly interface for entering information into a table.
  • You can display and edit the records of the underlying table, or create new records.
  • A form speeds the use of the database, because people don't have to search for what they need but can pull up table records in a form.
  • Forms let you select, from the many fields on a table, just those that you want the users to focus on when adding or editing data.
  • NOTE: You will need to add the joined KEY fields (Prinary, Foreign) from any tables you use for the form to be useable. Ex. CategoryID if it is the Primary key in a Categories table and a foreign key in another table.
  • Forms make it possible to NOT to expose all the data to in a table to everyone who works with it.
  • Forms can display messages about a database, like instructions for using the database, or assistance for troubleshooting any problems.
  • Forms can even act as a login tool for working on the database, based on a table of approved database "editors".
Form Views
  • Form View: How the living form looks and acts, and where you can actually use it to find and enter data. The form design is not editable from here.
  • Layout View: The layout of the form, where the design/layout is minimally editable.
  • Design View: The design of the form, where the layout of everything is editable.
  • Properties Sheet: This panel is available in both Layout and Design view, and is the primary place where you make Control behavior adjustments to controls in a form.
Form Creation: Wizards

Access has basic Form Wizards for you to create simple forms. You can then use the Design View to edit the form's appearance and to add additional items, like controls.

  • All-Fields Form: You can easily create an All-Fields Stacked form by simply selecting a table or query in your Navigation Pane, then clicking CREATE/Form. A simple form will simply open, and be saved, with the same name as the table or query you selected.
  • Single Table Form: If you want a simple form but want to choose only some of the columns of a table, Choose CREATE/Form Wizard, which will let you create a basic form using Wizard questions and column choices.
  • Multiple Table Form: Choose Create/More Forms/Wizard, which will create a basic form based on more than one existing table based on your wizard choices.
    • MUST be tables with an existing relationship to each other.
  • Forms in the Forms Wizard can be formatted with 4 different views: Columnar, Tabular, Datasheet, and Justified.
  • Access also offers other form types, like Navigation - which actually lets you embed several tables on one sheet, NOT create a link navigation.
  • You can use the List Form to create a list of linked tables, queries, forms, and reports for folks to click on - like an opening navigation screen.
Form Editing: Design View

You might create a simple form that has all the columns you want in it, but you also might want to change the spacing, font colors, and other things. You do this with the Form Design View.

  • You can select inside a Control item in Design View and edit it directly, such as selecting a label and changing the font color or type from the Home Ribbon.
  • You can select a Control item and change its size, and position.
  • You can see the forms header and footer regions. You might or might not use the headers and footers, but these would be more for display than acting-upon.
Form Properties Sheet

This panel is available in both Layout and Design view, and is the primary place where you make Control behavior adjustments to controls in a form.

  • You can edit the behavior and criteria of Controls in forms and subforms here.
  • The properties you set or change using the Form Properties Sheet only affects the form, NOT the properties of the bound table.
  • You can make format changes, such as borders, background colors, etc. in the Format tab.
  • You can add specific data formatting and validation rules in the Data tab. This can help ensure that all phone numbers are auitomatically converted to the format you want to appear in the table - assuming the table itself has this accounted for in the data type.
  • The Event tab (advanced) lets you add actions and special code to create behaviors of controls.

Joins

We did not discuss Joins last week when doing basic queries, but we might see issues with these when trying to create forms to enter data into (when building a form to allow entering data for input into 2 or more tables.)

When you include multiple data sources in a query, you use joins to limit the records that you want to see, based on how the data sources are related to each other. You also use joins to combine records from both data sources, so that each pair of records from the sources becomes one record in the query results. By default, a join is automatically created if there is already a relationship between two data sources that you use in a query. A join is also created if there are fields that clearly correspond to each other.

Join Types

  • Inner Joins: If you want to show only those rows that have matching values in the joined field, you use an inner join. Access creates inner joins automatically. When a query with an inner join is run, only those rows where a common value exists in both of the joined tables will be included in the query results.
  • Outer joins: These show all rows from one table, and only the corresponding rows that share a common value on both sides of the join from the other table.

Join Creation/Editing

Joins can be created / edited in the Relationships window, when you create a new relationship or edit an existing one. Steps:

  • Make sure the related tables, queries, forms, and reports are closed.
  • Go to Relationships window, and view the needed tables and their relationship.
  • Right-click on the relationship line to edit.
  • Click on the Join Type button in the pop-up wizard.

SubDatasheets

We also have not discussed Subdatasheets. When two tables have one or more fields in common, you can embed the datasheet from one table in another. An embedded datasheet, which is called a subdatasheet, is useful when you want to view and edit related orjoined  data in a table or query. If an expand indicator (+ sign) is present, then the table, query, or form has a subdatasheet.

For instance, if you have a table with a one-to-many relationship with another table, you can view and edit data such as the products included in a specific row by opening the subdatasheet for that related table. However, use them for Viewing only, and use Forms for editing.

Microsoft Office Access automatically creates a subdatasheet when you create a table that matches one of the following criteria:

  • The table is in a one-to-one relationship.
  • The table is the "one" side of a one-to-many relationship, where the table's SubdatasheetName property is set to Auto.
  • You can also create subdatasheets yourself: see Add a Subdatasheet.

DEMO

We'll do some simple form creation and editing.
  • Open the GardenCompanyForms.accdb database.
  • We'll create an All-Field stacked form: Products Table, Create/Form
    • Look at Form view, see how form works for Entering Info
    • Look at Layout view, Layout tabs
    • Layout View, modifying: Design/Theme, Arrange, Format contents
  • We'll create an All-Field stacked form: Customers Table, Create/Form
    • Look at how both the Customers table AND the Orders subdatasheet table are BOTH created in the form.
  • We'll use the Form Wizard to create a basic one-table form with selected columns.
    • Shippers Table, Create/Form Wizard, choose different formats, open to view
  • We'll use the Form Wizard to create a basic one-table form in Datasheet view.
    • Suppliers Table, Create/More Forms/Datasheet
    • Rename form to Suppliers Input Form.
  • We'll use the Form Wizard to create a basic two-table form with selected columns, much the way we do queries. (Products) ProductID, ProductName, LatinName, CategoryID (necessary "Join Key") and (Categories) CategoryName, Description.
    • View by Product, Tabular, and name Products/Categories Input Form.
    • Layout View, sort by Product ID
    • Form View, Try to add a new row.
    • Watch how adding a new category, Misc, affects the form when you finish typing the new row. Also notice what happens with the Product ID
    • Open and look at Products and Categories tables to see what happened.
BACK TO TOP

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Thursday

GAMEPLAN

  • Forms Design
  • Controls
  • Subforms

DISCUSSION

Forms Design View

This is the view in which you can edit the design of a form you create. You can also create forms from scratch with this view. You can:

  • Use rulers and a grid to help with positioning.
  • Add/edit Header and Footer items here.
  • Adjust the size of sections.
  • Apply a theme.
  • Change the size of controls.
  • Arrange controls logically to make data entry easier.
  • Adjust the properties of form elements in the Property Sheet.
  • Add fields from the Field List.
  • Add controls to limit data entry choices or add functionality to a form.
  • Set the tab order of the form fields for easier usage.
Controls

You use controls in a form to enter new information, to edit or remove existing information, or to locate information.

  • Controls are objects on a form, report, or data access page that display data, perform actions, or are used for decoration. 
  • Some controls are Bound: linked to a field in a table or the datasheet created by a query.
  • Some controls are Unbound, or not linked to any specific table/query and its data.
  • Text box controls: You can view or enter information in these controls. You use text boxes on a form, report, or data access page to display data from a record source. This type of text box is called a bound text box because it's bound to data in a field.
  • Label controls: These are heading labels, which tell you the type of information you are looking at in the corresponding text box control. Not actually bound to the data it is labeling from the table - you can delete the label if you wish.
  • List Boxes: A list box can give a user a limited/specific list of choices for adding data in a form, such as a list of States, Product names, etc. Bound
  • Combo Box: A combo box is a drop-down list that you can use in your form in place of a field. Bound
  • Option Buttons: An option button on a form or report is a stand-alone control used to display a Yes/No value from an underlying record source. Unbound
  • Images: You can embed an image on your form, like a logo. Unbound
  • Tabs and Page Breaks: Unbound
  • There are others, like frames, checkboxes, radio buttons, etc., you can research on your own.
Subforms

A Subform is a form that is contained within another form. You would use this to:

  • View data from multiple tables or queries on the same form (that is being used for input).
  • Display data from multiple tables where there is a one-to-many relationship, like if you wanted to display an order with the order details.
  • If there is already only one one-to-many relationship between the tables already defined on the Relationships page, the fastest way to create the form and its subform is by using the Form tool.
  • To create a main form and subform that include only some of the fields in their underlying tables, use the Form wizard, which offers a "Form With Subform(s)" option.
Form Used as Navigation

You don’t want your users navigating your database on their own, where they might add or delete incorrect or sensitive information. Instead, when they open the database, you might want them to access the data, reports, and queries through controls on an opening form — NOT the navigation panel:

  • Office button/Access Options.
  • Select Current Database from the left pane.
  • Under Applications Options, click the Display Form drop-down list and select the form you want to display when the database is opened.
  • Under Navigation, clear the Display Navigation Pane check box and click OK.
  • For entering new data, when form opens, choose New Record at bottom of form to add a new form page.

DEMO

We'll do some simple form editing in Design View. Use GardenCompanyForms2.accdb, ClayPots.jpg
  • First, we'll create an All-Field stacked form from a QUERY: Products and Categories Query, Create/Form
    • Look at Form view, see how form works for Entering Info
    • NOTE the problems entering data in the order the Tabs are set - the Category from the Products part of the query/form comes before the Category Name from the Categories part of the query/from - cannot enter NEW info here
    • We'll use Design View to move the Category ID, CategoryName, Description, and reset the Tab Order.
    • Design View: Also, let's lock the Discontinued field so that data entry cannot change it.
  • Next, for a NEW Form: Choose the Suppliers table. Then use the FormsWizard to make a Colunmar form of all the Suppliers information EXCEPT the Phone Number and Fax Number.
  • Layout View:
    • The Label is the "Placeholder" which identifies the field in which the actual data will be viewed and/or input. It is based on the Column name in the original table.
    • Let's adjust the width and height of a field.
    • Let's modify how a label looks - color, font, etc.
    • Let's add an Input Mask on one of the phone number fields.
    • Let's check the Tab Indexes to see where fields fall when tabbed on in Form View.
    • Use Layout View, Design Tab to Add Existing Fields - Phone Number.
  • Design View
    • Look at the Design area - the Header, Detail (body), and Footer.
    • Modify the Header area. Adjust the height of the Title Label, and also edit what is in it. Then insert a Logo. These are Unbound.
    • Use Design View, Design Tab to Add Existing Fields - Fax Number. Move and resize the fields so they match layout
    • Next, drag the PhoneNumber and FaxNumber Data fields further right than the other fields. Then select them and PostalCode and use Align Left to align them with PostalCode.
    • Adjust the width of specific data fields individually.
    • Adjust the Tab order.
    • In the Footer (we may have to expand this), add Date/Time, then move the position, set to left-align, and set to italic.
    • In the Body/Detail section, select all of the Controls, and change the background and line color. Also, in the Arrange tab, choose Size/Space, Arrange to Fit. Notice how they all remain anchored to the top left of each.
    • In the Body/Detail section, add a combo box next to the current City data field - select Design/Combo Box, then drag to draw. A wizard will open.
      • Choose Get Values from Table/Query, then choose the Suppliers table from the list,
      • Next, then choose the City field, Next, Sort Order City Ascending, Next, Hide Key Column, Remember the Value, Name the label City1, and Finish.
      • Note how this looks compared to the original City - you can choose but not ADD new cities.
      • NOW, Right-click the "Unbound" Input box, choose Properties, then choose Data/Control Source/City - this "binds" this to the Suppliers table. In Properties, see that the Row Source Type property is Table/Query. If so, and if the Row Source box is empty, type in the Row Source box: SELECT [Suppliers].[SupplierID], [Suppliers].[City] FROM Suppliers ORDER BY [City];
      • THEN, at Limit to List, choose No, and see the error. Fix the Format Column Width'd ro read the same number, then go back and try Limit at List again, and you will be able to set No. Then you can add new cities.

Let's do a quick and dirty Sub-form.

  • First, make sure that Products and Order Details have a 1-to-many relationship.
  • Form Wizard, Table: Orders - choose OrderID, OrderDate; Table: Products - choose ProductName, UnitPrice. Then choose By Description to get Form/Subform option, choose Tabular, name the two forms, finish.
  • Now let's do the same thing but using the Form Design.
    • First, use the Form Wizard, Orders - choose OrderID, OrderDate, Columnar. Name the table Orders Design Form, and choose Modify Design.
    • Go into Design View, and make the Form Detail area wider and taller (more space).
    • In the Design tab, find/click Use Control Wizard so it is orange. Then choose Subform/Subreport button, and draw/drag.
    • In the Subform Wizard, choose Use Existing Table/Query and click Next, then chooseTable: Products - choose ProductName, UnitPrice.
    • Choose Define my Own, Subform/Subreport fields, Product ID, Finish.
    • Look at the form in Form View, to see how it looks. Similar to the wizard, huh?

Let's try a simple Form for Form Links

  • Create/Form/Blank Form
  • You will get a Blank form background in Layout View. Save as Database Navigation.
  • In Form Layout Tools/Design, choose Hyperlink. Then click on the blank form space to initiate the hyperlink.
  • The Hyperlink will open a dialog box, asking what you want to link to. Choose "Object in this Database".
  • Select Shippers Input Form, Okay.
  • Choose Hyperlink again, and follow the same process, but select Suppliers Input Form this time.
  • View the form to see what you get. Looks like you could use a title, maybe an image, etc.
  • Go into Design view to add these things. A Title would go in the header. An image might go in the Detail/body area.
  • Play with colors, fonts, bold, etc.
  • View in Form View to try out the hyperlinks.
BACK TO TOP

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

TO DO

Resources
BACK TO TOP