How to Implement incremental search in MS Access 2000

[mpoincare]

Hi Everyone,
I want to know if anyone has some sample code available about how to implement incremental search in ms access 2000. Like in the windows application help mode. There is a textbox on top of list box. As you type into the textbox the corresponding set of characters is selected in the listbox. Thank you!

[olivthill]

You can have an editbox and a listbox below. When the user fills the editbox, the listbox is updated in real-time. The user will select an item by clicking in the short list of the listbox. See images below.

:o The first trick is to get the value of the editbox, on the fly, before waiting for the "enter" or "tab" key.
A poor solution would be to intercept the keys with the OnKeyPressed event. The problem is that you have to concatenate characters, to manage the backspace and delete keys, without talking about the home and end keys, mouse clicks, etc.
A better solution is the use of the OnChange event. The problem is that Access does not fill the "value" property of the field until the "validation" of the field. Fortunately, Access keeps the temporary value of the filed in the "text" property of the field.

:o The second trick is to update the listbox as soon as a new character has been typed in the editbox.
A simple solution is to use associate the listbox with a query, previously saved in the database.
That query takes an argument from a field in the form. The problem is that a query can use only the "value" property of an editbox, not the "text" property. So, I have chosen to have a hidden editbox whose "value" property will contain the "text" property of the editbox used by the user.

That's it. Here is my example. There is a table of words, a query for getting words in the table, and a form for the user interface, with a few lines of VB code.

:) The table I use in this example is called Tb_words.

:) The query, saved in the "query" section of the database, is:

SELECT [Tb_words].[a_word]
FROM Tb_words
WHERE a_word Like [Forms]![F_Semi_Auto]![E_criteria] & "*";


If the "F_semi_auto" (or whatever name) form is not currently loaded and you run the query, you are prompted for a value.
But if it is loaded, then the query fetches the field in the form without asking the user.

:) The form, named F_semi_auto, is in a simple mode with an instantaneous recordset. It has three fields:

1. An editbox where the user type the beginning of a word
Name: E_src_lookup
OnChange: EventProc

2. A listbox containing the list of words starting with the letters in the editbox.
Name: Lst_lookup
RowSourceType: Table/Query
RowSource: Q_Semi_Auto
OnClick: EventProc

3. A hidden edit box containing the beginning of a word
Name: E_criteria


:) The VB script is:


Private Sub Form_Load()
E_src_lookup.Value = ""
E_criteria = ""
End Sub

Private Sub E_src_lookup_Change()
Me.E_criteria.Value = Me!E_src_lookup.Text
Me!Lst_lookup.Requery
End Sub

Private Sub Lst_lookup_Click()
MsgBox ("Item selected #" & (Me.Lst_lookup.ListIndex + 1) & _
": " & Me.Lst_lookup.Column(0, Me.Lst_lookup.ListIndex))
End Sub

[olivthill]

Here is the database of the example.