Learn Microsoft Access Advanced Programming Techniques, 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.

  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.

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

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts