Databound Providing Partial Matching

Question:
I’m trying to create a DBCombo function on a form which operates similar to MS Access’s ComboBox. As the user types in an entry, character by character, the DBCombo’s list would show any/all partial matches (i.e., “AIR” yields “AIR”, “AIRCONDITION”, “AIRPLANE”) to that entry that were already in the table. As more characters were typed in, the list would get smaller, etc. (Very similar to the way Windows’ Help | Search functions…) The user could select an existing item on the drop-down list or use the new entry if it is unique.

Answer:
In the Change event for the dropdown, you’ll want to query your database for data LIKE what’s in the box. You can put ‘%’ signs around the criteria and it will work like a wildcard. For instance:

set ssTemp = db.CreateSnapshot(“SELECT Name from CUST where Name LIKE ‘%” & dbcombo.Text & “%'”)
I don’t think that is the correct syntax for DAO 3.0, but you get the idea. Then empty the combo box and refill it with the data from the snapshot. As you’ll probably realize, this will be a pretty slow process if you do it for each character. You could probably modify this to use a timer control, which starts each time the text box changes, and if the timer expires, then refill the list. I believe that’s the way MS Help works now.Alternately, if your combobox starts out with all the possibilities loaded, you could load a hidden listbox (with its sorted property set to TRUE) at the same time you initially loaded the combobox. Then all you’d have to do is search the hidden listbox for the partial matches, reloading the combobox from that.This would save all of the hits on your database which ought to speed things up a bit, especially if it’s a network app and the database lives on a different machine than the front end.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS