Applying WHERE conditions to Sub-Reports?

[Spenney]

I have a report designed to show a 'Primary' entity and two type of related 'Secondary' entities. These secondary entities are shown as sub-reports.

I have designed a form to allow a user to select which primary entity they wish to run the report for, and have used 'DoCmd.OpenReport...,[WHERE Condition]'. This works OK.

What I would now like to do is on the form, have two option groups (one for each type of relation) to allow the user to select whether they want to see only the current related entities or the historic ones as well. I have coded the WHERE conditions, but I'm unsure how to apply these to the sub-reports.

Here is my code as it stands...

Private Sub cmdOK_Click()

    Dim strDocName As String
    Dim strCorporateMemberCriteria As String
    Dim strDirectorCriteria As String
    Dim strControllerCriteria As String
                
    'Set Corporate Member WHERE condition to the value supplied by the user
    strCorporateMemberCriteria = "[ent_id] = " & Me.cboCorporate_Member.Column(0)
    
    'Build WHERE condition for all or current Directors
    Select Case Me.optDirectors
    Case 1 'All
        strDirectorCriteria = "([dbo_ent_relation].[er_start_date]) Is Null Or ([dbo_ent_relation].[er_start_date])<=Now())"
    Case 2 'Current
        strDirectorCriteria = "([dbo_ent_relation].[er_start_date]) Is Null Or ([dbo_ent_relation].[er_start_date])<=Now())" _
        And ([dbo_ent_relation].[er_cease_date]) Is Null Or ([dbo_ent_relation].[er_cease_date]) >= Now()
    End Select
        
    'Build WHERE condition for all or current Controllers
    Select Case Me.optControllers
    Case 1 'All
        strControllerCriteria = "([dbo_ent_relation].[er_start_date]) Is Null Or ([dbo_ent_relation].[er_start_date])<=Now())"
    Case 2 'Current
        strControllerCriteria = "([dbo_ent_relation].[er_start_date]) Is Null Or ([dbo_ent_relation].[er_start_date])<=Now())" _
        And ([dbo_ent_relation].[er_cease_date]) Is Null Or ([dbo_ent_relation].[er_cease_date]) >= Now()
    End Select
     
          
    strDocName = "rptCorporate_Member_Summary"
        
    DoCmd.OpenReport strDocName, acViewPreview, , strCorporateMemberCriteria
    
    'Leave Corporate Member Summary Data-entry form open ?
    If Me.chkClose = True Then
    DoCmd.Close acForm, "frmCorporate_Member_Summary"
    DoCmd.SelectObject acReport, strDocName
    End If

End Sub

Basically, how can I set 'strDirectorCriteria' as the WHERE condition to the first sub-report, and 'strControllerCriteria' to the second sub-report?

 

[dRahme]

Hi, That is an interesting question.

I would probably set the where clause in the subreport itself, though I have not tried this and do not know if it would work.

subRpt Open()

Dim StrSql as String

StrSql = MainBody of Sql

Select case forms!theForm.FrmOptionGp
Case 1
StrSql = Strsql & "Where....
Case 2
StrSql = Strsql & "Where....
End Select

me.recordsource = strsql

End Sub

Hope that Helps. It will be interesting to see your solution.

 

[dhookom]

You can't really modify the filter of a subreport at run-time. You would need to
1) add criteria to the record source of the subreport like:
Between Forms!frmDate!txtStart AND Forms!frmDate!txtEnd
2) Modify the sql value of the saved query a subreport is based on
3) create a single record table for criteria values and include it in your record source
4) rely on the Link Master/Child properties but this only works with single values, not ranges.
5) possibly other methods.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Spenney]

Thank you for the responses.

I did try creating the SQL dynamically and using this as the record source of each sub-report, but couldn't fathom out a Type Mismatch error that I was getting.

Anyway, eventually I achieved what I was trying to do using criteria in the query like Duane's first suggestion.

One again, thank you.


Stuart