Combo box recordsource requery

[scubadiver007]

I have a recordclone combo box for navigating between 240 company records, so the form will go to the correct record on updating the combo.

Instead of clicking on the down button and scrolling through the list, I would like to enter letters into the box and update the record source automatically as I enter them.

I'm not sure what events to use since there appears to be very subtle differences between them (eg between 'on dirty', 'on change' and 'before update')

So when I type in 'T', only those companies that start with 'T' are viewed in the combo box.

I have discovered the VBA 'dropdown' command which would be useful for the user.

company.dropdown

Table_Company

Company_ID: autonumber
Company_name: text

The name of the form is "form1" and the combo box name is "company".

The record source for the combo box is "query_company". In the form the ID field is hidden and the company name is not hidden.

The criteria I have for the combo would be something like this.

SELECT Table_Company.Company_ID, Table_Company.Company_name
FROM Table_Company
WHERE (((Table_Company.Company_name) Like [forms!]![form1]![company] & "*"));

Any useful pointers?

 

[pbaldy]

I would simply use the Auto Expand property of the combo. To do what you're describing, the Change event would be the only appropriate event, as it's the only one that fires with each keystroke. You would also have to use the .Text property of the combo, as the default .Value property isn't updated yet in the Change event.

I use the Dropdown you mentioned and Auto Expand so the user can see where they are in the combo, rather than trying to manipulate its source.

 

[CJ_London]

Ensure the column which is displayed in the combobox (not the dropdown) is sorted ascending

 

[scubadiver007]

So I'm just using the dropdown command on the "key down" event and when the first letter is entered it will go to the first record starting with that letter.

I can now see it wouldn't be possible to update the source as I thought I might.