Solved

Access 2007, VBA: Question about listboxes, multiple selections

Posted on 2011-02-14
2
432 Views
Last Modified: 2012-05-11
Hello, I have been working on a database with the help of some of the users of experts exchange and I was hoping I could get some comments about how to change how my form's list boxes are functioning.  I have several list boxes all of which pass their values to a variable nst and then this value is passed onto the [notes] field on my form/table.  The way the list boxes function now works pretty well, but I would like a user to be able to de-select a list choice in the listbox.  That is to say if he or she clicks one of the items it removes it as a selection.  The code for the list boxes right now is as follows:

Private Sub List58_AfterUpdate()

With Me.List58
If .Selected(.ListIndex) = True Then
    If nst = "" Then
        nst = .Column(0, .ListIndex)
        Else
                  If InStr(nst, .Column(0, .ListIndex)) = 0 Then
            nst = nst & ", " & .Column(0, .ListIndex)
        End If

    End If
   
    Else
    If InStr(rst, ",") > 0 Then
        If InStr(nst, .Column(0, .ListIndex)) = 1 Then
            nst = Replace(nst, .Column(0, .ListIndex) & ",", "")
        Else
            nst = Replace(nst, "," & .Column(0, .ListIndex), "")
        End If
        Else
        nst = ""
    End If
End If

End With
Me.[Notes] = nst
End Sub

Does anyone know if it is possible to accomplish what I have requested, and if so how should the above code be modified?

Thanks in advance for any comments,
Bevo
0
Comment
Question by:Bevos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 34891277
first change the multi select property to simple

use this revised codes, copy and paste


Private Sub List58_AfterUpdate()

With Me.List58
If .Selected(.ListIndex) = True Then
    If nst = "" Then
        nst = .Column(0, .ListIndex)
        Else
                  If InStr(nst, .Column(0, .ListIndex)) = 0 Then
            nst = nst & ", " & .Column(0, .ListIndex)
        End If

    End If
   
    Else
    If InStr(rst, ",") > 0 Then
        If InStr(nst, .Column(0, .ListIndex)) = 1 Then
            nst = Replace(nst, .Column(0, .ListIndex) & ", ", "")
        Else
            nst = Replace(nst, ", " & .Column(0, .ListIndex), "")
        End If
        Else
        nst = ""
    End If
End If

End With
Me.[Notes] = nst
End Sub

Open in new window

0
 

Author Comment

by:Bevos
ID: 34891752
This was perfect. Thanks cap!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question