Tuesday, March 13, 2007

Creating Google Suggest Style Text Boxes with Visual Basic 2005

I have created a simple tool that makes specific directories for our company when we get a new project in to scan. I have integrated a database to keep the information for searching and billing purposes. When we get updates to the projects I don't want to type everything again, so I looked into changing the text boxes to suggest project names from the database and such.

It really is very simple to do this.
Set the "AutoCompleteMode" of the text box to SuggestAppend (There are other options but this is the one that worked best for me.)

Set the "AutoCompleteSource" to "CustomSource".

In the "Form1_Load" add in the appropiate commands. I will explain this line by line below.

Create a connection to the database.
Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\databases\scanning.mdb")

Open the Connection

Use SQL here to select only unique (DISTINCT) Project Names and Scanning Numbers from the workflow table. Use the open connection above to execute the command.
Dim MyCommand As New OleDbCommand("SELECT DISTINCT Project_Name, DR_Number from Workflow", MyConnection)

Create a data reader and put the data returned from the connection in to it.
Dim MyReader As OleDbDataReader = MyCommand.ExecuteReader()

While the reader is being read populate the Text boxes "AutoCompleteCustomSource" with the unique data returned. The (0) and (1) is because the SQL command above puts the results of the query into an Array. This extracts just column "0" or "1" from the query array.

While MyReader.Read()
End While

There may be a better/more efficient way to do this task, but with my simple programming skills it works for me and is very responsive!

No comments: