Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Star Lounger
    Join Date
    Sep 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Shading alternate detail lines in a report (Access 2K)

    I would like to ba able to shade every second line in a report to make reading easier...does anyone know if this can be done. <img src=/S/help.gif border=0 alt=help width=23 height=15>
    Cheryl =^..^=

    IS Support, Adelaide <img src=/S/granny.gif border=0 alt=granny width=20 height=20>

    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shading alternate detail lines in a report (Access 2K)

    Hi

    Yes this can be done!

    You can use a little bit of VBA to do this.

    I have included a sample access 2K file, with a table and report that shades every second line.

    You use two events of the Report:
    -the On Open event of the report
    -the On Format of the detail section of the report

    In the On Open event of the report, set a module level variable to 0

    The detail section of a report is 'formatted' once for each line of the report. This means that for every line of the report, we can make a bit of VBA run by using the 'On Format' event of the detail section of the report.

    In the On Format event of the detail section, you need to:
    -increment the Line Number variable by 1
    -determine if the Line Number is odd or event

    To determine if the line number is odd or even the mod operator is used. The mod operator returns any remainder of the difference between the division of two numbers. For example, 3 mod 2 = 1 (because 3 / 2 = 1 with 1 remaining). If we divide by 2 and there are no remainders (ie. line number divide by 2 has no remainders) then the line is even, else it must be odd.

    We then use a if..then..else..end if statement to change the background colour to grey if the number is odd, or white if the line number is even.

    There you have it, a report with alternate grey and white lines!

    Hope this helps. Unzip the file and run the Acc 2K database, run the report and you will see the result.

    Here is the VBA from the Report Module

    <font color=6495ed>Option Compare Database
    Option Explicit
    'Declare the line number variable and make it available to all
    ' withing the report
    Private lngLineNumber As Long

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    '================================================= =========='
    ' Author: Jayden MacRae '
    ' Created: 26/10/2001 '
    ' Purpose: To increment the line number by one each time '
    ' and then to determine if the line number is '
    ' odd or even and set the background colour '
    ' appropriately or not. '
    '================================================= =========='

    'Increment line number by one
    lngLineNumber = lngLineNumber + 1

    'Determine if the current line number is a odd or even
    ' number
    If lngLineNumber Mod 2 > 0 Then
    Me.Detail.BackColor = 12632256
    Else
    Me.Detail.BackColor = 16777215
    End If
    End Sub

    Private Sub Report_Open(Cancel As Integer)
    'Initially Set the Line Number Variable
    lngLineNumber = 0
    End Sub</font color=6495ed>

    Cheers

    Jayden
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shading alternate detail lines in a report (Access 2K)

    I've just realised as an alternative, you can do it without the module level variable.

    In this example, I've used the same technique, but instead, just checked to see if the current colour of the detail section is white. If it is white, then the colour is set to grey. If it isn't white, then it is set to white.

    <font color=6495ed>
    Option Compare Database
    Option Explicit

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    '================================================= =========='
    ' Author: Jayden MacRae '
    ' Created: 26/10/2001 '
    ' Purpose: set the background colour appropriately or not. ' '
    '================================================= =========='

    'Determine if the current line number is a odd or even
    ' number
    If Me.Detail.BackColor = 16777215 Then
    Me.Detail.BackColor = 12632256
    Else
    Me.Detail.BackColor = 16777215
    End If
    End Sub
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Sep 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shading alternate detail lines in a report (Access 2K)

    Thanks for that...it looks quite easy. I have done quite a lot of programming in VBA but never to control the output of an Access report. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>
    Cheryl =^..^=

    IS Support, Adelaide <img src=/S/granny.gif border=0 alt=granny width=20 height=20>

    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Sample Reports Database (Access 2K)

    You can download a useful file from Microsoft called <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q231/8/51.ASP>RptSmp00.exe</A>, that contains a database of report samples, including one to shade every other line on a report. It has lots of other goodies in it as well, so I highly recommend getting it for reference.
    Charlotte

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sample Reports Database (Access 2K)

    I read about this topic in Ms. Feddema's "Inside Out" book (p. 295). I got the code to do the trick on-screen, but I don't get the same results on paper. Matter of fact, on-screen my report header and page footer both appear as "light gray," but they print out as plain-vanilla white. I don't want my report header and page footer to print with light gray backgrounds, but I would like to see the alternate line shading in the detail section. What have I overlooked?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sample Reports Database (Access 2K)

    Does the alternate shading of the detail section show on the screen, but not on the printout? You might have to make the dark color a bit darker, because many printers print light colors much lighter than they display on screen.
    A mid grey, e.g. RGB(192,192,192) is a bit dark on my screen as background, but prints nicely. You can experiment with other values.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sample Reports Database (Access 2K)

    The "light gray" looks pretty dark on screen, but I can detect no trace of it on paper, not even the faintest hint. I was hoping the answer would be as embarrasingly simple as the one you gave me about fractions rounding up in boxes too small to display them. Well, I'm not at work right now, so this'll have to wait till tomorrow. Thanks for the suggestion--I'll check it out tomorrow morning!

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sample Reports Database (Access 2K)

    Also make sure your printer will actually print a light gray. I've seen printers that wouldn't, or at least not until you turned on half-tones.
    Charlotte

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sample Reports Database (Access 2K)

    Another thought. If your printer is black and white, try using a yellowfor the shading. It prints light gray better than the grays do ... unless you're using a color printer, of course. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sample Reports Database (Access 2K)

    I use an HP Deskjet 970 (color inkjet). The report I'm building in Access will replace a similar document we created years ago in Word. The Word table is autoformatted ("Contemporary") for alternate line formatting, and said formatting prints just fine.

    In fact, my Access report includes lines and rectangles set to print some shade of gray, and they print OK! Do I need to do something with the "on print" event procedure?

    (For Helen This discussion brings up another question. The VBA code you provided in your book defines "vbLightGrey" as 12632256. Where does one find the "catalog" or whatever to get that string of eight digits to define specific colors? The More Colors dialog box displays numbers for Red/Green/Blue and for hue, saturation and luminescence, but...how does all that translate into that eight-digit number? I guess I need to understand this in order to change my code to specify yellow as the alternate-line shade.

    And oh, by the way, what does "vb" (as in vbLightGrey) stand for? THANKS!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sample Reports Database (Access 2K)

    1. The code to alternate the background color should be in the OnPrint or OnFormat event of the Detail section (not in any of the headers or footers).
    The controls in the detail section should have their background style set to transparent.
    If you still have problems, can you post the code you're using?

    2. Colors are defined by their RGB (Red/Green/Blue) values. Each can be 0 ... 255 (hexadecimal 0 .. FF).

    A color is stored as a long integer. The number representing a color is RedValue + 256 * (GreenValue + 256 * BlueValue).

    For example:
    Black has Red = 0, Green = 0, Blue = 0, so it is stored as 0 (hex 000000)
    White has Red = 255, Green = 255, Blue = 255, so it is stored as 255 + 256 * (255 + 256 * 255) = 16,777,215 (hex 00FFFFFF).
    Pure Green has Red = 0, Green = 255, Blue = 0, so it is stored as 0 + 256 * (255 + 256 * 0) = 65,280 (hex 0000FF00).
    Cyan has Red = 0, Green = 255, Blue = 255, so it is stored as 0 + 256 * (255 + 256 * 255) = 16,776,960 (hex 00FFFF00).

    A light grey could be Red = 192, Green =192, Blue = 192, so it is stored as 192 + 256 * (192 + 256 * 192) = 12,632,256 (hex 00C0C0C0).

    As you see, the hexadecimal notation is far more transparent than the decimal notation.

    If you look up RGB in the help index, you'll find more info.

    3. Microsoft uses a naming convention for constants: the first two letters are always lower case and represent the application; the part after that starts with an upper case letter. Examples:

    vbWhite: Visual Basic constant
    acQuery: Access constant
    xlCalculation: Excel constant
    wdCharacter: Word constant

    In VBA, there are 8 predefined constants for the basic colors, so you don't need to know the numbers for these colors:
    vbWhite, vbRed, vbGreen, vbBlue, vbCyan, vbMagenta, vbYellow and vbBlack.

    If a programmer wants to define another color constant, it is convenient to use the same prefix for consistency, like vbOrange. This is only a matter of consistency though, there is nothing to stop you from naming it conColorOrange or something like that.

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sample Reports Database (Access 2K)

    What a thorough response! (Where do you guys learn this stuff!?)

    The formatting is in the Detail section. (On the property sheet for the Detail section: Event tab, On Format...). The code I'm using came straight outta Helen's book (page 295, "Alternate Line Shading"). Thus:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Const vbLightGrey = 12632256

    If Me.CurrentRecord Mod 2 = 0 Then
    Me.Section(acDetail).BackColor = vbLightGrey
    Else
    Me.Section(acDetail).BackColor = vbWhite
    End If

    End Sub

    The formatting shows up on the screen exactly like I want it. Just doesn't print!

    Oh, yes: The controls in the Detail section all have their background set to transparent. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sample Reports Database (Access 2K)

    I'm reduced to incoherent rambling...

    I can't see anything wrong with the code. I copied it straight out of your post into a report, and it printed as desired...

    And since you mention that controls with a grey background are printed correctly, it seems not to be a printer setting.

    When I check Data only in the Page Setup dialog, the alternating bands disappear from both the screen display and the printout.

    What happens if you replace vbLightGrey by vbBlack? You should get horrible black bars.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sample Reports Database (Access 2K)

    Yeah..."horrible black bars" is correct! But...they won't print!

    I tried changing the border properties of all my Detail rectangles and text boxes to transparent/color White, but to no avail.

    So...next I'm gonna try printing this thing at a different system: Different computer, different printer, same OS.

    Thanks for all your time and advice.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •