|
Get More From Your List Boxes |
|
|
|
|
Get More From Your List Boxes |
|
|
|
Dave Gannon Silver Collection
Dave Gannon provides three neat tricks that you can use on your next form provided youre familiar with the properties of the Access list box.
I like list boxes and find them tremendously useful. In this article, Ill show you three tricks that Ive 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 Accesswhile Visual Basic has a list box control, it lacks some of the properties that Ill 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: Its a lot easier to give a user the ability to multi-select with a list box than it is with a combo box.
Figure 1
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 forms Open event to initialize the string, but you could also use the text boxs 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 boxs "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 boxs 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 doesnt 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).
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 thatwhen passed the form name, list box name, and a column numberwould 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 arent 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 boxs 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 routines 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 boxs 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):
=SumListbox([Name],[lstSalaries].[Name],4)
Since a Visual Basic list box cant be multi-column, it doesnt have a Column property and couldnt 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 doesnt search. Still, the technique that I used does rely on the Access list boxs 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 isnt the first column. Well, actually, it isbut only just.
Figure 3
The list boxs 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 forms 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.
Your download file is called 004GANNON.zip in the file SA200004_down.zip
This is found in the Silver Collection at http://www.vb123.com/smart/
