CIS 217, Spring 2001

1
Forms

• Uses of forms
• data entry screens to add, edit, delete records
• Bound to a query, table or SQL statement
• Best to base the form on a saved query
• Application flow such as switchboard forms
• Unbound forms which contain command buttons to
  open and close other database objects such as
  reports and forms
• Custom dialog boxes to obtain information from
  the user
• Unbound forms which can gather information then
  perform actions based on the values such
  passing parameters to a query

2
2/15/01 Class Outline

• Go through string demo with NormalRestaurant
  Answer File.mdb
• Class demo
• Write queries to get the data from CourseSchedule
  table to the 4 new tables in the file
  NormalCourses.mdb
• Review Last Weeks homework through question 4
• Last weeks notes
• This weeks notes

3
SubQueries

• An SQL SELECT statement inside another select
  query or action query
• You can enter these statements in the Field row
  of the query design grid to define a new field,
  or in the Criteria row to define criteria for a
  field
• You can use subqueries to
• Test for the existence of some result from the
  subquery (using the EXISTS or NOT EXISTS reserved
  words).
• Find any values in the main query that are equal
  to, greater than, or less than values returned by
  the subquery (using the ANY, IN, or ALL reserved
  words).
• Create subqueries within subqueries (nested
  subqueries)

4
SubQueries

• If you're using a subquery to define a Field,
  type a SELECT statement enclosed in parentheses
  in a Field cell
• After you leave the cell, Microsoft Access
  automatically inserts "Expr1", "Expr2", and so
  on, before the SELECT statement
• The subquery must return just one field for this
  to work without using the Exists keyword
• See qrySubQueryAsField in Normal Restaurant
  Answer File.mdb

5
SubQueries

• If you're using a subquery to define a criteria,
  type a SELECT statement enclosed in parentheses
  in a criteria cell
• The subquery should return an value appropriate
  for the field being tested
• If the subquery returns more than one record use
  the IN key word
• If the subquery returns one record you can just
  put the subquery in parenthesis with no keyword

6
SubQueries
Examples of expressions that use the result of
subqueries as criteria
7
Anatomy of a Form

• Header/Footer
• Content remains the same for all records
• Detail
• Content changes for each record displayed
• Page Header/Footer
• Only displays when the form is printed
• Properties of a form are set in design view
• Record source, editing properties, sorting and
  filtering, etc

8
Creating and Editing Forms

• Select New from the Form tab and use the wizard
  or design view
• Display the field list and drag and drop controls
  bound to particular fields
• Formats, validation rules, default values and
  lookups defined in a table at the time a control
  is dragged and dropped from field list will
  automatically be set in the form

9
Creating and Editing Forms

• Selecting and editing multiple form objects with
  the mouse
• Shiftclick to select multiple objects
• Click a control to obtain sizing handles moving
  things around
• Click in the ruler bar to select every object on
  a horizontal line
• Aligning objects to one another using FormatAlign

10
Creating and Editing Forms

• Tab order determined by order in which add
  controls to the form
• use View Tab Order or objects property sheet
  to revise
• set tab order for each sections objects
  separately, including multiple tabs
• Can create multiple page form using the Tab
  controls or by inserting a page break control
• Select Tab or Page Break control from the tool
  box and put different fields on each tab/page
• With page breaks you need to add your own buttons
  to jump from page to page

11
Important Properties of Forms

• RecordSource property
• Determines what record set is displayed, if any,
  in the form
• Can be a table, query or straight SQL statement
• Best performance when it is a saved query
• Change by viewing the forms properties then
  simply selecting a query, table or hit the build
  button to make an SQL statement
• If the record source property is set to nothing,
  the form is called an Unbound form and is
  probably used to control the flow of the
  database, pass parameters to queries or reports,
  or for gathering information from the user

12
Important Properties of Forms

• DefaultView property
• Determines the default view of the form when it
  is opened
• Single, DataSheet, Continuous
• ViewsAllowed property
• Can limit which views the form can have to
  prevent users from changing it
• ScrollBars
• Which, if any, scroll bars will appear on the
  form
• Vertical, Horizontal, None, Both
• If you do not display scroll bars but your form
  exceeds the width of a screen your user will not
  be able to easily move around the form

13
Properties of Forms

• RecordSelectors property
• Indicates if there will be a large bar on the
  left side of the form to indicate that the record
  is selected
• Useful for allowing multiple selected records in
  a datasheet view and for showing the user that
  the whole form is one record
• NavigationButtons property
• Will the form display the standard Access
  navigation buttons for adding a new record,
  moving forward, back, to the top and to the
  bottom of the record set and the record counters
  will be displayed

14
Controls for Forms or Reports

• Both forms and reports can be linked to the
  fields of a table or query with objects called
  controls
• ControlSource property indicates what field (if
  any) the control is bound to on the underlying
  query
• If ControlSource is blank, it is an Unbound
  control and the field is just a temporary
  variable of sorts for the user to enter
  information which can be used in code
