[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6260
  • Last Modified:

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.

Thanks!
0
neilanjk
Asked:
neilanjk
  • 6
  • 4
  • 3
  • +2
1 Solution
 
Stephen_PerrettCommented:
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:
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=-
0
 
ki_kiCommented:
you don't need code for the combo box, just created using the wizard...it will ask you to either enter data or use a look up table
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Stephen_PerrettCommented:
As  ki_ki suggests,

Use the wizard

See how the combobox functions basically.

It is possible to add a feature that allows you to directly add new data to the combobox list. This has to be added with extra coding, but see how the combo works first

Steve
0
 
Jeffrey CoachmanCommented:
neilanjk,

I played around with this for a while.

I have something that does what you want.....

But it is Brute Force and not very elegant.

But it does work!

If you are interested, I will post it.
0
 
Jeffrey CoachmanCommented:
neilanjk,

Whew!

Here is what I came up with.....

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_AfterUpdate()
    'Requery the ListBox
    Me.lstDisplayNames.Requery
End Sub

On the "On Change" Event of cboSearchNames, 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:

Private Sub btnClear_Click()
    'Clears the SearchNames ComboBox
    Me.cboSearchNames = ""
    'Requery the ListBox
    Me.lstDisplayNames.Requery
    'Highlights the Combobox
    Me.cboSearchNames.SetFocus
End Sub

Close the Visual Basic Editor
Save the form

Go back to Form View

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.


Let me know if this helps.

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

Steve
0
 
neilanjkAuthor Commented:
Boag2000, I will give this a try and get back to you...thanks...Jeff
0
 
neilanjkAuthor Commented:
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.

Let me know, either way and I will accept your solution.

Thanks again, Jeff
0
 
Jeffrey CoachmanCommented:
Thanks!

If you look at the time difference between my two posts, you can figure out how long it took me!!
DateDiff("h",[Post1],[Post2])

The notes are written in a certain manner because I am a Teacher by trade.

I thougt about "Sweetening" it up a bit, but I had a headache already and I was getting sleeeeeepy!
:)
(There are tons of posts here, on how to add an Item to the combobox automatically)

I just wanted to see:
1. If it could be done (it sounded like it could)
2. If "I" could do it!
3. If it would be an Acceptable solution.

Just transplant it into your Application and have fun!

Glad I could help!!!
:)
0
 
neilanjkAuthor Commented:
boag2000, Wow....you spent a bunch of time on this.  Thanks, for your help.  I am well on my way to adding the enhancements.  If you ever take this challenge any further, let me know.  Thanks, again, Jeff
0
 
Jeffrey CoachmanCommented:
Thanks, I probably will.

I am getting closer to "Master" level, when achieved I will put my email in my profile.

Take care!
0
 
jdks2006Commented:
Jeffery,

Either I did something wrong or it won't work with Access 2013...
0
 
Jeffrey CoachmanCommented:
post a database of what you tried and I'll have a look
0
 
Jeffrey CoachmanCommented:
FWIW, ...this works fine for me in Access 2013
NameSearch.accdb
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 6
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now