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 VBA
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 11th, 2007, 04:56 PM
Registered User
Join Date: Jun 2007
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Passing Criteria to a Sub Report from Filter Form

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).

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
        End With
        With results
            .CursorLocation = adUseClient
            .Open "select * FROM tbl_business_reqs", _
                CurrentProject.Connection, adOpenKeyset, adLockOptimistic
            reccnt = .RecordCount
        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."
        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
            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

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
How to display filter criteria in report header?? Jabba007 Access 2 July 17th, 2007 12:06 PM
How to pass filter form criteria to a sub report?? Jabba007 Access 0 July 12th, 2007 09:11 PM
Form Criteria for Report MArgente Access 10 June 8th, 2007 12:30 PM
Multiple criteria for a Report stealthdevil Access VBA 33 June 8th, 2006 10:38 AM
Print selection criteria on report sjperl VB Databases Basics 1 May 3rd, 2004 11:41 PM

All times are GMT -4. The time now is 08:55 AM.

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