Text divided by horizontal and vertical lines (1 Viewer)

[Laurad]

I have produced a Report, which is a record of pupil assessment each term, but am having problems with the lines between the blocks of text.

The text consists of 15 text boxes in 3 columns of 5 rows. Each box contains random text which can be as short as a few words or unlimited as the fields are memo fields.

I need to have vertical lines between the 3 columns and horizontal lines under each row of 3 text boxes.

The problem is that sometimes only the first or second row of text boxes have text, so I end up with 3 or 4 lines with no text between them (not sure if a line can have conditional formatting depending on whether there is text in a text box.. ?).

Also the other problem is that I cannot predict how long the text boxes are going to be - i.e., they could be one short sentence or several paragraphs which might even run onto another page.

Is there any way of eliminating the horizontal lines if a row is empty, and ensuring the vertical lines are long enough depending on how much text is produced?

I hope this is clear, if not I'll try to explain better.
Thanks
Laura

 

[gemma-the-husky]

try Allen Browne

http://allenbrowne.com/tips.html


from memory, I think there is a fair bit there about getting horizontal and vertical lines into reports

 

[Laurad]

Thanks Dave, I had a look but could not see a solution to my particular problem.

 

[vbaInet]

It may well be Leban:

http://www.lebans.com/PrintLines.htm

 

[Laurad]

It may well be Leban:

Thanks I downloaded and have a look and I'm sad to say it's all way beyond my Access programming skills. So many modules and calculations just to achieve vertical lines that grow depending on the size of the content in the memo boxes.

I thought I would try to create a sub form for each of the 5 rows and then to display the subform only if there was text in any of the 3 boxes in the row. I ran into the same problem of the vertical liines not growing with the height of the text box (memo field).

The data is in the detail section of the Report, BUT it has other fields above the 5 x 3 text boxes, so the lines cannot begin at the top of the detail section, but specifically under the first few text boxes.

I tried making a border around the text boxes, but it looks horrible... the text too close to the borders and no dividing lines.. they just look like boxes, not text in a nicely formatted report.

If anyone has a simple example that I could follow and build upon, that would be great. My skills are fairly limited.
Thanks
Laura

 

[gemma-the-husky]

Yes, it was lebans, I recall.

its not easy laura - access isnt really designed to do page layout with lines. for one thing, you only get lines on the detail rows, and you are likely to get a lot of blank space betwen the last detail row, and the footer/page bottom.

so it needs to be a psecial solution to give oyu lines- hence the code.


Often reports are often presentable without horizontal/vertical lines - is that not OK for you?

 

[Laurad]

Thanks for clarifying it for me. Yes, it's ok with me, but the people at school wanted the lines. I tried to dissuade them and I think I will try even harder now, now that I realise it's not an easy task. At the moment it looks a mess because if they have 2 x 3 rows with text they then have 3 floating and lost horizontal lines that serve no purpose but are there because I put them between the text boxes. The vertical lines hang in mid air and it looks terrible.

At least I know I am not missing something easy - thanks for your input.
Laura

 

[gemma-the-husky]

some things you can do

i HAVE put a few vertical lines in certain places - to separate sections. You need to make them as tall as the detail section, and set them to "can grow"

the other thing, is you can make alternate rows have a background colour (like old listing paper) a feint colour in the shaded lines looks OK.

In A2003, you had to programme it, but I think its a report option in A2007/2010

 

[vbaInet]

I guess you've already figured it's not going to be easy. What you need to do is use the LINE method of the report object (i.e. Me.Line) in the report's PRINT method to create vertical borders. It can't go in any other method so the idea is:

1. Get the height of the tallest control in the section (i.e. loop through the Controls collection and find which one is the tallest)
2. Set the report's ScaleMode to Twips (because that's the measurement you will get from step 1)
3. Use this height to draw the line

Here's a link about the Line method:
http://msdn.microsoft.com/en-us/library/aa221362(v=office.11).aspx

Here's a start:

    With Me.ControlName
        Me.Line (.Width + .Left + [COLOR=Navy]50[/COLOR], .Top + [COLOR=Red]5[/COLOR])-Step(0, maxHeight -[COLOR=Red] 10[/COLOR]), vbBlue
    End With

Where ControlName is the name of the control where you want the draw the line next to. The numbers are just offsets - play with those numbers afterwards.

Also, instead of trying to find the height of the tallest control, you may want to use the report's CurrentY property and apply some offsets to the y1 and y2 arguments of the Line method.

