ACCESS NOTES UNIT C

  • Forms generally show only one record at a time.
  • It is easier to enter data on a form than on a datasheet.
  • Fields can be added in any arrangement on a form.
  • Understanding the difference between bound,unbound, and calculated controls is essential.
  • CONTROL = an element of a form
  • BOUND CONTROL = e.g., a text box that displays variable data from the underlying field in a Query or Table, e.g. a COMBO BOX(which displays a drop-down list of fixed entries that a data-entry clerk mightchoose from, aswell as allowing the clerk to enter data freely—it is a combination of these two methods of data entry—hence the name COMBOBOX). Mnemonic: Think of the D in bound as indicating that data will fill the control item.
  • UNBOUND CONTROL = displays constant items: labels, art work, etc. it doesn't have a source of data in a field. Mnemonic: Think of the prefix UN (which usually negates something) as meaning NO data. Labels are the most common type of unbound control.
  • CALCULATED CONTROL = has as its value the result of evaluating an expression (Access’s term for formula) rather than a field with given data. A user specifies the desired value in the control by defining a (mathematical or even a text) expression. An expression is a combination of

=

operators (such as = and + ),

control-names,

field-names,

functions that return a single value,

and/or constant values. For example, the following expression calculates the price of an item with a 25 percent discount by multiplying the value in the Unit Price—use brackets around the field-name-- field by a constant value (0.75).

= ([UnitPrice] * 0.75)

  • FUNCTION EXAMPLE SHOWING USE OF PARENTHESES: (parentheses are used after a FUNCTION NAME, like SUM, LEFT, etc. and surround everything after the function name).
  • LEFT is a function that extracts the leftmost characters from a word or phrase. The numeral tells how many letters are to be extracted.
  • =Left([ProductName], 3)

Here, the Left function is used to extract and display the first 3 characters of the value of a field or control called ProductName. So if the word CADILLAC were in the field, ProductName, then CAD would be displayed in the query results.
  • “Calculations” can actually be made on text. For example, a common text expression is “adding”a first name to a last name plus a space separator. The formula for that would look similar to the following: =[FirstName] & “ ” & [LastName]. When concatenating pieces of text into one expression, the & (ampersand) is used to concatenate or “add” thepieces of text together. Here we conjoined THREE pieces of text to produce one value.
  • When controls overlap in Form View, they are hard to read and do not present data clearly. Avoid overlapping controls! Separate the field boxes.

Quick Quiz:

  1. T/F QUIZ

Determine which of the following expressions are valid. If valid put T; if not, put F and explain why.

=Cost+Tax F – field names should be surrounded by [square brackets]

[Cost]+ [Tax] F – the expression doesn’t start with an equal sign

= [Cost]+(Tax) F – Do not use (parentheses) around field names

=[Cost]+[Tax] T

  1. Write an expression that calculates the tour cost per day given the presence of a field named Cost and one named Duration.

Answer: = ([Cost]/[Duration])

  1. Write an expression that calculates text items, namely the concatenation (conjoining) of the value in field StreetNumber with the value in field StreetName with a (textual) space between them. Notice how this is handled.

Answer: = [StreetNumber] & “ ” & [StreetName] & “Ave.”

  • SPLIT FORMS

A split form gives you two views of your data at the same time displayed in one screen view— a Form is displayed on the top half of the screen and a Datasheet view (with several records) in the bottom half. The two views depend on the same records and are coordinated with each othercontinuously. Altering a field in the top part of the screen alters the same field in the bottom part of the screen (and vice versa). You can add, edit, or delete data from either half of the screen (provided the record source is changeable and you have not protected the data to prevent change).

  • GRAPHIC IMAGES

Inserting a graphic image: graphic images can be as simple as a line to as complex as a photo. Inserting an image in the form header means that it will print only once on the printout. Inserting it in the Detail section means that it will print for each record.