'Can shrink' question

  • Thread starter Thread starter Preston Bjorn
  • Start date Start date
P

Preston Bjorn

Much to my delight, I've completed a rather complex report: lots of
interlocking queries and intermediary tables--the data architecture is
pretty slick for a moron like me.

Problem is, one of the elements of my report is an address listing in a
typical [Address1] over [Address2] format. I've set the Can Shrink property
of [Address2] to 'yes,' with the understanding that if the field is empty,
then that line will not be seen, and will take up no space (such that the
[CityStateZIP] field will appear on the line directly under [Address1]).

Damned if I can get it to work, though. Been through the Help files a
couple of times, and can't seem to peg what I'm doing wrong.

Any thoughts? Much obliged for the help.

Pret Bjorn
Bangor, ME USA
 
CanShrink may not work if:
- There is an attached label beside the control.
- Other controls overlap it.
- There is another control beside the one you want to shrink (if it is also
partially beside the one on the next or previous row).
- There is any data in the control - even a space or zero-length string.

Additionally, set the CanShrink property of the (Detail?) section if you
want everything below to move up when the control shrinks.
 
Preston,
A more elegant way to manage address blocks is to use the
+ and the & operators to give you an address block
that "grows" and / or "shrinks". The + operator only has
effect when the field +'ed actually has data. In the
example below the first space (" ") only appears if there
is data in the title field. The CR and LF characters are
likewise.

=[Title]+" " & [first name]+" " & [last name] & (Chr(13)
+Chr(10)+[address line 1]) & (Chr(13)+Chr(10)+[address
line 2]) & (Chr(13)+Chr(10)+[address line 3]) & (Chr(13)
+Chr(10) & [locality] & " " & [state] & " " & [postcode])

This approach allows the address block to have other
controls beside it and it still behaves as expected.

Substitute your field names use one control for the entire
address.

hth,

Terry

This will give you:

Tom Jones
1 sample st
sampleton NY 5432

Rather than

Tom Jones
1 sample st

sampleton NY 5432
 
Is there a way to 'can shrink' an Bound object - a way to shrink a photo
frame if there is no photo??
David Proud



Allen Browne said:
CanShrink may not work if:
- There is an attached label beside the control.
- Other controls overlap it.
- There is another control beside the one you want to shrink (if it is also
partially beside the one on the next or previous row).
- There is any data in the control - even a space or zero-length string.

Additionally, set the CanShrink property of the (Detail?) section if you
want everything below to move up when the control shrinks.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Preston Bjorn said:
Much to my delight, I've completed a rather complex report: lots of
interlocking queries and intermediary tables--the data architecture is
pretty slick for a moron like me.

Problem is, one of the elements of my report is an address listing in a
typical [Address1] over [Address2] format. I've set the Can Shrink property
of [Address2] to 'yes,' with the understanding that if the field is empty,
then that line will not be seen, and will take up no space (such that the
[CityStateZIP] field will appear on the line directly under [Address1]).

Damned if I can get it to work, though. Been through the Help files a
couple of times, and can't seem to peg what I'm doing wrong.

Any thoughts? Much obliged for the help.

Pret Bjorn
Bangor, ME USA
Click to expand...
Click to expand...
 
Simply set the control's Visible prop to false in the Format event when
the control contains no data.


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Set the OLE Frame control's Visible prop to NO if empty.

If IsNull(Me.OLEBoundAutosize.Value) Then
Me.OLEBoundAutosize.Visible = False
Else
Me.OLEBoundAutosize.Visible = True
End If

End Sub


--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


David Proud said:
Is there a way to 'can shrink' an Bound object - a way to shrink a photo
frame if there is no photo??
David Proud



Allen Browne said:
CanShrink may not work if:
- There is an attached label beside the control.
- Other controls overlap it.
- There is another control beside the one you want to shrink (if it
Click to expand...
is
also
partially beside the one on the next or previous row).
- There is any data in the control - even a space or zero-length string.

Additionally, set the CanShrink property of the (Detail?) section if you
want everything below to move up when the control shrinks.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Preston Bjorn said:
Much to my delight, I've completed a rather complex report: lots of
interlocking queries and intermediary tables--the data architecture is
pretty slick for a moron like me.

Problem is, one of the elements of my report is an address listing in a
typical [Address1] over [Address2] format. I've set the Can
Click to expand...
Shrink
property
of [Address2] to 'yes,' with the understanding that if the field
Click to expand...
Click to expand...
is
empty,
Click to expand...
that
the
[CityStateZIP] field will appear on the line directly under [Address1]).

Damned if I can get it to work, though. Been through the Help files a
couple of times, and can't seem to peg what I'm doing wrong.

