How to: Fill the navigation bar from a table

Keep the groups, entries, and pages of a WklNavBar in a front-end table, so that adding a page means adding a row instead of changing the form and its code.

Requires WKL Forms Object Library 1.26 or later.

A bound bar reads its groups and entries from its RecordSource (a table, query, or SQL statement) and shows an entry's target by itself, without any code in Navigate:

• Fields. KeyField is required; TextField, GlyphField, ParentField, SortField, VisibleField, EnabledField and PlaceField are optional. A row that another row names in ParentField is a group.
• Targets. TargetKindField and TargetField give each entry a target: a subform target becomes the SourceObject of the subform control named in TargetControl, and a form or report target opens in its own window.
• Start page. NavigateOnOpen (default True) shows the target of the entry selected in the design once, when the form opens, without raising events.
• Unsaved changes. ConfirmUnsavedChanges (default True) asks the user before a subform target replaces a page that has an unsaved record.
• Problems. A faulty row never stops the form; LoadProblems lists every row that was skipped or shown without its faulty value.

The designer sets all of this up without code ([...] in the Custom row, Data source page). Its Create template table button creates a table with the designed entries and binds the bar to it.

1. Open the form with the bar in Design view and open the designer ([...] in the Custom row of the property sheet).
2. On the Data source page, choose Create template table (the table is named tblNavigation unless you change it). The bar gets a table with the fields Key, Caption, Glyph, ParentKey, SortOrder, Visible, Enabled, Place, TargetKind, and Target, filled with the designed entries; Visible and Enabled get the default value Yes. A designed group without entries is left out, because no row refers to it; the page tells you which one.
3. Under Targets, select the subform control in Subform for pages (TargetControl) and close the designer with OK.

4. Fill in the targets in the table. Here's an example with a „Data“ group, a page in the subform, a form in its own window, and an entry at the bottom:

   Key	Caption	Glyph	ParentKey	SortOrder	Visible	Enabled	Place	TargetKind	Target
   start	Home	E80F		1	Yes	Yes		Subform	frmStart
   data	Data			10	Yes	Yes
   orders	Orders	E8A5	data	1	Yes	Yes		Subform	frmOrders
   customers	Customers	E716	data	2	Yes	Yes		Form	frmCustomers
   options	Options	E713		1	Yes	Yes	bottom	Form	frmOptions

5. Open the form. The bar shows the rows, and sfPage shows frmStart when start is the entry selected in the design.

An application built from text files (for example, with the build tool) creates the table with a statement instead of the designer. This statement creates the same columns as Create template table:

CREATE TABLE tblNavigation (
    [KEY] TEXT(50) CONSTRAINT pkNavigation PRIMARY KEY,
    [Caption] TEXT(100), [Glyph] TEXT(10), [ParentKey] TEXT(50), [SortOrder] LONG,
    [Visible] YESNO, [Enabled] YESNO, [Place] TEXT(10), [TargetKind] TEXT(20), [Target] TEXT(64))

What's fixed and what's up to you:

• The names are fixed only to the extent that the field properties name them (KeyField, TextField, and so on). If you use other column names, set the properties to those names.
• The data types are up to you. The bar reads every value through the record source and interprets it: Visible and Enabled accept Yes/No, numbers (0 is No), or the words Yes/No, True/False, Ja/Nein, or Wahr/Falsch, and an empty value counts as Yes. SortOrder sorts numbers by value and text alphabetically, with empty values last. Place accepts Top/Bottom, 0/1, or oben/unten, and empty means top. TargetKind accepts the numbers 0 to 3 or the words listed under Remarks. Glyph accepts a code in the range E000 to F8FF as four hexadecimal digits, optionally prefixed with 0x, &H, or U+, or the character itself; empty means no symbol. Leading and trailing spaces are ignored everywhere.
• The lengths are up to you, with three rules. Key must be unique; the bar compares keys case-insensitively. ParentKey holds keys, so it must be at least as long as Key, or a group with a long key can't be referenced. Target names a form or report, so 64 characters (the limit for Access object names) are always enough.

To set up the same binding in code, for example in a form that's built at run time:

Private Sub Form_Load()
    With Me!navMain.Object
        .RecordSource = "tblNavigation"
        .KeyField = "Key": .TextField = "Caption": .GlyphField = "Glyph"
        .ParentField = "ParentKey": .SortField = "SortOrder"
        .VisibleField = "Visible": .EnabledField = "Enabled": .PlaceField = "Place"
        .TargetKindField = "TargetKind": .TargetField = "Target"
        .TargetControl = "sfPage"
        If Len(.LoadProblems) > 0 Then Debug.Print .LoadProblems
    End With
End Sub

After a change to the table while the form is open, call Requery:

Public Sub RefreshNavigation()
    Forms!frmShell!navMain.Object.Requery
End Sub

• Place applies to entries. A group always appears at the top, and its position among the rows at the top comes from SortOrder. Bottom in a group row's Place is ignored, and LoadProblems lists the row.
• Target kinds are the WklNavTargetKind numbers or the words Subform, Form, and Report (or Unterformular, Formular, and Bericht). The bar sets the name of a subform target as the subform control's SourceObject unchanged, so both a form name and a table in the form Table.tblOrders work.
• Events still occur. A user's choice raises BeforeNavigate (which can cancel it) before the bar shows the target, and Navigate afterward. Entries without a target leave everything to Navigate, as in an unbound bar.
• The bar follows its form targets. When a form opened as a target becomes the active window (by window, document tab, Ctrl+F6, or DoCmd.SelectObject), the bar selects its entry without raising events. This replaces the Form_Activate code that How to: Open forms from the navigation bar needs for an unbound bar.
• Yes/No columns are never Null in Access. A row added without a value in Visible is No, and therefore hidden; the template table gives both columns the default value Yes.
• What the table replaces. The rows replace the designed entries in the bar; Items keeps the designed entries, which are saved with the form and shown when the source can't be read. Code can add, change, and remove entries of a bound bar until the next Requery.
• Run-time state survives Requery. The selection, badges, and collapsed groups are kept for keys that still exist; no event occurs.
• A saved query can reference form controls as parameters; the bar fills them in the same way Access does when a form opens the query. You can use this, for example, to show entries based on the user's role.
• Access forms only. The bar reads the table through the Access instance of its form. On an Excel or Word UserForm there isn't one: the bar shows the designed entries, and Requery raises wkeSourceError („The bar is not in an Access form.“).

• WklNavBar overview
• How to: Stay on a page with unsaved changes: the check in your own code, for an unbound bar
• How to: Open forms from the navigation bar: forms in their own windows with an unbound bar
• Entries from the navigation table are missing
• API reference: WklNavBar (RecordSource, TargetKindField, TargetControl, NavigateOnOpen, ConfirmUnsavedChanges, Requery, LoadProblems)