Dave Gannon provides three neat tricks that you can use on your next form– provided you’’re familiar with the properties of the Access list box.
I like list boxes and find them tremendously useful. In this article, I’’ll show you three tricks that I’ve recently used with list boxes. All three of them demonstrate some of the ways that you can use the properties that are unique to the list box control. These only work in Access –while Visual Basic has a list box control, it lacks some of the properties that I’ll be demonstrating in this piece.
A searching list box
For instance, I was recently asked for a list box that would automatically find an item in a text box on the same form (see Figure 1). The user wanted to be able to type a name in a text box and also be able to select one or more names from a list. When the user entered the name in the text box, they wanted the corresponding name to be automatically selected in the list box. The functionality is very similar to a combo box with one important exception: It’s a lot easier to give a user the ability to multi-select with a list box than it is with a combo box.
My answer uses the KeyPress event of the text box to tie the text box and the list box together. When the user enters a character into the text box, the code in the KeyPress event searches the list box for a match. The code builds a string by adding valid characters (“a” to “z”) to a string as the user types them in. If the user presses the backspace key (ASCII code 8), the routine removes letters from the end of the string. After each keystroke, the code calls the CheckListbox routine, passing the current value of the string. In this example, I used the form’s Open event to initialize the string, but you could also use the text box’s GotFocus event:
Dim strStringTyped As String Private Sub Form_Open(Cancel As Integer) strStringTyped = "" End Sub Private Sub txtSurname_KeyPress _ (KeyAscii As Integer) Select Case KeyAscii Case Asc(" ") To Asc("z") strStringTyped = strStringTyped & _ Chr(KeyAscii) Case 8 If strStringTyped > "" Then strStringTyped = Left$(strStringTyped, _ Len(strStringTyped) - 1) End If End Select CheckListbox strStringTyped End Sub
The CheckListbox routine accepts the passed string and uses the Len function to find out how many characters it contains. The routine then uses a For…Next loop to find and set the entry in the list box whose first characters match as much of the string as has been entered:
Private Sub CheckListbox(strPass As String) Dim intCurrIndex As Integer Dim intStringLen As Integer intStringLen = Len(strPass) For intCurrIndex = 0 To lstPersons.ListCount - 1 If Left$(lstPersons.ItemData(intCurrIndex), _ intStringLen) = strPass Then Exit For End If Next intCurrIndex lstPersons = lstPersons.ItemData(intCurrIndex) End Sub
In this routine, I used the ItemData property of the list box. The ItemData property gives you the value of the list box’s “bound column.” An Access list box can have many columns, but only one is bound to a field in the Recordset underlying the form (the field specified in the list box’s ControlSource property). By using the ItemData property, I made sure that I was always searching the field that was updating the underlying record. While a Visual Basic list box has an ItemData property, its sole purpose is to store a numeric value associated with an entry in the list.
By the way, if the user enters a string that doesn’t match any of the entries, I select the item at the bottom of the list box. You might want to handle the situation differently.
Providing a sum for a list box
This was a quickie solution that I knocked off in answer to a request by one of our developers. The developer wanted a function that would provide a sum of one or more of the columns in a multicolumn list box (see Figure 2).
My solution was straightforward. First, I put a text box on the form to display the sum of one of the columns in the list box. I then created a function called SumListBox that–when passed the form name, list box name, and a column number–would return the sum of the values in the column.
After declaring its variables, the routine begins by getting references to the form and list box and checking to make sure that the column exists. If they aren’t found, the routine sets the text box to an appropriate error message and exits:
Public Function SumListBox(sForm As String, _ sCtrl As String, iColumn As Integer) As Variant Dim frm As Form Dim ctrl As Control Dim i As Integer Dim vSum As Variant On Error Resume Next Set frm = Forms(sForm) If Err <> 0 Then SumListBox = "ERR!FormNotFound" Exit Function End If On Error Resume Next Set ctrl = frm(sCtrl) If Err <> 0 Then SumListBox = "ERR!ListboxNotFound" Exit Function End If If iColumn > ctrl.ColumnCount Then SumListBox = "ERR!ColumnNotFound" Exit Function End If
Now that I know that everything is present, I initialize a variable and loop through the list box’s Column property, adding the values in the specified column. The Column property of the list box accepts two parameters: The first one specifies which column to use, and the second parameter specifies which row is to be used. The Column property numbers the columns from 0, so the second column in the list box is column number 1. Rather than force the routine’s user to perform those mental gymnastics, I subtract one from the column number passed to the routine before using it. That way the developer who wants to add up the second column can just pass my routine the number 2:
vSum = 0 For i = 0 To ctrl.ListCount - 1 vSum = vSum + ctrl.Column(iColumn - 1, i) Next i SumListBox = vSum End Function
The SumListBox function can be called from anywhere, but the developer that I wrote it for called it from the text box’s ControlSource property. To add the salaries in column four of a list box called lstSalaries, the ControlSource property would contain this code (the [Name] parameter picks up the name of the current form):
Since a Visual Basic list box can’t be multi-column, it doesn’t have a Column property and couldn’t run this code.
Search by the second column
This trick allows you to show one set of data, but search on another set. I wrote the routine for a list box that displayed customer last and first names, along with their addresses. The user wanted to be able to press the letter “b” and move to the first customer whose last name began with that letter. Once the user was there, they wanted successive presses of the letter “b” to move them through all the entries that began with that letter.
The Access list box already does that kind of search for the first column in the list box, of course. The problem was that the user wanted to type the first letter of the second column of the list box, which Access doesn’t search. Still, the technique that I used does rely on the Access list box’s ability to search on the first column, as long as that column is visible. However, if you look at Figure 3, the last name field isn’t the first column. Well, actually, it is–but only just.
The list box’s RowSource is set to a query based on the Persons table. This query retrieves four columns:
SELECT Person.Surname, Person.PersonRef, [Forename] & " " & [Surname] AS Fullname, Person.Address FROM Person ORDER BY Person.Surname;
In the list box, I set the column count to four and bound the second column of the list box (the primary key of the Person table) to a field in the form’s Recordset. I set the first column to be just .015 cm wide, and the second column not to appear at all. The result was that the surname field, while still the first column and available to search on, was just too small to see. The second column updated the record but was invisible. The third and fourth columns with the full name and address were all that the user could see in the list box.
As I said, I like list boxes and I really like the Access list box. These techniques will let you make this very useful tool do some crafty tricks.