Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    mdavid is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    160

    remove selected item from listbox with Row Source: QueryName

    Hi, I have a listbox with a query as the RowSource. How do I remove a selected item from the list?

    This is what I tried:



    Code:
    Private Sub lstFlopEngNames_AfterUpdate()
        I = Me.lstFlopEngNames.ListIndex
        Me.lstFlopEngNames.Selected(I) = False
        Me.lstFlopEngNames.RemoveItem (I)
    End Sub
    but I get an error saying this only works for a value list.

    Thanks for any help
    David

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you elaborate the requirement? Perhaps an example or 2. And show us the sql of the query.

    I found this from M$oft that seems to restrict the list "Also, the RowSourceType property of the control must be set to "Value List". "
    Code:
    Function RemoveListItem(ctrlListBox As ListBox, _ 
     ByVal varItem As Variant) As Boolean 
    
     ' Trap for errors. 
     On Error GoTo ERROR_HANDLER 
    
     ' Remove the list box item and set the return value 
     ' to True, indicating success. 
     ctrlListBox.RemoveItem Index:=varItem 
     RemoveListItem = True 
    
     ' Reset the error trap and exit the function. 
     On Error GoTo 0 
     Exit Function 
    
    ' Return False if an error occurs. 
    ERROR_HANDLER: 
     RemoveListItem = False 
    
    End Function

  3. #3
    mdavid is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    160
    OK, so the story is I have a list box where I need to edit/delete items in the list, the method I'm using is to select the item to edit, copy it to a text box, deselect it and remove it from the list. Now I can edit the text box and add it back, or leave it off (ie. delete from) the list.

    The SQL query:

    Code:
    SELECT tblCommonNames.CommonName
    FROM tblCommonNames
    WHERE (((tblCommonNames.SpeciesID)=[forms]![MempSpeciesfrm]![SpeciesID]) AND ((tblCommonNames.NameLang)="English"))
    ORDER BY tblCommonNames.CommonName
    ;

    I understand that I need to change the row source type from query to value list but not sure if this is possible in code.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If the listbox is based on the query you're showing, after you copy the value to the unbound textbox, delete the row from the underlying table at some point, then requery the listbox. I'm not certain as to what's what in your design, but the sql statement might look something like
    Code:
     DELETE FROM tblCommonNames.CommonName WHERE SpeciesID = [forms]![MempSpeciesfrm].ListBoxName.ItemsSelected
    Of course, you could expand on this and assign values to variables, trap for possible errors (such as the error you'd get if the item has been deselected) and use the db.Execute method or just DoCmd.RunSql
    Last edited by Micron; 10-11-2017 at 12:57 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    mdavid is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    160
    Hi Micron, thanks for your reply. I don't want to delete stuff from the underlying table. Can I take a query based listbox and copy the rows to a value list listbox?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with micron, but the devil is in the details.

    How do you determine-
    -the list of values you want to show in the list
    -the values you want to remove

    Can you do that determination before you present anything to the user? For example, just a select query on the values you need and use that. That's a read only on the underlying table.

    There may be options to achieve what you want--but we need to know clearly what exactly you want.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You said you had "a" listbox. Now you want to take the results of a query and turn that into a value list for another listbox?
    You cannot remove a list item from a listbox using RemoveItem unless the row source is a value list, as already mentioned.

    Based on post #3 I don't see why you don't just remove it from the table if you don't want to see it while you edit it, then put back the edited version. Or don't bother not showing it while you edit. Not sure why you're doing this anyway since the purpose hasn't been revealed. There's probably a better way to do whatever it is you're trying to end up with, like editing a subform datasheet or continuous subform directly. Listboxes aren't designed for providing data edit interfaces.

  8. #8
    mdavid is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    160
    I have a query based listbox the user needs to decide which items to remove from the list, which to modify and which to leave as they are. The rows in this new modified list will be output to a another table, without changing the original values. I thought that to allow the user to modify the query based list it he should work on a value based copy. Hope this is a bit clearer

  9. #9
    mdavid is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    160
    I have a query based listbox the user needs to decide which items to remove from the list, which to modify and which to leave as they are. The rows in this new modified list will be output to a another table, without changing the original values. I thought that to allow the user to modify the query based list it he should work on a value based copy. Hope this is a bit clearer

  10. #10
    mdavid is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    160
    Would it be possible to feed the results of a select query into a Value List listbox?

  11. #11
    mdavid is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    160
    Hi, need help with code to return results of sql select query into a semi-colon delimited list to populate a Value List listbox.
    Thanks for any help

    David

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think you should step back and tell us --in plain English-- what you are trying to accomplish.
    It's important to clearly describe what you are trying to do. There may be many options for how to do it.
    To me your posts are sort of a mix of how/what. For example, why a list? Why show something, then have the user remove things
    and then show the reduced list?

  13. #13
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Answer to #10 - yes. Per #12 - I agree; this might be best.
    I have ideas but are they applicable? Depends on the process and the elements involved, such as is the listbox multi column and so on.
    Last edited by Micron; 10-12-2017 at 02:00 PM.

  14. #14
    mdavid is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    160
    Hi, apologies for not being more forthcoming when you wanted to help. I was to wrapped up in getting a solution to my immediate access problem.
    Here's the form I'm working on:

    Click image for larger version. 

Name:	SpeciesForm.jpg 
Views:	17 
Size:	207.4 KB 
ID:	30782



    I'm creating a medicinal plants website, the info for this website comes from 2 separate sources represented by the MEMP and BERC columns above.
    The user of this form needs to take the information from both sources edit it, remove duplicate info, and merge it into the 3rd column - Flora Palaestina, the data in column MEMP and BERC can be copied to the Flora Palaestina column where the user will be able to make the necessary changes. At the moment I don't have any information in BERC for Arabic, Hebrew or Other Names I hope this info will be added in the future.
    So for example I have a list of say 10 Other Names in the MEMP column, the user would copy this list to the Flora Palaestina column and then remove or edit the names as necessary. Here's the code I found Googling and adapted to create the Value List listbox in the 3rd column:

    Code:
    Private Sub cmdCpyOthrNames_Click()
        Dim tmpRS As DAO.Recordset
        Dim tmpStr As String, retStr As String
    
    
        tmpStr = "SELECT CommonName FROM tblCommonNames WHERE SpeciesID = " & Forms!MempSpeciesfrm!SpeciesID & " And NameLang = 'Other'" & " Order By CommonName"
    
    
        Set tmpRS = CurrentDb.OpenRecordset(tmpStr)
    
    
        If tmpRS.RecordCount > 0 Then
            Do While Not tmpRS.EOF
                retStr = retStr & tmpRS.Fields(0) & ";"
                tmpRS.MoveNext
            Loop
        End If
        lstFlopOthrNames.RowSource = retStr
        Set tmpRS = Nothing
    End Sub
    Now I need to add code to RemoveItem, AddItem ect to the Flora Palaestina Other Names so the user can correct the list.

    When the user has finished correcting the info for a species the info in the Flora Palaestina column will be saved - still need to add this.

    Hope my situation is a bit clearer, happy to hear any comments or suggestions.

    David

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    An aside: If you have common names from A, and common names from B, you can use a UNION SELECT query to return only unique values.
    Not sure if it's relevant to your workflow, but it is a way to get unique/distinct values from multiple, similar lists.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 16
    Last Post: 03-31-2020, 10:19 AM
  2. Replies: 4
    Last Post: 07-26-2016, 02:53 AM
  3. Replies: 7
    Last Post: 11-29-2015, 07:24 AM
  4. Show Checkbox when listbox item is selected
    By gammaman in forum Modules
    Replies: 5
    Last Post: 06-04-2015, 09:46 AM
  5. Replies: 7
    Last Post: 06-05-2012, 03:22 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