show form in a subreport based on a filter

[ashij]

Hi! All, I'm a novice programmer, and the subject above doesn't really describe my problem fully. I work in a company which uses lots of database functions. I was asked to create an access report which acts as a template for the company's product sample. In this report, I have a Main Report (Main_NEW_NEWFORMAT) with several subreports in it (these subreports themselves have 1-2 level of sub-subreports in them). I have a main Form, which connects to any database that I point to and retrieves "Library Name" and "Process Name" from there. I'm almost done with this microsoft access "template" and had to do considerable amount of VB-Scripting.
My problem started when my boss wanted me to put images in the subreports, which were visible only if it matched the keyword criteria(dbo_T_PD_PROCESS_ELEMENT.C_Name). I have posted the sample code below so u can see exactly what i'm talking about. I can show the images when I put the coding ad view it form the subreport itself, but have a problem when i try to run that code from the Main Report, It gives me an error, saying that "You entered an expression which has an invalid reference to the property Form/Report". here is what the code looks like:
1) from Subreport:
'Design and Build
If Reports![PHASE_NEWFORMAT_R]![dbo_T_PD_PROCESS_ELEMENT.C_NAME] = "Design and Build" Then
Reports![PHASE_NEWFORMAT_R]![phase_Design_Build_Process].Visible = True
Else
Reports![PHASE_NEWFORMAT_R]![phase_Design_Build_Process].Visible = False
EndIf

2) from Main Report (erronous):
'Design and Build
If Reports![Main_NEW_NEWFORMAT]![PHASE_NEWFORMAT_R]![dbo_T_PD_PROCESS_ELEMENT.C_NAME] = "Design and Build" Then
Reports![Main_NEW_NEWFORMAT]![PHASE_NEWFORMAT_R]![phase_Design_Build_Process].Visible = True
Else
Reports![Main_NEW_NEWFORMAT]![PHASE_NEWFORMAT_R]![phase_Design_Build_Process].Visible = False
End If

I will be very thakful to whoever solves this problem, Please help me, I'm badly stuck at this point. thank you very much

Ash

 

[Dalain]

Hi Ashij

Just to see if i understand correctly, the subform only shows a picture? If not, it should. If there is any other info that is displayed, you should have it in another subreport.

If this is so then you don't need to have code in the subform module making the picture visible. Instead you have to do is make the subform itself visible or hidden depending on your criteria.

From your code it is very difficult to know what is the name of your fields and the name of your subform. The major problem seems to be how you are referencing the item in your report.

This is what you have in the module of the main report?

Reports![Main_NEW_NEWFORMAT]![PHASE_NEWFORMAT_R]![phase_Design_Build_Process].Visible

if so then

[Main_NEW_NEWFORMAT] = Main Report Name
[PHASE_NEWFORMAT_R] = ?
If this is the subform name, you can not referance a field in the subform to make the same subform visible or not.
[phase_Design_Build_Process] = Field Name

Here is an example that will make a subfom visible or not visible.

'Field name on the main Report = [dbo_T_PD_PROCESS_ELEMENT.C_NAME]
'Subform Name = [phase_Design_Build_Process]

    If Me![dbo_T_PD_PROCESS_ELEMENT.C_NAME] = "Design and Build" Then
        'Make Subform visible
        Me![phase_Design_Build_Process].Visible = True
    Else
        'Make Subform Hidden
        Me![phase_Design_Build_Process].Visible = False
    End If

Also I recommend that the only time you have to referance the name of the report Reports![Main_NEW_NEWFORMAT] is if you are referancing it from another report or from the module of the subform. If the Ref item belongs to the same report, you should use Me! which will make your code shorter and easer to debug.


Hope this help, if there are any questions let me know
Dalain

 

[ashij]

Hi! Dalain, thank you very much for your reply. I'm sorry that I couldn't explain my problem properly. What I was trying to say in simple words is (i'm making a tree diagram here):
Main Report
|--Sub Report
|----Form in Sub Report
What I wanted to do was, make a Form visible (which was visible=disabled initially) after a value(my filter) matched the value of a field in the Sub Report. I wanted to write the script for all this in the Main Report itself. I wasn't able to do this, and sorry to say, but your method didn't work for me either, but then i did some troubleshooting and I was able to get the desired result when I wrote the code in the Sub Report.
Still I'm trying to figure out how to do this through the Main Report.
Anyways, Thank you very much for your reply. (see what code worked for me below)

If Reports![Sub Report]![Field in Sub Report] = "Implementation" Then
Reports![Sub Report]![Form in Sub Report].Visible = True
Else
Reports![Sub Report]![Form in Sub Report].Visible = False
End If

what I want to do finally (if everything goes fine) is write the code in Main Report so that i don't have to print each sub report seperately to get the desired results. You don't know how much it will help me if you cud figure this out for me , while i'm working my brain on it here
Thank you very much again

AshiJ

 

[Dalain]

Well here is some more for you brain.

In order the Help more, I would need the know more how your report is working For example

Main Report (RecoreSource? , All Records or no records)
|--Sub1 (Link Master/child value, Recordsource)
|-Sub2 (Link Master/child value,Recordsource)

Note: don't need the entire field list, just the ones that the report is using to deturmine the printing

Because the Field for making the [Sub2] visible is in [Sub1] you will not be able to place the code in your main report.

Let say for this scenairo, the first record in your [Sub1] [Field in Sub1] = "Implementation")

What happends is Access processes the code for the Main report first. So since the first record in Sub1 the [Field in Sub1] = "Implementation" it will make Sub2 visible and that completes the code for the Main Report so every record in Sub1 will have the Sub2 visible. Now access moves to the next record in the main report.

So every [Sub2] will be visible no mater what the value for [Field in Sub1] is.

What you should try to do is make separate sub reports, for each type of print you want, where on thier own will Print correctly. Then you can attach all the sub reports in one main report, that way you only have to print the main to print all

For example
MainReport
|--SubReport1 (Recordsource [Field in Sub Report] = "Implementation" Form in Sub Report is always Visible)

|--SubReport2 (Recordsource [Field in Sub Report] <> "Implementation" This one has no other Sub forms)


Also if the Main Report has all the records as the RecordSource, you can make the subform1 or Subform2 visible just by using the Link Master/child criteria, so the only one of the two Sub forms are visible for each record (if no match is found for the LinkMasterFields and LinkChildFields criteria, the subform does not display)

Hope this helps
Dalain