Solved

Access 2007, VBA: Question about listboxes, multiple selections

Posted on 2011-02-14
2
396 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
2 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now