Fast Lookup in FoxPro

[VFP search for 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] 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)
   *-- Remember the cursor position
   lnLength = .SelStart
   If Seek (lcTarget)
     .ForeColor = 0
     *-- Fill the rest of the postcode and ...
     *-- ... put the cursor back where it was
     .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.