Solved

Need to move selected items from one list box to another. VBA

Posted on 2008-06-11
5
1,058 Views
Last Modified: 2013-11-28
I have an unbound list box that was populated by an ado recordset, then disconnected. When a row is selected, I would like the user to click an arrow button to apparently move the selected item to another list box. I would like another arrow button that moves the item back to the original list box. The second list box would then show which items have been selected.

I did this 10 years ago in Access, but have forgotten how. Any references would be helpful.

Thanks,
0
Comment
Question by:Dovberman
  • 3
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
koutny earned 250 total points
ID: 21759169
Assuming both list boxes are unbound and there is only one column
Private Sub cmdSelect_Click()

Dim vSelectedValue As Variant

    vSelectedValue = lstBox1

    If IsNull(vSelectedValue) Or IsEmpty(vSelectedValue) Then

        MsgBox "Select an item first", vbCritical, "No item selected"

        Exit Sub

    End If

    

    lstBox2.AddItem vSelectedValue

    lstBox1.RemoveItem lstBox1.ListIndex

End Sub
 
 
 

Private Sub cmdSelect_Click()

Dim vSelectedValue As Variant

    vSelectedValue = lstBox2

    If IsNull(vSelectedValue) Or IsEmpty(vSelectedValue) Then

        MsgBox "Select an item first", vbCritical, "No item selected"

        Exit Sub

    End If

    

    lstBox1.AddItem vSelectedValue

    lstBox2.RemoveItem lstBox2.ListIndex

End Sub

Open in new window

0
 
LVL 12

Expert Comment

by:koutny
ID: 21759175
Oops, the second procedure should have been cmdDeselect_click. But I am sure you get the drift
0
 

Author Comment

by:Dovberman
ID: 21761257
Your example refreshed my memory.

I changed the list box row source type to value list.
Add Item for multiple columns was tricky.
The column string value cannot contain embedded commas.
Column headers must be set to no. Otherwise replaced items will go into the header row.

Thanks,
0
 

Author Closing Comment

by:Dovberman
ID: 31466211
Here is the actual code:

Private Sub cmdSelectViol_Click()
    'Selects a violation from the general list
    'And moves the item to the selected list
   
    Dim intViolationID As Integer
    Dim strViolationDesc As String
    Dim strAuth As String
     
    On Error GoTo cmdSelectViol_Click_Err
   
    If IsNull(lstGenViolations.Column(0)) Or IsEmpty(lstGenViolations.Column(0)) Then
        MsgBox "Select an item first", vbCritical, "No item selected"
        Exit Sub
    End If
   
    intViolationID = lstGenViolations.Column(0)
    strViolationDesc = Replace(lstGenViolations.Column(1), ",", " ") 'Remove embedded commas
    strAuth = lstGenViolations.Column(2)
   
    If IsNull(lstGenViolations.Column(2)) Then
        lstSelectedViol.AddItem Item:=intViolationID & ";" & strViolationDesc
    Else
        lstSelectedViol.AddItem Item:=intViolationID & ";" & strViolationDesc & ";" & strAuth
    End If '!ViolationAuth IsNull
   
    lstGenViolations.RemoveItem lstGenViolations.ListIndex
   
cmdSelectViol_Click_Exit:

    Exit Sub
 
cmdSelectViol_Click_Err:
    LogError Err.Number, Err.Description, "Form: " & Me.Name & ", cmdSelectViol_Click "
    DoCmd.Hourglass False
    Resume cmdSelectViol_Click_Exit

 End Sub
0
 
LVL 12

Expert Comment

by:koutny
ID: 21761360
Yes, I knew I was being lazy when I assumed there was only one column...
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

22 Experts available now in Live!

Get 1:1 Help Now