Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 12th, 2007, 09:11 PM
Registered User
 
Join Date: Jun 2007
Location: , , .
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!

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 02:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.