Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 12th, 2007, 09:11 PM
Registered User
 
Join Date: Jun 2007
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to pass filter form criteria to a sub report??

I have a filter form that passes criteria to a report that contains two sub reports. Right now, I am able to pass filter criteria into the main report successfully. However, one of the filter criterion on the filter form is a column within one of the sub reports. The problem I am having is not being able to figure out how to pass criteria to the sub report dynamically (i.e. from the filter form). Otherwise, if I manually enter the filter criteria into the sub report's Filter property, then I am able to meet the requirement just fine. Unfortunately for me, the Filter property of the sub report needs to set dynamically from the filter form. Any ideas on how to pass form filter criteria into the sub report are greatly appreciated!

Below is the VBA code that resides behind the command button (cmdViewReport) on the filter form that opens the main report with embedded sub reports. Criteria that is selected within the filter form control ListNoteType should ultimately be used to filter the column note_type_id within the sub report named rpt_business_reqs_subreport_configuration_notes (not referenced in any of the code below).

Code:
Private Sub cmdViewReport_Click()
    Dim FilterString As String
    'Dim FilterString2 As String

    ' Add the controls to our report filter criteria
    AddFilterCriteria FilterString, ListRequirementID, "id"
    AddFilterCriteria FilterString, ListFunction, "function_id"
    AddFilterCriteria FilterString, ListProcess, "process_id"
    AddFilterCriteria FilterString, ListStatus, "status_id"
    'AddFilterCriteria FilterString2, ListNoteType, "note_type_id"

    'check to see if any results return
    Dim results As New ADODB.Recordset
    Dim reccnt As Integer
    reccnt = 0
    If FilterString <> "" Then
        With results
            .CursorLocation = adUseClient
            .Open "select * FROM tbl_business_reqs WHERE " & FilterString, _
                CurrentProject.Connection, adOpenKeyset, adLockOptimistic
            reccnt = .RecordCount
            .Close
        End With
    Else
        With results
            .CursorLocation = adUseClient
            .Open "select * FROM tbl_business_reqs", _
                CurrentProject.Connection, adOpenKeyset, adLockOptimistic
            reccnt = .RecordCount
            .Close
        End With
    End If

    ' Open the form
    Debug.Print FilterString
    If reccnt = 0 Then
        MsgBox "The selected filter criteria did not return any matching records.  Please clear your criteria and try again."
    Else
        DoCmd.OpenReport "rpt_business_reqs_with_configuration_details", acViewPreview, , FilterString
        If FilterString <> "" Then
            With Reports![rpt_business_reqs_with_configuration_details]
                .Filter = FilterString
                .FilterOn = True
                .lblFilterCriteria.Visible = True
                .txtFilterCriteria.Visible = True
                .txtFilterCriteria.Value = FilterString
            End With
        Else
            With Reports![rpt_business_reqs_with_configuration_details]
                .lblFilterCriteria.Visible = False
            End With
        End If
    End If
End Sub

I believe that user selections within the filter form control ListNoteType should be captured within a different string (e.g. FilterString2) than the other controls (all of which tie to the main report), but that's basically as far as I've gotten so far.

Please help!






Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamically pass ADO recordset from Form to Report tcarnahan Access 1 June 19th, 2008 11:31 AM
How to display filter criteria in report header?? Jabba007 Access 2 July 17th, 2007 12:06 PM
Passing Criteria to a Sub Report from Filter Form Jabba007 Access VBA 0 July 11th, 2007 04:56 PM
Form Criteria for Report MArgente Access 10 June 8th, 2007 12:30 PM
Pass Form Variable to Report (DataReport) coboy VB How-To 1 September 22nd, 2004 12:19 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.