Fast Lookup in FoxPro
![[VFP search for a postcode]](../media/images/b4.gif "Lookup form with partial match of a postcode")
If you want to let users make a fast choice from values in a table then you would
normally set that table as the RowSource of a combo box or list box. Either of these
controls will work well until you need to retrieve information from a
large table and then they get very slow. The technique described here lets the user
choose from a table holding a million and a half UK postcodes at a speed fast enough to
deal with telephone enquiries.
As the user starts to type the postcode into the textbox the search
mechanism finds and displays the address of the first entry which is a
partial match to the characters entered so far. The illustration
shows the situation after the user has typed the first two characters 'B4'.
The first full postcode matching this partial entry is 'B4 6AA' and the
full address of that postcode is being displayed.
![[VFP form with Incorrect postcode]](../media/images/b41.gif "Lookup form with non-existent postcode")
If the user carries on with a '1' then the search fails because no
UK postcode has 'B41' as its first three characters. The address textbox
changes to read 'Not found' and the postcode itself turns red.
As the user backspaces to delete the '1', the address reverts to
showing the last good entry matching 'B4' again.
The automatic fill of the postcode textbox and the display of the
matching address happen as quickly as the user can enter characters
into the textbox. By comparison, a combobox populated with the million and
a half postcodes took minutes to load and ran very slowly indeed.
How to do it
The code to achieve this effect is surprisingly simple. The form itself
opens the PostCode table and sets it into PostCode order. The rest of the
processing is performed in the InteractiveChange method of the textbox:
With This
lcTarget =
Left(Alltrim(.Value),.SelStart)
lnLength =
.SelStart
If Seek
(lcTarget)
.ForeColor
= 0
.Value
= postcode
.SelStart
= lnLength
.Parent.lblAddress.Caption
= address1 + ;
Chr(13) + address2 + Chr(13) + town
Else
.ForeColor
= 255
.Value
= lcTarget
.SelStart
= lnLength
.Parent.lblAddress.Caption
= 'Not found'
Endif
Endwith
All this is very much easier if you are using Visual Foxpro 9. Textboxes
in VFP 9 have the new
AutoComplete
property which gives much the same effect without the need for any coding
at all.
Workshop course
This particular solution came up during a
workshop
course where the delegate had a need for a fast and easy way of
retrieving data.
During that course we went on to convert the textbox into a self-contained
control and saved it as a class. This class has properties such as
cTableName, cOrder, cSearchField and cDisplayField so that its behaviour
could be defined at design time. It opened and managed the lookup table
itself and could be dropped onto any form in order to provide a fast
lookup facility with no further programming.
|
