Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Having problems using VB to conditionally format Report controls 1

Status
Not open for further replies.
dfwelch

dfwelch

Programmer
Dec 5, 2003
51
US
Here's me code:
Code: 
If BreakFlag1 Then
    Me.PageBreak1.Visible = True
Else
    Me.PageBreak1.Visible = False
End If


If IsNull(Unavailable) Then
Me.PageBreak2.Visible = False
Me.Label154.Visible = True
Me.Label136.Visible = True
Me.Label139.Visible = True
Me.Label160.Visible = False
Me.Text127.Visible = True
Me.Graph110.Visible = True
Me.GraphHeader.Visible = True
Me.Significant.Visible = True
Me.Aptitudes.Visible = True
Me.Significant.Visible = True
Me.AptitudeList.Visible = True
Me.Unavailable.Visible = False
Me.Unavailable1.Visible = False
Me.Unavailable2.Visible = False
Me.Unavailable3.Visible = False
Me.Unavailable4.Visible = False
Else
Me.PageBreak2.Visible = True
Me.Label154.Visible = False
Me.Label136.Visible = False
Me.Label139.Visible = False
Me.Label160.Visible = True
Me.Text127.Visible = False
Me.Graph110.Visible = False
Me.GraphHeader.Visible = False
Me.Significant.Visible = False
Me.Aptitudes.Visible = False
Me.Significant.Visible = False
Me.AptitudeList.Visible = False
Me.Unavailable.Visible = True
Me.Unavailable1.Visible = True
Me.Unavailable2.Visible = True
Me.Unavailable3.Visible = True
Me.Unavailable4.Visible = True
End If

If BreakFlag2 Then
    Me.PageBreak3.Visible = True
Else
    Me.PageBreak3.Visible = False
End If

If SOC = "21-1011" Then
    Me.License1.Top = 7200
    Me.Label9.Top = 7800
    Me.Unavailable1.Top = 8300
    Me.License2.Top = 7450
End If

If SOC = "49-3023" Then
    Me.Label8.Top = 8000
    Me.Label9.Top = 8000
    Me.subreport1.Top = 8400
    Me.subreport2.Top = 8400
    Me.subreport1.Height = 2000
    Me.Label87.Top = 10500
    Me.Text86.Top = 10920
    Me.Label10.Top = 9800
    Me.Significant.Top = 9800
    Me.GraphHeader.Top = 9800
    Me.Aptitudes.Top = 10220
    Me.Graph110.Top = 9980
    Me.AptitudeList.Top = 10234
    Me.Aptitudes.Top = 10220
    Me.Label19.Top = 12500
    Me.Label136.Top = 12620
    Me.Text18.Top = 12980
Else
    Me.subreport1.Height = 944
    Me.Label8.Top = 9900
    Me.subreport1.Top = 10320
    Me.Label87.Top = 11340
    Me.Text86.Top = 11760
    Me.Label10.Top = 9900
    Me.Significant.Top = 9900
    Me.GraphHeader.Top = 9900
    Me.Aptitudes.Top = 10320
    Me.Graph110.Top = 10080
    Me.AptitudeList.Top = 10334
    Me.Aptitudes.Top = 10320
    Me.Label19.Top = 12600
    Me.Label136.Top = 12720
    Me.Text18.Top = 13080
End If

If SOC = "33-3051" Or SOC = "33-9032" Then
    Me.Label8.Top = Me.Label9.Top
    Me.subreport1.Top = Me.subreport2.Top
    Me.Label87.Top = Me.Label10.Top
    Me.Text86.Top = Me.Aptitudes.Top
Else
    Me.Label8.Top = 9840
    Me.subreport1.Top = 10260
    Me.Label87.Top = 11280
    Me.Text86.Top = 11700
End If

If SOC = "47-2031" Then
    Me.Label8.Top = Me.Label9.Top
    Me.subreport1.Top = Me.subreport2.Top
    Me.Label87.Top = Me.Label10.Top
    Me.Text86.Top = Me.Aptitudes.Top
