Maximizing Screen Real Estate with Microsoft Access Forms

1
Maximizing Screen Real Estate with Microsoft
Access Forms
2
What is a Form

• An object
• Form is a bitmap until it has focus
• A structured window or box
• Contains header, detail and footer
• Contains pre-defined area for editing data
• Form is a collection of controls
• Control is a bitmap until it has focus

3
Why Use a Form

• To display and edit data
• Hide underlying tables or queries
• Provide ergonomic GUI for user
• Validate data before writing to DB
• Enforce business rules and logic
• Application flow
• Provides facilities for navigation
• Custom messages based on conditions

4
Types of Forms

• Navigation or menu forms
• Data input forms
• Search or find forms
• Pop up forms
• Modal forms
• Sub-forms
• Tabbed or toggle forms
• Message boxes
• Input boxes

5
Data behind a Form

• Un-bound forms
• Forms un-bound at run time
• Forms then bound with event procedures
• Bound forms
• Forms bound to table
• Forms bound to query
• Forms bound to code (VBA or SQL)

6
Controls on a Form

• Label Text box
• Option group toggle/option button
• Check box
• Combo box list box
• Command button
• Image (picture, logo or graphic)
• Bound Unbound object frame
• Tab and sub form
• Drawing controls (lines shapes)
• Active X (i.e. Calendar control)

7
Code behind a Form

• Form has 81 possible properties
• 32 of them are Event driven
• Access Macros (Just say NO!)
• Visual Basic for Applications (VBA)
• Structured Query Language (SQL)
• Application Programming Interface (API)
• Class modules
• Standard modules

8
Forms Real Estate

1. DB Design and Normalization
2. Creating the Sub Form Control
3. Linking the Sub Forms Controls
4. Referring to Sub Form Controls
5. Referring to Parent Form Controls
6. Other Considerations
7. Sample Application

9
1. DB Design and Normalization
First Normal Form Second Normal Form Third Normal Form

Remove repeating groups in individual tables Create separate tables for sets of values that apply to multiple records Remove fields that do not depend on a key
Create a separate table for each related data set Relate the above tables with a foreign key
Identify each set of related data with primary key

• http//www.troubleshooters.com/littstip/ltnorm.htm
  l
• http//www.datamodel.org/NormalizationRules.html

10
2. Creating the Sub Form Control

• Mimic form to sub forms behavior via
  relationships
• Avoid forms based on complex multi-table queries
• Design and complete the sub form
• Draw tab or toggle control on the main form
• Bound versus unbound sub forms and hardware
• Left Right versus Top Bottom
• Property sheet to construct sub form space
• Drag drop from Database container, or
• Use the form wizard, or
• Use the Subform/Subreport wizard

11
3. Linking the Sub Form Controls

• Source Object
• Master (PK) Field with Child (FK) Field
• Adhere to naming convention

12
4. Referring to Sub Form Controls

• Refer to sub form control from sub form
• Me.ControlName
• Examples
• Me.cboStateProvince.SetFocus
• Me.txtCompanyDateUpdated Date
• Me.fraSupplierInsured.Value 0
• Refer to sub form control from main form
• Me.subfrmName.Form!ControlName
• Examples
• Me.subfrmVenueProfile.Form!cboVenueTypeID.BackColo
  r 12632256
• Me.subfrmPerson.Visible False
• Forms!frmFindVenue!lstVenueTypes

13
5. Referring to Parent Form Controls

• Refer to sub form control from sub form
• Me.ControlName
• Examples
• Me.cboStateProvince.SetFocus
• Me.txtCompanyDateUpdated Date
• Me.fraSupplierInsured.Value 0
• Refer to main form control from sub form
• Me.Parent.ControlName
• Examples
• Me.Parent.Parent.txtCompanyDateUpdated Date
• Me.Parent.chkInsuranceExpired.Value -1

14
6. Other Considerations

• Strictly adhere to a naming convention
• Turn OFF subdata sheets
• Use surrogate keys whenever possible
• MsgBox and InputBox are your friends
• Custom Menus and the RunCommand acCmd
• http//www.tkwickenden.clara.net/
• Normalize, Normalize, Normalize
• Index prudently

15
7. Sample Application

• Requirements called for most info visible
• Each screen fulfills many tasks
• Forms display multiples level of info
• Forms are command and menu driven
• Navigate with keyboard (1) and mouse (2)
• 4 Escape keys exits application

16
Conclusion

• Forms are objects
• Visual filter for underlying data
• Easier to view and manipulate
• Offers security, flexibility and productivity
• Validates all entry before writing to DB
• Enforces business rules
• Allows users to navigate entire application
• Proper form layout maximizes efficiency

17
Questions

Ofer Shimrat Soundoff Computing [phone]
ofershimrat_at_compuserve.com

Thank You