Selected List Box Items and Dynamic Query

Introduction.

The Billing Department of Northwind Traders selectively processes their Orders for shipment of materials. An Order Selection Screen has been provided with a List Box on which they can highlight the Order Numbers, Filter the selected Orders from the main file and prepare Customer Invoices and Shipping Documents. They must be able to process all the Orders appearing in the list as well.

The items appearing in the List Box themselves are records selected from within a Date-Range from the Main Order File. By creating two text controls for start-date and end-date values we can control the selection of records for the list box if needed. But for now, we will work with items selected from the List Box, for simplicity.

Get Orders Table from Northwind.mdb

We need the Orders Table from the Northwind.mdb sample Database for our example.

1. Import the Orders Table from the sample Database NorthWind.mdb, if it is not already done in our earlier examples. If you are not sure about the exact location of this Database on your PC, visit the page: Saving Data on Form not in Table, for its location references.
   
   

   The SQL for sample Query.

2. Copy and paste the following SQL String into a new Query's SQL editing window and save the Query with the Name OrdersINQ
   

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

   With the above query, we are selecting all the Orders for the Month of May 1998 for the List Box items. The criterion is set as a constant in the Query.

3. Copy and paste the following SQL String into another Query's SQL editing window and save it with the Name OrdersOUTQ

   SELECT Orders.*
   FROM Orders
   WHERE (((Orders.OrderID) In (11071)));

   The second Query definition will be changed (the criteria part) dynamically, based on the selection of items from the List Box.

   Design a Sample Form.

4. Open a new Form in Design View.

5. If the Toolbox is not visible, click the Toolbox button on the Toolbar above or select Toolbox from View Menu.

6. Ensure that the Control Wizard button (top right button on the Toolbox) is selected. Select the List Box Control on the Toolbox and draw a List Box on the Form as shown in the design image below.

   



7. On the List Box Wizard ensure that the "I want the List box to look up the values in a Table or Query" is selected and click Next.

8. Select the Queries Option in the next view to display the Queries List. Scroll down the list and find the Query with the name OrdersINQ, select it and then click Next.

9. Select the Fields OrderID and CustomerID from the Available Fields list and move them to the Selected List window, click Next and then click Finish.

10. Align the List Box and its child Label as shown in the sample design above.

11. Click on the List box and display the Property Sheet (View - -> Properties) and change the following Property Values as given below:

    • Name = List1

    • Multi Select = Simple

12. De-select the Control Wizard Button on the Toolbox and select the Command Button Tool and draw a Command Button underneath the List Box.

13. Select the Command Button, display the Property Sheet, if it is not visible, and change the following property Values:

    • Name = cmdView

    • Caption = View Orders

14. Create another Command Button to the right of the earlier one. Display the Property Sheet and change the following Property Values:

    • Name = cmdReset

    • Caption = Reset

    
    

    The Form's Class Module Code.

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

    Private Sub cmdview_Click()
    '-----------------------------------------------------------------------
    'Author : a.p.r. pillai
    'Date   :  01/05/2008
    'URL    :  www.msaccesstips.com
    'All Rights Reserved by msaccesstips.com
    '-----------------------------------------------------------------------
    Dim strsqlO As String, crit As String, strsql As String
    Dim db As Database, qryDef As QueryDef
    Dim strOrders As String, xoutlist As ListBox, listcount As Integer
    Dim j As Integer, selectcount As Integer
    
    strsql0 = "SELECT OrdersINQ.* FROM OrdersINQ "
    crit = "WHERE (((OrdersINQ.OrderID) In ("
    
    Set xoutlist = Me.List1
    listcount = xoutlist.listcount - 1
    
    strOrders = "": selectcount = 0
    For j = 0 To listcount
      If xoutlist.Selected(j) = True Then
        selectcount = selectcount + 1
        If Len(strOrders) = 0 Then
           strOrders = xoutlist.Column(0, j)
        Else
           strOrders = strOrders & ", " & xoutlist.Column(0, j)
        End If
      End If
    Next
    
    If selectcount = 0 Then
       strsql = Trim(strsql0) & ";"
    Else
       strsql = strsql0 & crit & strOrders & "))); "
    End If
    
       Set db = CurrentDb
       Set qryDef = db.QueryDefs("OrdersOUTQ")
       qryDef.Sql = strsql
       db.QueryDefs.Refresh
       DoCmd.OpenQuery "OrdersOUTQ", acViewNormal
    
       Set db = Nothing
       Set qryDef = Nothing
    End Sub

     

    Private Sub cmdReset_Click()
    '-----------------------------------------------------------------------
    'Author : a.p.r. pillai
    'Date   :  01/05/2008
    'URL    :  www.msaccesstips.com
    'All Rights Reserved by msaccesstips.com
    '-----------------------------------------------------------------------
    Dim xoutlist As ListBox, j As Integer
    Dim listcount As Integer
    
    Set xoutlist = Me.List1
    listcount = xoutlist.listcount - 1
    For j = 0 To listcount
      If xoutlist.Selected(j) = True Then
         xoutlist.Selected(j) = False
      End If
    Next
    End Sub

    
    
    

    Test Run of the Program

16. Open the Form in Normal View. Click on a few Orders one by one in the List Box to select them. You can click on the desired item again to de-select it or click on the Reset Command Button to de-select all.

17. Click on the View Orders Command Button to redefine the second Query OrdersOUTQ and open it to show the selected Orders in Datasheet View.

If you need all items in the List for output, then Click Orders View Command Button without making any selection or after clicking Reset Command Button.

You can use the OrdersOutQ Query with selected items as a Source to link with other Queries or related Tables and design Reports to print Invoices or Design Screen to display selected Order Details.

• Filtering Data for Different Users
• Crosstab Union Queries for Charts
• Union Query
• Percentage on Total Query
• Selecting Office Assistant