Hello all,

I am making a report, that containts a subreport. I am hiding and showing (visible =false/true) on some textboxes/labels to achieve the display I want.
I have somewhat accomplished this but not exactly.
Let me post my code first:

Option Compare Database
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.FinalRecert_subrep_01.Report.Appnt = "LAN" Then
With Me
.FinalRecert_subrep_01.Report.Text29.Visible = True 'LAN
.FinalRecert_subrep_01.Report.Label30.Visible = True 'LAN
.FinalRecert_subrep_01.Report.Text31.Visible = False 'ECO
.FinalRecert_subrep_01.Report.Label32.Visible = False 'ECO
.FinalRecert_subrep_01.Report.text40.Visible = False 'GP
.FinalRecert_subrep_01.Report.Label33.Visible = False 'GP
End With
Else
If Me.FinalRecert_subrep_01.Report.Appnt = "ECO" Then
With Me
.FinalRecert_subrep_01.Report.Text29.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Label30.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Text31.Visible = True 'ECO
.FinalRecert_subrep_01.Report.Label32.Visible = True 'ECO
.FinalRecert_subrep_01.Report.text40.Visible = False 'GP
.FinalRecert_subrep_01.Report.Label33.Visible = False 'GP
End With
Else
If Me.FinalRecert_subrep_01.Report.Appnt = "Dynamics Great Plains" Then
With Me
.FinalRecert_subrep_01.Report.Text29.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Label30.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Text31.Visible = False 'ECO
.FinalRecert_subrep_01.Report.Label32.Visible = False 'ECO
.FinalRecert_subrep_01.Report.text40.Visible = True 'GP
.FinalRecert_subrep_01.Report.Label33.Visible = True 'GP
End With
Else
If Me.FinalRecert_subrep_01.Report.Appnt = "Hyperion Enterprise" Then
With Me
.FinalRecert_subrep_01.Report.Text29.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Label30.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Text31.Visible = False 'ECO
.FinalRecert_subrep_01.Report.Label32.Visible = False 'ECO
.FinalRecert_subrep_01.Report.text40.Visible = False 'GP
.FinalRecert_subrep_01.Report.Label33.Visible = False 'GP
End With
Else
If Me.FinalRecert_subrep_01.Report.Appnt = "Journals Access Folder" Then
With Me
.FinalRecert_subrep_01.Report.Text29.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Label30.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Text31.Visible = False 'ECO
.FinalRecert_subrep_01.Report.Label32.Visible = False 'ECO
.FinalRecert_subrep_01.Report.text40.Visible = False 'GP
.FinalRecert_subrep_01.Report.Label33.Visible = False 'GP
End With
Else
With Me
.FinalRecert_subrep_01.Report.Text29.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Label30.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Text31.Visible = False 'ECO
.FinalRecert_subrep_01.Report.Label32.Visible = False 'ECO
.FinalRecert_subrep_01.Report.text40.Visible = False 'GP
.FinalRecert_subrep_01.Report.Label33.Visible = False 'GP
End With
End If
End If
End If
End If
End If
End Sub
-------------------------------------------------
The code runs with no errors. The problem is that because I have my subreport in the detail section of my MAIN report, I am expecting some 'users' (people) to have multiple Appnt's; 'APPNT' is short for APPLICATION (applications: LAN, ECO, Hyperion E, etc). At the moment my code looks at the person and his first "appnt" (application) and it will display the visible code for the first "APPNT", then once the next "APPNT" (sport: golf, tennis etc) appears it will display the information of the first "APPNT" it had. so to illustrate:

James martin
APPNT: LAN LANID: Test1
APPNT: ECO LANID:

JOSE CUERVO
APPNT: ECO ECOID: Hello1
APPNT: Hyperion Enterprise ECOID:

THe errors are highlighted in green. For james martin once the APPNT: ECO appears it should pull his "ECOID" not repeat LANID, same with jose cuervo, because appnt= hyperion enterprise there should be no id at all, since all the visibles=false for that appnt.

Any idea what I am doing wrong?
I am attaching a screenshot of my design setup.

Try rewriting the code using a Select Case structure instead of your nested If...Else stuff.

I shortened the code up a bit. Are you intentionally nesting the If statements this way?
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.FinalRecert_subrep_01.Report.Text29.Visible = False 'LAN
Me.FinalRecert_subrep_01.Report.Label30.Visible = False 'LAN
Me.FinalRecert_subrep_01.Report.Text31.Visible = False 'ECO
Me.FinalRecert_subrep_01.Report.Label32.Visible = False 'ECO
Me.FinalRecert_subrep_01.Report.text40.Visible = False 'GP
Me.FinalRecert_subrep_01.Report.Label33.Visible = False 'GP

