"#Deleted" in all my fields!!!

[Bonnie]

Hi there! Using A02 on XP. Have mdb file in multiuser
environment. Have a mainform showing all contracts and
their info. Have a subform to key in records for
distributions from the contracts. Once in a while I am
told that all the fields for a certain subform record show
this: #Deleted

The best 'what I was doing when it happened' I have gotten
is this: a tech created 2 distribution subform records,
then with the navigation arrow moved up 2 contract records
on the main form, created 1 new distribution subform
record, remembered something was left from one of the 2
distribution subform records created initially and when he
moved back down 2 contract records and then from the first
subform record to the second, all the fields showed
#Deleted in them. He called someone who said, "Bonnie will
fix that but for now, just add another new record and key
in that data again. The tech did add a 3rd subform record
and it seems fine. Now the middle one with #Deleted in
every field will not let me delete it on the form, in the
table or even in the backend database holding all the
linked tables. In the past I have had to create a new
table and append all but the bad record. However, it is
not a fun process.

Any help or advice on a better way to handle this would be
VERY much appreciated! Thanks everyone!

 

['69 Camaro]

Hi, Bonnie.

Once in a while I am
told that all the fields for a certain subform record show
this: #Deleted

Does the subform's recordsource include a memo field where a text box on the
subform is bound to this field? This is a known cause of corruption.

In the past I have had to create a new
table and append all but the bad record.

Sometimes starting from scratch helps. Create a new database file and
create a new table with the same data structure as your problem table.
Don't copy/paste or import from the old table structure, which may have some
corruption in it. Next, import all of the objects one group at a time
(tables first, then queries, then forms, et cetera), but don't import the
problem table. Next, create a query which appends the records, except for
the corrupt record, from the problem table in the old database into the new
table that you created. Then compile the VBA code and compact/repair the
new database.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Bonnie]

Hey Gunny,

Thanks bunches for the quick response. I don't use memo
fields at all. I do have a number of list or combo boxes
on my subform. All my comments boxes are text boxes set
for max characters (255).

Maybe it is in Access' save on close process that this
error is occuring?

I'll check back for any further info you may wish to share.

Thanks again! B

 

[Bonnie]

Hello again Gunny,

I have to apologize, I went to close my table after
replying to you moments ago and guess what??? I saw one
little memo field way down in the table. I'll create a
text field and move the data in. Perhaps that IS the
reason for these problems. You mentioned a 'known cause of
corruption'...do you know where I could read about that?

Thanks again!

Bonnie

 

['69 Camaro]

Hi, Bonnie.

You mentioned a 'known cause of
corruption'...do you know where I could read about that?

Sure.

http://makeashorterlink.com/?E1256412A

http://makeashorterlink.com/?F2842212A

http://makeashorterlink.com/?H2152512A

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Marshall Barton]

I have to apologize, I went to close my table after
replying to you moments ago and guess what??? I saw one
little memo field way down in the table. I'll create a
text field and move the data in. Perhaps that IS the
reason for these problems. You mentioned a 'known cause of
corruption'...do you know where I could read about that?

The one situation I ran into was over 50% reproducible
(A97). If two users were editing the same record and the
record had a memo field, one of them would get the "another
user has changed ..." message, then no matter what they did
the memo field would become corrupted (sometimes the whole
record would become inaccessable).

It turned out that I had a bug in my code. When the users
were navigating between records, I inadvertantly dirtied the
record. This meant that if two people were working in the
data base, they only had to scroll through the same set of
records to run into the problem. Yech :-(

An alternative design that appears to be very safe is to use
a separate table (in a 1-1 relationship) just for the memo
fields. Then, the form's Current event had a line of code
to retrieve the memo field into an unbound text box. The
form's Before or After Update event can check if the memo
was edited and write it back to its table.

 

[Bonnie]

Hey Marsh!!! Happy Holidays to you and yours!!!

Thanks so much for the info on that memo field corruption.
I went in this morning and changed the few memo fields I
found to text fields (none of my comment fields need more
than 255 characters so length isn't a factor).

I'll see if we stop with the #Deleted problem now. It was
only happening every 6 months or so. If I need a memo
field in the future, I'll use a separate table as you
suggest.

Always a pleasure to see your name on a post. You explain
it right where I can understand it. Thanks for helping!
Luv U Guys!!!