How to control the can grow report option

I have a report that has a text box, the text box pulls data from a table.  This gives the user the ability to change the reports contents (Its a weekly letter).

All is working but since the users text from the table varies and can be longer than 256 the field in the table that populates the text box on the report is a memo field.  In the report, this text box pulls data with this data source: =DLookUp("RecallText","tRecallNotes","ID = 1").  In the report I've made the text box fairly small and set the property can grow to YES.

All is working great but I think the text box grows a little too wide and I'm getting a blank 2nd page on the report.  When I set the option Can Grow to NO, I don't have an issue.  Is there any way to prevent this or to limit the Can Grow property to only grow in height and the width remains locked/fixed?
thandelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
I cannot recall ever seeing a textbox grow in width, I don't think it can.

But if you are doing this in a report, I would encourage you to modify the reports recordsource and simply link the main table to your "tRecallNotes" table on the ID field.  That way you can avoid the DLOOKUP in the control source.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As Dale said, Textboxes do not grow horizontally.

Be sure of the spacing on your controls. Even a minuscule overlap of controls, or the relative position of those controls, could cause very oddly formatted reports.
Dale FyeOwner, Developing Solutions LLCCommented:
to further explain Scott's comment about overlap.

There should be no controls (you might be able to put a label in there as an exception to this) where any portion of the control extends into the space occupied by the control you want to expand vertically.  I generally make those controls the entire width of my report.  Then I set the height of the control to zero (0) and set the can grow property to Yes.  This way, if the control contains no data, it will take up no space on the report.  Unless, of course, you want the user to see that there is no data, in which  case you could change your control source (or query column) to:

=NZ(DLookUp("RecallText","tRecallNotes","ID = 1"), "No Data")
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jeffrey CoachmanMIS LiasonCommented:
Not sure either.

Just make sure that there are no unprintable characters stored in this field.
...Line feeds, Carriage Returns, ...etc

It would help if you posted a screenshot of this phenomenon...
Or better yet, post a small sample database that exhibits this issue...
It is not clear if other factors are contributing here:
(Force new page, Subreports, Grouping, Keep together, ...etc)

JeffCoachman
thandelAuthor Commented:
I can't figure out why its doing this... if I set can grow to no no blank page... if it can grow blank page.  If I set to not grow and manually make the box as large as possible (its the last item on the page its OK.  I'll let set not to grow and call it a day.
thandelAuthor Commented:
Dale how would I link to the 2nd table?
Dale FyeOwner, Developing Solutions LLCCommented:
In your Dlookup, you used a criteria "ID = 1", where does the 1 come from? Why isn't that criteria looking for an ID # equal to some value in your reports recordset, that would be the normal method for a relationship between tables.

If I said link, I meant JOIN.  How familiar are you with creating queries that contain more than one field?

The assumption is that you have two table that have field in common.  This is called a "key field".  In your case, it would probably be the "ID" field, although this is not very descriptive and is easy to confuse if you use this name for your autonumber fields.  The query might look like:

SELECT T1.*, T2.RecallText
FROM yourTable as T1
LEFT JOIN tRecallNotes as T2
ON T1.ID = T2.ID

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.