How to: Edit records in an Access form

Let users edit the records of a WklGrid, WklTreeList, or WklGantt in an ordinary Access form (as a dialog box, as a window, or docked next to the list) instead of in the list's cells.

Requires WKL Forms Object Library 1.30 or later.

Solution overview

The form is yours. Design a bound Access form for the table or query of the records, with the fields, validation rules and layout you want. It needs no code and no module. An unbound form that reads and writes its values itself (for example through stored procedures) works too; see An unbound form under Remarks.
The list opens it. EditForm names the form, EditFormMode sets where it appears. Double-clicking, Enter, F2, and EditSelected open it filtered on the selected record; Ctrl++ and NewRecord open it for a new record. When the user is done with the form, the list reads its records again.
Keys and kinds. EditKeyFields names the form's key fields when they differ from the list's KeyField; EditKindField and EditForms choose a form per kind of record, for a tree over several tables. A detail view of a WklGrid can have a form of its own (editForm in DetailViews).
What the form did. After the form, EditFormResult says whether the record is unchanged, changed, added or deleted; a record the form added is selected (EditFormNewKey).

`EditFormMode`	Where the form appears	When the list reads again
`wfmNone`	No form; the list edits in its cells (default)	-
`wfmDialog`	Modal dialog (`acDialog`)	When the dialog is closed
`wfmWindow`	Ordinary form window; the user can go back to the list	When the window is closed
`wfmDocked`	In the subform control `EditFormControl` of the same form; follows the selection	When the focus comes back to the list
`wfmAuto`	Docked while the list keeps 320 pixels beside the subform control, else a dialog	As docked or dialog
`wfmOverlay`	In the subform control `EditFormControl`, over the right part of the list (`EditFormWidth`), only while a record is edited	When the focus comes back to the list; the form is hidden then

Implementation

A form frmCustomers with the grid Grid1 on tblCustomer (key ID), and an edit form frmCustomer bound to tblCustomer with a text box per field.

In Design view of frmCustomers, open the grid's designer (Properties on the control's shortcut menu, or the […] button of a property in the property sheet), go to the Behavior page (in a WklTreeList: Grid, grouping and export), Edit form section:
- Form (EditForm): frmCustomer
- Display (EditFormMode): Dialog
Save the form and open it in Form view. Double-click a customer: frmCustomer opens as a dialog on that customer. Change a value and close the dialog; the grid shows the new value.

The same settings by code:

Private Sub Form_Load()
    With Me!Grid1.Object
        .EditForm = "frmCustomer"
        .EditFormMode = wfmDialog
    End With
End Sub

Docked beside the grid: add a subform control sfEdit to frmCustomers (for example in a pane of a WklDockLayout) and leave its Source Object empty; the grid fills it.

With Me!Grid1.Object
    .EditForm = "frmCustomer"
    .EditFormControl = "sfEdit"
    .EditFormMode = wfmDocked
End With

A tree over a UNION query of customers and orders, with a column Kind and a common key column ID:

With Me!Tree1.Object
    .EditKindField = "Kind"
    .EditForms = "{""forms"":[{""kind"":""Customer"",""form"":""frmCustomer"",""keys"":""CustomerID""}," & _
                 "{""kind"":""Order"",""form"":""frmOrder"",""keys"":""OrderID""}]}"
    .EditFormMode = wfmDialog
End With

A tree whose key is unique across several tables only through a text such as 'r5' (rule 5) or 'i5:resource' (item resource of rule 5): the union carries the table keys in columns of their own (Nr, Item), and sourceKeys takes the form's key from them. children lets Ctrl++ on a rule add an item to it, deleteFrom names the table its items are deleted from:

With Me!Tree1.Object
    .KeyField = "Key"
    .EditKindField = "Kind"
    .EditForms = "{""forms"":[" & _
        "{""kind"":""Rule"",""form"":""frmRule"",""sourceKeys"":""Nr"",""keys"":""Id""," & _
        """children"":[{""kind"":""Item"",""link"":""RuleId=Nr""}]}," & _
        "{""kind"":""Item"",""form"":""frmRuleItem"",""sourceKeys"":""Nr;Item"",""keys"":""RuleId;Item""," & _
        """deleteFrom"":""tblRuleItem""}]}"
    .EditFormControl = "sfEdit"
    .EditFormMode = wfmDocked
