Need to create dynamic vertical line in Access 2007 report

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
LVL 11
thydzikAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:

 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
 
rmonsenCommented:
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
 
thydzikAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
rmonsenCommented:
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
 
thydzikAuthor Commented:
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
 
rmonsenCommented:
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
 
thydzikAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
BTW, If Lebans class doesn't work, post back here and I'll take you through doing it in the onpage event.

JimD.
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
thydzikAuthor Commented:
JDettman,

can you please walk me though using the onpage event. this looks like a simpler method then the classes.
0
 
Jeffrey CoachmanMIS LiasonCommented:
^
<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
 
thydzikAuthor Commented:
JDettman, thank you. this solution was easy to implement.
0
All Courses

From novice to tech pro — start learning today.