Updating drop-down list of combobox based on it's own content

[rockman]

Is it possible to update the drop-down list of a combobox as the user types within the combobox.

e.g. Combobox is used to enter employee name. As user types "A", all employees who's name starts with "A" are displayed in the drop-down portion of the combo-box. Then when user types an "n", only employees who's name starts with "An" are displayed.

I searched the archieve and could find no help.

Thanks,
Jeff

 

[DBL]

Open the form in design view and right click the combo box, select Properties and select the Data tab, There's an option to Autoexpand, select Yes in the combo box and that should be you.

 

[vangogh228]

You may have to write a query that querires the existing entries from the underlying table field. Make sure that the query property for unique entries is set to yes. Then set the form field's record source to the query. Now, as I remember, the autocomplete feature from the dropdown that you are talking about requires that the record source property "Limit to List" property has to be set to 'yes.' If you want to be able to make new entries and have them become items on the menu, I believe the autocomplete feature will not work.

 

[rockman]

Thank you for your comments.

The AutoExpand is on. The Limit To List is on.

A query for the Rows Source is the way to reduce the down-down list items to just those meeting the criteria that the user has typed in. My query works in design mode (being based on the combobox itself [cmbEmployee.Text]). Here's where I am stuck.

Private Sub cmbEmployee_Change

     cmbEmployee.Dropdown
     cmbEmployee.Requery           <----- Errors here

End Sub

On the REQUERY line I generate an error saying "You must save the current field before you run the Requery action". Does anyone know what this error means? And what I need to do to get by this?

Thanks,
Jeff

 

[Alexandre]

I am still not clear why you do not seem to consider that the Autoexpand property matches your pb?

However, the error is because the user has began to input a value when you requery the comobox, then forcing the commitment of that value. One plausible solution would be to save the entered and uncommitted value, blank the combo, requery, re-fill the combo with the saved value.

Something like (not tested)

Dim strValue as String

strValue = Me.Controls("cmbEmployee").Text
Me.Me.Controls("cmbEmployee").Undo
Me.Controls("cmbEmployee").Requery
Me.Controls("cmbEmployee") = strValue
Me.Controls("cmbEmployee").Dropdown

Tortuous? Yes. That is why you should see if the Autoexpand feature answers your need before anything else.

 

[ABlair]

THe error occurs because you are trying to requery a combo box that is being edited. The code that was suggested by alex seems ok.

However, yesterday when I did this, I had to add the code the not in list and as well as after update. Further more you will have to lose focus on the field and come back to it.