Dynamic With Statement

[swalsh84]

 
With Reports![reportName]
        .Filter = strFilter
        .FilterOn = True
        If strStatus = "Like '*'" Then
            .txtCaseStatus.Value = "All"
        Else
            .txtCaseStatus.Value = Me.cboStatus
        End If

This is what I have. I know that a report name should go into the brackets above, but I have two different reports. The reports are identical except for one field. Basically what I would like to accomplish is to have the user select his/her filter options on a form and check a checkbox as to whether he wants to include the case notes or not. I don't want to have to rewrite all of the code for each report when a variable would do just fine. The problem with the with statement is that it requires an object, and I can't figure out how to make the with statement accept a string in the spot for "reportName".

Any thoughts?

[weekend00]

i don't think you can change any thing in a report afer opening.

you can pass parameter to the report, and deal with the parameter in report's open event.

[pbaldy]

Why not one report that only displays the case notes if chosen?

[swalsh84]

http://www.fontstuff.com/access/acctut19.htm

This is the basis for what I am attempting to do. I have a form that sets the filter and order options for the report. All I want to do is have a checkbox on my form that tells the form code which report to open with the parameters selected without having to rewrite the code for specific report instance.

[pbaldy]

My point is that if the two reports are identical but for the case notes, why not just use one report and only display the case notes when selected? Then you don't need your dynamic code at all.

[swalsh84]

That would work if I could get the syntax to work, but the detail section of the report would be extremely spaced out for the comments that would be invisible when I turn them off...

[pbaldy]

This is untested, but in the open event of the form, where you'd be making the textbox invisible anyway, you could also set the control source of the textbox to "" (making it unbound). That combined with setting the Can Shrink property of the textbox to Yes (or making it short to begin with and setting the Can Grow property to Yes) should cause it not to take up any room vertically.

[swalsh84]

I'm somewhat of a novice when it comes to VBA, but from what I have read, I cannot modify or even access the fields (the checkbox's value specfically) in a form from within the scope of a With statement referencing the report object. Your solution would work perfectly if I knew how to code it.

Thanks for the help!

[pbaldy]

I'm not clear what you mean. Here's an example of a report looking at an option group on a form to determine the sort order and grouping:

Private Sub Report_Open(Cancel As Integer)
  Select Case Forms!frmReports.fraGrouping
    Case 1 'sort/group on car type
      Me.GroupLevel(0).ControlSource = "CarType"
      Me.txtGroup.ControlSource = "CarDesc"
    Case 2 'sort/group on company
      Me.GroupLevel(0).ControlSource = "Company"
      Me.txtGroup.ControlSource = "Company"
    Case 3 'sort on date/time, no grouping
      Me.GroupLevel(0).ControlSource = "DispDateTime"
      Me.txtGroup.ControlSource = "DispDateTime"
      Me.GroupHeader0.Visible = False
  End Select
End Sub

[swalsh84]

This got me where I need to get. Again, thank you for the help!

[pbaldy]

Excellent! Glad we sorted it out.