In Access 2000, I have a subform that is a datasheet with the fields of Client ID (number) Date and Case Notes (memo). When I place this subform into a form, I want the memo field to grow taller based on the amount of typed data each new record will have. Therefore, there could be one record with only one word typed into the memo field, so it would remain the same, but if there was a paragraph typed into the memo field, that record would expand it's height so that the paragraph could be seen without having to manually adjust the height. I have it set to auto grow, but that does not seem to work. Any ideas on how to accomplish this?
A form is used to add or edit data. Therefore your form size would be changing for each record and may confuse the user. You could set the scroll bar of your memo field to Vertical or Both depending on the size of your field
What I do is to have a full size form open (for the record in question) when the memo field is clicked in/on.
Another way that could work, but would be real messy with a tabular form, would be use VBA or macro to change the height of the field and you could base this on the Len function. The Len function counts the number of characters. That would be easy with a Columnar form and placed OnCurrent. However, you would need to allow for the position of other fields which might get covered over by the expanding memo field. Poppa Smurf has it right, confusion and anger will rule
An extension of option 1 would be to use the Len function to detemine if there is more writing in the memo field than is showing. Thus you would use the Len function to have the writing in red if the character count was larger than was being full displayed, then the person would know to click so as to open a full size view of the field.
I don't know if Access 2000 has Conditional Formatting. If not, then in your query make two fields that are based on the Len count and with IIF. Say 100 characters was the basis, then one of the IIF created fiedls would be null if charcter count was a 100 or less and the other would be null if it was >100. When not null the IIF field would give the value of your memo field
In form design you would make the field that is >100 with red font, or whatever. Overlay the two fields and with the top field having a transparent background.
Intead of having the field show red font when character is >100 you could have the IIF produced field give a result like......Click here as to much writing to display.....
Note that the Len function counts spaces as characters. Thus if your field had an entry where there was a short line of writing and then one other line of writing that was postioned a couple of lines down (double space) and thus out of view the Len function would do what was required even if the number of letters was less than 100, or whatever number you chose.
As a side note, this is about 100 times easier and quicker to do than it is to describe it
But on a subform, even if you do as you suggest, the results will be less than desirable. The control would be sized only when you select a specific record and then ALL instances of that control would be resized in the subform.
You adjust the height and width of the field using the Current event of your form. But, as Mike has stated you need some spare "real estate" otherwise some your existing fields may be hidden etc.
Here is some sample code to adjust the height and width of a field depending on the number of characters including spaces any other punctuation symbols
Me!Description is the field name
1440 is in twirps and equal 1" therefore 1440 * 6 set it to 6" high
the Else bit resets it to the original size.
20 is the value to change the height
Private Sub Form_Current()
If Len(Me!Description) > 20 Then
Me!Description.Height = 1440 * 6#
Me!Description.Width = 1440 * 4#
Me!Description.Height = 1440 * 1#
Me!Description.Width = 1440 * 2#
That is why I said it would be real messy on a tabular form.
As far as I can know, the only answer is to open a form with a larger version of the field.
From a practical point of view that could be automated on a tabular form. I am assuming the person might click on the field so as to make an entry. The larger from could open based on Len() but I would do as currently do and just have the bigger screen open, without conditions.
The other solution that works, at least for my requirements and perhaps for some others, is to open another tabular form with the same records that are in the subform. Such a form does not have the same field size restraints etc as the subform.
I always find these problems interesting because there is no definite answer as a lot depends on how the data base is used. For my use, subforms just show insurance policy details at a glance. I need more than "a glance" then the separate form is opened.