Created by: gwideman, Jun 21, 2011 6:27 pm
Revised by: gwideman, Jul 18, 2012 3:38 pm (12 revisions)

At the moment, a collection of miscellaneous notes on Forms in Access 2010 (and probably 2007 too).

Challenges: Multiple grids, and one-to-many situations

Structures of UI one would like to be able to implement:

1. List of items, plus details of one item

Schematic:
Grid listing all items.
Preferably sortable etc
Panel showing details for the item
selected in the list above

2. Main item with subsidiary list of details (one to many)

Schematic
Single main item's details(Select/navigate using nav control)
Some detail might consist
of a list (show in grid).

3. Combination

Schematic
Grid listing all items.
Preferably sortable etc
Main item's details
Some detail might consist
of a list (show in grid).

4. Combo with multiple detail lists, using pages/tabs

Schematic
Grid listing all items.
Preferably sortable etc
Tab 1
Main item's
detail fields
Tab2
A "many"
detail list in grid
Tab 3
List grid
Tab 4
List grid

Alternative form views, pros and cons

To design a sophisticated form requires knowing the various different behavior possibilities of a single form, and then how to use one or more subforms within a main form.
Overarching considerations involve the way that, in Access, the concepts of "Form", "Dataset" (or Recordset), and "Grid" are all intermingled. This simplifies simple cases, but makes more complicated cases messier.
A form's design view presents five sections on to which controls can be placed. Of these sections, three are worth considering here:
  • Form Header section
  • Detail section
  • Form Footer section
  • (We can ignore Page Header and Footer)
The form itself has a RecordSource property by which the form is associated with a single table or query. Controls placed on the form are set (via ControlSource property) to connect to fields of the Form's RecordSource. (Note that Access offers no grid control per se, though another Form, configured for Datasheet view as described below, can be used more or less for this purpose.)
When the form is run, controls placed in the different regions of the form behave differently. Further, the form behavior differs depending whether it is run in Single Form, Continuous Form, or Datasheet mode ("View"). The following table describes this:

Single Form mode
Continuous Form mode
Datasheet Mode
Form Header
Each control appears once, showing selected record.
Not shown
Detail
Each control appears once
The control layout is replicated for as many rows as will fit. User can use scrollbar, and select a particular record.
The entire window is filled with a grid whose columns are the those with controls laid out in the detail area. Properties of those controls determines their field behavior in the grid, like enable, lock, control type.
Form Footer
Each control appears once, showing selected record.
Not shown
How to navigate records
Use nav control at bottom
Scroll and select records in detail area.

Comments, issues
No real behavior difference between
header, detail or footer areas
Continuous-ified detail area looks a bit like a grid, but no sort nor column resize.
The scrollbar fills the entire vertical height of the form, not just detail area to which it applies.
Resize of form squashes the detail area only.
This mode essentially turns the form into a grid. If used as a subform, then it essentially becomes a "grid component".

"Grid control": using datasheet-view subforms

To create forms with arbitrary "grids" which sync with single fields, or with subsidiary grids on subforms:
  • More or less forget about Continuous Form mode
    • Ie: Don't bother with the many-to-one relationship between detail and header/footer of same form.
  • Us a main form that is just form surface
    • Single-form mode
    • Use the header, footer and detail area according to what you want resize to shrink. Probably might as well just use the detail area
    • To check: Does detail area scroll? Yes
  • For "grid controls", use sub forms containing forms set to Datasheet view.
  • Sync different controls on the main form using intermediate (possibly invisible) control (see separate topic below)
  • For multiple detail regions, use Page control and tabs

