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?

[Lyle Fairfield]

Lyle Fairfield <Mi************ @Invalid.Com> should have written in
news:Xn******** ***********@130 .133.1.4:

If the field is vital I
always use a hidden control

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

[David W. Fenton]

"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:

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?

Heavens yes!

However, there's one circumstance where you'll want to use a hidden
field -- if you're referring to a field in the parent from from a
subform. If you don't use the hidden control, in A2K and higher, you
may end up with the dreaded Error 2486 (Google on it, and my email
address with the ".invalid" and you'll see the hair pull I went
through to diagnose it).

That is, if in a subform, you refer to:

Me.Parent!Field NameFromRecordS ource

you may run into the error, which is actually due to a problem in
MSCVRT.DLL.

The problem does not occur if the reference is to a control with a
control source pointing you your desired field.

That is the only situation in which I ever use hidden fields.

Mostly.

I sometimes have ID fields on the form that I have visible during
development but hide for distribution.

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

[Dimitri Furman]

On Apr 27 2004, 02:10 pm, Lyle Fairfield <Mi************ @Invalid.Com> wrote
in news:Xn******** ***********@130 .133.1.4:

Lyle Fairfield <Mi************ @Invalid.Com> should have written in
news:Xn******** ***********@130 .133.1.4:

If the field is vital I
always use a hidden control

Haven't seen you post verse before. Sounds like an incantation. Nice.

--
remove a 9 to reply by email

[Bob Quintal]

"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:

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?

How about an unbound parent form with two subforms, selecting a
record in the first sets a hidden control which is the parent
link.for the second form.

[David W. Fenton]

Lyle Fairfield <Mi************ @Invalid.Com> wrote in
news:Xn******** ***********@130 .133.1.4:

If the field is vital I
always use a hidden control

Care to unpack your reasoning, there?

How could one field be "vital" and one could be, what, irrelevant?

If it needs a control on the form, it should have a control on the
form, visible or not.

But if there isn't a need for a control, I see no reason or benefit
from putting on there on the form.

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