LEARN MS-ACCESS TIPS AND TRICKS: Selected List Box Items and Dynamic Query

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.

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.

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#));

Query

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)));

Query

Open a new Form in Design View.

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

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.

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.

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.

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.

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

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

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

Select the Command Button, display the Property Sheet, if it is not visible, and change the following property Values:
```
• Name    =  cmdView
• Caption =  View Orders
```
Create another Command Button to the right of earlier one. Display the Property Sheet and change the following Property Values:


• Name    =  cmdReset
• Caption =  Reset

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

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.

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 View Orders 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.

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

Labels: msaccess forms