Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query from Mulit-Selection List Box (2000)

    There is no way to do this from within the query. You will have to build a WHERE clause in the form from the user's selections in the listbox, then use that as one of the criteria when you open the Report using the Docmd.OpenReport method.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from Mulit-Selection List Box (2000)

    I have a multiselect listbox "lstSelectClients" that lists each of our clients. The user can select multiple clients from this list. There is an "After Update" event on this list box that calls this procedure:
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
    'for any form where users need to select multiple clients from a multiselect list box. This functions returns a WHERE statement that can be used in queries, report filters, or other procedures.

    'returned string looks like this:
    ' "[CLIENT]= '11124' Or [CLIENT]= '11128' Or [CLIENT]= '11134'"
    '
    '
    Public Function SelectedClients() As String
    'create filter for selected records
    Dim ctlSource As Control
    Dim strItems As String
    Dim intCurrentRow As Integer
    Dim intStringLength As Integer
    Dim strAster As String
    Dim strQuote As String
    strQuote = Chr(34)
    strAster = Chr(42)
    'put your form name here.
    Set ctlSource = Forms![frm_Your_Form_Here]!lstSelectClients

    'evaluate number of items on list to make the filter query
    'processes more than one item
    For intCurrentRow = 0 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
    strItems = strItems & "[CLIENT]= " & "'" & ctlSource.Column(0, _
    intCurrentRow) & "'" & " Or "
    End If
    Next intCurrentRow

    'remove the last "Or" from the search string
    intStringLength = Len(strItems)

    If intStringLength = 0 Then
    'If user does not select any clients from the list, the WHERE clause returns a wildcard
    strItems = "[CLIENT] Like " & "'" & strAster & "'"
    Else
    strItems = Left(strItems, (intStringLength - 4))
    End If
    'pass value to function
    SelectedClients = strItems

    'Reset destination control's RowSource property.
    Set ctlSource = Nothing

    End Function
    '''''''''''''''''''''''''''''''''''''''''''''''''
    In your query, put SelectedClients() as the parameter for selection.
    There are probably other ways to skin this cat but this works for me. Hope this helps.

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query from Mulit-Selection List Box (2000)

    AAP2, that looks promising.

    Let me play with that in a bit.

    Thanks!
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query from Mulit-Selection List Box (2000)

    Whether this query is in a report or a form, the fact remains that you can't reference the selected values of a multiselect listbox from a query.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query from Mulit-Selection List Box (2000)

    In your form, how are you calling this procedure in the After Update Event?

    In a new module I have created a new Public Function by basically copying your code & modifying it slightly:

    Public Function SelectedDocType() As String
    'create filter for selected records
    Dim ctlSource As Control
    Dim strItems As String
    Dim intCurrentRow As Integer
    Dim intStringLength As Integer
    Dim strAster As String
    Dim strQuote As String
    strQuote = Chr(34)
    strAster = Chr(42)
    'put your form name here.
    Set ctlSource = Forms![frm_Reports]!List36

    'evaluate number of items on list to make the filter query
    'processes more than one item
    For intCurrentRow = 0 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
    strItems = strItems & "[Document Type]= " & "'" & ctlSource.Column(0, intCurrentRow) & "'" & " Or "
    End If
    Next intCurrentRow

    'remove the last "Or" from the search string
    intStringLength = Len(strItems)

    If intStringLength = 0 Then
    'If user does not select any clients from the list, the WHERE clause returns a wildcard
    strItems = "[Document Type] Like " & "'" & strAster & "'"
    Else
    strItems = Left(strItems, (intStringLength - 4))
    End If
    'pass value to function
    SelectedDocType = strItems

    'Reset destination control's RowSource property.
    Set ctlSource = Nothing

    End Function


    By having SelectedDocType() as the criteria is not working, so that is why I am asking what you are doing in your AfterUpdate event on the listbox.

    Thanks again!
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  6. #6
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from Mulit-Selection List Box (2000)

    There is a button on my form "cmdOpenReport" that does this:

    Private Sub cmdOpenReport_Click()
    On Error Resume Next

    dim mstrRpt as string
    mstrRpt = "rptClientInventorySummary"

    DoCmd.OpenReport mstrRpt, acViewPreview, , SelectedClients()

    End Sub

    The report gets its data from the query "qryClientInventory" that selects, among other things, Client Number ([CLIENT]), Client Name, Inventory details...
    The public function SelectedClients() returns a WHERE clause used in the DoCmd.OpenReport mstrRpt statement above. The WHERE clause looks like this
    "[CLIENT]= '11124' Or [CLIENT]= '11128' Or [CLIENT]= '11134'"
    SelectedClients() returns data type string.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from Mulit-Selection List Box (2000)

    I suspect, but am not sure, that the problem may be the rowsource of your list box. The rowsource for the listbox on my form has two text fields, [CLIENT], [Client Name] The users selections are put into a text string and the report that is generated is filtered by a text string. If you are trying to build the WHERE clause with something other than text, you need to modify the SelectClients() function slightly to accomodate the data type of your bound field.
    Hope this helps.

  8. #8
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Query from Mulit-Selection List Box (2000)

    I have an Access 2000 database where there is a REPORTS form.

    On this form the user can input a date range & has a few other optional criteria selections. After the user makes their selections, there is a list of reports (presented as forms).
    The user would then select the report they want by clicking a button. The form for the selected report will then appear. This form has an underlying query that uses the criteria information entered on the Reports form. The reports are presented in forms instead of reports as the user then has the ability to drill into the summary data presented on the form.

    Currently 2 of those selections are in Combo Boxes.

    In my query I reference these combo boxes using the NZ function so if the user does not select any options, it is not filtered.
    Here is an example from my query - Like nz([forms]![frm_Reports]![Combo29],"*")
    This works great. If the user makes no selections, nothing is filtered here. If they make a selection in that combo box, it is filtered on that selection.


    What my user really wants to do is to be able to select none, 1 or more codes from the list.

    I can change from using a Combo Box to a List Box & have the list box Multi-selectable, but I do not how to have the query limited by the mulit-selections.

    I have not found any good examples yet looking through the Microsoft Knowledge Base. Article <!mskb=210203>Microsoft Knowledge Base Article 210203<!/mskb> sort of touches on this, but now how I need to use this.

    Does anybody have any good examples or ideas on how to approach this?

    TIA!
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  9. #9
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query from Mulit-Selection List Box (2000)

    Let me clarify things a bit. The "report" is actually a form, not a report. This form report is a summary report listing several areas of summarized data with the ability to drill into the detail for each section.

    On the Reports form, the On CLick action does:
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Frm-Qry_Return Orders - Summary Values by Order Reason 1"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    That form has a record source of Qry_Return Orders - Summary Values by Order Reason 1

    That query ties together 8 other queries. Most of those queries are either summary queries of other queries or have this filtering applied in those queries.

    I am looking at a different post (<post#=287626>post 287626</post#>) to see if I can incorperate some of what is being discussed there.


    Here is the SQL from one of my inital queries:
    SELECT [Return Orders].*, [Forms]![Frm_Reports]![FromDate] AS D1, [Forms]![Frm_Reports]![ToDate] AS D2
    FROM [Return Orders]
    WHERE ((([Return Orders].[SAP Add Date]) Between [forms]![Frm_Reports]![FromDate] And [forms]![Frm_Reports].[ToDate]) AND (([Return Orders].[Item Rejection]) Is Null) AND (([Return Orders].[Direct-Indirect]) Like nz([forms]![frm_Reports]![D-I-M-A],"*")) AND (([Return Orders].[Document Type]) Like nz([forms]![frm_Reports]![Combo29],"*")) AND (([Return Orders].[Order Reason]) Like nz([forms]![frm_Reports]![Combo32],"*")) AND (([Return Orders].[Qty Open])>0));


    So what I would like to change somehow is Like nz([forms]![frm_Reports]![Combo29],"*")
    to something along what is being described in the post I mentioned above so when the various queries that need this filter applied are executed, I can use that snip of SQL in those queries instead of generating the SQL by VB.

    This would have to take into account if nothing is selected also, so I may need to enclose that SQL within a Like nz( ).
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  10. #10
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query from Mulit-Selection List Box (2000)

    Thanks Mark & AAP2 for your input & comments on my task.

    Unfortunatly what I think I need can not be met with AAP2s example. (I can take & modify the output to appear in the query, but not be part of the selection criteria)

    My users want to be able to use 2 mulit-select lists (at least 2 more will be added).

    With the design of some of these summary reports, there are several queries that would need these lists as part of their criteria. (the final summary report ties 7 queries together. Many of those queries are summary queries of other selection queries measuring Order & line item counts, qtys & $ amounts of orders placed during a date range, how many are open that were placed during that date range & how many were closed during that date range, regardless of when placed.)

    What I think I need to do is add a macro to the OnClick events for when the user clicks on the button to execute the report that creates or fills a table (or 2) with the selections from each mulit-select list box.

    Then in my queries that need the filtering, do a left join to these tables (which could have 0 records if the user does not want to zero in on a few of the 60 possible selections) & then filter as needed.

    So, if anybody has or could point me to example code of filling a table from a multi-select list, that would be appreciated!

    Also if someone has a better idea, that would be great also.
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query from Mulit-Selection List Box (2000)

    To be honest, I think you are making this far too complicated. In my experience, users often claim that they need to be able to make very detailed selections, but if you make this possible, they hardly know what to do with them.

    Anyway, if you want to populate a table with items selected in a multi-select list box, you will need to work with a shared back end with the data tables and individual front ends for each user, otherwise you'll get into trouble if multiple users start populating tables (you could give the tables unique names in a shared front end., but I fear you'd run into corruption sooner rather than later).

    For what it's worth, here is code that will clear, then populate the MyNum field in a table tblNums (which must already exist) with items selected in a list box lbx1 on a form. It's implemented as the On Click event procedure of a command button on the form, but that is not essential.

    Private Sub cmdSomething_Click()
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim varItem As Variant

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection
    cnn.Execute "DELETE FROM tblNums", , adCmdText

    rst.Open "tblNums", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    For Each varItem In Me.lbx1.ItemsSelected
    rst.AddNew
    rst!MyNum = Me.lbx1.ItemData(varItem)
    rst.Update
    Next varItem

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  12. #12
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query from Mulit-Selection List Box (2000)

    Thank you Hans for your reply & information.

    Unfortunatly this DB does need to have the ability to filter on more then 1 selection as several codes in the list may be related to a more general order type. I am also hoping that this will be a 1 user DB after this is completed. I have created a few DB's in the past with a single backend DB on a network server & the front end on each user's PC. Trying to keep everybody up to date can be a real PITA when there are changes to the front end. (or when Access corrupts itself!)


    As far as using your code, I need to determine what Reference Library I am missing as my Access 2K does not like ADODB.Connection or ADODB.Recordset.

    Hopefully this DB will be short lived until something similar can be developed in the data host system instead of extracting that data into Access, but that is a few months away.

    John
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query from Mulit-Selection List Box (2000)

    If ADODB.Connection is not recognized, select Tools | References... (in the Visual Basic Editor). Locate and tick the Microsoft ActiveX Data Objects 2.n Library; select the highest version number (2.7 on my PC, but it might be different on yours), then click OK.

  14. #14
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query from Mulit-Selection List Box (2000)

    Thanks again! Saved me a bit of looking, trial & error.

    Doing a right join to the table that gets loaded with the items selected & then using "Is Not Null" for that table/field works great.

    I am also populating a text box with the values that was selected so I can reference that list on the report form (showing what filters was applied) using AAP2's code, but modified a bit.

    I am a bit brain dead at the moment doing data validation on a diferent project. Hopefully I can clear my head enough to create an IIF statement in my query looking to the form to see if it contains "ALL" (nothing was selected so I want everything) or shows a diferent value, then filter the query. (I can also change that to have nothing if no items are selected in the list box) This text box will be hidden.

    So I am thinking of using something along the lines of iif(forms!form_name!field_name = "ALL", do nothing, is not null) in my criteria statement in the querie(s) under the right joined table.

    Back to data validation :-(
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query from Mulit-Selection List Box (2000)

    If this is a question about a different project, I would suggest starting a new thread. This makes it easier for others to follow the discussion. Thanks.

Page 1 of 2 12 LastLast

Posting Permissions

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