Else
    Me.Label8.Top = 9840
    Me.subreport1.Top = 10260
    Me.Label87.Top = 11280
    Me.Text86.Top = 11700
End If

I've been building this code to deal with records in the report that cause the 2-page report for each record to be poorly formatted (either on 3 pages or a poorly placed page break). But, it seems like every time I add something new something I did previously gets screwed up! I think I understand why, but dealing with it is another issue, LOL. It seems like I might need to change the order of my If .. Then statements. I've tried this, and it just confuses me more. I tried changing the Else statements to ElseIf IsNull(Unavailable), but the problem with that is AFTER the other records are affected, the setting needs to be changed back. I guess that's my problem. I need to be able to CHANGE SETTINGS BACK after each record affected. This Else method is just not working.
 
Sort by date Sort by votes
You don't explain exactly what the problem is, but one thing I see is that your not resetting the variables when SOC is not equal to "21-1011". Also, later on in the code you're assigning
Code: 
Me.Label8.Top = Me.Label9.Top
. Label9.Top could be one of many values at that point, depending what the value was in the prior record.

Hoc nomen meum verum non est.
 
Upvote 0 Downvote
Thanks Cosmo.
The problem I am seeing is that I am not resetting the properties in any of the circumstances. What I am doing is saying what the property should be in all other cases. That's not what I want.

I guess I need to explain something about the report. It is designed so that each record takes up two pages. The page break is automatically determined unless I have set one of the BreakFlags to manually set the page break. Most of the sections of the report have CanGrow set to Yes, so I need to be careful about how I manually set the Top properties.

To answer the question about Label9.Top, I am saying in that chunk of code that I want Label8 to be vertically aligned with Label9 and so on for the other three statements. Your concern about what Label9.Top is at that point should not be an issue, since in the absence of any code, the Top property for every control needs to be calculated based on the CanGrow property of previous controls. That's the whole issue and relates to what I said about resetting properties: I only want to reset the property to having the ability to be dynamically calculated according to the CanGrow propertie

I am correct in assuming that each piece of code is run for every record, right? Then, when I say:
Code: 
If SOC = "33-3051" Or SOC = "33-9032" Then
    Me.Label8.Top = Me.Label9.Top
    Me.subreport1.Top = Me.subreport2.Top
    Me.Label87.Top = Me.Label10.Top
    Me.Text86.Top = Me.Aptitudes.Top
Else
    Me.Label8.Top = 9840
    Me.subreport1.Top = 10260
    Me.Label87.Top = 11280
    Me.Text86.Top = 11700
End If
I am really saying that for every other record, these are the settings I want for the Top of these controls. What if that's not the case?
 
Upvote 0 Downvote
Perhaps if you saw some examples of the reports. I've uploaded a few pages in a zip file if you want to take a look.


Page1 and Page2 are an example of one record. I've flagged BreakFlag1 to insert a page break BEFORE the Transferable Work Content Skills section for this record.

Page3 and Page4 are examples of the first of 10 records like this, that have no data in a bunch of sections. These are handled specially using the chunk of code that checks if Unavailable IsNull. This works for most of the records, depending on the location of some of the other If .. Then chunks.

Page5 and Page6 show one of the exceptions that is working right now. Notice that the Physical Abilities section is next to the Transferable Work Content Skills section for this record, which is necessary to keep this record at a 2-page length.
 
Upvote 0 Downvote
There is a problem with the logical flow of the code. Follow the value of Label8.Top when SOC = "49-3023":
Code: 
If SOC = "49-3023" Then
Me.Label8.Top = 8000
Code: 
    Me.Label9.Top = 8000
    Me.subreport1.Top = 8400
    Me.subreport2.Top = 8400
    Me.subreport1.Height = 2000
    Me.Label87.Top = 10500
    Me.Text86.Top = 10920
    Me.Label10.Top = 9800
    Me.Significant.Top = 9800
    Me.GraphHeader.Top = 9800
    Me.Aptitudes.Top = 10220
    Me.Graph110.Top = 9980
    Me.AptitudeList.Top = 10234
    Me.Aptitudes.Top = 10220
    Me.Label19.Top = 12500
    Me.Label136.Top = 12620
    Me.Text18.Top = 12980