Any thoughts? Much obliged for the help.

Pret Bjorn
Bangor, ME USA
Click to expand...
Click to expand...
Click to expand...
 
This method makes the control invisible, but the object frame still produces white space in the report which i do not want.

I have 'can shrink' set to yes for the detail region of the report. I do not want a blank page (with an empty photo frame) following every text page of my report. I want the second page to disappear if there is no photo for that record.
 
What do you mean by "object frame"?
If you have another control :framing the Bound OLE object Frame control
then simply set it's Visible prop to NO as well.
--
HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


David Proud said:
This method makes the control invisible, but the object frame still
Click to expand...
produces white space in the report which i do not want.
I have 'can shrink' set to yes for the detail region of the report. I
Click to expand...
do not want a blank page (with an empty photo frame) following every
text page of my report. I want the second page to disappear if there is
no photo for that record.
 
I have a 'Bound Object Frame' where the controlsource is my 'Photo5'. The border style is transparent and there are no objects adjacent (to the left or right) the Bound Object Frame.

The detail section is 36cm high and 28cm wide. The width fits fine in A4 page landscape format. The trouble is that even when i make the Bound Object Frame not visible, with your code, the white space is still making a blank page where i do not want one. I have 'can shrink' set to yes for the detail region of the report, but i fear that unlike Text Boxes, which 'can shrink', Bound Object Frames even when invisible are still there - "Not Shrunk".
 
Send me the MDB and I will have a look at it for you.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


David Proud said:
I have a 'Bound Object Frame' where the controlsource is my 'Photo5'.
Click to expand...
The border style is transparent and there are no objects adjacent (to
the left or right) the Bound Object Frame.
The detail section is 36cm high and 28cm wide. The width fits fine in
Click to expand...
A4 page landscape format. The trouble is that even when i make the
Bound Object Frame not visible, with your code, the white space is still
making a blank page where i do not want one. I have 'can shrink' set to
yes for the detail region of the report, but i fear that unlike Text
Boxes, which 'can shrink', Bound Object Frames even when invisible are
still there - "Not Shrunk".
 
I knew I had posted a solution for this issue so when I searched Google
I found I had left out a line of code.

Me.Section(acDetail).Height = 0

Add the line of code after your current code in the Detail Format
section and everything will work as expected.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


David Proud said:
I have a 'Bound Object Frame' where the controlsource is my 'Photo5'.
Click to expand...
The border style is transparent and there are no objects adjacent (to
the left or right) the Bound Object Frame.
The detail section is 36cm high and 28cm wide. The width fits fine in
Click to expand...
A4 page landscape format. The trouble is that even when i make the
Bound Object Frame not visible, with your code, the white space is still
making a blank page where i do not want one. I have 'can shrink' set to
yes for the detail region of the report, but i fear that unlike Text
Boxes, which 'can shrink', Bound Object Frames even when invisible are
still there - "Not Shrunk".
 
Thanks I will try it tomorrow
David Proud

Stephen Lebans said:
I knew I had posted a solution for this issue so when I searched Google
I found I had left out a line of code.

Me.Section(acDetail).Height = 0

Add the line of code after your current code in the Detail Format
section and everything will work as expected.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.



The border style is transparent and there are no objects adjacent (to
the left or right) the Bound Object Frame.
A4 page landscape format. The trouble is that even when i make the
Bound Object Frame not visible, with your code, the white space is still
making a blank page where i do not want one. I have 'can shrink' set to
yes for the detail region of the report, but i fear that unlike Text
Boxes, which 'can shrink', Bound Object Frames even when invisible are
still there - "Not Shrunk".
Click to expand...
 
David it works here with both A2K and A2K2.

Here is the code copied from your report:

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

' Set the OLE Frame control's Visible prop to NO if empty.
'Me.Section(acDetail).Height = 0
If IsNull(Me.Photo5.Value) Then
Me.Photo5.Visible = False
Else
Me.Photo5.Visible = True
End If

Me.Section(acDetail).Height = 0

End Sub

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
It works here David.
What version of Access are you using?

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


David Proud said:
Both of the following work to suppress the height of the Photo5 Bound
Click to expand...
Object Frame, but there is still an unwanted blank page (the white space
represented by the 36cm detail section height). What should i try next?
 
I ended up making the detail section only one page high (to fit the fields on that page) and making Photo5 grow from 3 to 9979 when notnull- producing a second page when needed. Thanks for your help.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer
If IsNull(Me.Photo5.Value) The
Me.Photo5.Height =
Els
Me.Photo5.Height = 997
End I
Me.Section(acDetail).Height =
End Sub
 
Back
Top