Create List from another Listbox. ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

If you haven’t tried the earlier example, “Selected List Box Items and Dynamic Query,” please visit that page and complete it before proceeding. The same table and queries created in that example will also be used here.

Here’s a brief overview of what you need to do before continuing:

Import the Orders table from the Northwind.mdb sample database.
Create two Select Queries by copying and pasting the SQL strings provided below into the SQL editing window.
Save the queries using the suggested names.

Query: OrdersinQ

SELECT Orders.*
FROM Orders
WHERE ((([Orders].[OrderDate]) Between #5/1/1998# And #5/31/1998#));

Query: OrdersOUTQ

SELECT OrdersINQ.*
FROM OrdersINQ
WHERE (((OrdersINQ.OrderID) In (11067)));

In the earlier example, we used a method where selected items were highlighted and used directly as criteria for the output query. In this example, we’ll achieve the same result using a different approach — in several creative ways.

When you create a Form using the Microsoft Access Form Wizard, it displays a list of available fields from the source table or query in one list box and allows you to select which fields to include on the form. You can move the selected fields to another list box and add or remove fields before proceeding to the next step.

We’re going to create something similar to that.

To try this method, we’ll use two list boxes on a form:

When the form opens, the first list box will be populated with Order Numbers and Customer Codes from the source query OrdersinQ, which we used in the earlier example, Selected List Box Items and Dynamic Query.
The user can then select one or more items from the first list box and click a command button with a right-arrow (>) indicator to move the selected items to the second list box — while simultaneously removing them from the first.

A sample image showing the form in action is provided below.

The List Boxes Image

Right-click to open Large Image in New Tab/Window.

If the user changes her mind and wants to remove one or more items from the second list, she can do so by highlighting the desired items and clicking the second command button (with the left arrow < as its indicator). The selected items will then be moved back into the original list and removed from the second list box. The user can repeat this process any number of times until satisfied with the final selection, and then click the Preview Orders button to open the output query OrdersOutQ, or a report or form based on it.

The selected order numbers are extracted from the list and used to redefine the SELECT query OrdersOutQ, as we did in the previous example.

Since this approach is different from what we implemented earlier, it requires an additional list box and three more event procedures to handle the item transfers and interactions.

The Form_Load() event procedure is used to populate the first list box dynamically, instead of assigning the OrdersInQ query as its Row Source manually. This design gives the user full control to move items freely between the two lists while automatically removing moved entries from the source list.

Because of this dynamic behavior, we cannot directly use the OrdersInQ query as the Row Source for the list box, as we did in the earlier example. Instead, we must populate it through VBA code to maintain full control over the item transfers.

Preventing Multiple Item Selection

We can easily implement another variation of this technique with just a single line change in each of the click event procedures of the command buttons—and even remove both command buttons entirely.

Simply change the header line
Private Sub cmdIn_Click() to Private Sub List1_Click()

for the first command button, and likewise change

Private Sub cmdOut_Click() to Private Sub List2_Click()

for the second command button. With these minor edits, both command buttons become unnecessary and can be safely deleted from the form.

However, note that multiple item selection will not be possible with this approach, since each item is transferred immediately when clicked.

You now have three distinct methods for working with list boxes—each suited to different scenarios in your projects. Use them as needed, and demonstrate your versatility in handling list box interactions with creative approaches.

Open a New Form in Design View.
Disable the Control Wizard on the Toolbox and create a List Box on the Form. Change the Label Caption to Orders.
Click on the List Box and display the Property Sheet (View -> Properties) and change the following Properties:
- Name = List1
- Row Source Type = Value List
- Column Count = 2
- Bound Column = 1
- Column Width = .5";1"
- Multi Select = Simple
Select the existing List Box, then copy and paste it to the right side of the form, leaving enough space between the two List Boxes to accommodate the Command Buttons (as shown in the design above). Change the Name property of the newly created List Box to List2, and verify that all other property settings match those of the original List Box. Finally, update the Caption of the attached label to “Selected Orders.”
Create two small Command Buttons in between the List Boxes. Change the Name Property of the top one to cmdin, and put a greater than symbol (>) in the Caption Property.
Change the Name Property of the second Command Button to cmdout and insert a less-than symbol (<) in the Caption property.
Create another Command Button below both List Boxes. Change the Name Property to cmdPreview and change the Caption Property to Preview Orders.
Select File -> Save and save the Form with the name ORDERLIST.

Display the VBA Code Module of the Form (View -> Code). Copy and paste the following code into the VBA Module of the Form.

Form Class Module Code.

Private Sub Form_Load()
'------------------------------------------------------
'Author: a.p.r. pillai
'Date  : 10/05/2008
'URL   : http://www.msaccesstips.com
'All Rights Reserved by msaccesstips.com
'------------------------------------------------------
Dim db As Database, rst As Recordset
Dim xinlist As ListBox, strlist As String

Set db = CurrentDb
Set rst = db.OpenRecordset("OrdersinQ", dbOpenDynaset)
Set xinlist = Me.List1
strlist = ""

Do While Not rst.EOF
    If Len(strlist) = 0 Then
        strlist = rst![OrderID]
        strlist = strlist & ";" & Chr$(34) & rst![CustomerID] & Chr$(34)
    Else
        strlist = strlist & ";" & rst![OrderID]
        strlist = strlist & ";" & Chr$(34) & rst![CustomerID] & Chr$(34)
    End If
rst.MoveNext
Loop
rst.Close

xinlist.RowSource = strlist
xinlist.Requery
End Sub

Private Sub cmdin_Click()
'------------------------------------------------------
'Author: a.p.r. pillai
'Date  : 10/05/2008
'URL   : http://www.msaccesstips.com
'All Rights Reserved by msaccesstips.com
'------------------------------------------------------
Dim xinlist As ListBox, xoutlist As ListBox, strRSource As String
Dim listcount As Long, j As Long, strRS2 As String

Set xinlist = Me.List1
Set xoutlist = Me.List2

listcount = xinlist.listcount - 1
strRSource = xoutlist.RowSource
strRS2 = ""

For j = 0 To listcount
    If xinlist.Selected(j) = True Then
        If Len(strRSource) = 0 Then
            strRSource = xinlist.Column(0, j)
            strRSource = strRSource & "; " & Chr$(34) & xinlist.Column(1, j) & Chr$(34)
        Else
            strRSource = strRSource & ";" & xinlist.Column(0, j)
            strRSource = strRSource & ";" & Chr$(34) & xinlist.Column(1, j) & Chr$(34)
        End If
    Else
        If Len(strRS2) = 0 Then
            strRS2 = xinlist.Column(0, j)
            strRS2 = strRS2 & ";" & Chr$(34) & xinlist.Column(1, j) & Chr$(34)
        Else
            strRS2 = strRS2 & ";" & xinlist.Column(0, j)
            strRS2 = strRS2 & "; " & Chr$(34) & xinlist.Column(1, j) & Chr$(34)
        End If
    End If
Next
xoutlist.RowSource = strRSource
xinlist.RowSource = strRS2
xoutlist.Requery
xinlist.Requery
End Sub

Private Sub cmdout_Click()
'------------------------------------------------------
'Author: a.p.r. pillai
'Date  : 10/05/2008
'URL   : http://www.msaccesstips.com
'All Rights Reserved by msaccesstips.com
'------------------------------------------------------
Dim xinlist As ListBox, xoutlist As ListBox, strRSource As String
Dim listcount As Long, j As Long, strRS2 As String

Set xinlist = Me.List1
Set xoutlist = Me.List2

listcount = xoutlist.listcount - 1

strRSource = xinlist.RowSource: strRS2 = ""
For j = 0 To listcount
    If xoutlist.Selected(j) = True Then
        If Len(strRSource) = 0 Then
           strRSource = xoutlist.Column(0, j)
            strRSource = strRSource & ";" & Chr$(34) & xoutlist.Column(1, j) & Chr$(34)
        Else
            strRSource = strRSource & "; " & xoutlist.Column(0, j)
            strRSource = strRSource & ";" & Chr$(34) & xoutlist.Column(1, j) & Chr$(34)
        End If
    Else
        If Len(strRS2) = 0 Then
            strRS2 = xoutlist.Column(0, j)
            strRS2 = strRS2 & ";" & Chr$(34) & xoutlist.Column(1, j) & Chr$(34)
        Else
            strRS2 = strRS2 & ";" & xoutlist.Column(0, j)
            strRS2 = strRS2 & ";" & Chr$(34) & xoutlist.Column(1, j) & Chr$(34)
        End If
    End If
Next
xinlist.RowSource = strRSource
xoutlist.RowSource = strRS2
xoutlist.Requery
xinlist.Requery
End Sub

Private Sub cmdPreview_Click()
'------------------------------------------------------
'Author: a.p.r. pillai
'Date  : 10/05/2008
'URL   : http://www.msaccesstips.com
'All Rights Reserved by msaccesstips.com
'------------------------------------------------------
Dim strsql0 As String, crit As String, strsql As String
Dim db As Database, qryDef As QueryDef, crit0 As String
Dim strOrders As String, xoutlist As ListBox, listcount As Integer
Dim j As Integer

strsql0 = "SELECT Orders.* FROM Orders "crit0 = "WHERE (((Orders.OrderID) In ("

Set xoutlist = Me.List2
listcount = xoutlist.listcount - 1
If listcount < 0 Then
    strsql = strsql0 & ";"
    GoTo nextstep
End If

For j = 0 To listcount
    If Len(strOrders) = 0 Then
        strOrders = xoutlist.Column(0, j)
    Else
       strOrders = strOrders & "," & xoutlist.Column(0, j)
    End If
Next
strsql = strsql0 & crit0 & strOrders & "))); "
nextstep:
Set db = CurrentDb
Set qryDef = db.QueryDefs("OrdersoutQ")
qryDef.Sql = strsql
db.QueryDefs.Refresh
DoCmd.OpenQuery "OrdersOutQ", acViewNormal
End Sub

Display the Property Sheet of the Command Buttons and check whether the setting [Event Procedure] is appearing in the On Click property. If not, then select [Event Procedure] from the drop-down list on the right side and save the Form.
Open the Form in normal view and click on any number of items one by one in the first List Box, and click the Command Button with the > indicator. The selected items will move from the first list into the second one.
Select one or two items from the right side List Box and click the Command Button with the < indicator. The selected items will move back to the end of the first list. Try the selection method this way a few more times.
When you have completed the selection process, click the Preview Orders Command Button.

Using the Output Query.

The OrderOUTQ Query will open in Datasheet View with the filtered data with the selected Order Numbers. You can use this Query for preparing reports or designing Forms to display the contents or use it as the source for other processing steps.

If the right-side List Box is empty when you click the Preview Order command button, the OrderOUTQ Query will pick all the Items from the OrdersINQ for Output.