Vertical Column Shading

[RichardBarth]

I have a problem where I want to shade vertical columns. The obvious approach is to change the background color of alternate columns. That works, but my problem is that one of the fields on the row is "can grow". When that field expands to require an additional row, then there's a gap in the vertical shading. I've tried changing the height of the shaded fields in the print format event, but that doesn't seem to work.

Any ideas would be greatly appreciated.

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmm ... mmmmmmmmm

I think you need to look more carefully at the routine which changes the control height. There are a number of threads which deal with variable height controls in these fora. Mostly, I recall ones which dynamically create "boxes" or "Lines" which organize / seperate contgrol groups, but the techniques should be applicable.




MichaelRed

 

[GingerR]

I've just spent 45 minutes looking thru tons of posts and other web sites. I've used some of the vertical line-producting code successfully before, But nothing I find discusses text boxes or their shading. Even if the height of the detail section is determined at the Detail's OnPrint event, as far as I can tell the formatting is done first in the OnFormat event, so the shading won't change.

Try Stepen Leban's PrintLines class (

http://www.lebans.com/PrintLines.htm).

It's hard for me to week thru but maybe you can, and maybe it will give you some ideas.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

hmmmmmmmmmmmm ... ... mmmm ... mm ...mmmmmmmmmmmmmmmmmm,

Mixed metaphors or a learning opportunity? Surfing (e.g. posts and other web sites) vs tone. So, my first question is the conversion of posts and / or web sites to Weight (of even mass).

