Results 1 to 5 of 5
  1. #1
    kmw is offline Novice
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    27

    Cycling through values in a list box

    Hello,



    I have a multi-select list box and I'm having trouble with the visual basic code to cycle through the values. My append query currently adds all of my fields except for the multi-select list boxes.



    This is what I have for On Click:

    Private Sub List0_Click()

    Dim lngRow As Long
    Dim strMsg As String

    With Me.List0
    For lngRow = 0 To .ListCount - 1
    If .Selected(lngRow) Then
    strMsg = strMsg & ", " & .Column(1, lngRow)
    End If
    Next lngRow
    End With

    ' strip off leading comma and space '
    If Len(strMsg) > 2 Then
    strMsg = Mid(strMsg, 3)
    End If
    MsgBox strMsg



    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That looks okay offhand, though I would just loop the selected items:

    http://www.baldyweb.com/multiselect.htm

    How are you using the string?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    kmw is offline Novice
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    27
    The issue is that I just copied and pasted code into visual basic and don't understand the program. I used what you sent me and I am still having the same issue. Do I need to create something called "ItemsSelected"


    Dim strWhere As String
    Dim ctl As Control
    Dim varItem As Variant

    'make sure a selection has been made
    If Me.List0.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 employee"
    Exit Sub
    End If

    'add selected values to string
    Set ctl = Me.List0
    For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
    'Use this line if your value is text
    'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    Next varItem

    'trim trailing comma
    strWhere = Left(strWhere, Len(strWhere) - 1)

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, but I highly recommend you understand code that you incorporate into your application. Both sets of code simply build a string. I used it in the OpenReport line. How are you trying to use it?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    kmw is offline Novice
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    27
    I have a form that is used to input information. After everything is entered into the text boxes and list boxes I have a button that runs an append query to add everything.

    I think I am having trouble incorporating that string. What if I found code to save that string into a new text box and the append query just added the stored information from the text box?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-22-2012, 12:49 PM
  2. Replies: 5
    Last Post: 08-16-2012, 11:05 AM
  3. Cycling through subform Records
    By paddon in forum Programming
    Replies: 3
    Last Post: 05-10-2011, 10:20 AM
  4. List all values in one cell?
    By Remster in forum Queries
    Replies: 5
    Last Post: 12-17-2010, 04:33 AM
  5. Printing reports and cycling through records
    By dabman in forum Programming
    Replies: 4
    Last Post: 12-15-2009, 11:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums