Billing Department of Northwind Traders processes their Orders selectively 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.

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.

    

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.

    

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 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 earlier one. Display the Property Sheet and change the following Property Values:

     

    • Name = cmdReset
    • Caption = Reset

     

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

     




 

16. Open the Form in Normal View. Click on few Orders one by one in the List Box to select them. You can click on the selected 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 Source to link with other Queries or related Tables and design Reports to print Invoices or Design Screen to display selected Order Details.