Solved

Access 2007, VBA: Question about listboxes, multiple selections

Posted on 2011-02-14
2
425 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

696 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