End With

Buttons of your own for each kind use NewRecordOfKind and CanAddKind:

Private Sub Tree1_CommandStateChange()
    Me!cmdNewRule.Enabled = Me!Tree1.Object.CanAddKind("Rule")
    Me!cmdNewItem.Enabled = Me!Tree1.Object.CanAddKind("Item")
End Sub
 
Private Sub cmdNewItem_Click()
    Me!Tree1.Object.NewRecordOfKind "Item"
End Sub

Remarks

Validation stays in the form. Required fields, validation rules and errors of the data source appear in the edit form, as in every Access form; the list shows no messages of its own for them. An edit form that does not exist, or a key that does not fit the form's key fields, is reported through DataError (or a message box without a DataError procedure).
New records of a linked list. When the list is linked to a master record (LinkChildFields), the controls of the edit form bound to the link fields get the master values as default value. The form needs a control for each link field; it may be invisible. In dialog mode such a form is opened hidden, prepared and then shown with Modal = True instead of as acDialog, because a dialog cannot be changed once it is open: the list still waits until it is closed, but Access's main window stays enabled.
What the form did. The list reads the record from its record source before the form opens and afterwards, and compares all fields: EditFormResult is wfrUnchanged also when the form saved without a change. For a new record it compares the keys the list holds before and after (in server mode the largest key of one numeric key field); it then selects the new record, expands its parent and scrolls to it. A list over a Recordset reports wfrUnknown. AfterEditForm still reports NewRecord = True for a form opened for a new record. Because the list reads its record source itself, wfrChanged is reported for any change the record source shows afterwards, also one the form wrote through a stored procedure instead of saving its own record.
Selecting a record the form added another way. When the new key cannot be found by comparing keys (several users adding at once, a key that is no growing number), set SelectedKey in AfterEditForm: the list has read its records again before the event.
An unbound form. The form is opened with a WhereCondition on the key (docked: Filter and FilterOn); an unbound form gets no record from it and no OpenArgs. It reads the key from the list (Forms!frmList!Grid1.Object.SelectedKey, or Key of BeforeEditForm) and loads and writes its values itself, for example through a pass-through query. EditFormResult still reports wfrChanged when the record source shows a change afterwards, also when the form saved nothing itself (wfrUnknown for a list over a Recordset). Use the dialog mode for it: in docked and automatic mode the list only changes the form's filter on each selection, which an unbound form does not notice.
Records of a UNION query. Access cannot change a union, so a record of a kind with sourceKeys or deleteFrom is deleted in that table (or the form's RecordSource), over the form's key fields, with BeforeDelete and the question as usual. With edit forms only records whose kind has a form can be edited and deleted. A kind that may be added and deleted but not changed needs no setting of the list: set Allow Edits (AllowEdits) of its form to No, and the form opens read-only.
Window mode. Opening the form again for another record while its window is open shows the other record in the same window: Access opens a form without module only once. Closing the list's form leaves such a window open with its changes, as Access leaves every other form open; the list asks nothing.
Docked mode. Access saves the record of the docked form when the focus goes back to the list, as for any subform; a record that cannot be saved keeps the focus in the form with Access's message. Clicking another record in the list moves the focus back to the list, so the shown record is saved as described, and the docked form then shows the selected one. Closing the list's form saves the docked record as for every subform.
Overlay mode. The subform control must come after the list in the form's order (Arrange › Bring to Front) to lie above it. The list places it over its right part (EditFormWidth, else the control's own width) when a record is edited and hides it when the focus comes back to the list; the overlay does not follow the selection.
Detail views. A detail view has no subform control of its own, so docked and automatic open its form as a dialog. Its BeforeEditForm and AfterEditForm are raised by the master grid.
Events need a module, the form does not. BeforeEditForm (choose another form, cancel) and AfterEditForm are events of the control and need the form's module; opening, filtering and reading again work without them.
Not in UserForms. In Excel and Word there is no Access form to open; the properties are kept but have no effect.

How to: Edit records in an Access form

Solution overview

Implementation

Remarks

Related topics