CSE1IS Information Systems Week 8 Lecture 2 Microsoft Access Forms

1. Available Controls:

Standard Controls:

• Label
• Text Box - may be formatted as single or multi-line
• Option Group
• Toggle Button
• Radio Button (placed inside an Option Group)
• Check Box
• Combo Box
• List Box
• Command Button
• Image
• OLE object (embedding another application)
• Page Break
• Tab Control
• Subform
• Line
• Rectangle

Advanced Controls that may require installation:

• Open/Save File Dialog Box
• Font and Colour selector
• TreeView
• Rich Text Box
• Slider
• etc.

2. Validation:

• Capture errors during input and prior to processing and storage
• Some controls have validation built in - i.e. it is not possible to select/enter an incorrect value
• But, especially for text boxes, it is necessary to tell the system what types of input are acceptable to the system.

2.1 Input Masks:

• 3 sections separated by semi-colons:
  • the mask itself
  • a zero or a one.
    0=store all chars in the field (default)
    1=only store the typed chars
  • character to display where data entry is to occur (default = underscore)
• Special characters of the input mask:
  •       L    -  alphabetic characters only (A->Z only) entry required
  •       A    -  letter or digit, entry required
  •       a    -  any character, entry optional
  •       9    -  digits or space, entry not required
  •       0    -  digit (0->9) only, entry required
  •       #    -  digits, space, plus or minus sign, entry optional
  •       C or &    -  any character or space, entry optional
  •       <    -  convert to lowercase
  •       >    -  convert to uppercase
  •       !     -  cause input to fill from right to left  rather than left to right
  •       \     - causes the character immediately following to be displayed literally. eg. \A displays 'A'

eg.1     Field name:   SURNAME
            Data Type:   Text
            Field Size:     10
           Input Mask:    LLLLLLLLLL

eg.2     Field name:   PHONE_NO
            Data Type:   Text
            Field Size:     14
            Input Mask:   (99) 9999 9999

eg.3     Field name:   DOB
            Data Type:   Date (Medium Date)
            Format:        dd-Mmm-yy
            Input Mask:   00->L<LL-00;0;_

• Input masks provides a significant amount of power and control over your data entry and validation process
• These design capabilities are provided to make sophisticated design possible for the nonprogrammer and also to make applications easier for experienced ACCESS users

Default value may be one of:

• a value that you specify, either as part of table design or in the input control. eg. $0.00
• a “system generated” suggested value. eg. customer number

2.2 Validation Rule and Validation Text Properties:

• Validation Rule:
  • a boolean expression that evaluates to True or False
  • evaluated for the field after data entry when the focus leaves the field
  • specify rule for the field's value. eg:
          > 8
         Between 1 And 16
         Like 'R%'  
         >= #01-Jan-2006# And <= #31-Dec-2006#
         < 2 Or > 4
• Validation Text:
  • The text displayed when the Validation Rule evaluates to False

2.3 Other Validation Controls:

• Missing data
  • Specify "Required" fields in table design
  • e.g. code, name ...
• Ensure correct field length
  •  use template (input masks)
  •  e.g. - 99/99/99  or  
  •         - LLLLL  for A->Z entry required,
  •          -  <CCCCCC     converts all characters to lower case
• Data has unacceptable composition
  • eg. For a code
  • use template (input mask)
• Standard Formats       
  • eg. currency - $3,547.99,
  •       percent  -  123.00%
  •       short date - 13-Sep-2006
  •       long date - Sunday, 19th March 1601
• Data range restriction:
  • specify low and high values for numeric data
  • e.g.  qty between 1 - 16
  •          validation rule: between 1 and 16
  •          On Error print appropriate error message then reentry
• Multiple choice selection:
  • use radio buttons - only one can be selected from the group
  • use check boxes for true/false or yes/no
  • list box allows multiple items to be selected
• New Data does not match with stored data:
  • usually cross-file checks
  • e.g. does entered item-code exist in inventory file?
  • If referential integrity set up properly (in relationships window) Access will automatically inform the user of the error

3. SubForms:

• Subform is a form embedded within a form (the parent)
• Ideal for displaying data across a one-to-many relationship
• The relationship must have been previously set in the Relationships window.
  
   
• Parent and subform's data items are connected by Master/Child Link fields
  • Link fields specified in the subform control's properties on the Master form
  • Link fields do not have to have the same name, but must be compatible data types.
• Simple example using a simple datasheet for the subform:
  
  
• Data RecordSource for main form =
• RecordSource for subform =
• Link Field for Master form =
• Link Field for Child form =
• How do you change from one ward's information to another?
• How do you select a patient from within a ward?
• How do you add a new patient into a ward?
• How do you add a new ward?
• List problems with this form:
  

3.1 Design of the Subform:

• The subform can be constructed separately to the main form:
  
   
• Form Header information does not scroll
• Add Patient button contains code to :
  • automatically calculate next patientID
  • plus allocate intial values for the new patient
  • including wardID - read from the parent form
• Note that the patient's doctor can be read from the tblDoctors via a comboBox.
  • doctorID named comboBox in the subform above
  • RecordSource = what is displayed in the comboBox
    Query of two columns based on tblDoctors table can be setup in the query screen:
    SELECT docID, surname & ", " & firstname as fullname FROM tblDoctors ORDER BY surname, firstname
  Bound column =1
  • Column widths = 0cm; 3cm     (hide the docID column in the comboBox)
• Final Form:
  

References:

• Shelly, Cashman & Rosenblatt, Systems Analysis and Design, 6th Edition,  Course Technology, 2006.

NOTE: this webpage has not been brought up to WCAG 1.0 standard nor does it contain validated HTML