Else
    Me.subreport1.Height = 944
    Me.Label8.Top = 9900
    Me.subreport1.Top = 10320
    Me.Label87.Top = 11340
    Me.Text86.Top = 11760
    Me.Label10.Top = 9900
    Me.Significant.Top = 9900
    Me.GraphHeader.Top = 9900
    Me.Aptitudes.Top = 10320
    Me.Graph110.Top = 10080
    Me.AptitudeList.Top = 10334
    Me.Aptitudes.Top = 10320
    Me.Label19.Top = 12600
    Me.Label136.Top = 12720
    Me.Text18.Top = 13080
End If

If SOC = "33-3051" Or SOC = "33-9032" Then
    Me.Label8.Top = Me.Label9.Top
    Me.subreport1.Top = Me.subreport2.Top
    Me.Label87.Top = Me.Label10.Top
    Me.Text86.Top = Me.Aptitudes.Top
Else
Me.Label8.Top = 9840
Code: 
    Me.subreport1.Top = 10260
    Me.Label87.Top = 11280
    Me.Text86.Top = 11700
End If
This and subsequent If..Then..Else blocks change Label8.Top to 9840. You will probably need either a Select Case statement, a GoTo statement to exit these blocks when SOC is equal to the value you are testing for, or a combination of the two.


Hoc nomen meum verum non est.
 
Upvote 0 Downvote
I'm gonna try using a Select Case statement.
But will this cover the instances where I need to change the property BACK to what it was BEFORE the Case? Remember, that's the only reason I am including the Else statement to begin with. I wish there were a way I could change the property just for that specific record, and have it go back after.
 
Upvote 0 Downvote
The Select Case statement will allow you to explicitly define the Top values for each SOC. Do you think that will help you accomplish that??

Hoc nomen meum verum non est.
 
Upvote 0 Downvote
Yeah, I think so. What I'll need is a Case Else statement that covers any changes I've made in any of the cases.
 
Upvote 0 Downvote
The Case Else would probably only come into play if the code finds an SOC that you haven't accounted for. You might want to include default Top values (if there are any), or a MsgBox or similar notification that there is a new SOC....

Hoc nomen meum verum non est.
 
Upvote 0 Downvote
I have found that the Else statement is necessary because if I change the value of, say, Me.Label9.Top for SOC 49-3023, then this property is changed for every SOC following 49-3023. It's a pain, but I don't know of any other way to make the change only for the indicated record and have it default back for all others after it. Again, this is the only reason I started using the Else. I think it's dumb that the property is changed for every record following the indicated record, but I guess that's the way the Access report function works, eh?
 
Upvote 0 Downvote
I had it working perfectly and realized I needed to include the page footer section to add a page number. This created another problem. Here's the code I ended up with that worked perfectly before the page number was added to the report:
Code: 
If BreakFlag1 Then
    Me.PageBreak1.Visible = True
Else
    Me.PageBreak1.Visible = False
End If

If BreakFlag2 Then
    Me.PageBreak3.Visible = True
Else
    Me.PageBreak3.Visible = False
End If

