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:
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
