Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Question Unanswered: List Box - Adding/Removing items

    I am trying to allow the user to select multiple records from the records in a subform. To do this I created a list box and a command button that will take the selected item in the subform and add the ID and Amount to a list box. I do this with something similar to:

    Code:
    lstAP.RowSource = lstAP.RowSource & Me.ApRecords.Form.APID & 
    ";" & Me.ApRecords.Form.openAmt & ";"

    Two questions.
    1. Is their a better way to add items to the list box as the user selects records in the subform and clicks the add to list button?

    2. When the user clicks the "remove from list" button, how can I adjust the record source to no longer include the item that is currently selected in the list box?

    In both cases, I need the subform records to change to reflect the current status of the records (in the list or still in the recordset). To do this, I am looping through all the items in the list and adding
    "Where apid <> lstAP.column(count,1)"
    to the SQL string that becomes the record source.

    I just feel like I am going about this the hard way and could use some better direction.

    Thanks.
    Steve

  2. #2
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I am in the same boat... kinda.

    I have a form that has a bunch of list boxes. The first one opens one or two more depending on the selection. Then I click what I want from those. I click a button and it brings up a list (this is a kind of search function) of parts. From that list I can select one and add it to another list box (and to the subform below). Since I added the subform I can no longer remove the the items from the list because I do not know how to remove them from the subform. I will give you all the code I have... (some of it may be irrelavent).

    My add to list and subform code...
    Code:
    Private Sub cmdAddtolst_Click()
    Dim existing As Boolean
    Dim s As String
    existing = False
    s = lstParts.Column(PartID)
    For i = 0 To (lstSelectedParts.ListCount - 1)
        If lstSelectedParts.Column(PartID, i) = s Then
            existing = True
        End If
    Next i
    
    If existing Then
        MsgBox "This part is already in the list."
    Else
        lstSelectedParts.RowSourceType = "Value List"
        lstSelectedParts.AddItem (lstParts)
    End If
    
    If IsNull(Salesman.Value) Then
        CUser = CurrentUser()
        Salesman.Value = CUser
        Date.Value = DateTime.Date
    End If
    
    Dim frm As Form
    Set frm = Me.SOCostD.Form
    If DCount("PartID", "SO Query", "PartID = '" & lstParts.Value & "' AND SalesOID = " & SalesOID) Then
        MsgBox "Item already selected. Please select another."
    Else
        Me.SOCostD.SetFocus
        frm.Quantity.Value = 1
        frm.PartID = lstParts.Value
        DoCmd.GoToRecord acActiveDataObject, , acNewRec
    End If
    
    SelectedExit:
        Exit Sub
    
    SelectedMsgBox:
        MsgBox "Item already selected. Please make another selection."
        Resume SelectedExit
    End Sub
    My remove from list code before I added the subform...
    Code:
    Private Sub cmdRfromlst_Click()
    On Error GoTo cmdRfromlst_err
        lstSelectedParts.RemoveItem (lstSelectedParts.ListIndex)
    
    cmdRfromlst_exit:
        Exit Sub
        
    cmdRfromlst_err:
        MsgBox "Please select a part to remove."
        Resume cmdRfromlst_exit
    End Sub
    If you find a way to remove an item from the subform let me know. I am pretty sure it will be a SQL delete function of some sort.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  3. #3
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    I like the addItem, removeitem functionality, but I can't get them to work with a multiple column list box. Any ideas how to make that work?

    As far as the subform. I have mine running off a SQL statement which is updated after the list box is changed so the items are removed. Functional but cumbersome.

  4. #4
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Let me see if I can do some searching and see if I can find anything on the multiple columns.

    I am pretty sure there is something on the .AddItem () property that will allow you to put in multiple columns.

    Hopefully I can find something,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Well a way you can do it is to have the listbox based on a table (temporary or not) where you have a column of "IsVisible" (or named something similar) that indicates whether the record should be displayed. Your record source for the listbox will read like: SELECT * FROM ListBoxTableName WHERE (IsVisible=True);

    When you select an item (or groups of items) you merely trip the visible flag to hide ... To restore the list just reset the flag to true ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Quote Originally Posted by M Owen
    Well a way you can do it is to have the listbox based on a table (temporary or not) where you have a column of "IsVisible" (or named something similar) that indicates whether the record should be displayed. Your record source for the listbox will read like: SELECT * FROM ListBoxTableName WHERE (IsVisible=True);

    When you select an item (or groups of items) you merely trip the visible flag to hide ... To restore the list just reset the flag to true ...
    Is there a way to use vba and add multiple columns of information? I currently use .additem() to add my single column but he needs multiple columns of info.

    btw Long time no see. I havent been on here in a while

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  7. #7
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you can add multiuple items seperating them with ";"
    Code:
    'for an 3 Columns list
    MyListbox.AddItem Item:="146;Smith;Fl"
    ghozy.

  8. #8
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Quote Originally Posted by ghozy
    you can add multiuple items seperating them with ";"
    Code:
    'for an 3 Columns list
    MyListbox.AddItem Item:="146;Smith;Fl"

    Sweet. Thanks for checking on this and giving the answer...

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Just 1 thing to remember with the AddItem property ... It's available in Access 2002 and up. It doesn't exist for 2000 and before ... Also, reminder on the "Values List" : there is a limit to the # of items available ... Under 2000, you had a size limit of 2000 bytes of data. So keep this in mind when designing your form ... If you have a list of info that may exceed that you'll want to switch to a table/query format.
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I didn't know that. thanks for the info.
    ghozy.

  11. #11
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Thank you as well. I also didnt know that good information.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

Posting Permissions

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