Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    19

    Unanswered: Passing a collection through to a query

    I have a form with a listbox on it. User can select multiple selections and they are placed into a collection. How do I pass this collection through to a query. By the way, the query is a query of customer transactions. The collection is the customer numbers. I am wanting to pass the collection to the selection criteria of the customer number field in the query. This query is calld qryCustTrans and was not created using code, but with the query window. The listbox is lbCusts and the form is frmCusts.

    Thanks

    here is my code for the collection:

    Dim colCust As New Collection
    Dim varCust As Variant


    For Each varCust In lbCusts.ItemsSelected()
    colCust.Add lbCusts.Column(0, varCust)
    Next

  2. #2
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    You have to build your 'Where' clause in code. This means you have to work on your querydef through code. In a multiuser environment this is a pest because a querydef is a persistant object.
    You could get your result in the following way:
    Create your query WITHOUT parameter (select all records)
    Create a form based on your query and apply a filter on it, as shown below.


    Now, your form that contains the list box:
    Create a command button and insert the following code for the Click event:

    '-------------start------------------
    Dim myfltr As String 'this is a filter built on-the-fly
    Dim ctl As Control 'object variable-your list box
    Dim varItm As Variant 'variable to loop through items
    Set ctl = Me("YourListBoxName") 'set the object variable

    For Each varItm In ctl.ItemsSelected
    'extract each value and place it into a string
    myfltr = myfltr & ctl.ItemData(varItm) & ", "
    Next varItm

    'build the final filter - note the In operator
    myfltr = "TableName.FieldName In (" & Left(myfltr, Len(myfltr) - 2) & ")"
    DoCmd.OpenForm "SecondForm", acFormDS, , myfltr
    '-------------end---------------------

    Replace TableName with the name of the table that stores the information listed in the list box
    Replace FieldName with the name of the field that stores the information listed in the list box
    Replace SecondForm with the name you have given to the newly created form.

  3. #3
    Join Date
    May 2002
    Posts
    19

    -Continued-

    The problem I have (actually, I am not sure if it is a problem), I have other queries that use this first query as a base. Just returning the results to a form will not allow the other queries to operate, will it?

  4. #4
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    Well, try this:

    Dim myqdf As QueryDef
    Dim frm As Form, ctl As Control
    Dim varItm As Variant
    Set ctl = Me("YourListBox")
    For Each varItm In ctl.ItemsSelected
    myfltr = myfltr & ctl.ItemData(varItm) & ", "
    Next varItm
    Set myqdf = CurrentDb.QueryDefs("YourQueryName")
    myqdf.SQL = "Select * From Downloads Where MsgID In (" & Left(myfltr, Len(myfltr) - 2) & ")"
    DoCmd.OpenQuery "YourQueryName"

    I tested it with one of my tables, so you have to replace the SQL string with what you have. If you have other conditions except this one in your query, post the SQL you have and we'll fix it.
    This changes the definition of your query within the database, so all processes will see the changes immediately.

    HTH

    Dan

  5. #5
    Join Date
    May 2002
    Posts
    19

    -Continued-

    HEy, thanks alot. I think I am getting closer. I have enclosed the SQL of the original query. I did not mention it earlier (did not think that it was relavent), but there is also a data criteria. I have two text boxes that the user inputs begining and ending data and the query filters out the transaction with the appropriate date range. The current criteria for the customer number is just a parameter that prompts for a single customer. As you know with the listbox, I want to change that to the string of customers number.


    SELECT (***Field Names***)
    FROM tblContribMargin
    WHERE (((tblContribMargin.CustNo)=[Forms!AVR ReportSwitchboard]![CustNo]) AND ((tblContribMargin.[Inv Date]) Between [Forms]![frmAVR Report Switchboard]![begDate] And [Forms]![frmAVR Report Switchboard]![endDate]));

  6. #6
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    Change the Where clause:

    WHERE (((tblContribMargin.CustNo) In (" & Left(myfltr, Len(myfltr) - 2) & ")) AND ((tblContribMargin.[Inv Date]) Between [Forms]![frmAVR Report Switchboard]![begDate] And [Forms]![frmAVR Report Switchboard]![endDate]));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •