Access Hacks

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.

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 ...