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

• DB Design and Normalization
• Creating the Sub Form Control
• Linking the Sub Forms Controls
• Referring to Sub Form Controls
• Referring to Parent Form Controls
• Other Considerations
• Sample Application

9
1. DB Design and Normalization

• 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