Copying list item from one list Box to another

copy list items

In Microsoft Access, the list box is an object that displays a list of values. However, the list in a list box consists of rows of data. Moreover, these rows can have one or more columns and can appear with headings if it is been activated. So, you can use an unbound list box to store values or to find a record based on the value that you select in the list box. Please follow the below steps for copying list item from one list box to another. 

In many cases, it's quicker and easier to select a value from a list than to remember a value to type. A list of choices also helps ensure that the value that's entered in a field is correct.
Microsoft
Microsoft Listbox Object

Back to our subject, the main idea here is to go through the steps to copy one or more items from one list box to another. First, you will need to create two list boxes and you may attach them to queries or tables.

The second step is to enable the multi-selection in both and create the needed buttons as shown in the picture. Thus, these buttons will handle the below VBA codes to select all items, clear them or transferring them from one list to another.

Please download our example as it will give you a clear picture of the coding before going into the VBA Codes

Download Sample

Select all items from a list box:

				
					Dim x As Integer
For x = 0 To Me.lstFirst.ListCount - 1
Me.lstFirst.Selected(x) = True
Next x

Clear all items from a list box 

				
					Dim x As Integer
For x = 0 To Me.lstFirst.ListCount - 1
Me.lstFirst.Selected(x) = False
Next x

Selected list from one list box to another

				
					Const KEY_VIOLATION = 3022
Dim ctrl As Control
Dim strSQL As String
Dim varItem As Variant
Dim x As Integer

Set ctrl = Me.lstFirst


If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblNamesSelected(ContactID,FirstName,LastName) " & _
"SELECT ContactID,FirstName,LastName " & _
"FROM tblNames " & _
"WHERE ContactID = " & ctrl.ItemData(varItem)


CurrentDb.Execute strSQL, dbFailOnError
Next varItem


Me.lstSecond.Requery


For x = 0 To Me.lstSecond.ListCount - 1
Me.lstSecond.Selected(x) = False
Next x
End If


Exit Sub


Err:


Select Case Err.Number
Case KEY_VIOLATION
Resume Next
Case Else
MsgBox Err.Description, vbExclamation, "Error"


End Select

Remove selected items from the new list box 

				
					Dim ctrl As Control
Dim strSQL As String
Dim varItem As Variant
Dim strContactIDList As String
Dim x As Integer

Set ctrl = Me.lstSecond


If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strContactIDList = strContactIDList & "," & ctrl.ItemData(varItem)
Next varItem


strContactIDList = Mid(strContactIDList, 2)


strSQL = "DELETE * FROM tblNamesSelected " & _
"WHERE ContactID IN(" & strContactIDList & ")"


CurrentDb.Execute strSQL, dbFailOnError


For x = 0 To ctrl.ListCount - 1
ctrl.Selected(x) = False
Next x


Me.lstSecond.Requery
End If

To use Microsoft Access Programs.
You’ll need to have Microsoft Office installed including the bundle of MS Access or to download the free Microsoft Access 2016 run-time.

Please check our Pro Microsoft Access Programs here.