Users like to see the big picture, and often that includes items they’ve selected as well as what they haven’t selected. Check boxes are interface widgets that meet these needs, but they can be cumbersome to maintain. Chris Weber shows us his technique for creating dynamically generated check box lists that don’t require intervention by the developer when the possible selections change.
One of the downfalls of using check boxes and option groups is that the user interface needs to be updated (read “redone”) whenever the choices change. For example, check boxes used for a distribution list of departments that receive a memo would have to be updated whenever a department was added, deleted, or renamed. So, when I see an interface that uses option groups or check boxes, I see work down the road. The options invariably expand or the selections change. I’m grateful for the work, but I also take pride in the solutions I provide. I don’t think the way to ensure my future employment is to create non-scalable interfaces that need reworking when my client’s business changes.The upside of option groups is that users see all the choices available, and they see what they’ve selected, and they see what they haven’t selected. That’s true, provided the option group is up-to-date—that is, implemented with the current selections in mind.
In every case where an option group is used, a combo box that dynamically provides the most current choices can replace option groups in your UI. Not only will it save you work down the road, it will save you space on your forms. The downside is that users need to be familiar with the contents to know what wasn’t chosen. Alternatively, a single-select list box also provides the current choices and reveals both the selected and non-selected values.
The same arguments don’t hold true for a group of check boxes, which imply that the user can select none, one, or multiple choices. Combo boxes are never multi-select. An extended list box, however, can provide the same functionality. Unfortunately, for child lists like a memo distribution list, a list box needs to be programmatically updated for each parent record, as well as for each insert and delete. And, as far as some users are concerned, holding down the Ctrl key to make or remove selections is non-intuitive and cumbersome.
Lastly, in a many-to-many relationship like memos and departments, it’s impossible to let users select combinations of departments and memos unless the relationship already exists. Well, it’s not impossible, as you’ll see.
When the gods are angry with the users, they get exactly what they ask for…
The first answer
On a recent project, my client insisted on a checklist of departments for the distribution of documents. They wanted check boxes that their users could tab through, and the list of selections was about to change with a reorganization of the departments. The relevant data structure is shown in Figure 1.
Figure 1
Initially, I didn’t see a problem. I told my client that I’d just use a list box that has a true/false field in it that reflects the existence of the key value in the memo distribution list. But, I discovered, Access list boxes won’t display check boxes for true/false fields. They simply display a 1 or 0, and that wasn’t acceptable to my client. I created a very simple mock-up of the interface (as shown in Figure 2), and began to mull over my options. You can see the void where the dynamic check box distribution list should be.
Figure 2
As I talked myself through the problem, I realized that the list my client wanted was really a union of two lists. The first list consisted of what was selected—that is, those records in tblMemoDistribution whose MemoIDs matched that of the memo of interest. The second list consisted of those departments in tblDepts whose DeptID didn’t appear in tblMemoDistribution with the MemoID of the current memo. That second list is really a not-in query of the departments not in the first list.
The first list was simple to produce. I selected all of the records in tblMemoDistribution whose MemoID matched the one on my form. I saved this query as qfsubMemoDistList. The second part of the Unioned list, the not-in query, was also simple—a variation on the first with a Not In() clause. The resulting SQL statement looked like this:
SELECT tblDepts.Dept, tblMemoDistributionList.DeptID AS SelectedDeptID FROM tblDepts INNER JOIN tblMemoDistributionList ON tblDepts.DeptID = tblMemoDistributionList.DeptID WHERE tblMemoDistributionList.MemoID = [forms]![frmMemos]![txtMemoID] ORDER BY tblDepts.Dept UNION SELECT tblDepts.Dept, tblDepts.DeptID FROM tblDepts WHERE tblDepts.DeptID Not In (SELECT DeptID FROM qfsubMemoDistList WHERE DeptID Is Null) ORDER BY tblDepts.Dept
This query returned all of the departments, regardless of whether or not they were selected. The problem was, there was no way to tell from the list whether the department was, indeed, selected. Then I found my solution: I’d fill both queries with the same fields and convert the MemoID (where it existed in tblMemoDistribution) to a Boolean that could then be displayed as a check box in a subform.
In this example, I’ve aliased the parent DeptID as LookupDeptID, the Boolean MemoID as itself, and the child DeptID as SelectedDeptID. Because the Boolean MemoID and SelectedDeptID will never appear in the second list, they’ve been replaced with a zero-length string:
SELECT tblDepts.Dept, tblDepts.DeptID AS LookupDeptID, CBool(Nz(tblMemoDistributionList.MemoID, 0)) as MemoID, tblMemoDistributionList.DeptID AS SelectedDeptID FROM tblDepts INNER JOIN tblMemoDistributionList ON tblDepts.DeptID = tblMemoDistributionList.DeptID WHERE tblMemoDistributionList.MemoID = [forms]![frmMemos]![txtMemoID] ORDER BY tblDepts.Dept UNION SELECT tblDepts.Dept, tblDepts.DeptID AS LookupDeptID, "" as MemoID, "" as SelectedDeptID FROM tblDepts WHERE tblDepts.DeptID Not In (SELECT SelectedDeptID FROM qfsubMemoDistList WHERE SelectedDeptID Is Not Null) ORDER BY tblDepts.Dept;
I saved this query as qunn_fsubDeptMemo. The output of the query for a MemoID of 1 (the record shown in Figure 2) can be seen in Figure 3. The first column gives me my complete list of departments, the second gives me their DeptIDs, the third gives me my check box value (whether they’ve been selected), and the fourth gives the DeptIDs of just those that have been selected.
Figure 3
Figure 4
I then built a subform to fill the void in my interface design. The subform uses just two controls: a check box for MemoID and a text box for Dept. And, as per my client’s request, I also added Select All and Clear All buttons. The resulting form is shown in Figure 4.
There were two problems, however. The first was that a Union query is never updateable, so the check box reflected the data but couldn’t be used to update the underlying dataset. The second was that the records in the record source, qunn_fsubDeptMemo, couldn’t all be linked to the main form through the MemoID field. This is because:
- MemoID isn’t actually a field in the recordset.
- Even if it were, not all the records would have one, therefore, the unselected departments would never show.
So, I had a subform without linking Master / Child fields filled in. Back to the drawing board.
The final answer
In fact, all I’d built was a read-only form. I needed a way to update the underlying record(s) by having my user click on the check box. Unfortunately, I soon learned that the On_Click event of the check box wouldn’t fire because the underlying field wasn’t updateable. How could I check a check box when its underlying field locks the control? The answer was obvious: Don’t click the check box; click something else.
Figure 5 shows my subform, fsubMemoDistList, with an added transparent button that I moved directly on top of the locked check box. Now I can add my code to the button’s OnClick event to handle my selections.
Figure 5
Without the benefit of updating bound controls, I had to write INSERT and DELETE statements for each of my button clicks. I had to insert a new record into tblMemoDistribution if the click occurred on a record where the check box was False, and I had to delete the extant record if the check box showed True. This is my code for my transparent cmdCheck:
Me.Parent.Dirty = False
If Not IsNull(Me.Parent.MemoID) Then
lngDeptID = Me.LookupDeptID
If Nz(Me.MemoID) Then
'already checked
CurrentDb.Execute "DELETE * " & _
" FROM tblMemoDistributionList " & _
"WHERE MemoID = " & Me.Parent.MemoID & _
" AND DeptID = " & lngDeptID, _
dbFailOnError
Else
CurrentDb.Execute "INSERT INTO " & _
" tblMemoDistributionList (MemoID, DeptID) " & _
"VALUES(" & Me.Parent.MemoID & ", " & _
lngDeptID & ")", dbFailOnError
End If
The code begins by trying to save the parent record. If the save fails, an error message in my error handler will inform the users what they must do to complete the Memo, and the routine exits. If the save succeeds, I then check to be sure a parent record exists. If the user is on a new record, nothing happens. Otherwise, the code continues and stores away the LookupDeptID of the current row into lngDeptID.
I then test to see whether the department is already selected by examining the True/False value of my Boolean MemoID. If True, I execute a DELETE statement to remove the association in tblMemoDistribution. If False, I execute an INSERT statement to add the association to tblMemoDistribution. Finally, to reflect the changes, I requery the subform:
Me.Painting = False Me.Requery With Me.RecordsetClone 'get back to the original record .FindFirst "LookupDeptID =" & lngDeptID If Not .NoMatch Then Me.Bookmark = .Bookmark End If End With
Unfortunately, the requery method moves the subform’s cursor to the first record and, if the list is very long, this scrolls the user back to the first record in the list. So, first I stop the form from painting—I don’t want the user to see this inadvertent scrolling.
Then, the value in lngDeptID comes into play. I use the subform’s RecordsetClone method to find the department that the user first clicked on and move back to it. In the Exit statement I always turn the Painting back on. I also reset the focus on the hidden cmdCheck; otherwise, the actual check box gets the focus and my users must move off to a different record and then return should they wish to check the box again:
Exit_cmdCheck_Click: Me.Painting = True cmdCheck.SetFocus Exit Sub
Lastly, I wrote methods for the Select All and Clear All buttons. The code for Clear All is the simpler routine. It deletes all tblMemoDistributionList records for this MemoID:
Me.Parent.Dirty = False If Not IsNull(Me.Parent.MemoID) Then CurrentDb.Execute "DELETE * " & _ "FROM tblMemoDistributionList " & _ "WHERE MemoID = " & Me.Parent.MemoID, _ dbFailOnError Me.Requery End If
The Select All is a two-step process. Instead of writing an individual insert statement for each DeptID not yet associated with the current MemoID, I first delete all extant associations by calling cmdClearAll_Click. I then append all the DeptIDs to tblMemoDistributionList. This is much faster and lets me reuse some code:
Me.Parent.Dirty = False If Not IsNull(Me.Parent.MemoID) Then Call cmdClearAll_Click CurrentDb.Execute "INSERT " & _ "INTO tblMemoDistributionList " & _ "( DeptID, MemoID ) " & _ "SELECT tblDepts.DeptID, " & _ Me.Parent.MemoID & " AS MemoID " & _ " FROM tblDepts", _ dbFailOnError Me.Requery End If
With quite a bit of extra work, my client got exactly what they asked for. Yes, it was a relatively expensive solution, but the client’s users see their information just the way they want it. More importantly, it’s a self-maintaining solution so they don’t have to call me back after their reorganization.
Since this experience, I’ve reused the pattern throughout the application in many-to-many relationships so each additional implementation costs less than the previous. My client is happy, I’ve learned not to be bound by bound-forms even in a purely Access solution, and I can still take some pride in my work.
Other Page On This Site That You Might Like To Read
Using List Regions with Many-to-Many Relationships
