CanShrink in VBA?

[p00le]

Hello,
I am trying to manipulate the CanShrink property of multiple textboxes on a report using VBA code. Is this possible? In the detail section of my report I will have textboxes containing Null values. Some of these boxes need to shrink while others should be visible and full size. I have created an If...then statement to manipulate these controls. Thus far I cannot find a command able to mimic the CanShrink property available in Access. Can anyone suggest a similar command? I have defaulted to calling on the Borderstyle and Height properties in VBA, but haven't achieved the desired results. Your input is appreciated. Thanks, Julia

 

[chrismcbride]

Julia
I think you are on the right track. I do something like that in order to show/hide Tax, Discount, etc fields that are calculated controls on my various Quotation reports. I use the Height, Width properties to change the size (label too, if you want).
You can then use the Top property to move the controls around (in case you want to preserve spacing). Have included a rather long winded example...
Chris

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
Me.notes = DLookup("notes", "tblNotes", "trans_no = '" & Me.quote_no & "'")

If IsNull(Me.notes) Then
Me.lblNotes.Visible = False
End If

' Set to Invisible if there is no value.

If Me.gst = 0 Then
Me.gst_value.Visible = False
Me.gst_lable.Visible = False
Else
Me.gst_value.Visible = True
Me.gst_lable.Visible = True
End If

If Me.pst_value = 0 Then
Me.pst_value.Visible = False
Me.pst_lable.Visible = False
Else
Me.pst_value.Visible = True
Me.pst_lable.Visible = True
End If

If Me.discount_value = 0 Then
Me.discount_value.Visible = False
Me.discount_lable.Visible = False
Else
Me.discount_value.Visible = True
Me.discount_lable.Visible = True
End If

If Me.cfi_value = 0 Then
Me.cfi_lable.Visible = False
Me.cfi_value.Visible = False
Else
Me.cfi_value.Visible = True
Me.cfi_lable.Visible = True
End If

If Me.ship_value = 0 Then
Me.ship_lable.Visible = False
Me.ship_value.Visible = False
Else
Me.ship_value.Visible = True
Me.ship_lable.Visible = True
End If


' set initial positions and heights assuming all controls will be shown.

With Me
.ship_value.Height = 300
.ship_value.Top = 550
.ship_lable.Height = 300
.ship_lable.Top = 550

.discount_value.Height = 300
.discount_value.Top = 920
.discount_lable.Height = 300
.discount_lable.Top = 920

.cfi_value.Height = 300
.cfi_value.Top = 1290
.cfi_lable.Height = 300
.cfi_lable.Top = 1290

.gst_value.Height = 300
.gst_value.Top = 1660
.gst_lable.Height = 300
.gst_lable.Top = 1660

.pst_value.Height = 300
.pst_value.Top = 2030
.pst_lable.Height = 300
.pst_lable.Top = 2030

.total_value.Height = 300
.total_value.Top = 2400
.total_lable.Height = 300
.total_lable.Top = 2400
End With

' if a control has no value then shift all the controls beneath up

If Me.ship_value = 0 Then
With Me
.discount_value.Top = .discount_value.Top - 370
.cfi_value.Top = .cfi_value.Top - 370
.gst_value.Top = .gst_value.Top - 370
.pst_value.Top = .pst_value.Top - 370
.total_value.Top = total_value.Top - 370
.discount_lable.Top = .discount_lable.Top - 370
.cfi_lable.Top = .cfi_lable.Top - 370
.gst_lable.Top = .gst_lable.Top - 370
.pst_lable.Top = .pst_lable.Top - 370
.total_lable.Top = total_lable.Top - 370
End With
End If
If Me.discount_value = 0 Then
With Me
.cfi_value.Top = .cfi_value.Top - 370
.gst_value.Top = .gst_value.Top - 370
.pst_value.Top = .pst_value.Top - 370
.total_value.Top = .total_value.Top - 370
.cfi_lable.Top = .cfi_lable.Top - 370
.gst_lable.Top = .gst_lable.Top - 370
.pst_lable.Top = .pst_lable.Top - 370
.total_lable.Top = total_lable.Top - 370
End With
End If
If Me.cfi_value = 0 Then
With Me
.gst_value.Top = .gst_value.Top - 370
.pst_value.Top = .pst_value.Top - 370
.total_value.Top = .total_value.Top - 370
.gst_lable.Top = .gst_lable.Top - 370
.pst_lable.Top = .pst_lable.Top - 370
.total_lable.Top = total_lable.Top - 370
End With
End If
If Me.gst_value = 0 Then
With Me
.pst_value.Top = .pst_value.Top - 370
.total_value.Top = .total_value.Top - 370
.pst_lable.Top = .pst_lable.Top - 370
.total_lable.Top = total_lable.Top - 370
End With
End If
If Me.pst_value = 0 Then
Me.total_value.Top = Me.total_value.Top - 370
Me.total_lable.Top = Me.total_lable.Top - 370
End If

If Me.pst = False And Me.gst = False And Me.cfi_value = 0 And Me.discount_value = 0 _
And Me.ship_value = 0 Then

Me.total_value.Top = Me.total_value.Top - 370
Me.total_lable.Top = Me.total_lable.Top - 370
Me.sub_total.Visible = False
Me.lblSub_Total.Visible = False
End If

End Sub