Results 1 to 4 of 4

Thread: ListBox (A2K)

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ListBox (A2K)

    I use the following code for the use of muliple selections (which are numeric) in a list box and all works great.
    <hr>
    Set ctl = Me.lstImpact ' Name of the Listbox

    For Each varItem In ctl.ItemsSelected
    sTemp = sTemp & ctl.ItemData(varItem) & ", " 'Adds the comma and space to the string
    Next varItem

    If sTemp = "" Then
    Else
    sTemp = Left$(sTemp, Len(sTemp) - 2) 'Remove the last comma and space
    End If
    <hr>

    Now I need to break the multiple selection apart for another purpose and retrieve each numeric value independantly . How can I achieve that?
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ListBox (A2K)

    If you still have the list box, you can look at the varItem values directly, no need to look at sTemp.

    Otherwise, you can use the Split function to create an array of individual values:

    Dim varArray
    Dim i As Integer
    varArray = Split(sTemp, ", ")
    For i = LBound(varArray) to UBound(varArray)
    ' do something with varArray(i) here
    Next i

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox (A2K)

    As always Hans, thanks for your assistance.

    I took your ladder suggestion and have the following:
    <hr>
    Dim varArray
    Dim i As Integer

    Set db = CurrentDb
    Set rst = Nothing
    Set rst = db.OpenRecordset("tbProImpacts", dbOpenDynaset)
    'For separate each item in list box
    varArray = Split(sTemp, ",")

    'Append each item from list box to table "tbProImpacts"
    'along with the corresponding project number
    For i = LBound(varArray) To UBound(varArray)
    With rst
    .AddNew
    ![Impacts] = i
    ![fkProjectNoID] = iPN
    .Update
    End With
    Next i

    Debug.Print i

    rst.Close
    <hr>

    I tried to run this 2 times.
    First I select 2 choices from the listbox (12 & 13) and 12 & 13 appear in the unbound control.
    Second I selected 2 more choices from the listbox (1 & 2) and once again 1 & 2 appear in the unbound control
    (the unbound control verifies that the code posted previously (and is part of this procedure) to this code is returning the correct values)

    I should have had 4 records:
    12 and correct project no.
    13 and correct project no.
    1 and correct project no.
    2 and correct project no.

    Instead I have 4 records:
    0 and correct project no.
    1 and correct project no.
    0 and correct project no.
    1 and correct project no.

    Now what have I done wrong?
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox (A2K)

    I've got it ....and again, thanks for your help.

    <hr>
    Dim ctl As Control
    Dim varItem As Variant
    Dim sTemp As String
    Dim i As Integer

    Dim db As Database
    Dim rst As Recordset
    Dim sPN As String
    Dim iImpact As Integer
    Dim iPN As Integer

    Set ctl = Me.lstImpact ' Name of the Listbox

    iPN = DLookup("[pkProjectNoID]", "tbProjectNo", "[ProjectNo] = Form![ProjectNo]")

    Set db = CurrentDb
    Set rst = Nothing
    Set rst = db.OpenRecordset("tbProImpacts", dbOpenDynaset)

    For Each varItem In ctl.ItemsSelected
    With rst
    .AddNew
    ![Impacts] = varItem
    ![fkProjectNoID] = iPN
    .Update
    End With
    sTemp = sTemp & ctl.ItemData(varItem) & ", " 'Adds the comma and space to the string
    Next varItem

    If sTemp = "" Then
    Else
    sTemp = Left$(sTemp, Len(sTemp) - 2) 'Remove the last comma and space
    End If

    tImpact.Visible = True
    Me!tImpact = sTemp ' This part puts the string of information into the field named tImpact ***

    'NOTE the field is updated if you change the clicked items and press the Selected Items button again
    tImpact.SetFocus
    DoCmd.GoToControl "tImpact"
    lstImpact.Visible = False
    <hr>
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •