Opening a subreport specific to a record off main report

[orange]

A couple of points upfront.
1)I know diddly about Sharepoint.
2)I don't use macros, I use VBA-but you're working with Sharepoint so you need macros.
3) You use lookups at the table field level in places, and that can cause lots of issues "behind the scenes". I would avoid them, but I'm a little old school.

When looking at the report FundingByProject, and running it then I look at the immediate window, I see that you are constraining the report to ProjectID = 1. And if I look at the projects table I see that Id 1 is Computer Project.

When I click command58 I get a report as attached(afterClickingCommand58.jpg).

How exactly are you identifying the type of Project to be reported? Where/How do I change to another type of Project, if I'm suppose to?

When I go to your form and hit Open Selected Project, I get the error shown in the attached jpg
indicating frmTabbedProject is an error.

The macro refers to that form name also.

Do you have it clearly identified what you mean by Project and Product?

Hope this is useful. If I'm missing something basic, please provide details on what I should look at/do.

[warren0127]

Hi Orange,

Thanks for your response and your help as usual

I don't use macros, I use VBA-but you're working with Sharepoint so you need macros

Well, I am only linking the tables to SharePoint lists for an established connection, while keeping the DB in SharePoint. As long as the lists are linked to the tables, the data can be modified from both ends and changes will automatically take place. I have other VBA scripts that seem to work. I also do not want to use Macros, nor do I intend to since VBA can accomplish a bit more (from what I understand).

You use lookups at the table field level in places, and that can cause lots of issues "behind the scenes". I would avoid them, but I'm a little old school

I am using lookups because I want to make it easier for our users to know which Primary Key (Project ID) is associated with Project Names. When I created the lookups, I gave it the option to hide the key. So when I select Computer Project, it will know there is a key (ProjectID) attached to it. I just did this for visual purposes.. so the user doesnt have to go back to know/manually type which ID number pertains to, for example, Products. One project can have many products - and its easier, IMO, to see the actual Project Name, select the Project Name that would be a foreign key for the Product table, etc. If there is another way to do this without Lookups, I'd be willing to try it out

When looking at the report FundingByProject, and running it then I look at the immediate window, I see that you are constraining the report to ProjectID = 1. And if I look at the projects table I see that Id 1 is Computer Project.

I certainly didnt intend to constrain the report to just the ProjectID of 1. I assumed the VBA line of code would tell it to show only the Project (or Project Record) that is selected from the Main Report - thanks for pointing that out because I didnt know where to look in the immediate window to see this.

How exactly are you identifying the type of Project to be reported? Where/How do I change to another type of Project, if I'm suppose to?

The Project table is where you could change the project type. I am identifying the type of Project to be reported based on the project name field, which is associated with the ProjectID (primary key) from the Project table. So when I click on a record in the report, and click the button, it should know which ProjectID to match from the Product Report (sub report) because each project is associated with multiple products since the ProjectID is a foreign key in the Products table.

If you're referring to the report, I assumed that the user could select/click in a Project Record to identify the project he/she wishes to see. IE: I click in Power Project in the report, and click the command button, the sub report should only display the Power Project's products (product sub report I have the command telling it to open). I'm assuming I may be missing something here. Right now, it only seems to work for the Computer Project (or, its how it is defaulting to no matter "where" you click in the report).

IE in the Products Table I have:

ProjectID ProductID Product

Power Project 41 Battery
Power Project 42 Power Source
Power Project 43 Generator

This relationship is established so it knows which products belong to the projects based on the linkage of keys.

As for the continuous form, that was my mistake. I forgot to remove that out of the box macro when I deleted the initial big Project Form I used for the main DB. The continuous form was a starting point to open up a Main form for the project selected (which worked perfectly). I just forgot to remove that macro since the form no longer exists for this smaller, dummy downed version of the DB


Thanks again for your help. It seems my VBA is off since it is only recognizing the ProjectID of "1". I'd like it to recognize any Project ID based on where the user clicks in the Main Report. Hope I provided enough information. Thank you!

- Warren

[orange]

Re Macros --I think you have to use macros (no vba) with sharepoint --but I don't know sharepoint.

The immediate window can be found by hitting ctrl G. One of the windows will be the immediate window, or you can open it form there if it isn't open.

The OpenReport with parameters ProjectID = ... is what is constraining the Report.

I must be misunderstanding this

The Project table is where you could change the project type.

since
YOU NEVER LET USERS WORK DIRECTLY WITH TABLES

I don't have your database here at the moment, but I don't recall seeing a subreport???

You wouldn't normally show a report, then click on the report to do something with that report. In effect a report is sent to the printer (or file). You might open a form with some controls and/or dialog and prompt the user to make a selection representing the report and options they wish to execute/get. Then a button to click with the selection and options sent to open the report.

See this site for a tutorial on dynamic reports via form/dialogs.

[warren0127]

Hi Orange,

Sorry for the confusion. The only user that would modify tables would be the other admin if he wanted to make changes to the project type without having to go to the forms (which the forms are for the end users of course). I have several forms for the end user to change their project types, etc, on their reports - so that is where that would occur. Sorry for leaving those forms out.

Yeah - so I would need to create a form/navigational switchboard to open reports and drill down to sub reports, instead of trying to drill down from an actual report page. I'll have to explore this a bit more - but thought it would be interesting if I could somehow filter a project record from the main report that opens a sub report.

The sub report I was referring to was the Products Report. Which is what the command button was for on the Projects report. I wanted the Products report to open from the command button based on where the user clicked, or made a selection, from the main Project Report. I figured, since I got this far with being able to open the Products report from the Projects report, I could possibly make the filter for any of the Projects based on which one was selected or clicked, which the command button seems to be doing with ProjectID = 1 / Computer Project (but not for the other Projects).

Much appreciated for your help and thanks for sharing the reports tutorial

Thanks again
- Warren

[orange]

Typically a sub report is a report within a report.
see https://support.office.com/en-ca/art...0-ce817093bb33

I'm not sure if you want a sub report??
Or a form dialog to choose which report(s) you want to create?

It's always easier if you can describe WHAT you're trying to do in simple English, and include an example or 2.
Once, readers understand what you want, then option and advice can be focused.

Good luck

[warren0127]

Thanks Orange!

My terminology as you noted was off. What I meant by subreport was the second report (Products) opening from the first report (Projects). I figured, since I was able to get the second report to filter the Computer Project products (as its constrained to ID 1) off of the first report, I could do the same for the other two projects (Power and Integration) if I had clicked in their record line.