Ask the MultiValued Visual Basic Expert - #4

(as published in Spectrum magazine)

updated March 11, 1997

To email your questions to "Ask the MultiValued VB Expert", click here.
Copyright 1996-97 Caduceus Consulting. All rights reserved.

Searching a List as the User Types

Many commercial packages have a real nice way of looking up information, searching through a list of options as you type and narrowing the possibilities as you enter more letters. How can I do the same thing for a client name list? What if my client list is on my MultiValue host? - R. McLaughlin, Keystone Mercy Health Plan

This is an excellent question, since it allows us to look at a perfect example of where client/server technology can really go to work for you. So many legacy applications demand that you type in a Customer Code or a Part Number - as if anyone could memorize such things or look them up while you have a client on the phone placing orders or requesting information. The usual solution is to enter the first few characters and then hit Enter or press some escape key so that a search will take place and a list may be presented. That kind of solution is okay when all of the users have to share the same processor, but it is does not take advantage of the processing power of an independent client PC.

I’m going to divide my solution to this problem into two parts: The Visual Basic code to perform automatic lookups; and a possible scheme to implement this functionality when your data resides on a remote host.

Visual Basic Auto-Search

We begin with a ComboBox, since that is Windows’ best control for combining the typing of text with the storage of a lookup list all in the same control. As we type, we want the system to zip through the list, showing the first match for the letters that we have already typed. At any point we should be able to switch from typing to just drop down the list and see the possible entries, starting with that first match.

We use the ComboBox’s KeyPress event, because we want the system to automatically do the work everytime the user presses a key. Also, since this code is highly reusable, I have put it in a General procedure that can be called from anywhere in the project:

Sub Combo1_KeyPress (KeyAscii As Integer)
    AutoSearch Combo1, KeyAscii
End Sub

The procedure will take what the user has typed so far and then locate the first match by adding that text as an entry to the list, noting where it was added, and then removing it again. As cumbersome as this may sound, on a PC it all takes place much faster than you can type. Note that the Sorted property of the ComboBox must be set to True!

Here is my General procedure:

Sub AutoSearch(MenuBox As ComboBox, KeyAscii%)
Static ActualTyping$
With MenuBox
If .ListCount = 0 Then
    ActualTyping$ = .Text & Chr(KeyAscii%)
    Exit Sub
End If
If (KeyAscii% < 32) Or (KeyAscii% > 126) Then
   
' Not a normal character...
    Start% = .SelStart
    If KeyAscii% = 8 And Start% = Len(ActualTyping$) And Start% Then
       
' Special processing for Backspace...
        .SelStart = Start% - 1
        .SelLength = 9999
        ActualTyping$ = Left(ActualTyping$, .SelStart)
    Else
        ActualTyping = ""
        Exit Sub
' no auto-search
    End If
Else
    ActualTyping$ = Left(ActualTyping$, Len(.Text))
    If .SelStart <> Len(ActualTyping$) Then
       
' Not appending to end of typing...
        ActualTyping$ = Left(.Text, .SelStart) & Chr(KeyAscii%)
    Else
        ActualTyping$ = ActualTyping$ & Chr(KeyAscii%)
    End If
End If
KeyAscii% = 0
Entry$ = UCase(ActualTyping$)
.AddItem ActualTyping$
NewNdx% = .NewIndex
.RemoveItem NewNdx%
If NewNdx% <> 0 Then
    If UCase(.List(NewNdx% - 1)) = Entry$ Then
        .ListIndex = NewNdx% - 1
' perfect match
        Exit Sub
    End If
End If
If NewNdx% = .ListCount Then
' no match
    .Text = ActualTyping$ ' reset
    .SelStart = 9999
Else
    If UCase(Left(.List(NewNdx%), Len(Entry$))) = Entry$ Then
        .ListIndex = NewNdx%
' match so far
        .SelStart = Len(Entry$)
        .SelLength = 9999
    Else
        .Text = ActualTyping$
' reset
        .SelStart = 9999
    End If
End If
End With
End Sub

Looking Up Data on Remote Host

If the list that you want to search is stored on a remote MultiValue host, then we have a problem: How do you select up the possible choices and load them into the ComboBox on the PC without sending the user off for a coffee?

To overcome this obstacle, let us break it down into smaller problems: (1) Getting the data; and (2) reducing the choices to a loadable list for our ComboBox.