Settings for subforms used in datasheet mode

  • The form itself that is to play the role of subform
    • DefaultView: Datasheet
    • Allow Datasheet View: Yes.
    • Disallow other views.
    • Disable Navigator buttons (but keep Record Selectors)
    • Assuming this grid is supposed to be navigate only (no editing:
      • Disable Additions, Deletions, Edits
      • DataEntry: No -- note this property is badly named. It means "present only new records" (don't navigate to existing records)
    • Field Controls
      • Since editing has been disabled for the form, individual control Enabled and Locked are not needed to prevent editing
        • But setting Enabled and Locked does affect whether Search uses that field. Grrr.
        • And it also affects whether inconsistent repaint/unpaint of selection highlight (probably a bug) occurs.
          • Repaint fixed by scrolling out of view.
        • Not sure best combinations here
  • Settings for the containing subform control on the main form
    • Enabled: Yes: Can't disable because then can't navigate
    • Locked: Yes: Prevents editing (redundant, but OK)

Settings for subforms showing single record (Single Form view)

To be completed
  • The form which is to play the role of subform itself
    • Allow Edits
    • Disallow Additions, Deletions IMPORTANT, otherwise actions like Tab-off-last-field creates a new record, resulting in unwanted new record and FK violation. Maybe should automate this setting!?
    • DataEntry: No (No = don't restrict form to new records only)
    • Field controls
      • Set Id field controls Enabled: No, Locked: Yes
      • Set other field controls to Enabled: Yes, Locked: No

Synchronizing records between one subform and other subforms

(LinkMasterFields syntax, intermediate control)
  • Set up the outer most form so it doesn't have a data source. We won't use data features of outer form.
  • Example master subform: subfrmThings, displaying a grid of table Things
    • Contained in control subfrmctlThings
  • Suppose the PK field for table Things is ThingId:
    • Name the control for that field txtThingId (in subfrmThings design view)
  • Intermediate control on main form.
    • Name text control txtSelectedThing
      • txtSelectedThing.ControlSource = "=[subfrmctlThings].[Form]![txtThingId]"
        • Note the "="
      • Probable want to set this control Enabled: No and Locked: Yes, and possibly Visible: No.
  • Example slave subform: subformDoodads, displays grid of table Doodads
    • Contained in control subfrmctlDoodads
    • Objective: Slave record display to be selected or filtered by FK ThingId match to txtSelectedThing
    • Set subfrmctlDoodads.LinkMasterFields: txtSelectedThing
    • Set subfrmctlDoodads.LinkChildFields: ThingId
    • Note
      • subfrmctlDoodads.LinkMasterFields can refer to a field (of containing form's RecordSource) or a control (on containing form)
      • subfrmctlDoodads.LinkChildFields must refer to a field name. I don't think it can be control or expression.

Unsolved: Immediate update of peer subforms

When controls are edited in a Single Form subform, that doesn't cause fields in peer Datasheet-view subform to update. Have to move off current record to update.
  • Presumably this is because the the Single Form is editing a different record set than the Datasheet.
    • Might be fixed by replacing the Single Form by individual controls with expression pointing to fields in the Datasheet-view subform.
      • But is that two-way? Need to investigate.

Resizing Subforms

  • Can use the Horizontal and Vertical anchor properties on Subform (container)
  • Also
    • CanGrow, CanShrink?
    • Min height seems to be set by combo of
      • design-view height of Subform and/or
      • design-view height of the Form that's used in the Subform

Setting custom primary key during insert

Objective -- to set a custom-calculated key value when inserting a new record.
If Insert is fired by form (like when using insert button)
  • Can use Form's Insert event to make up key.
  • But maybe should (also) catch key-collision event... need to investigate that.
Single inserts directly into table (ie: from Access datasheet view of table):
  • Just have to call key-gen procedure manually
Bulk inserts from other table
  • Example: a newly imported set of data in a "staging" table, that is to be cleaned and then merged into existing "main" data table.
  • Need proc to bulk add keys. Key-gen proc needs to have an option to check existing keys in main table and also in the staging table.

How to lock formatting of subform datasheet

There are a number of settings to set on the subform itself, and on the subform (container) control to make the most of the grid behavior, and to restrict opportunities for users to mess up the grid layout and appearance.
  • If grid is for navigation only, then:
    • On subform (form): Disable Data Entry, Additions, Deletions, Edits, and navigation buttons. (Keep Record Selector column)
    • On subform (container) control: Enabled: No, Locked: Yes. This prevents selecting by clicking in the grid, which might have been nice but causes inconsistent repaint of selection highlights. Can still select using the record selector column.
Other features to set:
  • Pre-deploy: Change Settings to change ribbon and menus
  • Events to capture user doing silly things
  • OnLoad runs code to set properties sensibly.

Updating tab captions

Use Current event on the table that drives selection, and run queries to update the captions.
To reduce flicker:
  • Can use Echo off while updating multi captions,
  • only update captions whose text changes.

Abstracting repetitive stuff

  • Tables
    • Table name
    • PK field name
  • New Record form for multi tables
    • New PK algorithm for multi tables
    • Auto FK for new records
  • Form features
    • Multi tabs in page control
    • Record Source

Front-end in different database than data

Benefits:

  • Can have succession of versions of front end independent from progress in data collection -- makes back up and restore easier.

Awkwardnesses:

  • Requires setting links from front-end to back-end tables. That requires fixing if back end is moved. (Links use absolute paths. At least, they did as of 2003, may have changed?)
  • VBA containing algorithm for calculating Ids belongs with the data. But VBA in back end can't be run from front end where it needs to be launched.

References