Results 1 to 11 of 11
  1. #1
    swalsh84 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    36

    Dynamic With Statement


    Code:
     
    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?

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    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.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Why not one report that only displays the case notes if chosen?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    swalsh84 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    36
    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.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    swalsh84 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    36
    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...

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    swalsh84 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    36
    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!

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    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:

    Code:
    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
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    swalsh84 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    36
    This got me where I need to get. Again, thank you for the help!

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Excellent! Glad we sorted it out.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dynamic Web page
    By KevinH in forum Access
    Replies: 3
    Last Post: 01-03-2010, 06:56 PM
  2. Dynamic Report
    By vCallNSPF in forum Reports
    Replies: 0
    Last Post: 12-08-2009, 04:19 PM
  3. Dynamic Query
    By pushpm in forum Queries
    Replies: 0
    Last Post: 04-22-2009, 12:58 PM
  4. Dynamic reporting
    By pushpm in forum Programming
    Replies: 0
    Last Post: 04-22-2009, 12:45 PM
  5. dynamic textbox
    By vam in forum Programming
    Replies: 1
    Last Post: 06-04-2006, 08:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums