CanGrow "Can't assign value to this object" Error 1

[corycrum]

I'm trying to record the "natural" height of certain controls on a report by setting the CanGrow and CanShrink properties to True. I keep getting an error message that reads: "you can't assign a value to this object." I'm trying to set a property, not assign a value to an object. I've only been doing Access VBA for about a week so I'm very new to this.

It's hanging up on the line "Me.Section(acDetail).CanGrow = True"

Here is my code:

Option Compare Database

Private shadeNextRow As Boolean

Const shadedColor = 8454143
Const normalColor = 16777215

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

' Choose a color based on the shadeNextRow value
If Abs([EST_FLOW]) > 250000000 Then
Me.Section(acDetail).BackColor = shadedColor
Else
Me.Section(acDetail).BackColor = normalColor
End If

'PART II: RECORD THE INITIAL HEIGHT OF THE CONTROLS IN THE REPORT

Me.Section(acDetail).CanGrow = True
Me.Section(acDetail).CanShrink = True

IHEST_DATE = Me.Controls![EST_DATE].Height
IHACT_DATE = Me.Controls![ACT_DATE].Height
IHBUS_LINE = Me.Controls![BUS_LINE].Height
IHINV_CENTER = Me.Controls![INV_CENTER].Height

Me.Section(acDetail).CanGrow = False
Me.Section(acDetail).CanShrink = False


Thank you so much - Cory

 

[Remou]

I do not think that the Format event is suitable. Try the report Open event for setting the CanGrow property of the detail section.

 

[dhookom]

It isn't clear to at least me why you want to set these properties during run-time. The "grown" property of controls and sections is not available until the On Print event. This is always too late to set any format properties.

As you have experienced there are some properties that are very touchy about when you try to set them.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[corycrum]

What I'm ultimately trying to accomplish is vertically centering the contents of all controls on each row of a report. (See my earlier post: "Vertically Aligning Text in Access Reports") I think I can accomplish this with the following four-step process:

STEP 1:
Record the initial, "natural" height of each control in the row while the CanGrow and CanShrink properties are set to True. These values will be stored in variables like "ControlOneInitialHeight".

STEP 2:
Find the maximum height of the controls in the row (i.e. find the tallest control’s height) and assign its value to a variable "FinalMaxHeight".

STEP 3:
Change the height of each control in the row to the "FinalMaxHeight". By doing this each control's height is the same across the row and the row height accommodates the "tallest" control.

STEP 4:
Set the top margin of each control equal to the following (example is for the first control):

(FinalMaxHeight – ControlOneInitialHeight)/2

In theory, this will perfectly, vertically align the contents of each control on the report. It's execution may be problematic though. Does this make sense? Any guidance on how could I could accomplish this?

Thanks again for your help! - Cory

 

[dhookom]

This makes sense but if full of holes. You can't get the grown height until the On Print event. This is too late to set the height of other controls.

You could find the max height and then use the Me.Print method to actually print all text. This method doesn't support word wrapping.

Have you checked

http://www.lebans.com

for a solution? Stephen Lebans has the best collection of this type of solution.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[corycrum]

I've tried lebans. The solution on that page only works when controls are fixed in size - they can't grow or shrink. If all the rows were the same height, then it would work. In my report, the row height needs to be able to change from one row to the next to accommodate different amounts of data put into a "memo" field. The controlls on some rows are very tall (8 lines tall), and the controls on other rows may only have one line of text. I need the report to dynamically adjust the row height from one row to the next to accommodate this so that on rows with controls that are 8 lines high, every control on that row grows to the same height. What I'm saying is that I need each control to grow and shrink together rather than independently.

Thanks, Cory.

 

[dhookom]

Didn't you also have a requirement to center all text vertically? If so, this would be much like the functionality of an HTML table which Access doesn't support. V 2007 might have a solution but I haven't spent much time in it.

If you can get by without centering your text vertically then you wouldn't have much difficulty.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[corycrum]

Centering the text vertically is a requirement. I think that if I could get the controls to grow and shrink together then taking the next step to center the text in the control would be easy by manipulating the margins.

Thanks - Cory

 

[dhookom]

I would create an ASP page to display the report in IE. I think all of your requirements would be met quite easily in HTML.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[corycrum]

I've looked at HTML. I don't think that's going to work. This is a report our whole group will need to work on. They can work in Access but learning HTML is probably not worth it for this single report.

I appreciate your help on this very much.

Thanks, Cory

 

[Remou]

It is not difficult to create a HTML page using code; just loop through the recordsets and write out lines to a text file. I have often created reports this way. In addition, there are a number of advantages to this method, such as it works well with Word and email.

 

[dhookom]

Remou is correct. Users don't have to learn HTML. All they would need to learn is how to use IE.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]