Database development and training for Birmingham and the midlands
Specialists in Microsoft Access and Visual Foxpro database training and development
I am happy to hear from existing clients but I am taking no new calls.
Fast Lookup in FoxPro
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.
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:
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.