Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    filter report by groups

    I have a report that prints values grouped by Dept from a table.
    I have been asked to create a form with a list of Depts that can be selected (toggled, ticked) in any combination so that only the chosen depts print.

    This is what I want:

    As report is formatted, each dept is checked to see if selected on picking form and included if it is.

    Can it be done this way or another way and coding please.
    "Heading for the deep end"

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

    Re: filter report by groups

    Put a list box lstGroups on your form and set its Multi Select property to Simple or Extended. Set its Row Source to a table or query that lists all departments.

    Put a command button cmdReport on your form, and create an On Click event procedure for it:

    Private Sub cmdReport_Click()
    Dim strWhere As String
    Dim varItem As Variant

    On Error GoTo ErrHandler

    If Me.lstGroups.ItemsSelected.Count > 0 Then
    For Each varItem In Me.lstGroups.ItemsSelected
    strWhere = strWhere & ", " & Me.lstGroups.ItemData(varItem)
    Next varItem
    strWhere = "[DeptID] In (" & Mid(strWhere, 3) & ")"
    End If

    DoCmd.OpenReport "rptSomething", acViewPreview, , strWhere
    Exit Sub

    ErrHandler:
    If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    Replace DeptID with the name of the field identifying the department, and rptSomething with the name of the report.
    I have assumed that the "DeptID" field is numeric; if it is text, change

    strWhere = strWhere & ", " & Me.lstGroups.ItemData(varItem)

    to

    strWhere = strWhere & ", " & Chr(34) & Me.lstGroups.ItemData(varItem) & Chr(34)

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: filter report by groups

    Many, many thanks Hans for your coding, it was just what I needed. I'm at the frustrating stage of knowing it can be done, being able to start vaguely in the right direction and quickly getting stumped.
    "Heading for the deep end"

Posting Permissions

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