Need to shrink detail section of report

[Cosette]

Hi all,

I have a detail section that lists members and spouses on the row below if there is a spouse in the record set. I have an 'on format' trigger that makes txtspouse not visible if the value is null. So far so good.

Unfortunately, even though I have allowed the detail section to shrink, it doesn't shrink. I have the suspicion that making txtspouse not visible is not enough. How do I go about the detail section to shrink and not include txtspouse when the value is null?

Thank you

 

[dhookom]

Set the Can Shrink properties of the text box and section to Yes.

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]

 

[Cosette]

Hi Dhookhom,

I did that already to no avail. If I leave an extra 1 inch of blank in the detail section, it doesn't shrink, even when I select can shrink for both the text box and the detail section!

Thanks for any other idea you may have.

Cosette

 

[LarrySteele]

You can move the spouse to a subreport. You then set the subreport itself to can shrink. So, when there's no spouse, the control effectively disappears.

HTH,
Larry

 

[dhookom]

A section will only shrink controls and the horizontal space they occupy. Don't expect vertical gaps between controls to go away. The section also won't shrink if any other control is to the left or right of the can shrink text box.

Your statement "extra 1 inch of blank" confuses me.

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]

 

[Cosette]

Hi Duane and Larry,

Larry thanks for the advice, I will try it if I can't troubleshoot the existing reply from Duane.

Duane, what I meant is that I created the detail section with 'txtmember' and 1 inch below 'txtspouse'. That way, I could test if, when spouse is empty, the next name appears an inche bleow or just below. After making sure the text box and the detail section are yes to can shrink, I still get 1 inch between names on the report, even when there is no spouse.

Cosette.

 

[dhookom]

Set the border around txtSpouse to display. If the value is actually Null and the text box is set to can shrink, the text box will not be visible. What are your results?

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]

 

[Cosette]

Duane,

By display I guess you meant to give the border an actual thickness, which I did. The text box only appears when there is a name inside it, and the border is thick, but when null, it doesn't shrink.

Sorry, I don't know what I am doing wrong. I set the event for 'on Format'. Could that be the problem?

Thanks

 

[dhookom]

You shouldn't need any code. I don't understand "The text box only appears when there is a name inside it, and the border is thick, but when null, it doesn't shrink." How do you know the text box doesn't shrink?

Do you see any border if the value is null?
What is the control source of the text box?
What do you see if you add another text box with a control source like:
=IIf(IsNull([Your Field]),"It's really Null", "It ain't Null")


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]

 

[Cosette]

Duane,

Thank you for your help.

There is no border if the value is null.
The control source of the text box is =[tblSpouse.FirstName] & " " & [tblSpouse.LastName]

I did what you told me and the text box does return "it's really null when there is no spouse in the record and "it ain't null when there is a record' so that seems correct.

I then have this code:

If IsNull([tblSpouse.spouseID]) Then
Me.txtspouse.Visible = False
Else: Me.txtspouse.Visible = True

end IF

You say I don't need code, is this the wrong way to go about it? There is also code before hand. Could code stop the detail sextion from shrinking if the code is only if then statement about visible?

Thanks

 

[dhookom]

This value will [red]never[/red] be null
=[tblSpouse.FirstName] & " " & [tblSpouse.LastName]
That is the text box I was expecting you to test since you stated earlier "txtspouse not visible if the value is null". There was no indication that you were testing one control and setting another visible.

Change the control source to:
=[tblSpouse.FirstName] + " " + [tblSpouse.LastName]
and set the Can Shrink to Yes. Remove your code.

Again, this assumes there are no other controls to the left or right of the text box.


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]

 

[phanbui]

Cosette:

Try this. Set the text box to able to grow and resize the text box that would allow for a single line printing. If it has more than one line, it will automatically grow. Good luck!

 

[Cosette]

Duane,

Thank you for all your work on this issue. However, I did what you said and the Detail section does not shrink when there is no data in the field. I have tried other fields and I get the same problem. Even though it is the last field in the detail section and both the are set to can Shrink, it is not shrinking. I assume that there is something else stopping it from shriniking, but none of the code is related to shriniking.

But just to let you know, I create a basic db with four records (first name and last name), two of them having no last name. I then set up my detail section with first name and an inch below last name. I then selected last name and the detail to shrink and it didn't!

Could it be a setup option at the DB level?

Thanks

David

 

[dhookom]

There is absolutely no DB option that controls shrinking.
Please answer these questions:
1) do you realize that
=[tblSpouse.FirstName] & " " & [tblSpouse.LastName]
will never shrink regardless of the values (or lack of) in the field?

2) Do you understand vertical spacing between the bottom of one control and the top of another will never shrink?

3) Are there any controls (lines, labels, text boxes,...) that have their top or bottom between the top and bottom of your Can Shrink text box?

4) Did you try
=[tblSpouse.FirstName] + " " + [tblSpouse.LastName]
as I suggested in my previous reply?

5) And, did your remove the ...invisible... code from your report?

6) Did you leave the border of the text box showing?

7) If you answered Yes to 4, 5, & 6 did the border disappear for Null values?


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]

 

[Cosette]

OK Duane,

I think there is a major misunderstanding from my first thread here. My only goal was to remove the space between two controls.

You wrote: 2) Do you understand vertical spacing between the bottom of one control and the top of another will never shrink?

If that is the case, I can never shrink the detail section of a report. Sorry for not being clear enough in my first post. I am looking specifically to shrink the size of the detail section if there is no info.

If my report has five text boxes one below the other and the last four are empty of data, the report will look poorly formatted to have all these empty spaces.

Anyway, thanks for your help, and I am sorry to have taken so much of your time.

 

[dhookom]

That is why I stated about 4 days ago "Don't expect vertical gaps between controls to go away".

There are ways to control this by placing a text box between the text boxes. Use a control source like:

=IIf(IsNull(SpouseID),Null," ")

Set the font of this text box to very large and set it to can shrink. Have it fill nearly all of the vertical space between your text boxes. If SpouseID is null, the text box will be null and the vertical area of the text box will shrink.

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]