Can not set the canshrink property of a control on a report

[Lucky33]

I am using Access 2003. I have an application for a medical lab and all is working fine except when it comes to printing the test's results. The problem is with formatting and not with getting the data into it. There are two sets of groups of tests : Main tests (Mtest table) and Sub tests (Stest table). Part of the main tests are just individual tests, i.e. they have their own definitions and results, and the rest of the main tests are the head of a group of sub tests with their own similar types of definitions and results. (see pdf copy attached).
The problem is that the sub tests are printed without any empty space between records (under CBC in my example), while the main tests that have no sub tests come with one empty line ( under Chemistry). I want the main tests printed the same like the sub test without trailing empty line. I know that the empty lines represent the sub test that do not exist. I tried the code below to change the canshrink property of all the sub tests. The first part of the code to make the borders transparent is working, but the code is hanging on the canshrink part with a message that i can not assign a value to this object. Any suggestions? Help is appreciated
This is the code for the OnFormat property of the Detail section of the report:
"
If IsNull(STest) Then
Me.STest.BorderStyle = 0
Me.Result.BorderStyle = 0
Me.SUnits.BorderStyle = 0
Me.SRange.BorderStyle = 0
Me.STest.CanShrink = True
Me.Result.CanShrink = True
Me.SUnits.CanShrink = True
Me.SRange.CanShrink = True
Me.Section(acDetail).CanShrink = True
Else
End If
"
Thanks

 

[boblarson]

Set the can shrink in DESIGN VIEW. There is no reason to set it at runtime.

 

[Lucky33]

if i do that in design view, all fields without data will shrink and the borders will disappear and the report looks bad (see attached). I believe the only way is to set the canshrink property with VBA which is not allowing it... The code worked with the ForeColour and BorderType but not the CanShrink

 

[boblarson]

This property setting is read-only in a macro or Visual Basic in any view but Design view.

You will have to use the control HEIGHT property instead.

 

[Lucky33]

Thank you for the efforts. The control HEIGHT did not give the needed results. In the code I set the height to 0 and in design view I set the Detail section CanShrink to yes. In the printout, the part with the sub-tests more lines were added and the part with main tests, the Can Shrink did not work (copy attached). If only we know why the code is not working with the CanShrink property, although all readings say that that property can be set with VBA
Thanks

 

[boblarson]

although all readings say that that property can be set with VBA

I don't know what readings you have been reading but didn't you read what I last wrote:

This property setting is read-only in a macro or Visual Basic in any view but Design view.

You can't set it anywhere but in DESIGN VIEW - even with VBA. You would need to put the report into design view when setting it from code, which would defeat the purpose of what you are trying to do.

And I didn't say to set the CAN SHRINK to YES if you were using the code to set the height of the controls to 0. Set the can shrink to NO and then set the control heights.

 

[Lucky33]

I apologize for missing your quote. I have set the Can Shrink to No in design view and got a new look (report attached). While the code took care of the extra lines in the Main Test part (under Chemistry) which originally was the problem, it also suppressed the rest of the lines having valid data in the sub test ( under CBC and Urine).. Here is the code:
"
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If IsNull(StestID) Then
Me.StestID.Height = 0
Me.STest.Height = 0
Me.Result.Height = 0
Me.SUnits.Height = 0
Me.SRange.Height = 0
Me.Section(acDetail).Height = 0

End If
End Sub
"
STestID is an auto number
Thanks

 

[stopher]

This should do the trick:

If IsNull(STest) Then
    Me.MoveLayout = False
Else
    Me.MoveLayout = True
End If

