Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Multiselect List Box Problem

Multiselect List Box Problem

Multiselect List Box Problem

Here’s another one…those darned list boxes again.  This time I’m working with a multiselect list box (this is a new one for me).  Running a querydef from the SQL I’m wanting to populate the criteria in a field in the query with the Block ID numbers of the Blocks chosen from lstBlck.  This will show data about the selected Blocks.  Of course, in the criteria., if more than one is selected the query will need an “Or Like” in between the Block ID numbers.  For instance, if I select two blocks and the code runs it tries to put “8 Or Like 51 Or Like” (strList equals this) into the query as the criteria.  This is no good.  It needs to place … 8 Or Like 51 into the criteria instead.  Can anyone tell me how to rid the result of "strList" of the quotations and the trailing “Or Like”?

In other words:
strList = “8 Or Like 51 Or Like”
It needs to be this:
strList = 8 Or Like 51
Here is the code I’m using:

Private Sub lstBlck_AfterUpdate()
Dim db             As DAO.Database
Dim strSQL         As String
Dim intI           As Integer
Dim ctlBlck        As Control
Dim strList        As String

Set ctlBlck = [Forms]![frmWllRprtFltr]![lstBlck]

With ctlBlck
    If .MultiSelect = 0 Then
        txtselected = .Value
        For Each varItem In .ItemsSelected
            strList = strList & .Column(0, varItem) & " " & "Or Like" & " "
        Next varItem
    End If
End With

strSQL = ""
strSQL = strSQL & "SELECT tblpklstSctn.SctnID, tblpklstSctn.SctnBlckID, "
strSQL = strSQL & "tblpklstSctn.SctnNmbr "
strSQL = strSQL & "FROM tblpklstSctn "
strSQL = strSQL & "WHERE (((tblpklstSctn.SctnBlckID)=" & strList & ")); "

Set db = CurrentDb

    For intI = 0 To db.QueryDefs.Count - 1
        If db.QueryDefs(intI).Name = "qrylstSctn" Then
            db.QueryDefs(intI).SQL = strSQL
        End If
    Next intI

End Sub

Dan Rogotzke

RE: Multiselect List Box Problem

If (Right(strList, 8) = "Or Like ") Then

    strList = Left(strList, Len(strList) - 8)

End If

After the While Loop  --- BUT count the chars again --- my fingers are tired on Fri afternoons!!

There is never time to do it right but there is always time to do it over

RE: Multiselect List Box Problem

Sorry I didn't get back to ya earlier..been busy finishing the project.  Anyway, that's what I needed.  Thank you!  With a little manipulation here and there everything worked great.

Dan Rogotzke

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close