I recently had a project which required me to populate the strings collection in an AutoComplete control from data contained in the database. I wanted to take a second to outline how I populate the control’s strings, as well as create a reusable script to simplify the process in the future.
The FillAutoCompleteString method I’ve created is pretty simple. It accepts parameters for the control name, as well as Select, from and where clauses to build a SQL statement. (The Where parameter is optional, but you do need to pass at least a blank string.)
The method simply loops through a recordset based on the SQL parameters provided, and writes each item to the autocomplete control.
Sub FillAutoCompleteString(ByRef objControl, ByVal strSelect, ByVal strFrom, ByVal strWhere)
Dim objNames
Dim sSql
objControl.Strings.Clear
Set objNames = CreateObject(“ADODB.Recordset”)
With objNames
Set .ActiveConnection = Application.GetNewConnection
.LockType = 1 ‘adLockReadOnly
.CursorLocation = 3 ‘adUseClient
.CursorType = 0 ‘adOpenForwardOnly
sSql = “select ” & strSelect & ” from ” & strFrom & ” product”
If Len(trim(strWhere & “”)) > 0 Then
sSql = sSql & ” where ” & strWhere
End If
.Open sSql
While Not (.BOF or .EOF)
objControl.Strings.Add(.Fields(strSelect).Value & “”)
.MoveNext
Wend
.Close
End With
Set objNames = Nothing
End Sub
I put together a simple test form to check out the functionality. After adding a “Select” and “From” value, clicking the Fill AutoComplete Strings button will add those items to the Autocomplete control. The user will now be presented with a list of potential items, based on the characters they’ve entered.
Obvously, querying the database in this way can be slow if dealing with an exceptionaly large database, so keep that in mind. Otherwise, feel free to use this code in your own implementations. Thanks for reading! [:)]
