memo fields - asking for trouble?

[deko]

I have an mdb (2002 file format) that uses memo fields extensively. I've
read a lot about how problematic memo fields can be and that avoiding them
is a good idea. But I'm stuck with them and was wondering if there are any
best-parctices for optimizing or mitigating the risk of using them. Here's
what one table looks like:

NoteText - Memo
NoteDate - Date/Time (indexed, dupes ok)
NoteBrief - Text (length = 254)
Entity_ID - (Long, Foreign Key, indexed, dupes ok))
Note_ID - (Autonumber, Long, Primary Key)

This table is getting awfully big - would putting the NoteText field in it's
own table (so that's the only thing in the table) help, or does it really
make a difference?

[deko]

> And I wouldn't do the updates in SQL, unless they were stored in a

different table. All I'd do is include the memo field in the form's
recordsource, and just not bind the control. For updates, something
like this in the control's AfterUpdate event:

Me!MyMemo = Me!fldMyMemo

Is this what you mean:

In the table I have "memNoteTex t", and in the form I have an unbound field
"txtNoteTex t" I have an OK button with:

Me!memNoteText = Me.txtNoteText

That puts the contents of the textbox into the table.

And Form_Open has this:

Me!txtNoteText = Me!memNoteText

Which populates the textbox with the data in memNoteText

Seems to be working. I thought I had to have a text box on the form to do
that. I didn't know I could reference the table field with the "Me.Field"
syntax without a textbox on the form - thanks for the tip!

[Trevor Best]

deko wrote:

that. I didn't know I could reference the table field with the "Me.Field"
syntax without a textbox on the form - thanks for the tip!

Ooooh don't go down the route of the dot, imagine a field called "Name",
what do think me.name would return?

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[Trevor Best]

David W. Fenton wrote:

Using a rich-text control of any kind is asking for trouble.

Why?

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[deko]

> Ooooh don't go down the route of the dot, imagine a field called "Name",

what do think me.name would return?

10-4 - dot notation should be dropped like a piece of used dental floss...
still, the cool thing is being able to populate a table field *without a
corresponding bound control* on the form - as in Me!memNote = Me!txtNote
(where Me!txtNote is an unbound text box and Me!memNote is a memo field in
the table)

As for all those controls hiding behind the "More Controls" button on the
toolbox toolbar (e.g. the RTF textbox control) - Are these used often? How
are they different from the other "regular" controls? I couldn't get that
Microsoft Rich Textbox Control to work. I noticed some chart controls in
the list that looked interesting. Do you know of any code samples that
demonstrate how to use these?

[Trevor Best]

deko wrote:

As for all those controls hiding behind the "More Controls" button on the
toolbox toolbar (e.g. the RTF textbox control) - Are these used often? How
are they different from the other "regular" controls? I couldn't get that
Microsoft Rich Textbox Control to work. I noticed some chart controls in
the list that looked interesting. Do you know of any code samples that
demonstrate how to use these?

They're different in that they're not actually part of Access, just
controls that are registered. Code samples, if there any would be
numerous and diverse, each control will have it's own properties and
methods. Apart from .top, .left, etc you get with all controls the RTF
control has a .Text and .TextRTF properties, use the former for a
similar function to a textbox without the 64K limit.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[David W. Fenton]

"deko" <de**@hotmail.c om> wrote in
news:kd******** **********@news svr25.news.prod igy.com:

And I wouldn't do the updates in SQL, unless they were stored in
a different table. All I'd do is include the memo field in the
form's recordsource, and just not bind the control. For updates,
something like this in the control's AfterUpdate event:

Me!MyMemo = Me!fldMyMemo
Is this what you mean:

In the table I have "memNoteTex t", and in the form I have an
unbound field "txtNoteTex t" I have an OK button with:

Me!memNoteText = Me.txtNoteText

That puts the contents of the textbox into the table.

And Form_Open has this:

Me!txtNoteText = Me!memNoteText

Which populates the textbox with the data in memNoteText

I'd populate Me!txtNoteText in form's OnCurrent event, and save the
changes in the AfterUpdate even of the unbound control.
Seems to be working. I thought I had to have a text box on the
form to do that. I didn't know I could reference the table field
with the "Me.Field" syntax without a textbox on the form - thanks
for the tip!

Well, I'd use Me!Field, but, yes, of course it works.

It has always worked, from the earliest days of Access.

That is, the form has a Controls collection and a Fields collection.
The latter is all the fields in the underlying recordset, not all of
which need appear in controls on the form.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Trevor Best <nospam@localho st> wrote in
news:40******** *************** @auth.uk.news.e asynet.net:

David W. Fenton wrote:

Using a rich-text control of any kind is asking for trouble.

Why?

Because of DLL Hell.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[deko]

> That is, the form has a Controls collection and a Fields collection.

The latter is all the fields in the underlying recordset, not all of
which need appear in controls on the form.

Hmmm... I guess that means I don't need to use a hidden (i.e. visible = no)
text box on a form to hold stuff like ID numbers. I was in the habit of
putting hidden text boxes on the form whenever I needed to do stuff like
this:

Private Sub Categories_Clic k()
Dim intCatId As Integer
intCatId = Me!Cat_ID '<<== Cat_ID text box on form
CallSomeFunctio n (intCatId)
End Sub

I've been experimenting - even the Link Child/Link Master properties for Sub
Forms/Main Forms seems to work without the hidden text box - Is there ever a
time when I need to use a hidden text box on a form to hold an ID number?

[Trevor Best]

deko wrote:

I've been experimenting - even the Link Child/Link Master properties for Sub
Forms/Main Forms seems to work without the hidden text box - Is there ever a
time when I need to use a hidden text box on a form to hold an ID number?

The LinkChild will work only with columns from the recordsource and not
textboxes AFAIK.

I usually have a hidden control on each form called txtPK, which is
bound to the primary key field. It just makes it easier that I know I
can reference txtPK rather than have to remember what the primary key is
actually called.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[Lyle Fairfield]

"deko" <de**@hotmail.c om> wrote in
news:KE******** *********@newss vr27.news.prodi gy.com:

That is, the form has a Controls collection and a Fields collection.
The latter is all the fields in the underlying recordset, not all of
which need appear in controls on the form.

Hmmm... I guess that means I don't need to use a hidden (i.e. visible =
no) text box on a form to hold stuff like ID numbers. I was in the
habit of putting hidden text boxes on the form whenever I needed to do
stuff like this:

The recordsource's fields are saved when the form is saved. If you change the
form's recordsource at run time you may very well have to use a hidden
control to interact with a new field. If the field is vital I always use a
hidden field to ensure that, if in some new mainfestation of the form I
change the recordsource, the reference to the field is always there.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)