• Changes to a bound control will make changes to
  the underlying table
• Changes to an unbound control will not change any
  data in the database

15
Controls for Forms or Reports

• It is important to understand what the different
  types of controls are called and when you should
  use them
• Data types have default controls that the wizard
  uses or that are created when you drag and drop
  the field from a field list
• Depending on how you put the control on the form
  different default values for all the properties
  will be set for different control types

16
Name Property

• Name property of a control indicates the name of
  the control which can be referenced in code
• Often the same as the ControlSource of the
  control but it does not have to be and is in no
  way related
• Controls placed on a form with the tool box are
  named sequentially based on their type
• Cmb1 is combo box 1, the next one put on the form
  would be Cmb2etc.
• You should change the name of the controls to be
  meaningful so that your code becomes self
  documenting
• Events use the NAME of the control when naming
  the sub procedure for the event

17
Controls for Forms or Reports

• All but one of the control types we are going to
  discuss can be placed on both Forms and Reports
  in the same fashion
• You can copy and paste controls from a form to a
  report in design view
• Why would you want to do this?
• Reports allow for easier printing and more
  sophisticated sorting a grouping
• You often create a form for a series of fields
  before you create reports

18
Tool Box

• Select objects
• Change from add control mode to select a control
  mode
• Control Wizard
• Change from automatically prompting the control
  wizard to never prompting the control wizard
• Label
• Not bound to data
• For headers, footers, etc

19
Tool Box

• Text Box
• Bound to data
• Use for dates, text fields, number fields, nearly
  all fields
• Use with built in Access functions Page and
  Date() for inserting page numbers and the current
  date in your report
• When a text box is generating Access
  automatically creates a linked label which can
  then be edited to indicate what is in the Text
  box control
• The actual text box is bound to data, the label
  is static and must be filled in by the designer
• Default control for most types of fields

20
Tool Box

• Option Group
• Allows the user to select one and only one option
  from a set of choices
• A label is displayed but a numeric value is
  actually saved in the field
• The option group is associated with one field but
  contains multiple option controls such as radio
  buttons, toggle buttons or check boxes
• Use the wizard to create and chose the style of
  buttons to display

21
Tool Box

• Toggle Button, Radio Button and Check Box
• Allows the user to select either on or off
• For Yes/No fields
• Access automatically creates a label linked with
  the actual control
• Can display a question in the label
• By clicking the option you are placing a value of
  yes, or true, in the bound field
• Do not get these controls confused with creating
  an option group
• Use the Option Group wizard to link multiple
  radio buttons to the same field

22
Tool Box

• Combo Box
• Like a look up wizard
• It displays text but can save either the text or
  a related number such as a foreign key
• Can be linked to a foreign key of a many table to
  display the descriptive field of the
  relationships such as customer name
• Can only have ONE value at a time
• Values usually must be from the list of options
• Default control for fields defined with a lookup
  wizard in the table
• You can use the control wizard to add such a box
  if a look up is not defined

23
Tool Box

• List Box
• Like a combo box in that it can display text and
  save numbers
• Different than a combo box because it displays
  multiple rows at a time
• Can also be changed to allow for multiple
  selections
• This is advanced and require some coding to
  retrieve the selections made by the user
• Can also be used as a lookup wizard
• Can easily change a combo box to a list box by
  right clicking the combo box
• Use the control wizard to add to create

24
Tool Box

• Image
• Use for adding bit map or other images to your
  forms and reports
• Follow the wizard to select the type of file
• Once the image is in your report it is not linked
  with the original fiel and can not be edited
• Unbound Image
• Similar to an image but maintains the reference
  to the original file
• If you double click the object you can edit it in
  the applicable software
• The Unbound refers to it not being linked to an
  actual field in your database

25
Tool Box

• Bound Image
• Control used to display an OLE object type field
• Bound refers to it being bound to the data in the
  field
• See the Northwind database for an example
• Only relevant if you have OLE field(s) in your
  database

26
Tool Box

• Page Break
• Use to create a hard page break in a form or
  report
• Appears in design view as
• Tab Controls
• Create more elegant multiple page forms
• Not applicable for reports
• Add the tab control then drag and drop your
  controls to the tab

27
Tool Box

• Sub Form/Report
• Use to show related many records while viewing
  the one records
• You can create the actual sub form first and use
  the wizard to create the link for you, or
• The wizard will actually create a new sub form
  for you

28
Tool Box

• Line
• Add a line or arrow to your form or report
• Not linked to any data
• Use to make the form look more organized
• If it is placed in the detail section it will
  repeat with each record over and over
• Rectangle
• Add a rectangle to your form or repor
• Not linked to any data
• Use to make the form look more organized

29
Tool Box

• More Controls/Active-X Controls
• Additional standard windows controls which can be
  added to your form
• Can be used to make your database application
  look more professional
• Many Active-X controls have been written by third
  parties and could be used in Access
• We will not cover them because they can get very
  technical