If Me.FinalRecert_subrep_01.Report.Appnt = "LAN" Then
Me.FinalRecert_subrep_01.Report.Text29.Visible = True 'LAN
Me.FinalRecert_subrep_01.Report.Label30.Visible = True 'LAN
Else
If Me.FinalRecert_subrep_01.Report.Appnt = "ECO" Then
Me.FinalRecert_subrep_01.Report.Text31.Visible = True 'ECO
Me.FinalRecert_subrep_01.Report.Label32.Visible = True 'ECO
Else
If Me.FinalRecert_subrep_01.Report.Appnt = "Dynamics Great Plains" Then
Me.FinalRecert_subrep_01.Report.text40.Visible = True 'GP
Me.FinalRecert_subrep_01.Report.Label33.Visible = True 'GP
Else
If Me.FinalRecert_subrep_01.Report.Appnt = "Hyperion Enterprise" Then
Else
If Me.FinalRecert_subrep_01.Report.Appnt = "Journals Access Folder" Then
Else
End If
End If
End If
End If
End If
End Sub
If it's not intentional, I agree a Select Case statement would be cleaner.

Sorry for not replying, im at work and have a ton on my hands.

I am not that experienced with coding. I am not sure how to beging with a select caase statement?
Though, I have asked a friend at work abuot my issue and he fixed it for me. Apparently I had the code in the wrong spot. Instead of having it on the detail section of the main report, he transferred the code to the APPNT group header of the subreport. Now, what I was trying to accomplish works. However, my code is about to increase by like 4x due to the other applications and hiding labels/boxes I have to include.

If a select case statements will shorten this, that would be great.
If someone could give me a direction or help as to how to do it, I would appreciate it.

Thank you all.

Here's what it would look like as a Select Case structure.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

With Me

Select Case .FinalRecert_subrep_01.Report.Appnt

Case "LAN"
.FinalRecert_subrep_01.Report.Text29.Visible = True 'LAN
.FinalRecert_subrep_01.Report.Label30.Visible = True 'LAN
.FinalRecert_subrep_01.Report.Text31.Visible = False 'ECO
.FinalRecert_subrep_01.Report.Label32.Visible = False 'ECO
.FinalRecert_subrep_01.Report.text40.Visible = False 'GP
.FinalRecert_subrep_01.Report.Label33.Visible = False 'GP

Case "ECO"
.FinalRecert_subrep_01.Report.Text29.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Label30.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Text31.Visible = True 'ECO
.FinalRecert_subrep_01.Report.Label32.Visible = True 'ECO
.FinalRecert_subrep_01.Report.text40.Visible = False 'GP
.FinalRecert_subrep_01.Report.Label33.Visible = False 'GP

Case "Dynamics Great Plains"
.FinalRecert_subrep_01.Report.Text29.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Label30.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Text31.Visible = False 'ECO
.FinalRecert_subrep_01.Report.Label32.Visible = False 'ECO
.FinalRecert_subrep_01.Report.text40.Visible = True 'GP
.FinalRecert_subrep_01.Report.Label33.Visible = True 'GP

Case "Hyperion Enterprise"
.FinalRecert_subrep_01.Report.Text29.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Label30.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Text31.Visible = False 'ECO
.FinalRecert_subrep_01.Report.Label32.Visible = False 'ECO
.FinalRecert_subrep_01.Report.text40.Visible = False 'GP
.FinalRecert_subrep_01.Report.Label33.Visible = False 'GP

Case "Journals Access Folder"
.FinalRecert_subrep_01.Report.Text29.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Label30.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Text31.Visible = False 'ECO
.FinalRecert_subrep_01.Report.Label32.Visible = False 'ECO
.FinalRecert_subrep_01.Report.text40.Visible = False 'GP
.FinalRecert_subrep_01.Report.Label33.Visible = False 'GP

Case Else
.FinalRecert_subrep_01.Report.Text29.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Label30.Visible = False 'LAN
.FinalRecert_subrep_01.Report.Text31.Visible = False 'ECO
.FinalRecert_subrep_01.Report.Label32.Visible = False 'ECO
.FinalRecert_subrep_01.Report.text40.Visible = False 'GP
.FinalRecert_subrep_01.Report.Label33.Visible = False 'GP

End Select

End With

End Sub

You can even make the code shorter if you eliminate the redundant code.

wow i like this, it has a cleaner look to it. I will implement it.

BTW, suppose if case was "ECO"
say I wish for:
.FinalRecert_subrep_01.Report.Text31.Visible = True 'ECO
.FinalRecert_subrep_01.Report.Label32.Visible = True 'ECO

these two, to move closer to each other, or say i wish to move text31, 4 spots to the right, and label32: 3 spots to the left, is this doable as well? or Im I doing some wishful thinking

Like RuralGuy said: Remove redundant code...

The section marked with the "***" may not even be necessary. Try saving the subreport with those fields' Visible property set to False and put a comment quote in front of the marked lines. Run the report to see if it works...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

With Me.FinalRecert_subrep_01.Report

.Text29.Visible = False 'LAN ***
.Label30.Visible = False 'LAN ***
.Text31.Visible = False 'ECO ***
.Label32.Visible = False 'ECO ***
.text40.Visible = False 'GP ***
.Label33.Visible = False 'GP ***

Select Case Me.FinalRecert_subrep_01.Report.Appnt

Case "LAN"
.Text29.Visible = True 'LAN
.Label30.Visible = True 'LAN

Case "ECO"
.Text31.Visible = True 'ECO
.Label32.Visible = True 'ECO

Case "Dynamics Great Plains"
.text40.Visible = True 'GP
.Label33.Visible = True 'GP

End Select

End With

End Sub

thank you all.