After getting medieval on the problem, Chris Weber develops a solution that allows users to sort the data in their forms (or subforms) that you can add to your application easily (and spend even less time maintaining). Along the way, he discusses the characteristics of a well-designed module.
“Hear ye! Hear ye! Hear ye! The court of King Requests is now in session. Let all who would petition the king step forward.”
“If it would please m’Lord,” ventured a voice in the crowd, “m’Lady Customer asks that her entourage be allowed to sort their fields on her farms easily, in any combination, in an intuitive fashion.”
“This sounds like a goodly request,” replied the king, “and certainly it would be useful throughout my domain. Let it be so. Which of my developing knights would accept this challenge?”
But all the knights cowered at the thought. “M’Lord,” protested one, “m’Lady’s entourage can already sort their farms from the ubiquitous toolbarn.”
“What’s more,” said another, “they might switch their farms to datasheet view and move field columns as they wish for sorting purposes.”
“Bah!” bellowed the king. “Haven’t you been listening?” (For the knights were fond of not listening.) “Lady Customer’s entourage must sort by multiple fields across the farm. And you all know that, in my kingdom, datasheet views are forbidden! Would you have my people view hidden fields and secreted key values? They might even lose field columns, man! Clearly one amongst you can see the value in this request.”
Lowly Sir Worksalot then stepped forward. “M’Lord, I would accept this quest. I think I can have it done in a morning’s span, if it can be done at all.”
“Sir Worksalot, you have a brave heart,” answered the king, “but, because you always underestimate the time required to fulfill your quest, I will give you half a morning more.”
Sir Worksalot glowed under the king’s approval. “It shall be done, m’Lord.”
“And while you’re at it, find us a method to spin gold from straw,” said the king.
“Not a problem,” replied the knight. He bowed to the court, turned on his heel, and was off to pursue his quest.
Sound familiar? Sometimes it’s the challenge that keeps this business interesting. When one of my clients added the requirement in our story to a change list, I thought it was a very reasonable thing to have. But, over the phone, I had no way of ensuring my success in coming up with a deliverable. I said I’d “look into it” and bid on the entire change list with a high and low range of hours, estimating three-quarters of a day for the sorting trick. The range estimation let me spread the risk of “what can go wrong will” across the entire list and, hopefully, I could expect a reasonable return for my effort.
Draw a map…
Whenever I have to invent something “new,” I always go for a walk and think about it first. My walk is usually the most productive part of my day. After pondering this particular request, I came up with a refined list of requirements:
- The sorting interface should be generic to any form in any database.
- It should also work with subforms.
- It needs to be implemented as expediently as possible to fulfill the bid, and be easily maintained in the future.
- It should be intuitive or reminiscent of other interfaces in Access and Windows.
I decided to use a dialog box with a pair of listboxes. Users could open the dialog box from their form, then select fields from the list on the left and transfer them to the list on the right in whatever order they wanted. While prototyping the interface, I realized I also needed a way for users to specify whether a column is to be sorted in ascending or descending order, so I added an option group below the selection buttons (see Figure 1). This way, users can select the sort order and then move the fields from left to right.
Having established the look, I needed to figure out the inner workings of my sort routine. My first step was getting the dialog up on the screen from wherever my user called for it. This told me that I’d need to have either a button on the form to open my dialog, or a globally available method to retrieve the fields and open the dialog. I chose the latter route for ease of implementation (I didn’t want to have to change every form that could use the sorting tool and, as a result, lose screen space on those forms in the process). I decided to create a custom shortcut menu that had a selection that would call my public function. The menu is shown in Figure 2, along with the property sheet for my custom Multifield Sorting entry.
To have a menu item call a custom function is a two-step process. You need to create a menu item and then assign the custom function to its On Action property. The first step that I use in creating the menu may seem counterintuitive: I adapt an existing command from the customize menu dialog and any command will do. I usually drag a dummy macro onto my menu bar and then change its name and button image as desired.
Once the menu is created, assigning the function is simple. I just overwrite any existing On Action entry for the menu with the name of my function, preceded by an equals sign. There are three characteristics of the On Action entry: It must be a function and not a subroutine, the function must not return a value, and you must include the parentheses. This is the entry I used to open my sorting dialog:
Now, when my user chooses Multifield Sorting from the menu, the menu item will call my OpenMultiFieldSorting function, which will open my dialog. Here’s what that function looks like:
Function OpenMultiFieldSorting() DoCmd.OpenForm "fdlgMultiFieldSorting", _ windowmode:=acDialog
This single point of coupling allowed me to encapsulate the balance of the functionality in the dialog itself to ensure its cohesiveness and maintainability. What a mouthful! However, those few $10 words describe four characteristics that I believe a developer should always focus on when writing a function:
- Coupling refers to the points of entry for any interface or code. The points of interaction between your routines should be kept to a minimum (this is referred to as “loosely coupled” design). This is why global variables are to be avoided at all costs. Every global variable becomes another hidden point of coupling between your routine and the rest of your project. Ideally, your functions should have a single point of coupling with the outside world. This way, the entry/exit of information can be controlled and unintentional side effects are avoided.
- Encapsulate refers to the ability of a routine to wrap together all the functionality required and hide it from the calling program. The code that’s specific to the object remains within it. Encapsulation is closely tied to coupling–the more points of interaction that you have with a routine, the more you have to know about the routine and how it works; the fewer points of interaction that you have, the less that you have to know about the routine that you’re calling.
- Cohesiveness is a fuzzier term. A cohesive code routine or object performs just one function (or as few functions as possible)–just enough to make the routine useful. It’s the degree to which your code is single-minded. Cohesive routines are both usable and reusable throughout the system. The more that a routine does, the more likely it is that the routine will do “too much” when called from your application.
- Maintainability really sums up the previous three characteristics. Code routines and objects that embody the previous three principles are far easier to maintain, change, debug, and distribute to others. Your life is easier as a result.
Now that I’d established the single point of entry to my routine, any form or subform would be able to call my sorting dialog by having its Shortcut Menu property set to my custom shortcut menu. Now if my dialog would only do something…
So many fields to be sorted
My next step was to get the listbox on the left side of my dialog (lstFields) filled with fields from the form or subform being used. There are many ways of filling a listbox. You can base the list on a table, a value list, or even use a callback function to fill the list (that is, pass a reference to a function to the dialog and have the dialog call that function). I avoid callback functions because they’re not object-based and are relatively complicated to work with (and relatively complicated to explain, as you may have noticed from puzzling out my definition). I’ve often used strings containing values separated by some delimiter (usually a comma) to pass data. But, for this interface, I’d need to treat the list as a collection of fields and use a host of string manipulation functions to add and delete field names in the list.
I opted for a simple system table. This isn’t an optimal solution: Effectively the table was another point of contact between my application and the sorting routine. I needed to keep the bid profitable, and a simple implementation would work toward that goal. The system table would also give me a visual check of what was happening in my code just by opening the table.
The design of the zstblMultiFieldSorting table, with its four fields, is shown in Figure 3. The ListName field allows me to use the table for both the lstFields listbox on the left of the dialog and the lstSort listbox on the right. The field FldName has a unique index so that the same field name can never appear twice. The AscDesc field denotes the sort order and is really only used for fields moved over to the lstSort listbox on the right (until a field is picked for sorting, it can’t have a sort direction). As you’ll see, the SortOrder field is also used for lstSort so that I can create an OrderBy string for my form.
Once the table is filled, my listboxes can query the table to get their field lists. The SELECT statement for each list box retrieves the fields listed in the table:
SELECT FldName FROM zstblMultiFieldSorting WHERE ListName="lstFields" ORDER BY FldName SELECT FldName, AscDesc FROM zstblMultiFieldSorting WHERE ListName="lstSort" ORDER BY SortOrder
Connecting to the form
By using the Screen.ActiveForm in my dialog’s Form_Open event, I had hoped to glean a list of fields from the calling form’s RecordsetClone. This should work reliably because the ActiveForm will still be the calling form when my dialog opens. However, this turned out to be trickier than I thought, as the ActiveForm only refers to main forms, not subforms. A subform is never the active form on the screen. I could have used the Screen object, which returns a reference not only to the active form but also to the active control (a subform is held inside a SubForm control). By examining the control’s Parent.Form property, I could glean my list of fields. My initial implementation used the Screen.ActiveControl to get a handle on the desired form and, subsequently, the fields from its RecordsetClone:
Dim frm as Form Dim fld as Field Set frm = Screen.ActiveControl.Parent For Each fld In frm.RecordsetClone.Fields ...
This worked fine, but I eventually ran into a bug when I tried using my dialog on the Employees form in the Northwind database. When controls appear on a tabbed interface, their direct parent is the tab control, not the form itself. The assignment of my form variable crashed when I tried to use it to hold a reference to a tab control.
Somehow, I had to work my way up the parent hierarchy until I encountered a form. What makes this problem more interesting is that Access objects don’t have methods for reporting their object type. My solution was to use a simple loop that continues until an object that has the “HasModule” property pops to the top. Controls don’t have modules, so the first object I encounter having that property must be the parent form of the control that had the focus. My final implementation uses my HasProperty function, a handy function I often use in my Access databases. HasProperty returns True if the object has a specific property, False if the object does not.
Here’s the code, beginning with the module-level variables that it uses to pass information between routines in the module. The key code is in my dialog’s Open event, which deletes all the records in my table. The code then loops through all the fields that make up the form’s recordset (I retrieve the recordset for the form through the form’s RecordsetClone property). However, just because a field appears in the Recordset, it doesn’t mean the field is actually displayed on the form. So my code then loops through the controls on the calling form and, if the control is using one of the fields in the RecordsetClone and is visible, the code adds the field to the table, marked to be used by the lstFields listbox:
Dim frm As Form 'the calling form or subform Dim rst As Recordset 'recordsource for the listboxes Dim iSortOrder As Integer Private Sub Form_Open(Cancel As Integer) On Error GoTo ErrorHandler Dim fld As Field, ctl As Control, obj As Object Set obj = Screen.ActiveControl.Parent Do Until HasProperty(obj, "HasModule") Set obj = obj.Parent Loop Set frm = obj CurrentDb.Execute _ "DELETE FROM zstblMultiFieldSorting", dbFailOnError Set rst = CurrentDb.OpenRecordset( _ "zstblMultiFieldSorting", dbOpenDynaset) For Each fld In frm.RecordsetClone.Fields For Each ctl In frm If HasProperty(ctl, "controlsource") Then If ctl.ControlSource = fld.Name Then If ctl.Visible Then rst.AddNew rst!ListName = "lstFields" rst!FldName = fld.Name rst.Update End If End If End If Next ctl Next fld lstFields.Requery lstSort.Requery Exit_Here: Exit Sub ErrorHandler: MsgBox "Error #" & Err.Number & ": " & _ Err.Description & " by " & Err.Source, _ vbOKOnly, "Error in procedure Form_Open" Resume Exit_Here End Sub Function HasProperty(pobj As Object, _ pstrName As String) As Boolean On Error Resume Next Dim strProperty As String strProperty = pobj.Properties(pstrName).Name HasProperty = (Err = 0) Err.Clear End Function
The check for a control being visible was something that I added after the initial implementation. For the same reason that I don’t think it’s a good idea to let users switch a form into datasheet view, I didn’t think that it was a good idea to let users sort on any hidden fields in the underlying query (such as key values) or fields linked to hidden controls. I added several nested If statements within the loop that iterates through the controls on the calling form. The first If…Then again uses HasProperty() to determine whether the control has a control source. Only then do I compare the field name against the form’s RecordsetClone.Fields collection.
If you’re going to use this function, it’s a good idea to supply meaningful names to your fields in your Select statement in the underlying query for the form. I don’t think users should be exposed to field names like EmpFirst, EmpLast, and so on.
Users can select fields to sort on by clicking the four selection buttons on the dialog:
- Add–Copies a field from the listbox on the left to the one on the right.
- Add all–Copies all the fields from the listbox on the left to the one on the right.
- Remove–Removes one of the fields moved to the box on the right.
- Remove all–Deletes all fields from the box on the right.
Each button uses the module-level recordset tied to zstblMultifieldSorting to edit the table’s contents and requery the listboxes. Here’s the code from those four buttons with the error handling removed to make the procedures easier to read (you can get the full version of the code from the database in the download file):
Private Sub cmdAdd_Click() If lstFields.ItemsSelected.Count Then With rst .FindFirst "FldName='" & lstFields.Value & "'" If Not .NoMatch Then iSortOrder = iSortOrder + 1 .Edit !ListName = "lstSort" !AscDesc = AscDesc() !SortOrder = iSortOrder .Update End If End With End If lstFields.Requery lstSort.Requery End Sub Private Sub cmdAddAll_Click() Dim fContinue As Boolean fContinue = True With rst .MoveFirst Do While fContinue .FindFirst "ListName='" & "lstFields" & "'" If Not .NoMatch Then .Edit iSortOrder = iSortOrder + 1 !ListName = "lstSort" !AscDesc = AscDesc() !SortOrder = iSortOrder .Update Else fContinue = False End If Loop End With lstFields.Requery lstSort.Requery End Sub Private Sub cmdRemove_Click() If lstSort.ItemsSelected.Count Then With rst .FindFirst "FldName='" & lstSort.Value & "'" If Not .NoMatch Then .Edit !ListName = "lstFields" !AscDesc = Null !SortOrder = Null .Update End If End With End If lstFields.Requery lstSort.Requery End Sub Private Sub cmdRemoveAll_Click() With rst .MoveFirst Do While Not .EOF .Edit !ListName = "lstFields" !AscDesc = Null !SortOrder = Null .Update .MoveNext Loop End With lstFields.Requery lstSort.Requery iSortOrder = 0 End Sub
The only trick here is maintaining iSortOrder for lstSort. I could have used a SQL statement in my code to directly manipulate the table, but as I already had to use a recordset to load the table in my Form_Open event, I just keep it available for the selection routines. And keeping the recordset present greatly simplifies incrementing and decrementing iSortOrder. The Add buttons also call a simple AscDesc() function that returns either ASC or DESC as indicated by the user’s option group selection. Figure 4 shows zstblMultifieldSorting with the results from the Employees form selections in Figure 1.
Sorting them out
Now that I can retrieve and select the visible fields on the form, and specify their sorting order, I need to apply the selections to the calling form. The click event of cmdApply applies the sort to the calling form or subform. The code first sets the Sort property of the recordset to the SortOrder field. Because I’ve used the OpenRecordset method specifying that I wanted a dynaset (which gives me better performance but contains only the primary keys of the specified records), setting the Sort property doesn’t automatically sort the records. However, all I need to do is create a new recordset (rstSorted) based on my first record.
With my sorted recordset in hand, I loop through the records creating a sort order string from those fields appearing in lstSort. After the loop, if strOrderBy has length, I trim off the trailing comma-space combination and apply it to the calling form. Here’s the code, again with comments and error handling removed for easy perusal:
Private Sub cmdApply_Click() Dim strOrderBy As String, rstSorted As Recordset rst.Sort = "SortOrder" Set rstSorted = rst.OpenRecordset With rstSorted .MoveFirst Do While Not .EOF If !ListName = "lstSort" Then strOrderBy = strOrderBy & !FldName & " " _ & !AscDesc & ", " End If .MoveNext Loop End With If Len(strOrderBy) Then strOrderBy = Left(strOrderBy, Len(strOrderBy) - 2) frm.OrderBy = strOrderBy frm.OrderByOn = True End If Exit_Here: rstSorted.Close DoCmd.Close acForm, Me.Name Exit Sub End Sub
There’s some cleanup required to ensure that my sorting dialog has no impact on the application it’s used from. That cleanup code is in the OnClose event of my dialog, which closes the rst recordset and sets it to nothing. Once the dialog closes, my users see their form sorted as desired.
There are two minor caveats to this technique. My dialog depends upon the form having a control that can get the focus. So, if you’ve locked all the controls on your form for read-only purposes, you’ll have to add at least one control that can gain focus. Also, if used on a subform, the user must click in the subform first so that some control on the subform can gain focus.
I think both stipulations are a small price to pay to provide this handy functionality. To employ my technique in your databases, just import the zstblMultiFieldSorting table, the fdlgMultiFieldSorting form, and copy or write an entry point function as shown earlier. Don’t forget, you’ll need a customized menu to call the entry point function or some other way of opening fdlgMultiFieldSorting from your form.
“Hear ye! Hear ye! Hear ye! The court of King Requests doth recognize the return of Sir Worksalot.”
“Step forward, good knight,” spoke the king. “What say you? Have you found a way to sort the fields?”
“I have, m’Lord,” replied the knight. “Now, all of your people can sort their farms by multiple fields, both ascending and descending or any combination thereof.”
“Excellent!” replied the king. “I knew you could do it. Pray tell, what progress have you made spinning gold from straw?”
“I’m afraid I’ve had no success, m’Lord. I spent the morning and a half allotted on the sorting, and then some.”
“Off with his head!”