Select Case SOC
    Case "21-1011"
        Me.License1.Top = 7200
        Me.Label9.Top = 7800
        Me.Unavailable1.Top = 8300
        Me.License2.Top = 7450
    Case "33-3051", "33-9032", "47-2031"
        Me.Label8.Top = Me.Label9.Top
        Me.subreport1.Top = Me.subreport2.Top
        Me.Label87.Top = Me.Label10.Top
        Me.Text86.Top = Me.Aptitudes.Top
    Case "49-3023"
        Me.Label8.Top = 8000
        Me.Label9.Top = 8000
        Me.subreport1.Top = 8400
        Me.subreport2.Top = 8400
        Me.subreport1.Height = 2000
        Me.Label87.Top = 10500
        Me.Text86.Top = 10920
        Me.Label10.Top = 9800
        Me.Significant.Top = 9800
        Me.GraphHeader.Top = 9800
        Me.Aptitudes.Top = 10220
        Me.Graph110.Top = 9980
        Me.AptitudeList.Top = 10234
        Me.Aptitudes.Top = 10220
        Me.Label19.Top = 12500
        Me.Label136.Top = 12620
        Me.Text18.Top = 12980
    Case Else
        Me.Label8.Top = 9840
        Me.subreport1.Top = 10260
        Me.Label87.Top = 11280
        Me.Text86.Top = 11700
        Me.subreport1.Height = 944
        Me.Label10.Top = 9900
        Me.Significant.Top = 9900
        Me.GraphHeader.Top = 9900
        Me.Aptitudes.Top = 10320
        Me.Graph110.Top = 10080
        Me.AptitudeList.Top = 10334
        Me.Aptitudes.Top = 10320
        Me.Label19.Top = 12600
        Me.Label136.Top = 12720
        Me.Text18.Top = 13080
        
End Select

If IsNull(Unavailable) Then
Me.PageBreak2.Visible = False
Me.Label154.Visible = True
Me.Label136.Visible = True
Me.Label139.Visible = True
Me.Label160.Visible = False
Me.Text127.Visible = True
Me.Graph110.Visible = True
Me.GraphHeader.Visible = True
Me.Significant.Visible = True
Me.Aptitudes.Visible = True
Me.Significant.Visible = True
Me.AptitudeList.Visible = True
Me.Unavailable.Visible = False
Me.Unavailable1.Visible = False
Me.Unavailable2.Visible = False
Me.Unavailable3.Visible = False
Me.Unavailable4.Visible = False
Else
Me.PageBreak2.Visible = True
Me.Label154.Visible = False
Me.Label136.Visible = False
Me.Label139.Visible = False
Me.Label160.Visible = True
Me.Text127.Visible = False
Me.Graph110.Visible = False
Me.GraphHeader.Visible = False
Me.Significant.Visible = False
Me.Aptitudes.Visible = False
Me.Significant.Visible = False
Me.AptitudeList.Visible = False
Me.Unavailable.Visible = True
Me.Unavailable1.Visible = True
Me.Unavailable2.Visible = True
Me.Unavailable3.Visible = True
Me.Unavailable4.Visible = True
End If

The problem I am encountering is strange. For the second and subsequent instances of Not IsNull(Unavailable), in other words the Else section, the formatting is not being used. When that section of code is above the Case statement, the Else statement is never used. Again, this only started when I inserted a page footer with a page number. I thought I would have bigger problems than this when I inserted the page number, with the available vertical space changing, but I guess this is the only one!
 
Upvote 0 Downvote
Sorry, correction. I just noticed that it is NOT the case that the first instance of Not IsNull(Unavailable) is noticed.
 
Upvote 0 Downvote
Sorry, I'm having trouble understanding...Is it that the code is not working when the first Not Null Unavailable is found?? What is the data type of the Unavailable field??

Hoc nomen meum verum non est.
 
Upvote 0 Downvote
Unavailable is a string type. It only started happening when I added the page number in the page footer. Possibly important, the page footer was not there previously, just a section footer. The code is not working for any instances when Unavailable is not Null.

Could it have something to do with page length?
 
Upvote 0 Downvote
The Top property is measured from the top of the section where the control resides. Did you have to reduce the size of the section to add the page footer?? If so, that could affect some of the Top values towards the bottom of the section...

Hoc nomen meum verum non est.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

breezett93
  • Locked
  • Question
Report often takes multiple minutes to load, lots of formatting time between pages
Replies
5
Views
535
dhookom
dhookom
FancyPrairie
  • Locked
  • Question
How to Format a date on a report
Replies
1
Views
390
sxschech
sxschech
JimStrand
  • Locked
  • Question
Expression Limit on Sum IIf In Method ? Used in Report Calculation
Replies
1
Views
551
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Print multiple copies of report data 1
Replies
5
Views
527
dhookom
dhookom
SamuelR
  • Locked
  • Question
Dlookup in a report
Replies
13
Views
635
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top