Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Report filtering by Mult-select list box?

    I have a list box from which a user can select multiple Focus Factories (FF). What I want is the report to filter it's output based on the selected FF's. Currently I have the Query created that drives the report (no defined criteria). The report is also created. On my form, I have the list box (ListFilter) that contains the list of FF's and the button to run the report.

    List Box Loop code:
    Code:
    Private Function GetCriteria() As String
       Dim stDocCriteria As String
       Dim VarItm As Variant
       For Each VarItm In ListFilter.ItemsSelected
      stDocCriteria = stDocCriteria & "[FF] = " & ListFilter.Column(0, VarItm) & " OR "
       Next
       If stDocCriteria <> "" Then
      stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
       Else
      stDocCriteria = "True"
       End If
       GetCriteria = stDocCriteria
    End Function
    Button Code:
    Code:
    Private Sub previewReportButton_Click()
    DoCmd.OpenReport "rptInventory Detail Current Quarter - Report", acPreview, , GetCriteria()
    End Sub
    If no FF's are selected, report shows them all as expected. If I select, say, "MaxiBolt" and "BiMetal", I get a Enter Parameter Value popup (see picture) that says "MaxiBolt", then another that says "BiMetal". If i enter in the corresponding FF's into those boxes, the report works fine. If I enter nothing, the report returns nothing. But obviously, i don't want these popups happening. Any ideas?
    Attached Thumbnails Attached Thumbnails ScreenHunter_01 Oct. 14 14.57.gif  

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I tink you need apostrophes round the filter variable as they are text/string type fields ie.

    stDocCriteria = stDocCriteria & "[FF] = " & ListFilter.Column(0, VarItm) & " OR

    HTH

    MTB

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by MikeTheBike View Post
    Hi

    I tink you need apostrophes round the filter variable as they are text/string type fields ie.

    stDocCriteria = stDocCriteria & "[FF] = " & ListFilter.Column(0, VarItm) & " OR

    HTH

    MTB
    That was it! Thank you!

  4. #4
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1

    Report based on multiple selected fields

    Hi

    I am trying to base a report on selected fields in a list box. However even though I am sure I followed the code noted above, when I run the report, it returns all records in the table NOT just the selected ones.

    I understand that Access will do this if no records are selected at all.

    Can anyone offer any pointers?

    My attempt at this is noted below.


    Private Function GetCriteria() As String
    Dim stDocCriteria As String
    Dim VarItm As Variant
    For Each VarItm In ListFilter.ItemsSelected
    stDocCriteria = stDocCriteria & "[NodeName] = '" & ListFilter.Column(0, VarItm) & " OR "
    Next
    If stDocCriteria <> "" Then
    stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
    Else
    stDocCriteria = "True"
    End If
    GetCriteria = stDocCriteria
    End Function

    Private Sub ButtonOpen_Click()
    DoCmd.OpenReport "List_All_Hosts", acPreview, GetCriteria()
    End Sub

  5. #5
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1

    Report based on multiple selected fields

    As a final point, in the VB editor, when I select the ListFilter object, Access inserts a couple of lines of code like "Private Sub ListFilter_BeforeUpdate(Cancel As Integer)".

    Do I need this?

    Thanks hugely in advance. I am dying here.... ;(

  6. #6
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Raddle View Post
    Hi

    I am trying to base a report on selected fields in a list box. However even though I am sure I followed the code noted above, when I run the report, it returns all records in the table NOT just the selected ones.

    I understand that Access will do this if no records are selected at all.

    Can anyone offer any pointers?

    My attempt at this is noted below.


    Private Function GetCriteria() As String
    Dim stDocCriteria As String
    Dim VarItm As Variant
    For Each VarItm In ListFilter.ItemsSelected
    stDocCriteria = stDocCriteria & "[NodeName] = '" & ListFilter.Column(0, VarItm) & " OR "
    Next
    If stDocCriteria <> "" Then
    stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
    Else
    stDocCriteria = "True"
    End If
    GetCriteria = stDocCriteria
    End Function

    Private Sub ButtonOpen_Click()
    DoCmd.OpenReport "List_All_Hosts", acPreview, GetCriteria()
    End Sub
    Make sure that you are referring to the filter box's name. I name mine "ListFilter". Did you name yours the same? Otherwise the code doesn't know where to look for the variants (selected criteria).

  7. #7
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Hey Clawlan

    Thank you so much for coming back to me. Yes I already called the list box ListFilter. Still no joy.

    I wonder if I can send a screen shot of the properties tab?

    Any more ideas very welcome.

    UPDATE -

    The issue seems to be that I can't get the code in the code page to link to ListFilter. This is what I end up with:

    Private Sub ButtonOpen_Click()

    DoCmd.OpenReport "List_All_Hosts", acPreview, GetCriteria()

    End Sub

    Private Function GetCriteria() As String
    Dim stDocCriteria As String
    Dim VarItm As Variant
    For Each VarItm In ListFilter.ItemsSelected
    stDocCriteria = stDocCriteria & "[NodeName] = '" & ListFilter.Column(0, VarItm) & " OR "
    Next
    If stDocCriteria <> "" Then
    stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
    Else
    stDocCriteria = "True"
    End If
    GetCriteria = stDocCriteria
    End Function


    Private Sub ListFilter_Click()

    End Sub


    I think the code needs to be placed under this "Private Sub ListFilter_Click()
    " section but I can't seem to get it in there and save without an error.
    Last edited by Raddle; 12-05-10 at 08:19.

  8. #8
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Make sure both are on the form's code page (rather than on different pages/modules) and get rid of the "private" in both

    Also, the things that you are selecting in the list box, are they strings? Perhaps you can provide some screen shots.

  9. #9
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Last piece of information.. even if I deliberately get the name of the list filter wrong in the code, it does not error, it just posts all results back. It is like it is not even seing that code at all.

    I know, I know, I hate me too


  10. #10
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    you are also missing an apostrophe!! Maybe this is the issue, haha!

    Here is your code:
    stDocCriteria = stDocCriteria & "[NodeName] = '" & ListFilter.Column(0, VarItm) & " OR "

    There is an apostrophe missing before the "OR":
    stDocCriteria = stDocCriteria & "[NodeName] = '" & ListFilter.Column(0, VarItm) & "' OR "

  11. #11
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Hey .. thanks again. So I put them both on the Form's code page. I did this by selecting From in the Properties tab and then clicking View Code. It opens the VB editor with code already in there though.

    Function GetCriteria() As String
    Dim stDocCriteria As String
    Dim VarItm As Variant
    For Each VarItm In ListFilter.ItemsSelected
    stDocCriteria = stDocCriteria & "[NodeName] = '" & ListFilter.Column(0, VarItm) & "' OR "
    Next
    If stDocCriteria <> "" Then
    stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
    Else
    stDocCriteria = "True"
    End If
    GetCriteria = stDocCriteria
    End Function


    Sub ButtonOpen_Click()
    DoCmd.OpenReport "List_All_Hosts", acPreview, , strWhere
    End Sub

  12. #12
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    This is slightly wrong:

    Sub ButtonOpen_Click()
    DoCmd.OpenReport "List_All_Hosts", acPreview, , strWhere
    End Sub

    Should be:
    Sub ButtonOpen_Click()
    DoCmd.OpenReport "List_All_Hosts", acPreview, , GetCriteria()
    End Sub

    If that still doesn't work, copy/paste your code again from the code page now that it is all in one place and see where the error's are occurring.

  13. #13
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    ooooooooooooh Clawlan, the Clawman!!

    Fantastic. Wonderful. Happy Christmas. Happy birthday. All of it matey.

    Thanks you sooo much.



    So the final code (althgough I havre no idea if this is on the form or not,, the editor has me foxxed) ---------------------------------------------------------------------------------------------------------------------------------------------

    Function GetCriteria() As String
    Dim stDocCriteria As String
    Dim VarItm As Variant
    For Each VarItm In ListFilter.ItemsSelected
    stDocCriteria = stDocCriteria & "[NodeName] = '" & ListFilter.Column(0, VarItm) & "' OR "
    Next
    If stDocCriteria <> "" Then
    stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
    Else
    stDocCriteria = "True"
    End If
    GetCriteria = stDocCriteria
    End Function


    Sub ButtonOpen_Click()
    DoCmd.OpenReport "List_All_Hosts", acPreview, , GetCriteria()
    End Sub

    Private Sub Form_Load()

    End Sub

  14. #14
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Cool, glad we got it figured out.

    EDIT: You can also get rid of:

    Code:
    Private Sub Form_Load()
    
    End Sub
    That is not doing anything.

  15. #15
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Need the Claw ...

    Looking for the code to unhighlight selections in that fileter list.

    Any ideas?

Posting Permissions

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