Opening a subreport specific to a record off main report

[warren0127]

Hello,



Before, I was able to create a continuous form and create a button that would open the specific record the user's cursor had clicked that opens another form that displayed that records information. IE: Select Project, click the open button, main form opens with only that project's record information.

Now, I am trying to do this for a Report - assuming it works the same way. I created a master "Project" report that shows the "rolled up" details of each project and the "totals". I also have another "sub report" that breaks each project down - that shows "products" for all projects.

I would like the user to be able to highlight/select a project from the "Main" report and have it filter that specific project Record while opening the sub report for products under that specific project (kind of how I did it for my form example above). When the user clicks on a project line in the report, the user can then click a "Button" (in the Report footer) to open the subreport with that specific record information about the particular project to show.

I tried using the following VBA code and doesnt work - I've also tried to build a Macro, but couldnt figure out how to have the subReport open with the filtered data about the specific project:


Private Sub cmdCommand58_Click()

DoCmd.OpenReport "FundingByProduct", acViewPreview, , "ProjectID = " & ProjectID

End Sub


What am I missing? I know this is probably extremely easy to do, but I cannot find out what I am doing wrong here. Thank you.

- Warren

[ranman256]

I use
DoCmd.OpenReport "FundingByProduct", acViewPreview, , "ProjectID = " & ProjectID
works everytime.

You DO have the projectID in the report ,right?

[warren0127]

Hi Ranman,

Yep - I have the ProjectID in the report as one of the column headers. The Product report also has the Project ID in the report - so its definitely in both reports. I wasnt sure if I am missing a step to connect them together -- or if I even need to connect them together.

Right now, it only opens the other sub report with all products when I click the command button from the main report. But I need it to open only the subreport to filter the specific record the user has highlighted from the main report. I've been stumped on this one for some time now. I tried doing it through a Macro, but I am getting errors because I'm probably not putting in the right conditioning formula.

Thanks,
- Warren

[warren0127]

It appears to work now - but its not filtering records based on my click in the select of the report. It does filter some records, but not the ones I am selecting. I told it to match whatever record is selected based on the ProjectID, but it seems to only be filtering 5-6 records each time, and leaves out others.

[orange]

I don't think your comment

It appears to work now - but its not filtering records based on my click in the select of the report. It does filter some records, but not the ones I am selecting. I told it to match whatever record is selected based on the ProjectID, but it seems to only be filtering 5-6 records each time, and leaves out others.

would support the "It appears to work now"

I don't think it's working until it does what you specified.

[warren0127]

Hi orange,

Yes, sorry - It still does not work the way it should be. Before, it was opening the subreport with all of the records. Now, its only opening a certain record set.. and ignores the others. In fact, it defaults to a specific record set no matter which item is selected, which isnt so good..

It seems like some sort of bug - I'm not sure what is causing it to not filter properly.. When I select a specific record, and click the open button, it opens a totally different project record set instead of the one selected.

Any idea what could be causing this? VBA code I am using:


Private Sub Command58_Click()

DoCmd.OpenReport "FundingByProduct", acViewPreview, , "ProjectID = " & ProjectID


End Sub

[orange]

It's trying to open the report on record where ProjectID = ProjectID

First, I would add this line before the DoCmd...

Debug.print ProjectID

It should print the value in the immediate window. That may give you an idea of which ProjectID is expected.
And may give you a starting point to look for other issues.

[warren0127]

I've went through about 12 articles of similar problems and still isnt filtering based on the ProjectID. What it is doing is, it opens up the subreport, but displays the last "6" records of the subreport, which are bundled under one of the particular projects. But the problem is, it still isnt opening the subreport based on other record selections - just keeps displaying the same last 6 from the subreport.

Debug.print ProjectID was added - but still no change.

[orange]

Did you look in the immediate window to see what ProjectID Access is using/interpreting?

The debug.print won't change the openreport. What is does is show you the value of ProjectID
jut before you try to open the report.

Perhaps you could zip and post your database --remove anything private/confidential first.

[June7]

Is the 'Main' report open in ReportView or PrintPreview?

[warren0127]

Hi orange - I will try and attach the DB soon. Having some issues since the tables are connected to SP Lists. Seems like no matter how many copies I make locally, it seems to still sync data information to the main file. IE: I create a replica, save it a different name, move it somewhere different, and any changes to that will still change my "original" DB. I'm guessing I can remove the SP connection from the replica and then zip it.

Hi June - I have the Main report open in ReportView. Basically, in my Main report, I have records displaying from a query (I created a report from a query). I created a button at the bottom of the report so if I were to click within a record, another subreport opens with that record information filtered. I assumed this would work with no check boxes next to the record, as it worked with a continuous form (click in a record, and use a button to open another form).

[warren0127]

Hi orange,

I've attached a dummy-downed DB and kept the reports and associated tables/queries. The Project Report is the main report - which is the report I am using to open the sub report of Product Reports. I created the command button below, and when you click into a record (a project name - which is linked to the ID) and click the button - it only opens up a certain record and not the one you choose. I am assuming I am missing an item as part of the filtering process.

Thanks,
- Warren

[June7]

Code in the Detail section Click event works.

[warren0127]

For me, its only filtering the "Computer Project" products - and isnt working for the other two projects. IE: when I click in the Integration or Power project, it still produces a sub report of Computer products

[June7]

As I said, Detail section Click event works. The button Click event does not.