Change report filters and report label caption based on form command

[newdistrict]

ETA: working with MS Access 2010

I have a form, frmProjects, that has 30 command buttons. Each command button has a unique caption (e.g. Team 1 Active Projects, Team 1 Upcoming Projects, Team 2 Active Projects, Team 2 Upcoming projects, etc.).

Right now, I have it set up so that each command button on the form opens a unique report, e.g. rptTeam1_Active, rptTeam1_Upcoming, rptTeam2_active, etc. The reports are all similarly designed, but have different filters for each respective team and project status, so only that team's active and/or upcoming projects is displayed. The only difference in design is a Label (lets call it Label2 for reference) at the top of the report that has the Team's name and assignment type as the caption for the label.

This set up makes it extremely tedious to make any changes to the report design, since each time I want to make a modification, I have to apply it manually to 30 different reports, i.e. each time I want to add a new column to the report, I have to make the change 30 times so that it is reflected in each report for each team and project status.

Is there anyway I can program the command buttons to open the same report object, lets call it rptProjects, but to apply different filters for each command button? For example, if I click on CommandTeam1Active on frmProjects, can I program the event procedure so that it will open rptProjects with the Data filter for the report set to (([tblProjects].[Status]="Active" And [tblProjects]![Team] = "Team1"))? This way, I only modify one report object (rptProjects) each time I want to make a change to the report's design.

Additionally, is there a way to have Label2 on rptProjects populate with the caption of the Command Button the user clicks on in frmProjects?

Thank you in advance for your help.

 

[pbaldy]

I would likely have a dropdown for team, another for status, and a single button/report. You can use a parameter query or this type of thing to filter the report:

http://www.baldyweb.com/wherecondition.htm

 

[newdistrict]

I would likely have a dropdown for team, another for status, and a single button/report. You can use a parameter query or this type of thing to filter the report:

Thanks, I will play around with this and see if it will work. I was hoping to avoid creating drop-down menus to select values (even if it would only be two drop downs). The users I am working with want to avoid multiple clicking, i.e. they really like having a stand-alone button for each team/project status report.

 

[pbaldy]

Not very dynamic, but you could keep the buttons and use the link above to put a different wherecondition behind each one. That leaves you with a single report to maintain.

 

[newdistrict]

I was having trouble with the syntax for the WhereCondition - I'm still getting my bearings around SQL. If you have the time to show me an example of how you would setup a wherecondition for the DoCmd.OpenReport "rptProjects" with the Where conditions: [tblProjects]![Status]="Active" And [tblProjects]![Team] = "Team1", I would greatly appreciate it.

Otherwise, I was able to do the following "work-around" where I create a separate query for each filter that I need and then use the FilterName argument, for example:

Private Sub Command1_Click()
    
    DoCmd.OpenReport "rptProjects", acViewReport, "qryTeam1 Upcoming"

Exit_Command1_Click:
    Exit Sub

This works perfectly, except, of course, I need to now create 30 queries for each of the 30 command buttons (still beats having to redesign 30 reports).

Although I am still struggling with coming up with a way to have the heading label for the report to dynamically change based on the form command, e.g. if I click the Team 1 Active Projects command button in frmProjects, is there a way I can tell Access to change the caption on the label on the report to display, "Team 1 Active Projects"?

Again, thank you for your help.

ETA: Never mind! I actually figured it out, here is the code I used for the WhereCondition, and it works great (and saved me the trouble of creating 30 queries... thank goodness). I still need help with the label caption on the report if you have any suggestions for that... Thank you.

Private Sub Command1_Click()
    
    DoCmd.OpenReport "rptProjects", acViewReport, , "([tblProjects]![Status]='Upcoming' And [tblProjects]![Team]='Team1')"

Exit_Command1_Click:
    Exit Sub

ETA2: Sorry for the multiple edits... I just wanted to update that I was able to figure out a solution to the dynamic label caption in the report, using OpenArgs.

In the Form On Click properties:

Private Sub Command1_Click()
    
    Dim strLabelCaption As String

    strLabelCaption = "Team 1 Upcoming Assignments"

    DoCmd.OpenReport "rptProjects", acViewReport, , "([tblProjects]![Status]='Upcoming' And [tblProjects]![Team]='Team1')", acWindowNormal, strLabelCaption

Exit_Command1_Click:
Exit Sub

In the Report On Open properties:

Private Sub Report_Open(Cancel As Integer)
    Dim strLabelCaption As String

    strLabelCaption = Me.OpenArgs

    Me!ReportName.Caption = strLabelCaption

End Sub

In short, thank you for the initial tip - it led me to discovering all the different properties of DoCmd.OpenReport that I wasn't fully aware of before, and really got me thinking in the right direction!

 

[pbaldy]

Happy to help and welcome to the site by the way! I just got back from a street festival, which is why I hadn't replied.

 

[newdistrict]

No problem, and thank you for the welcome! I've been lurking around for awhile and have found this forum to be a wealth of information.