Access report "Can Grow" property needs to effect neighboring controls

Question

I have a report with a bunch of controls in the Detail section. I am working off a word document that was given to me as a sample and I recreated it in access almost perfectly. What I need now is a way to handle overflowing the text boxes. I have "Can Grow" enabled, but here is the real issue:

I have a fake table going on. Multiple text boxes arranged in a way that just doesn't work in a subform. Certain cells have red, green or yellow backgrounds while others are just plain white. When one of the text fields overflows, and "grows", the other text boxes in the same row stay the same size as before and it looks very very odd (703 twips vs 300). I would really just love for it to work as if it were a table in word/excel and the entire row would grow at once (all =703 twips), but seeing as how it isn't literally a "row" I just want a way to associate the height of these text boxes with each other.

Is anything like this possible? If I need to clarify anything just let me know, I hope I've given ample information.

Sportscast

where did you set the 'CanGrow' Property, for the detail section, or just the single control? See if setting the property for the entire section helps. – Sayer

It is set on the entire detail section as well as every control within it. I know the issue is because each text box is independent, I need a way for them to grow together as if they were a real table though... – Sportscast

Answer 1

Ok here we go. So I had a fake table, I needed it to have table borders around each text box and when one text box got taller than the others in the row, the borders would look totally wrong. So, what we have to do is literally draw on the report at runtime, which can be done in any view including print preview. This code must be placed in the Print event

'step one. find out which box in the row has the greatest height value. 
'You can come across this information however you want. 
'It will likely depend on what data goes in the boxes.
'For the sake of the answer length we will skip that actual code

'step two. Take measurements and store them in variables.
'You will need a start point, and an end point in standard (x1,y1),(x2,y2) form.
Dim t As Integer 'top
Dim l As Integer 'left
Dim b As Integer 'bottom

'step three. Use these measurements and draw your lines.
'Try to use looping if your naming and report layout work will allow it.
Me.Line (l, t)-(l, b), RGB(0, 0, 0)
'       (x1,y1)(x2,y2),pick a color
'We just drew a line straight down the length of the control
'If you plan ahead, and place a line on the report permanently on top of the first row
'and below every row, you will only need to draw vertical lines.
'The lines below each row will be pushed down by the tallest control

I hope this helps. I had no idea this stuff existed before at all. Here is the MSDN info about it: http://msdn.microsoft.com/en-us/library/aa221362%28v=office.11%29.aspx

Sportscast

The answer below my answer is also very useful, many people will only require what is listed below – Sportscast

Answer 2

In the design view of your report select all the textbox controls in the detail section and all the labels in the page header section. Right click on one of the textbox controls and select Layout -> Tabular. The controls and labels should now align with each other.

You've not set which version of Access you're using but this works in Access 2007.

Turgeon

I actually just discovered another workaround. The tabular layout is incredibly close to what I need so I will upvote this so that others searching through will see this answer as well as the one I will post that allowed for a more table like display. The only problem with the tabular layout is that there is this space between the controls I can't seem to get rid of. Other than that it is absolutely perfect. – Sportscast

I just ran into this same issue. I'd like to add that I got around the spacing problem by adding gridlines to the tabular layout, then changing the padding to 0" in some of the table cells. I changed the gridline color, and now it all looks just like it did before except the whole row grows with the content. – Armin

Answer 3

OK...

1 - Go into Design view of your report

2 - Select ALL the textbox controls in the detail section that you want to be table like and ALL grow if any one grows.

3 - Right click on one of the selected textbox controls and select Layout -> Tabular.... but... now... after you have your textbox row controls as a tabular layout... You must do one thing more...

4 - Inside your reports FORMAT TAB properties... Set ALL PADDING properties to 0.00... 0.00 for Top Padding, 0.00 Right Padding, 0.00 Left Padding and 0.00 Bottom Padding. (default is usually around 0.0208)

5 - Please note that changing your textboxes to a Tabular Layout kinda throws your textboxes to the right (at least for me) so you may need to re-adjust them so they are aligned with your Page Header textboxes again (if you have any that you might be using to header name your table like columns).

6 - Make sure ALL SPACE is removed in the detail area by having the bottom bar (page or report footer) snugly up against the bottom of your Tabular Textbox Layout and also ditto with the upper detail bar (page or report header).

7 - Now when you look your report in print preview with max 200% or more zoom you WILL still see very very minor spaces between your cells in your layout but when you actually print they will be almost unnoticeable unless you take out a magnifying lens.

Cutback

Answer 4

I found a solution for my situation but I would like to have a better one... In my case, a TextBox at the left side is describing the content of 1 to 3 pictures on the right side. The TextBox, if enlarging more than the height of the first picture, would shift pictures 2 and 3 down, in spite of a properly designed Top-Attribute in the picture controls.

My solution: I put the height attribute of the text box to 15cm (e.g 2 Inch) and enable it as shrinkable. However, strange, but this helps for positioning the Pictures at its desired Top-Locations... :-) as well as minimizing the horizontal space used.

Overstudy