Hack 84. Save Values from Unbound Controls for Later Recall
Give users a way to automatically recreate the way a form was set up so that they don't have to reenter information
Imagine a form that is filled with many unbound controls. A user makes several selections and expects to need to reuse the same selections another time. Saving the values in the unbound controls, and making them identifiable and recallable, can be a big timesaver. Let's call this a scheme.
Saving the values from unbound controls doesn't make them bound to anything. The values are saved to a table but only by creating code to do so. Figure 8-20 shows a form with three unbound listboxes.
Figure 8-20. A form in which schemes of control values are saved
A selection has been made in each listbox, a scheme name has been entered, and the Add/Update button has been clicked. This has created a scheme that stores the values from the listboxes.
8.14.1. The Code
The code behind the Add/Update button looks like this:
Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset ssql = "Insert into tblSchemes Values(" ssql = ssql & "'" & Me.txtSchemeName & "', " ssql = ssql & "'" & Me.listOffices & "', " ssql = ssql & "'" & Me.listItems & "', " ssql = ssql & "'" & Me.listDeliveryMethod & "')" 'delete scheme first delete_ssql = "Delete * From tblSchemes Where Scheme='" & schemename & "'" conn.Execute (delete_ssql) 'now insert scheme conn.Execute (ssql) conn.Close Set conn = Nothing Me.lstSchemes.Requery Exit Sub err_end: MsgBox Err.Description
Note that some of the code has been removed. Checking for nulls and such isn't shown here, to keep the focus on the point of the hack.
8.14.2. Running the Code
The values in the listboxes, along with the supplied name of the scheme, are inserted as a record into the tblSchemes table shown in Figure 8-21.
Figure 8-21. The control values stored in a table
As more and more schemes are saved, the listbox of schemes (on the left) fills up. From this list, a scheme can be reentered on the form. Figure 8-22 shows the numerous schemes now available.
Figure 8-22. Many schemes to choose from
The Load and Delete buttons work with the table records and the form. Load populates the unbound controls with values stored in the table for whichever scheme is selected in the scheme listbox. Delete simply deletes the appropriate record from the table.
8.14.3. Hacking the Hack
The final thing to consider is what to do when unbound listboxes and combo boxes are set to multiselect. This allows more than one item to be selected. In this situation, you're storing relational data. For example, one scheme can have more than one selection in a listbox. To handle this, you literally create a set of related tables. One holds the general scheme information, and the other holds a record for every value in the listbox. The tables relate on a keythe scheme name.
Figure 8-23 shows how this works.
Figure 8-23. Saving schemes that have multiple selections per control
The secondary table stores the scheme name, the name of the control, the value of the control, the item's position in the list, and whether the item is selected. Every list item is stored, along with its position and its selected state. When a scheme is reloaded, the various parameters are needed to recreate the list and to reflect which items were selected.