If you have not tried the earlier example Selected List Box Items and Dynamic Query please visit that page and try it out before continuing with this. The Table and Queries created for that example are used here also.

I will give you briefly what you should do before continuing. Import the Orders Table from the Northwind.mdb sample database. Create two Select Queries by copying and pasting the following SQL String into the SQL editing window and save the Queries with the names as suggested below.

  1. Query : OrdersinQ
    SELECT Orders.*
    FROM Orders
    WHERE ((([Orders].[OrderDate]) Between #5/1/1998# And #5/31/1998#));
    
  2. Query : OrdersOUTQ
    SELECT OrdersINQ.*
    FROM OrdersINQ
    WHERE (((OrdersINQ.OrderID) In (11067)));
    

In the earlier example we have used a method by highlighting items and using them as criterion for the output Query. Here, we are using different methods for the same result in a variety of ways.

When you create a Form with Microsoft Access Form Wizard it will give you a list of Fields from the Source Table or Query in one control and will ask you to select the required fields for the Form. You are allowed to select the fields and put them into another List Box for creating the Form with the selected list. You are allowed to Add or Remove Fields from the List Boxes before moving into the Next step. We are going to create something similar to that.

To try this method we need two List Boxes. When the Form is open the first List Box will be populated with Order Numbers and Customer Codes from the Source Query OrdersinQ, that we have used in the earlier example Selected List Box Items and Dynamic Query. The User will select one or more items from the first List Box and click a Command Button with the right arrow > indicator to move the selected items to the right side List Box and remove them from the first one. A finished sample image in running view is given below.

If the user changes her mind and wants to remove one or more items from the Second List she can do so by highlighting them and clicking the second Command Button (with the left arrow < indicator). The selected items will be moved back into the original list and removes them from the second List Box. The user can repeat this selection process any number of times till she is satisfied with the final list and ready to click the Preview Orders Button to open either the Output Query OrdersOutQ itself or a Report or Form designed using it.

The selected items' Order Numbers will be extracted from the list and used for redefining the Select Query OrdersoutQ, as we did in the previous example.

Since, the method is different from what we have tried earlier, it takes one more list box and 3 more Event Procedures to implement it.

We use the Form_Load() event procedure to populate the first list box items rather than setting the Row Source property with the Columns of OrdersinQ Query manually. The user is given the freedom to move the items from one list to the other at will and the moved items are removed from the source list. To do this kind of tricks we cannot directly use the OrdersinQ Query as Row Source for the List Box, as we did in the earlier example.

We can easily implement another trick with only one line change in each of the Click Event Procedures of the Command Buttons and can remove both the Command Buttons altogether. Change the Header line Private Sub cmdin_Click() to Private Sub List1_Click() of the first command button. Similarly change Private Sub cmdout_Click() to Private Sub List2_Click() of the second Command Button and you can eliminate both the Command Buttons.

With this method multiple item selection is not possible because when you click on an item it will move out into the other list immediately.

You have now three different methods for List Boxes that you can use suitably in your Projects as the situation demands and show off your skill in variety of methods.

  1. Open a New Form in Design View.
  2. Disable the Control Wizard on the Toolbox and create a List Box on the Form. Change the Label Caption to Orders.
  3. 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
  4. Select the List Box, copy and paste it to the right side, giving enough space between them to create Command Buttons (as shown in the design above) and change the Name Property of the new List Box to List2. Check and confirm that the other properties are matching with the above values. Change the child Label's Caption as Selected Orders.
  5. 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.
  6. Change the Name Property of the second Command Button to cmdout and insert a less than symbol (<) in the Caption property.
  7. Create another Command Button below both List Boxes. Change the Name Property to cmdPreview and change the Caption Property to Preview Orders.
  8. Select File – -> Save and save the Form with the name ORDERLIST.
  9. Display the VBA Code Module of the Form (View – -> Code). Copy and paste the following Codes into the VBA Module of the Form.
    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
    
  10. 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 at the right side and save the Form.
  11. 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.
  12. 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 into the end of the first list. You may try the selection method this way few more times.
  13. When you have completed the selection process click the Preview Orders Command Button.

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 design Form to display the contents or use it as source for other processing steps.

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

StumbleUpon Toolbar