You obviously will need to do this for as many times you desire a vertical line.

Fyi: A line control doesn't have a Can Grow property

 

[Laurad]

Phew.. lots of homework.. I'll be back, no doubt, when I've figured it out.

A question to begin with - where would I put the code you supplied "With Me.Controlname... " Presumably you mean by "controlname" one of the 15 text boxes (which have "can grow" set to "yes")? This would be where I work out the height of the tallest control in the row? Then I could put a rectangle around each row of 3 boxes and if a row has no text in it, then I don't need to draw the rectangle. If I put a rectangle around each row that does have text in it, then it will appear to have vertical and horizontal lines..

You're right, it's not easy. Thanks for help and input.
Laura

 

[vbaInet]

About where to put the code was explained in my last post. This is an advanced technique and I don't think I will have time to troubleshoot every problem because it will require testing on my end. I think I've given you enough information to get you going.

I presume your layout is as follows:

[B][COLOR=Blue]|    [/COLOR][/B][_] [_] [_] [_] [COLOR=Red][_][/COLOR]   [B][COLOR=Blue]|[/COLOR][/B]   [_] [_] [_] [_] [COLOR=Red][_][/COLOR]   [B][COLOR=Blue]|[/COLOR][/B]   [_] [_] [_] [_][COLOR=Red] [_] [/COLOR]   [B][COLOR=Blue]|[/COLOR][/B]

The blue lines indicate the vertical line and this [_] represent the textboxes. The red ones indicates ControlName and you determine the tallest control from those 15 controls.

A rectangle control doesn't grow so that won't work.

 

[ChrisO]

Attached is an A2K3 version of drawing lines around text boxes on a report.

You can also change the margins if the text is too close to the borders.

Chris.

 

[Laurad]

Thank you both.. this is turning out to be rather complex and my apologies for asking dumb questions and taking up your time. I've been working all day trying to understand the Line function and am getting more informed by experimenting.

vbaInet, this may be another dumb question but what is maxHeight? My testing stopped at this point and I wasn't sure where I was to obtain that.

Chris0, the code is also useful. My problem is that I have text boxes with varying size memo fields on up to 5 lines (rows), not just one. Often it's just two or three, so I'm trying to avoid having horizontal lines that serve no purpose if only two rows have text. This is more ore less what it would look like if all the Memo fields had text... with vertical and horizontal lines between. All of this would be in the detail section of the Report.

TEXT FIELD    
TEXT FIELD
 
________________________________________
 |            |            |            | 
 |  MEMO BOX  |  MEMO BOX  |   MEMO BOX | 
_|____________|____________|____________|_
 |            |            |            | 
 |  MEMO BOX  |  MEMO BOX  |   MEMO BOX | 
_|____________|____________|____________|_
 |            |            |            | 
 |  MEMO BOX  |  MEMO BOX  |   MEMO BOX | 
_|____________|____________|____________|_
 |            |            |            | 
 |  MEMO BOX  |  MEMO BOX  |   MEMO BOX | 
_|____________|____________|____________|_
 |            |            |            | 
 |  MEMO BOX  |  MEMO BOX  |   MEMO BOX | 
_|____________|____________|____________|_

My memo fields are called:
target1 strategy1 outcome1
target2 strategy2 outcome2
target3 strategy3 outcome3
target4 strategy4 outcome4
target5 strategy5 outcome5

Sometimes all of the boxes will have text and sometimes only one or two rows.

Maybe it's just too complicated.

 

[ChrisO]

Try number two.

If you can’t get that working then can you please post a sample of your Report in Access version 2003?

Chris.

 

[Laurad]

Thanks, I will - No. 2 was very hopeful, but I got stumped by the fact that it used 3 memo boxes in one row in the detail section which I could handle, I think. My version has 5 rows (3 text boxes in each row) in the one detail field, so I have to ascertain whether any one of those 5 rows is empty and in many cases it's either the 5th or 4th and 5th. Sometimes they are all filled in. It's not boxes I need, (unless I can make the boxes merge somehow to look like liines), it's horizontal lines under each row (where text exists) and vertical lines all the way to the bottom of the last filled in text box.

The file is not complicated, it's 2 text fields for firstname and surname and 15 memo fields as itemised above, target1, strategy1, outcome1 and then on the next row, target 2, strategy2, outcome 2, up to the 5th row with target5, stategy5 and outcome5, all fitting into the detail section of a report, (don't worry about header and footer).

