Syntax wrong? or what could it be?

E

Espinoza84

Registered User.
Local time
Today, 07:14
Joined
May 30, 2008
Messages
55
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.


 

Attachments

  • report_designview.jpg
    report_designview.jpg
    84.1 KB · Views: 143
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?
Code: 
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.
Code: 
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...

Code: 
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
 
You must log in or register to reply here.

Similar threads

S
Solved Timing or other issue when closing Excel from Access
Replies
13
Views
490
sxschech
S
M
Upgrading VBA to 64bits, What does it means?
Replies
9
Views
2,791
isladogs
isladogs
S
Make controls Visible or Not Visible
Replies
4
Views
1,445
spikepl
spikepl
J
Form 'OnCurrent' Event not firing properly, but does when stepping through it??
2
Replies
24
Views
3,371
vbaInet
V
A
Report footer to bottom of page or page footer to last page only
Replies
1
Views
6,772
stopher
stopher

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom