[AccessD] Select all items in listbox and build sql string

Susan:

Why wouldn't the row source of the ListBox alone work?  Are you making a 
delimited list for use in an IN(...) clause?  If so, you could just drop the 
row source in the IN clause if it is an SQL string.

If you have too many items selected in the list, the string you build 
through concatenation could exceed the 2k character limit of a dynamically 
generated SQL string.  At that point it would be necessary to use a saved 
querydef and change its SQL property.

To build a string, you iterate from 0 to ctl.ListCount -1 instead of the 
iterating ItemsSelected collection.

Your usage of ctl before .column is redundant as it is inside a With ctl
code block.

Hen


>From: "Susan Zeller" <szeller at cce.umn.edu>
>Reply-To: accessd at databaseadvisors.com
>To: <accessd at databaseadvisors.com>
>Subject: [AccessD] Select all items in listbox and build sql string
>Date: Thu, 17 Apr 2003 13:58:37 -0500
>
>I have some code from Arthur Fuller that makes a string out of all the
>selected items in a listbox.  What I need now is to build a string out
>of All the items in the listbox whether they are selected or not. I'm
>not sure how to adapt the code I have.  I'm guessing I need to do For 0
>to the number of items in the listbox in some way, but not sure of the
>syntax for this.  Here's what I have now:
>
>     Dim intI As Integer, intX As Integer
>     Dim strSQL As String
>
>         With ctl
>             For intI = 0 To .ItemsSelected.Count - 1
>                 intX = ctl.ItemsSelected(intI)
>                 If intI > 0 Then
>                     strSQL = strSQL & ",'" & ctl.Column(0, intX) & "'"
>                 Else
>                     strSQL = "'" & ctl.Column(0, intX) & "'"
>                 End If
>             Next intI
>         End With
>
>-Susan
>
>
>Susan B. Zeller
>Office of Information Systems
>College of Continuing Education
>University of Minnesota
>306 Wesbrook Hall
>77 Pleasant Street SE
>Minneapolis, MN 55455
>Phone:  612-626-4785
>Fax:  612-625-2568


_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.  
http://join.msn.com/?page=features/featuredemail