MoveLayout is normally true and is what instructs Access to scroll to the next line (that's the best way I can explain it). So we can set it to false when required so that it doesn't scroll i.e. the next line is printed on our blank line.

The above can be condensed to a single line:

Me.MoveLayout = Not (IsNull(STest))

hth
Chris

 

[boblarson]

I apologize for missing your quote. I have set the Can Shrink to No in design view and got a new look (report attached). While the code took care of the extra lines in the Main Test part (under Chemistry) which originally was the problem, it also suppressed the rest of the lines having valid data in the sub test ( under CBC and Urine).. Here is the code:
"
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If IsNull(StestID) Then
Me.StestID.Height = 0
Me.STest.Height = 0
Me.Result.Height = 0
Me.SUnits.Height = 0
Me.SRange.Height = 0
Me.Section(acDetail).Height = 0

End If
End Sub

You need to set a height if there is data. So you need an ELSE (and you don't set the detail section to 0.:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const TW As Integer = 1440 ' TWIPS constant 
With Me 
If Len(.StestID & vbNulllString) = 0 Then
   .StestID.Height = 0
   .STest.Height = 0
   .Result.Height = 0   .SUnits.Height = 0
   .SRange.Height = 0
 Else
   .StestID.Height = TW * 1.5 ' this would be 1.5 inches so 1.5 times twips per inch
   .STest.Height = TW * 1.5 
   .Result.Height = TW * 1.5 
   .SUnits.Height = TW * 1.5
   .SRange.Height = TW * 1.5
End If
End With
End Sub

So this would be setting to 1.5 inches but check to see what the actual control size you need is and then multiply that by the TWIPS constant. If you are using metric measurements then it is 564 TWIPS per cm instead of 1440 TWIPS per inch.

 

[Lucky33]

The good news is that the code worked but only after a little modification. I ran the code as is and i got to the very first report that I attached with my original post, extra empty lines were still there. I added one line, setting the Detail section height to 0 and voila, it is perfect. I am attaching the final report and it looks great. Here is the winning code (in case another visitor faced the same problem):

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const TW As Integer = 1440 ' TWIPS constant
With Me
If Len(.StestID & vbNulllString) = 0 Then
.StestID.Height = 0
.STest.Height = 0
.Result.Height = 0
.SUnits.Height = 0
.SRange.Height = 0
.Detail.Height = 0
Else
.StestID.Height = TW * 0.1875 ' this would be 0.1875 inches so 0.1875 times twips per inch
.STest.Height = TW * 0.1875
.Result.Height = TW * 0.1875
.SUnits.Height = TW * 0.1875
.SRange.Height = TW * 0.1875
End If
End With
End Sub

Million thanks for your help and patience

 

[Lucky33]

Stopher
thanks for the try. just out of curiosity i tried your code. while it solved the problem of the empty lines, it created something weird (see the attached, 2 pages). I think with some tweaking it might do the job
thanks again

 

[stopher]

Yes, I hadn't considered the borders

If you're still interested for curiosity's sake you could try:

Me.Section(acDetail).Visible = Not (Len(Me.STestID & vbNulllString) = 0)

In other words, make the whole detail section invisible if there is a null record (I've used the null test that Bob proposed).

Chris

 

[Lucky33]

And they say " curiosity may kill " not in this case. with the new line of code the report came out also perfect, same result as with the code from Bob. Also for any visitor who faces this problem, the following code is as good as Bob's :

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

Me.MoveLayout = Not (IsNull(STest))
Me.Section(acDetail).Visible = Not (Len(Me.StestID & vbNulllString) = 0)

End Sub

Thanks to both

 

[stopher]

And they say " curiosity may kill " not in this case. with the new line of code the report came out also perfect, same result as with the code from Bob. Also for any visitor who faces this problem, the following code is as good as Bob's :

Thanks to both

Actually, only the one line is needed (it's a slightly different approach). Sorry if that wasn't clear:

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

Me.Section(acDetail).Visible = Not (Len(Me.StestID & vbNulllString) = 0)

End Sub

Thanks for the feedback.

Chris

 

[Lucky33]

Wonderful. I am not a novice but not an expert either. Yes the one line code did the whole trick. Just for the records so others like me know that this code will hide any section in a report conditionally. Just replace the section name and the field name. I tried it with the Report Footer where i have a Remark field. The code hid the footer when the Remark was null.
Thanks again