Memo text box

[Julie]

I have put a memo field into my database and I wanted to know how to "wrap"
the text so that the text entered goes to a new line (so to speak) when it
reaches the length of the text box or if an entry has already been made in
the Memo box and the user wants to add a new comment, I would like for it to
start on a new line. Is this possible? The reason for asking is because
sometimes we may want to delete a particular entry in the memo field but not
delete the other entries. In my table I have designated that field as Memo.

 

[Julie]

Just to let you know, I have already changed the "Enter Key Behavior" from
Default to New Line in Field. But I need to be able to view all entries when
I click on it and delete specific entries.

 

[Linq Adams via AccessMonster.com]

Whether each "entry" is on its own line or not doesn't really come into it!
The only way to delete "entries" in a memo field is to highlight the "entry"
and hit <Delete> or the Space Bar.

 

[Julie]

Thank you for that but how do I ensure that there is a carriage return at
then end of the width of the text box. The other question is how can I view
all the information that may be in the text box because as it is, I can only
see the first line and I don't want this massive memo box on my form. I
would sooner have something like the looks of a combo box but didn't know if
I could make a combo box for the memo.

 

[Philip Herlihy]

Thank you for that but how do I ensure that there is a carriage return at
then end of the width of the text box. The other question is how can I view
all the information that may be in the text box because as it is, I can only
see the first line and I don't want this massive memo box on my form. I
would sooner have something like the looks of a combo box but didn't know if
I could make a combo box for the memo.

One thing you can do with the control on the form is to set the "Can
Shrink" and "Can Grow" properties. If you do this, the control (e.g.
text box) will adjust as needed to display what's there, and will
disappear altogether under some circumstances if there is nothing in the
field (if you don't like this, don't use "Can Shrink").

The only way to put newlines into your memo field that I can think of is
to use VBA, which you've already ruled out, and it would be messy anyway.

However, you're going about this ALL WRONG! Your table design "violates
first normal form". That's jargon for saying that stuffing multiples of
a "thing" (in this case a user comment) into a single field is
recognised as bad practice which will make your life unnecessarily
difficult. It takes a bit of getting used to, but the "relational
database" way of dealing with data like this is to create a separate
record for each comment. That suggests a new table, "tblComments",
which contains the primary key (unique identifier) of whatever entity
your users are commenting on, optional fields (like the primary key of
the user making the comment, and the date) and the text of the comment
itself. Easy to delete one of those! A rule of thumb: if you have a
"list", think records, never commas or newlines!

An example. Say you have a range of food products, and you need to
record the ingredients of each. Some may have only one or two
ingredients; complex processed meals may have scores. So, you'd have
one table for the product, with a unique identifier ("primary key"), the
name of the product, and maybe other fields for relevant information
like price. Then you'd have a table of Ingredients, with two essential
fields: one would have the unique identifier of the product, and one
would identify the ingredient. So for a complex product, the
Ingredients table would have many records containing that product's
unique identifier (as a "foreign key") giving a reference to the main
product record.

Many (most!) people would go further, and have the Ingredients table
contain only the Ingredient name and a unique identifier for the
Ingredient. Then you'd have a further "associative" table (you could
call it "tblRecipe") with two fields: the unique-identifier of the
product and the unique-identifier of the Ingredient (and possibly
further fields, like maybe the date that Ingredient started being used
in that Product, or something to identify the percentage). So, if you
wanted to know what ingredients were in "Curried Beans", you'd simply
query tblRecipe for records containing the unique-identifier of "Curried
Beans".

I think you urgently need to put some study into what's called
"normalisation" - the collective experience of generations on how to
divide up data into tables so as to make it easy and flexible to work
with. You've got this far, so you won't find it that hard. Time spent
reorganising your tables will pay huge dividends.

This is the third time I've replied to one of your posts recently. It's
always useful to know how you get on - whether things are clear.

Phil, London

 

[Arvin Meyer [MVP]]

One thing you can do with the control on the form is to set the "Can
Shrink" and "Can Grow" properties. If you do this, the control (e.g. text
box) will adjust as needed to display what's there, and will disappear
altogether under some circumstances if there is nothing in the field (if
you don't like this, don't use "Can Shrink").

CanShrink and CanGrow are report properties which only are effective when
printing. If you print a form, you may also use the properties. You cannot
use those properties in form view when displaying on screen.

 

[Ken Sheridan]

Take note of what Phil has said. By storing each 'entry' as a line in a memo
field you are using the memo field as a data structure. The only data
structure which should be used in a relational database is a table.

By moving the 'entries' from the memo field into a related table you can
then change your text box on the form to a subform (in continuous form or
datasheet view) based on the related table and linked to the parent form on
the primary/foreign keys fields of the two tables. To enter a new 'entry'
you'd then enter a new record into the subform; to delete an 'entry' you'd
delete a record from the subform. The subform can be as shallow or as tall
as you wish. With a shallow one you'd scroll down to see the other rows.
Another option you might like to consider is putting the subform on the
second page of a tab control, with some of the controls bound to the parent
form's underlying recordset on the first page. This would enable you to make
the subform deeper and show more records at a time after tabbing to page two
of the tab control.

Ken Sheridan
Stafford, England