One solution to getting the data is to have it on your PC in the first place. Remember that all you need is the actual index text to search on, such as client name, part description, whatever, and a key to get the rest of the details off the remote host. If you have a number of PC’s networked together, you can store the index text on a central server which is the next best thing. Update the index from the data on your remote host as often as is necessary for your situation: daily, weekly, you choose. If you have middleware that allows you to read an index off your remote host at an acceptable speed, then of course you can do that, and keep all your data together. With TCP/IP connections, I frequently keep the index and the full data in sync all on the remote host.

If you use integer keys, you can use the ComboBox’s ItemData property to store the corresponding keys. Otherwise you will need a separate array variable.

The other problem was how and when to load the choices into the ComboBox. Visual Basic can run into certain limitations with string space, and I believe that at best a ComboBox is limited to 64K characters of contents. The load time can vary from about 1 second for the first 300 entries, to 5 seconds for last 300 of about 3000. If you have less than 1500 entries you could probably load them into the ComboBox in the Form_Load event without too much delay. Once they are there, the search will happen as fast as the user can type.

If you have more than 1500 entries, you would have to load smaller lists on the fly. In other words, the user would type the first 1, 2, or 3 letters before the load and autosearch begins. How many letters thay have to type depends on how long your lists are. Each list should contain no more than about 200 entries if you are loading while the user is in the field.

The following table shows the real-world results of such divisions for a typical client name database:

# of chars # of sub-indices Total # of entries
1 25 4000
2 700 100,000
3 1100 160,000

Let's look at an example of how this all works:

Example
You have a client file on your MultiValue host called CUST with 20,000 items. The client name is in attribute 3. Your PC is connected to the host by a serial link only. You have the ability to read a client record from the host, given the item ID (which is an integer). From the previous table, by grouping them into lists by the first 2 letters of the name, you should end up with about 700 lists, all with less than 200 entries.

Step 1
Create an index file CUST.NDX on the host and run the following Host BASIC program:

OPEN "CUST" TO CFILE ELSE STOP
OPEN "CUST.NDX" TO IFILE ELSE STOP
SELECT CFILE
10 READNEXT CKEY ELSE STOP
READV NAME FROM CFILE,CKEY, 3 ELSE STOP
IKEY = OCONV(NAME[1,2],"MCU"):".TXT"
READ LIST FROM IFILE,IKEY ELSE LIST = ""
LIST<-1> = NAME:",":CKEY ;* assumes no ","s in NAME!
WRITE LIST ON IFILE,IKEY
GOTO 10

Step 2
If you are keeping the index on a PC, import the items from CUST.NDX (using a terminal emulator or other software) into a directory on your PC as separate flat files:

CUST.NDX AB.TXT >>> c:\ndx\ab.txt ...etc.

Step 3
Add the following code to your Visual Basic form:

Sub Combo1_KeyPress (KeyAscii As Integer)
CurText$ = Combo1.Text
Start% = Combo1.SelStart
Select Case Start%
Case Is < 1
    If Combo1.ListCount Then
        Combo1.Clear
        Combo1 = CurText$
        Combo1.SelStart = Start%
    End If
Case 1
    ' Read the index keyed by Combo1.Text
    ' and load entries into Combo1...
    FileNm$ = "c:\ndx\" & Combo1 & Chr(KeyAscii) & ".txt"
    Combo1.Clear
    On Error Resume Next
    Open FileNm$ For Input as #1
    If Err Then Exit Sub
    Do While Not EOF(1)
    Input #1, CName$, CKey%
    Combo1.AddItem CName$
    Combo1.ItemData(Combo1.NewIndex) = CKey%
    Loop
    Close #1
End Select
AutoSearch Combo1, KeyAscii
End Sub

Sub Combo1_LostFocus
If Combo1.ListIndex <> -1 Then
    CKey$ = Combo1.ItemData(Combo1.ListIndex)
    ' <Read the item.>
Else
    ' <New item.>
End If
End Sub

To email your questions to "Ask the MultiValued VB Expert", click here.
Copyright 1996-97 Caduceus Consulting. All rights reserved.
Revised: March 11, 1997.

Return to Caduceus Consulting Home Page

Copyright 2006 intellact
Last modified: Thursday May 25, 2006