The SaveToDB add-in allows updating cells with active table row values and changing the active table row values using the cells.
Such cells should have special names like field_<table name>_<field name>.
You may define names manually using the Define Name button in the Formulas menu. Omit spaces in field names.
Also, you may use the Add Form Fields item in the Form Wizard menu to insert entire field set at once.
Then you may delete unused cells. Use the Clean Named Cells item in the Form Wizard menu to remove names for deleted cells.
To highlight the active table row, you may add a cursor using the Add Cursor item in the Form Wizard menu.
Cursor rows can be changed from VBA macros using the SaveToDB methods like MoveNext, MovePrevious, MoveFirst, and MoveLast.
Cells can have multiple names. This feature can be used to create master-detail tables.
The idea is simple. A cell must have two names:
- field_<master table name>_<field name>
- <detail table parameter name>
So when a user selects a row in the master table, SaveToDB updates the cell with the field value.
Then SaveToDB updates the query on the cell change event as the parameter value has been changed.
See Query Parameter Groups about using and defining parameters.
This technique can be used several times on a worksheet.
Here is example of described features:
The CustomerIndex table is a master for the Customers table.
The Customers table is a master for the Orders table.
The Orders table is a master for the OrderDetails table.
All the three tables have cursors.
The Customers and Order tables have hidden columns. The values of hidden columns are shown in the form fields.
You may see the selected cell with the field_Customers_City name.
You may find this example in the Northwind folder of examples for Microsoft SQL Server and Microsoft SQL Server Compact.