A slight variation on Retrieve cities from a database post by Pamela Denchfield‌ where Pamela uses a dropdown to create a cascading dropdown filter on a SQL Request control.

We have a lonnnnnnnngggggggg list of suppliers who our users can select from when they want to request a new product.

We've cleansed this list as best we can but it still numbers thousands and it's not fun for our users to have to scroll through the dropdown to try and find the needle in the haystack. Because there's no set choice of parent values that would allow us to do a cascading dropdown, we want to let our users search for the suppliers.

First add a text box to the form and give it a name, in our case I'm it "SupplierSearch".

Then we simply add our SQL Request Control to our form

and fill in the db details

The important part for us is the Query.

select name from vw_sp_CleansedSuppliers WHERE name LIKE '%SupplierSearch%'‍‍

We're just using the LIKE operator and wildcards either side of the reference to our text box to give our users the best chance of returning results.

So now the user can type a value in the text box and then select the dropdown box and get a much more succinct list of values.

The ability to define our SQL query with this control and insert references from our control gives us very valuable and flexible tool for presenting users with SQL data.

I'd be interested to hear of any other techniques people are using on this control to make their users life easier.