My VB skills are very basic, as you will have gathered. I probably need to loop through each row to find out firstly if it's empty or not and secondly to ascertain the highest of the 3 in each group of 3 so that I can determine how long the vertical line should be so that it reaches below the last filled in memo field, be it one row two rows, etc. and have a horizontal line appear only IF the row has text in it. I don't know enough to know how to loop through the memo fields in each row to check.

I even tried a suggestion to colour each "row", but I wasn't sure how to do that, so I made the background colour of every alternate row light grey, but the outcome was messy - if a memo box had only 2 words in it I got a small grey box, if it had a lengthy sentence I got a large grey shaded area and it looked very untidy. The thing is that each row height needs to be determined by the tallest memo box and the "rows" are only "rows" because of the way I have laid out the memo boxes, 3 across and 5 down.

Thanks again for everyone's input. This is something I've been trying to resolve for many months. At the moment their report prints off with one or two rows with a nice line under the ones that have text and then 3 random lines which appear with no text above, horizontal lines that start nicely at the top of the first row of text boxes, but end somewhere random in the middle of the report because I cannot predict how long they should be.

 

[ChrisO]

Laura.

How does my last demo not comply with your request?

Chris.

 

[Laurad]

Chris, my apologies, when you said try No. 2, I thought you meant the second example in the first database you sent me which is why I replied saying I had 5 rows in the detail section, not 1. I'm new to this Forum, so did not notice the attachment in your earlier post, sorry.

I've been at work, but am now working my way through what appears to be something I can really understand, it looks simple, yet effective and I'm very hopeful that it will be what I need. I will do my homework again and in the meantime, express my gratitude at your patience and assistance. I will be back to let you know how I got on and thank you so much!!

Laura

 

[Laurad]

Chris0, I've spent the time to understand the modules and functions to draw the boxes around the text boxes and I think I understand it all fairly clearly now. Thank you so much.

I think I will be able to implement it, but using your demo version, I managed to make the corners square, which is what I want, but one problem I still have - is there any way I can make the boxes ever so slightly larger than the actual text - i.e., I need padding (white space) around the text, it's all too close to the borders. I tried adding 100 to lngMaxHeight, but it didn't work. I suspect it's going to take a bit of extra maths, maybe too much, because each rectangle is based on the height of the tallest box in a row and then the next row starts on a new line and needs to be jutting up against the box above in order to make the borders look like lines between the text, rather than making it look like text in boxes. If we make the boxes bigger than the text boxes then they're going to overlap, I think?

Sigh... this has proved so complicated, but I've learned a lot on the way.

Thinking aloud here, but using your method of finding the tallest text box, then making all the boxes in each row the same height, wouldn't we then be able to just draw lines under each row of boxes, after having made the actual text box borders transparent, (as you did) and not painting rectangles? Could we not also have an incrementing variable that adds up all the tallest box heights to then draw vertical lines? I think I'll see what I can discover.

One other question, maybe not in the right place, but when previewing a report, is there any way to make the actual window wider? I know we can modify the zoom, but the window is always too small. In previous versions of Access that didn't seem to be a problem.

Thanks again, your help and examples are much appreciated.
Laura

 

[ChrisO]

Each of the text boxes has four margin properties, try adjusting those.

I think drawing lines would work but I don’t see why you would need to do so if you adjust the margins.

Not sure what you mean by making the window wider.
You could try DoCmd.Maximize or change the AutoExec macro from: -

This…
PreviewAndZoomReport("rptDemo", 0)
To this…
PreviewAndZoomReport("rptDemo",150)

Chris.

 

[Laurad]

I made a lot of progress today and combining some code from http://www.lebans.com/PrintLines.htm and your example, I actually came up with a way to draw vertical and horizontal lines depending on whether each row had text in it.

I'm satisfied now that I can use either method as I see from your last post that there are margin properties which I didn't even think to look for. For some reason I just assumed it was not possible.

Using the Lebans example, I incremented the height of the tallest text box for each row that had text in it and added 200 to take into account the spaces between the text boxes (mine weren't abutting each other), using this code:

For Each ctl In Me.Detail.Controls
        If ctl.Tag = "Line" Then
            Me.Line (ctl.Left, ctl.Top)-Step(0, lngMaxHeightAll)
        End If
Next

I drew horizontal and vertical lines, set their attributes to not visible, and then in the code, if there was text in the row, made them visible.

I think both methods will work and as I said, I've learned a lot. My report is going to look very smart now, thanks again.

As for the window size, no matter what the zoom level is the actual window is always on the small side and I have to always widen it to preview the report.