How to add an auto-complete textbox control to an MS Access Form?

Hi, in Access 2000, as text is typed into a text control, I would like to display a list of matching words populated from a 'lookup' table. Each time another character is typed, the list should be filtered accordingly. At any point, the user should be able to select from the list of matching words. Ideally, the same function should apply to every word that is typed in the text box, i.e., every time a space is detected. Alternatively, the selected word could be appended to a string in another textbox.

I have found similar functions for VB 6.0 and VB.net, but they don't seem to translate to MS Access.

The textbox should not flicker as each letter is typed.

Here's an example of what I am looking for:

If the lookup table contains the following words "Ape", "Apex", "Apple", if the user types "A", all three words would appear in a select list. When the user adds the letter "p", i.e., "Ap", all 3 words still display. When the user adds the letter "e", i.e., "Ape", only "Ape" and "Apex" appear. The user may then select from the list to chose "Apex". A space key is pressed and the user types "a", and again all 3 words would be displayed.

The simplest answer to this is to use a combobox instead of a text box.
you won't get everything you want with that but access comboboxes work very well
You may need to investigate the NotInList event if you need to add new items.

Steve

0

neilanjkAuthor Commented: 2005-04-25

Steve, I considered a combobox. If there's code to make it function as described above (more-or-less), that might serve the purpose. j=-

Lets use the Northwind Sample database, just to test this out.
(You might want to copy this and print it out, so it is easier to follow. It’s long, but easy to follow)
(I tested it myself)

Create a blank form in Design View
Drop in a Combobox (Hit cancel if the Wizard opens)
Drop in a Listbox (Hit cancel if the Wizard opens)
Drop in a small Command button (Hit cancel if the Wizard opens)
Name the combobox "cboSearchNames"
Important!!!! Set the combobox's "AutoExpand" property to NO!
Name the listbox "lstDisplayNames"
Name the button "btnClear"
(We can set the Labels and Captions later; I just want to see if this does what you want)
Close this form
Save the form with the name: "frmSearchNames"
.

Create a query in Design View
Select the "Customers" Table
Drop in the "ContactName" Field
Sort: Ascending
(Run the query and see that it displays all the Contact Names)
Save the query with the name: "qrySearchNames"
Close the query

Now create another Query that is identical to the first. (But we will give it a different name)
Create a query in Design View
Select the "Customers" Table
Drop in the "ContactName" Field
Sort: Ascending
(Run the query and see that it displays all the Contact Names)
In the Criteria section put the following:
Like [Forms]![frmSearchNames]![cboSearchNames] & "*"

Save this query with the name: "qryDisplayNames"
Close the query

Re-open the form "frmSearchNames"
Go into design View
Right-Click "cboSearchNames" and choose "Properties"
Set the "Row Source" property to: "qrySearchNames"
On the "After Update" Event, put the following code:

Private Sub cboSearchNames_Change()
'Moves the focus to the List
Me.lstDisplayNames.SetFocus
'Moves the focus back to the combobox
Me.cboSearchNames.SetFocus
'When a control recieves the Focus it selects
'the entire value, so if you typed anything, it would
'overwrite it.
'This puts you bask in "Edit" mode
SendKeys "{F2}"
'Requery the ListBox
Me.lstDisplayNames.Requery
End Sub

Close the Visual Basic Editor
Save the form

Right-Click "lstDisplayNames" and choose "Properties"
Set the "Row Source" property to: "qryDisplayNames"
Save the form

Now, about hitting the "Space bar" to clear out the combobox. You should never make users hit the spacebar to clear a field. Hitting the spacebar in a highlighted field gives the "Appearance" of clearing the field. When in fact you are inserting a "Space" character into that field! (I always cringe when I see users doing this!)

So I’ll use the button to clear the field.

Right-Click "btnClear", and choose Properties
Go to the "On Click" event and insert the following code:

The combo box should be blank
The list box should Display all the names.
Type "A" into the comboBox
All the names starting with the letter "A" will be displayed (Even though the list box may be to small)
Now type an "l" ("l" looks like a "One" (1), but is really a lowercase "L")
Now only, "Alejandra Camino" and "Alexander Feuer" should be displayed
Now type in "ex"
"Alex" should be displayed in the Combobox and only "Alexander Feuer" should be displayed in the Listbox
(Is this what you wanted?)
Click "btnClear"
The combobox should clear and the List box should display all of the names
(Try this with the people whose name starts with "J", they are more fun!)

As an added bonus, users can just click the dropdown arrow on the combo box and select the Name.

BUT, If you want users to be able to Click a name from the List box and insert it into the combobox, insert the following code on the listboxes "On Click" event:

Private Sub lstDisplayNames_Click()
'Puts the value of the Listbox into the combobox
Me.cboSearchNames = Me.lstDisplayNames.Value
'Selects the combobox
Me.cboSearchNames.SetFocus
End Sub

Save and test!

Whew!

Now all you have to do is write your code for what to do with the Selected value in the Listbox.

Wow boag2000 some hefty work there! I'll have to have a look at it some time too

Steve

0

neilanjkAuthor Commented: 2005-04-26

Boag2000, I will give this a try and get back to you...thanks...Jeff

0

neilanjkAuthor Commented: 2005-04-26

Boag2000....this is actually quite ingenious. Using SendKeys "{F2}" and the Like [Forms]![frmSearchNames]![cboSearchNames] & "*" and setting the Autoexpand property to No are key to this approach. Nice job.

What would make it even 'sweeter' would be this (A mouseless approach to selecting the desired item would be key):

1) user narrows the list using the combobox
2) using the up and down arrow keys selects an item from the listbox
3) press the Enter-key to append the selected item to a string in another text box (therefore, building a string of words).

If the user typed a word that was not in the list, perhaps Ctrl-Enter or Shift-Enter could be used to append the word in the Combo box to the string of words.

I can probably figure this out, unless you have some other tricks up your sleeve.

Featured Post

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.