Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need to create dynamic vertical line in Access 2007 report

Posted on 2011-03-01
13
Medium Priority
?
2,096 Views
Last Modified: 2012-05-11
I am having a very difficult time trying to achieve a simple task of creating a dynamic height vertical line.

here is my setup.
The first textbox has hide duplicates as yes. this means that any hidden duplicates creates an ugly looking hole in the report. I need a vertical line to fill this hole.

I have attached screen shots which better shows this.

please advise how I can proceed. It may not be adding a vertical line specifically, I just need a method to show a line in the missing area.

 design view
 report view
0
Comment
Question by:thydzik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 3

Expert Comment

by:rmonsen
ID: 35015646
Create a copy of the leftmost textbox, and set its "Control source" to blank. This will create an unbound text box of the same size and style as the other, then use send to back to get it below the other box, and place it in the exact same position (same "Left" and "top" properties). Since the new text box is below the other, it will only be visible when the dublicate-hidden box is not showing.

If you really want just a line to the left instead of a complete grid, just use the line tool to draw a simple line of the same height as the text box and again use "send to back" to get it below the textbox.

Remember that anything you put into the detail row will be repeated for each row, so you don't need any special trick to get that line to repeat.
0
 
LVL 11

Author Comment

by:thydzik
ID: 35015903
this sounded like a good idea, but I can't get it working.

see attached screenshot.

its seems that if the textbox is not inline on the row, it doesn't behave the same way. In this case it wouldn't grow.

 report view test1
0
 
LVL 3

Expert Comment

by:rmonsen
ID: 35015995
Are you sure your new textbox is the same size as the duplicate-hiding textbox?

From the screenshot of your design view, it looks like you have manually set the height of all the boxes, and are not using access' "can grow" property. And assuming this is the case, the unbound text box should keep the size you give it in design view. Make sure the "CanShrink" property of the textbox is set to "No" though.

If your other boxes are set to automatically grow, one workaround is to bind the text box to the same data as one of your other columns (that 7th column in your report looks like the largest), and set this box to "CanGrow", and the text to white (making it invisible). This should only be done if the other text boxes uses "CanGrow" though.


Note that you don't have to use a text box at all, you can use a label or a rectangle as well, as both can have borders and user-defined sizes. They will work just as well unless you need the CanGrow property.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 11

Author Comment

by:thydzik
ID: 35016169
thanks,

the textbox is definitely the same size.

I have all other textboxes as 'Can grow No' and 'Can shrink yes'
This new textbox I have as Can grow yes' and 'Can shrink yes'

There is no column that will always be the highest, it depends on what is entered.
0
 
LVL 3

Expert Comment

by:rmonsen
ID: 35016303
As long as your other text boxes are not set to grow, you should set this "placeholder" textbox to "No" for both "CanGrow" and "CanShrink", as you want it to be the exact size you specify, and not dependant on the data contained within (which is none).

Also, as long as you are not dealing with growing rows, you might wish to follow my other options of using a simple rectangle (from the Design Toolbox) instead. You can set the border style for this the same as for the textbox, and it should serve to nicely create the border you desire.
0
 
LVL 11

Author Comment

by:thydzik
ID: 35016346
rmonsen, not obvious on the screenshots, but I am dealing with shrinking rows, which is creating problems.

if I set cangrow and canshrink to no, any shrunk rows will be padded with space to account for the non-shrinking new textbox.
0
 
LVL 58
ID: 35016477
Not sure if this will work in A2007:

HTTP://WWW.LEBANS.COM/PRINTLINES.HTM

but give it a shot.  If not, best approach to drawing lines is to use the OnPage event to draw lines.  At that point, the page is fully rendered ready to be printed, so sections have already grown/shrunk, etc making it easy to do.

JimD.
0
 
LVL 58
ID: 35016492
BTW, If Lebans class doesn't work, post back here and I'll take you through doing it in the onpage event.

JimD.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35019122
FWIW, I gave up years ago on trying to give Access reports this "Excel/Grid" type design.
(With boxes and lines that grow and shrink with the height of the detail section...)

For me, it was not worth all the custom code and trouble.

IMHO, simple horizontal lines separating each record look more elegant anyway...

Just my 2c
;-)

JeffCoachman
untitled.JPG
0
 
LVL 11

Author Comment

by:thydzik
ID: 35053812
JDettman,

can you please walk me though using the onpage event. this looks like a simpler method then the classes.
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 35058982

 Simple enough.  Would look something like this in the OnPage event:

   Me.Line 1440, 0, 1440, 7200

   Arguments are line top x, line top y, bottom x position, and then bottom y position.  So the above would draw a line 1 inch from the left and 5 inches long.  The units are in twips, and there are 1440 twips to an inch.

  If you want to work in units other then Twips, which is the default, you can look at the code here:

ACC2000: How to Create a Line That Can Shrink and Grow in a Report
http://support.microsoft.com/?kbid=197595

  Which shows you how to use ScaleMode to change the units your working with.

  Last thing; keep in mind that in the OnPage event, the page is full rendered.  You can add lines to it, but that's about it.  You can't hide or add controls at that point.

JimD.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35059607
^
<Last thing; keep in mind that in the OnPage event, the page is full rendered.  You can add lines to it, but that's about it.  You can't hide or add controls at that point.>
Good point Jim.
;-)

thydzik, also note that the Page Event will not fire if the Report is opened in "Report View" (the default in Access 2007), so you will have to open the report explicitly in Print Preview.
(Or set Print Preview as the default Report View...)
0
 
LVL 11

Author Closing Comment

by:thydzik
ID: 35065393
JDettman, thank you. this solution was easy to implement.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question