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

Introduction.

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.

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 have used a method by highlighting items and using them as a 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 in 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, which 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 the running view is given below.

The List Boxes Image

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 < as an indicator). The selected items will be moved back into the original list and removed 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 trick we cannot directly use the OrdersinQ Query as Row Source for the List Box, as we did in the earlier example.

Preventing multiple Item Selection

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 a variety of methods.

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 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 matched with the above values. Change the child Label's Caption as 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 Codes 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. You may 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 design 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.