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!