Results 1 to 8 of 8
  1. #1
    Fred615 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    6

    Divider lines instead of alternating color rows?

    I would like to be able to have row dividers every 4 or 5 rows in my report instead of alternating colors every row. Is that possible in Access 2007 or am I going to have to look into something like Crystal Reports to be able to do this?


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Can be done in Access. Requires VBA code in the report Detail section format event. Need a line control in the Detail section and code like:

    Me.linename.Visible = expression that evaluates to true or false

    The real trick is figuring out the expression - how to determine every fifth record.

    Try this.

    Create a textbox named tbxCount in Detail section with ControlSource of: = 1
    Set RunningSum to OverAll.
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.Line11.Visible = Me.tbxCount Mod 5 = 0
    End Sub

    Be aware, Format event executes only in PrintPreview or straight to printer.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Fred615 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    6

    Halfway there....

    June7,

    It's been a long, long time since I wrote any code within Access. WHere and how do I enter the "Private Sub... End Sub" code?

    Thanks for the help..

    Fred615


    Quote Originally Posted by June7 View Post
    Can be done in Access. Requires VBA code in the report Detail section format event. Need a line control in the Detail section and code like:

    Me.linename.Visible = expression that evaluates to true or false

    The real trick is figuring out the expression - how to determine every fifth record.

    Try this.

    Create a textbox named tbxCount in Detail section with ControlSource of: = 1
    Set RunningSum to OverAll.
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.Line11.Visible = Me.tbxCount Mod 5 = 0
    End Sub

    Be aware, Format event executes only in PrintPreview or straight to printer.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Open report in design view, select the Detail section (click on Detail bar), on Events tab of Properties dialog, select [Event Procedure] in the Format event, double click the ellipses (...) to go to procedure in VBA editor, type code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Fred615 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    6
    June7,

    Thanks for the info. I did as you stated but I am getting an error message when I go to print I'm getting an error message as in the first attachment. I also attached a view of the code I entered.

    Any thoughts or suggestions?

    Thanks so much,
    Fred

    Quote Originally Posted by June7 View Post
    Open report in design view, select the Detail section (click on Detail bar), on Events tab of Properties dialog, select [Event Procedure] in the Format event, double click the ellipses (...) to go to procedure in VBA editor, type code.

    Click image for larger version. 

Name:	Access1.jpg 
Views:	3 
Size:	39.0 KB 
ID:	6594
    Click image for larger version. 

Name:	Access3.jpg 
Views:	3 
Size:	15.9 KB 
ID:	6593
    Attached Thumbnails Attached Thumbnails Access2.jpg  

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Are your controls actually named Line11 and tbxCount or are those just the names from my example? Must use your control names.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Fred615 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    6
    I missed the part of your original response about a "line control". I now added a line in the Dtail section and changed the Me.Line to Me.Line19. Now I am able to print normally, however, I still am not seeing the line every 5th row. See enclosed capture..



    I notice that I am now getting line numbers on the right side which is probably a good thing for future reference work.
    Any other thoughts on the phantom lines?

    Thanks...
    Attached Thumbnails Attached Thumbnails Access4.jpg  

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    It works fine in my project. If you have the controls correctly referenced in the code and the code is correct syntax and viewing report in PrintPreview, don't know why not working for you. So would have to examine your project to analyse issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 02-09-2012, 05:43 PM
  2. Replies: 1
    Last Post: 11-02-2011, 03:15 AM
  3. Replies: 2
    Last Post: 08-28-2011, 06:06 AM
  4. Alternating Greenbar report
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-30-2010, 10:05 PM
  5. Replies: 0
    Last Post: 10-24-2008, 11:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums