Login / Register  search  syndication  about

          Jason Buss' Blog

Jason Buss on SalesLogix development & customization, SQL, and more.

Populating AutoComplete Strings

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!  Smile

  Attachment: Test AutoComplete.zip

What's This?
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.



Related Content
   Picklist Column types - Dispaying ticket status in the SalesLogix LAN datagrid
I recently had to build a datagrid displaying ticket information, including the current Status value. Un
Posted on Aug 24, 2010 by Jason Buss to Jason Buss' Blog
 
   Useful Functions from the SLX_Common script (SalesLogix LAN)
The SLX_Common script included in SalesLogix contains a number of simple, yet useful, functions that you
Posted on Jul 19, 2010 by Jason Buss to Jason Buss' Blog
 
   Running a SQL Update Statement from Saleslogix Web
I am working for a client who would like a button to set all of the records in a table to eithe
Posted on Jun 23, 2010 by George Jensen to George Jensen's Blog
 
   SalesLogix basics - Easy Data access in SalesLogix LAN
In this basics article, I wanted to outline the use of include scripts within the SalesLogix LAN client.
Posted on Jun 22, 2010 by Jason Buss to Jason Buss' Blog
 
   Adding Activities and History to a Custom Main Entity in the Saleslogix Web Client (Final)
I am at the end of a series of posts providing some easy to follow steps that will help a developer&
Posted on Jun 09, 2010 by George Jensen to George Jensen's Blog
 
Comments

 

Twitted by 4SalesLogix said:

Pingback from  Twitted by 4SalesLogix

July 1, 2009 8:22 AM

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2010 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register