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

Every time a form is closed, the values in unbound controls are lost (this isn't always strictly true, but it generally is).

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.