Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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.

Share:

3 comments:

  1. Hi

    Hope you can help me, I have a combo box on a form wich filters data depending on information in a text box. This works fine, but i need to give the user a possibility to check a box that says unfilter to show all data instead of filtered data. I have tried for probably 8-10 hours now, and i give up :)

    Regards

    Bjorn

    ReplyDelete
  2. Hi,

    Since, you are using a Text Box to set the criteria to filter data in the ComboBox you can use the same Text Box to remove the filter as well.

    When you set some value into the Text Box and leave out of the Text Box (Lost Focus Event) the Combo Box uses the Data in the Text (if available) to filter the Combobox contents or if the Text Box is empty then the ComboBox wil show the entire Source data.

    This can be done by evaluating the text box contents and build an SQL String and change the Row Source Property of the Combobox and Requery the Combobox contents.

    The sample code is given below, which uses the Order Details file from Northwind.mdb database:

    Private Sub Criteria_LostFocus()
    Dim xOrderID, SQL As String

    xOrderID = Nz(Me![Criteria], 0)
    If xOrderID = 0 Then
    SQL = "SELECT [ORDER DETAILS].* FROM [ORDER DETAILS];"
    Else
    SQL = "SELECT [ORDER DETAILS].* FROM [ORDER DETAILS] WHERE ((ORDERID=" & xOrderID & "));"
    End If
    Me.cboOrder.RowSource = SQL
    Me.cboOrder.Requery

    End Sub

    I have used the TextBox Name as Criteria and ComboBox Name as cboOrderin the Code.

    Regards,
    a.p.r. pillai

    ReplyDelete
  3. Hi

    It worked! After that i did som thinking and created a button instead to call the event to show all data unfiltered, it seems to be more logical for the user instad of a checkbox.

    Thanks alot for your help!

    Regards

    Björn

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code