Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Multi selecting from a list (2002)

    Hi,

    I have a form that is used to open various reports called frmClientListt. A list called LstOffice is on the form and is used to select the office to filter rptActivePatientCaseload by. I am filtering the report in the query that the report is bound to using [forms]![frmClientList]!lstOffice]. This works fine. I'd like to be able to select more than one office at a time without selecting all of the offices. I tried changing the multiselect property of the listbox to simple or extended vs none but it doesn't work. What am I missing?

    Thanks,
    Leesha

  2. #2
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multi selecting from a list (2002)

    Thanks Wendell for pointing me in the direction of this thread. I've tried the examples given in the sample database and was able to get the first one to run but kept getting errors on the query related example. I now have a better idea of how it works in relation to records but am unclear as to how I would open a report based on it. I tried the code but the report openned with no date. I'll keep experimenting.

    Thanks,
    Leesha

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Multi selecting from a list (2002)

    Have you done a search of the Access forum? If you do one for "multiselect list box" you should find <post#=402675>post 402675</post#> by <!profile=Francois>Francois<!/profile> which contains a link to a very useful article on how to create a multiselect list box.
    Wendell

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multi selecting from a list (2002)

    OK, I've had success getting the report to open and it even opens to the correct offices. I'm thrilled! My problem is that I need to have the the report only show active records .........[status_a] = "a" ..............I tried putting this in the query, which is where it was originally but every time the code executes it changes the format of the query that the report is bound to. I tried adding it as a where statement that opens the report but it kept blowing up on me. As always I'm missing some expression. This is the code that I am presently using that works fine but does not filter [status_a] by "a".


    Dim Q As QueryDef, DB As Database
    Dim Criteria As String
    Dim ctl As Control
    Dim Itm As Variant

    ' Build a list of the selections.
    Set ctl = Me![lstOffice]

    For Each Itm In ctl.ItemsSelected
    If Len(Criteria) = 0 Then
    Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
    Else
    Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
    & Chr(34)
    End If
    Next Itm


    ' Modify the Query.
    Set DB = CurrentDb()
    Set Q = DB.QueryDefs("qryActiveListByOffice")
    Q.SQL = "Select * From tblClientList Where [Off_Name] In(" & Criteria & _
    ");"
    Q.Close

    DoCmd.OpenReport "rptActiveListbyOffice", acViewPreview

    It is different than than the sample database code in that I don't actually have the query open but rather the report.


    Any help with the filtering would be greatly appreciated!

    Thanks,
    Leesah

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi selecting from a list (2002)

    Just add the supplemental condition to your query definition :
    Q.SQL = "Select * From tblClientList Where [Off_Name] In(" & Criteria & _
    ") AND [status_a] = 'a';"
    Francois

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multi selecting from a list (2002)

    Thanks! Worked like a charm.

    One more question on this thread (I think). Does the IsNull coding work differently with multiselect lists? When I have the list as none for muliselect and I use an IsNull statement to prompt the user to select an office name from the list (if the list is blank) it works fine. When I set the control to extended under the multiselect property, my message box prompt comes up even when one or more names is selected from the list.

    Thanks,
    Leesah

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

    Re: Multi selecting from a list (2002)

    You can use ctl.ItemsSelected.Count (where ctl is the list box) to determine the number of items the user has selected. You can test like this, instead of using IsNull:

    If ctl.ItemsSelected.Count = 0 Then
    ' Nothing selected
    ...
    Else
    ' At least one item selected
    ...
    End If

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multi selecting from a list (2002)

    Here we go, another new area to learn. I've never used itemsSelected before. It worked beautifully!!

    Thanks,
    Leesah

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

    Re: Multi selecting from a list (2002)

    If you position the insertion point in ItemsSelected and press F1, you'll get help for this property, with an explanation and examples of its use. By clicking on "See Also", you can get information on related subjects, in this case ItemData and MultiSelect. You can also click "Applies To". If you select ListBox, you can view all properties, methods and events of the ListBox object, get help about those etc.
    Often, this is a good way to find out how to use an object.

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multi selecting from a list (2002)

    Thanks for the tip. I will try that. I find it so frustrating knowing that there is a lot more I could be doing but I have no idea it exists. I do love the adrenlin rush of learning something new which is why I keep plugging at it and I certainly appreciate the wealth of knowledge and patience in the lounge!

    Have a great day,
    Leesha

Posting Permissions

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