Solved

Need to create dynamic vertical line in Access 2007 report

Posted on 2011-03-01
13
1,968 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 57
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 57
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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now