The various porperties of the controls can generally be set either at design OR run time. Mr. Ubiquitous ([/b]{F1}[/b] (aka H E L P) is almost always his ubiquitous and verbose best when this is NOT the case. At least in my copy, he is somewhat insistient that the various and collected properties are available through VB, as well as others. Since VB is ~~ run time I am SURE that it is possible. Secondly, I have "first hand" knowledge that the height of controls can be 'adjusted' at run time either through the Can [Grow | Shrink] properties (at least for text boxes) and may be set through vb, so this is not (at least for me) an issue / question. Since the actual back color would appear to be fixed for the individual controls, I'm now somewhat at a loss as to the (exact) problem. "Adjusting" one porperty (at design OR run time does NOT generally affect other (particularly un-related ones) other potperties. I cannot really envision that height will reset the back color porperty, so setting the back color at design time and adjustin the height of all of the controls in the print event to the height of the tallest control in the "row" would seem to be both the necessary and sufficient.

GingerR, please let me know what your expert opinion (and extensive research) think.





MichaelRed

 

[GingerR]

Hi Michael. Not very clear on what you're saying, but I get an error in OnPrint "cannot set that property" or something like that, when trying to set the height of a text box. It's not as simple as

me.ShorterTextBox.height = me.TheGrownTextBox.height

It can be set in the detail's OnFormat property, but I couldn't determine what height to make it until the OnPrint event. catch 22. OnFormat launches before OnPrint. In the OnFormat property, all text boxes, even the one set to Can Grow, are the same height, which is whatever is in the property "height" in design mode. Even tho a text box Grows, it's height at OnFormat is the designed height.

I'm not an expert, just fiddled around for quite a while trying lots of different things, trying to play with/tweak different bunches of code and classes I found on line.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

I agree with GingerR on this. I read tons of report questions and try tons more during experimentation. She is correct that you can't determine the Grown height of a control until the Print Event. At this point, it is too late to set the other control's height.

I thought you could use the Line method to draw a rectangle in the On Page event like:

Private Sub Report_Page()
    Me.Line (2000, 0)-Step(2000, 15000), 15461355, BF
End Sub

This code draws the shaded box but the text boxes are not visible.

I did find that I could shade the back ground of a text box "space" by using code like:

Private Sub Detail_Format(cancel As Integer, FormatCount As Integer)
    Me.Line (Me.OrderDate.Left, 0)-Step(Me.OrderDate.Width, 15000), 15461355, BF
End Sub

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[GingerR]

Excellent Duane. A final solution...put something in the Tag property of each control you want shading (i put "Shade"), and run this:

 Dim ctrl As Control
 For Each ctrl In Me.Controls
    If ctrl.Tag = "Shade" Then
       Me.Line (ctrl.Left, 0)-Step(ctrl.Width, 15000), 15461355, BF
    End If
 Next ctrl

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

Good addition GingerR.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[MichaelRed]

Conceeding to the knowledge and wisdom of the experts.

I did 'elaborate" dhookom procedure a bit and ran it "successfuly" on a test report. My "elaboration" consists of little more than a wrapper for his second code snippet above, with the following "Aims":

1[tab]Provide the shading on a number of columns (vs, a single one).

2[tab]increase the "width" of the (shaded) box a small amount beyond the control width (same amount for each "Column".

3[tab]provide a more-or-less generic procedure to be useable on various reports (or forms) with minimal change (just the names of the controls in the detail section which will recieve the back shade).


Although the already presented approaches seem somewhat 'simpler', they require attention to the individual controls, to set the "Tag" property, which may be used for other purposes (see various / numerous references to this in the fora and other literature).

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

    Dim lngBkColor As Long
    Dim Idx As Integer
    Dim strCtrlNames() As String
    Dim intCtrlPos() As Long
    Dim tmpName As String
    Dim tmpPos As Long
    Dim Sorted As Boolean
    Dim blnFstPass As Boolean
    Dim MyCtrl As String
    Dim intCtrlCnt As Integer
    Dim MaxCtrl As Control

    intCtrlCnt = Me.Section(0).Controls.Count
    ReDim strCtrlNames(intCtrlCnt)
    ReDim intCtrlPos(intCtrlCnt)

    lngBkColor = RGB(190, 190, 190)      '12632256

    While Idx < intCtrlCnt
        strCtrlNames(Idx) = Me.Section(0).Controls(Idx).name
        intCtrlPos(Idx) = Me.Section(0).Controls(Idx).Left
        If (Me.Section(0).Controls(Idx).height > MaxHt) Then
            MaxHt = Me.Section(0).Controls(Idx).height
        End If
        Idx = Idx + 1
    Wend

    'Sort the Names according to the Position
    Idx = 0
    Sorted = False
    While Sorted = False
        Sorted = True
        While Idx < intCtrlCnt - 1

            If (intCtrlPos(Idx) > intCtrlPos(Idx + 1)) Then
                Sorted = False
                tmpCtrlPos = intCtrlPos(Idx)
                tmpCtrlName = strCtrlNames(Idx)

                intCtrlPos(Idx) = intCtrlPos(Idx + 1)
                strCtrlNames(Idx) = strCtrlNames(Idx + 1)

                intCtrlPos(Idx + 1) = tmpCtrlPos
                strCtrlNames(Idx + 1) = tmpCtrlName
            End If

            Idx = Idx + 1
        Wend
    Wend

    Idx = 0
    While Idx < intCtrlCnt
        If (Idx Mod 2 = 0) Then
            MyCtrl = strCtrlNames(Idx)
            If (MyCtrl <> "") Then
                Me.Line (Me(MyCtrl).Left - 10, 0)-Step(Me(MyCtrl).Width + 10, MaxHt), lngBkColor, BF
            End If
        End If
        Idx = Idx + 1
    Wend

End Sub

MichaelRed

 

[nissan240zx]

Found this on a faq site

There are two ways to do it, one with VB code (the "proper" way), and one
without, with a little trick. The funny thing is the code one is a lot
slower, and it becomes apparent in big reports!
Both ways rely on a line counter in the detail section of the report. To do
that, while in design view, add a new textbox, call it txt Counter and set
its ControlSource property to =1, and its Running Sum property to Over Group
(or over all, depending on how you want it to behave)! You can also set its
Visible property to No, so it doesn't show or print.
This done, the background is changed by examining the residue of the value
in txtCounter divided by 2 (which will always be 0 or 1).
Doing it through code, you would need something like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Counter Mod 2 = 0 Then
Me.Detail.BackColor = 12632256
Else
Me.Detail.BackColor = vbWhite
End If
End Sub

behind the Format event of the detail section (works great but slow).

Alternatively: add another unbound textbox in the detail section, with its
ControlSouce property empty (delete its label), and drag it to cover the
entire detail section area. While it's selected go Format > Send to Back (so
the data controls will always be on top), and then go Format > Conditional
Formatting; in the dropdown under Condition 1 select "Expression is", and
type the following expression next to it:

[txtCounter] Mod 2 = 0

Then right underneath, select the background colour you want ("bucket"
tool), and you're done!

 

[MichaelRed]

Not sure that you really understand this thread. Your discriptions suggest that you are doing the alternate ROW shading, with techniques which have been discussed in these fora extensively. This Thread was (is?) a discussion on shading COLUMNS, which is somewhat different.



MichaelRed

 

[RichardBarth]

Michael is correct. The issue is not how to cause shading to appear that's easy. Even shading columns is not difficult. The issue is how to do it in selected colums where one of the columns (not shaded) is set to "can grow". When the data in that column is sufficient, it causes the entire row (i.e. the row height) to increase in order to accomodate the larger quantity of text. When this happens, the shaded columns do not get any larger.

The height of the textbox cannot be changed in the Print event; it must be changed in the Format event. However, there is no way to know what to set it to in the Format event.

I still have not found an acceptable solution to this problem.

 

[GingerR]

RichardBarth--why not use the code I have above? I just re-tried it (haven't been here for a while and just stumbled upon this) and it works perfectly!

I put the code in the OnPrint event of the detail section of the report.

In the text boxes that I want to be shaded, I put the word SHADE in the tag.

Even if there is an unshaded control that DOES GROW VERY BIG, the shaded controls, even if they have a few characters in them, shade just fine!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RichardBarth]

Ginger,

I've tried your code and it's quite elegant. In fact, it works perfectly - sort of. The results appear perfectly shaded, even on the longer rows resulting from "can grow" in Print Preview. However, when I actually print the page, all of that shading is gone!!

Any thoughts would be appreciated?

 

[GingerR]

odd. works fine for me on 3 diff printers.

if you make the backcolor of any other control (title or something else on a report) grey or another color, does it print properly?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244