Choosing a record in a combo 1

[malbreht]

Hi!

I've got a combo-box in a form (gets data from a query), where the user has to select one employee. When testing it with 10 employees, everything works just fine.

However, the user will actually have to choose between 100 or even more employees. That's why I want to give him/her the chance to only choose from a part of all the employees.

Here's how I imagine it:
1. When the form opens, the combo is blank and there are all the employees available. (works)
2. As entering first letter of one surname in the combo, the list shortens and displays only employees, that have that first letter in their surname.
3. The process of shortening the list continues while the user is typing next letters.

To be even more specific, here's the example:

1. (blank); Mayer, Meyer, Meyinger, West, Cent
2. "M"; Mayer, Meyer, Meyinger
3. "Me"; Meyer, Meyinger

Many thanks!

 

[Remou]

You will need a listbox, lstNames, and a textbox, txtSurname, for this idea. The row source of the listbox is:

SELECT tblPersons.PersonKey, [Surname] & ", " & [Forename] AS PersonName, IIf(Not IsNull([DelDate]),"Deleted") AS Deleted FROM tblPersons WHERE (((tblPersons.Surname) Like [forms]![frmAddPerson]![txtSurname].[text] & "*"));

The above is a sample from my database, so you will have to change the names of fields and form and controls to suit. I have left in "Deleted", which is taken from a DelDate field in the persons table. This field is by no means necessary, but depending on your application, it may be useful to know if a person used to exist.

Some code for the textbox:

Private Sub txtSurname_Change()
    Me.lstNames.Requery
End Sub

 

[ycim]

Check to make sure your "autoexpand" property (under DATA) is set to YES. This should make it go automatically for you.

 

[malbreht]

Thank you Remou for such a quick response...I'm just testing it, but you get the star in any case, of course!

 

[Remou]

malbreht

Thank you for the star, I hope you did not miss ycim's very relevant post when looking at my more elaborate, and perhaps, unnecessary, code.

 

[malbreht]

Well, ycim's suggestion works a little bit different...it highlights and jumps to the most relevant row, but the drop down menu itself doesn't shorten.

Your suggestion Remou works perfectly!

I'll test both suggetions and then decide...

Thanks to both of you!

 

[bez999]

I have a need to do this kind of thing too and using a listbox/textbox combination the listbox only updates when I click out of the textbox. Even then I could only get it to work using the AfterUpdate event. The OnChange event seemed to give the textbox content before the keypress. I used Debug.Print statements to check which events were firing.

What I REALLY want to do is use this technique on a continuous form with a textbox for the selection in the form footer.

Is it possible to force the listbox to requery at every keypress without having to press ENTER?


Regards
Chris Bezant