Though I've implemented anti corruption measures I will read up
again on the corruption pages. Maybe I have to separate the memo
fields in a separate table. Ouch, that will take a lot design
changed.
The first thing to do is just to change memo editing to use unbound
controls. You leave the memo field in the form's recordsource, but
don't bind it to a textbox. Instead, in the OnCurrent event, you
assign the memo field(s)' value(s) to the textbox(es). In the
AfterUpdate event of each unbound textbox, you write the value of
the unbound textbox back to the field in the form's recordsource.
There are a few downsides to that (memos are no longer searchable
with the built-in FIND function, for instance), but they are minor
in comparison to the dangers of memo corruption.
If you want bullet-proof memo fields, well, that's not entirely
possible because there's always the possibility even with unbound
editing of something happening. However, moving the memo fields to a
separate table certainly protects the non-memo data, as a corruption
of a memo field will never lead to a loss of any non-memo data.
There are two choices:
1. move multiple memos to a single 1:1 record in your memo side
table, OR
2. normalize 1:N and have one row per memo, with a TYPE field that
indicates which kind of memo it is.
It might be tempting to implement #1 with a join in the form's
recordsource, but then you have to use a left join (otherwise
records with no memos yet won't show up in your recordset). This can
lead to interesting UI issues.
I think it's better, even with option 1, to use subforms for the
memos. The problem with #1 is that you might not want all your memos
in one place on your form, and you really don't want multiple
subforms based on the same table, as you can end up with write
conflicts (which are problematic for memo fields because of the
amount of data involved). Sure, you can work around it, but I just
think it's better to go the normalized route, as it makes things
much simpler.
All that said, I've never done either of these. I just use unbound
memo fields in apps where there's danger.
