I have a combo box with an SQL query (Select [Skill ID] from [Skill Master]
as the rowsource, with values like:
Access 97, ADA, C++, C++ Use, COBOL
The default value for the combo has been set to '*' so that all these values show up in the list initially. But, I want to go a step further and be able to filter/show values from this list. So that when the user types something like C*, the list should show C++, C++ Use, COBOL. For C+*, list shld show only C++, C++ Use and so on.
(1) Unfortunately, the combo box does not have a Filter property, so that cldn't work.
(2) I also tried to set the Rowsource of the Combo as follows:
Private Sub Skill1_Change()
Skill1.RowSource = "SELECT [Skill Master].* FROM [Skill"
Master]WHERE [Skill Master].[Skill Id] LIKE '"
& Skill1.Text & "'; "
End Sub
(Pl. assume that syntax is right, cldn't fit the code to look legible, so removed some & _ etc...)
But this slows down the search tremendously, since the actual list is pretty large, and this query is bad performance-wise due to use of LIKE and wildcard chars(but I can't help that), and to worsen matters, this event happens on every keystroke.
(3) So, I put the SQL in the MouseUp event instead, adding a "Skill1.dropdown" statement at the end, since w/o it, everytime the users types something and clicks the drop-down arrow, the dropped-down list does not stay. So, this solves the problem crudely, since then the list appears to drop-down twice...
So, is there a better solution out there????
Thanks!
as the rowsource, with values like:Access 97, ADA, C++, C++ Use, COBOL
The default value for the combo has been set to '*' so that all these values show up in the list initially. But, I want to go a step further and be able to filter/show values from this list. So that when the user types something like C*, the list should show C++, C++ Use, COBOL. For C+*, list shld show only C++, C++ Use and so on.
(1) Unfortunately, the combo box does not have a Filter property, so that cldn't work.
(2) I also tried to set the Rowsource of the Combo as follows:
Private Sub Skill1_Change()
Skill1.RowSource = "SELECT [Skill Master].* FROM [Skill"
Master]WHERE [Skill Master].[Skill Id] LIKE '"
& Skill1.Text & "'; "
End Sub
(Pl. assume that syntax is right, cldn't fit the code to look legible, so removed some & _ etc...)
But this slows down the search tremendously, since the actual list is pretty large, and this query is bad performance-wise due to use of LIKE and wildcard chars(but I can't help that), and to worsen matters, this event happens on every keystroke.
(3) So, I put the SQL in the MouseUp event instead, adding a "Skill1.dropdown" statement at the end, since w/o it, everytime the users types something and clicks the drop-down arrow, the dropped-down list does not stay. So, this solves the problem crudely, since then the list appears to drop-down twice...
So, is there a better solution out there????
Thanks!
