Data Shifting Between Records

[Neil]

Has anyone ever had experience with data moving from one record to another?
I have a form in which I'm using the FMS rich text control ("Total Access
Memo"). Several times when there has been an error (such as the user unable
to print, or other error), the user finds that data in one or two of the
rich text fields are actually data that had previously been saved in another
record. This has happened two or three times in the past few months. And,
while not a frequent problem, it is especially troubling when it happens.

I've checked the FMS web site, and haven't seen anything about this. And
their technical support is pretty rudimentary. Not much help there. So I was
wondering if anyone has ever experienced anything like this, either with or
without a rich text control.

I'm using an Access 2000 MDB with a SQL Server 7 back end, with ODBC linked
tables.

Thanks for any assistance!

Neil

 

[Jeff Boyce]

Neil

I'm curious how the users might be able to enter data "in the wrong record".
Is there a chance they are working directly in the tables?

Given the frequency with which this happens, are you sure you want to work
out an automated solution? Would it be easier (i.e., less work) just to
clean it up periodically? Or to prevent it from happening in the first
place by how your users access the data?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Neil]

Neil

I'm curious how the users might be able to enter data "in the wrong
record".
Is there a chance they are working directly in the tables?

No, they don't enter data in the wrong record. The data already exists in
the record, but it's transferred after the error.

Consider: record 1 has for two fields: A = Pete and B = John.
Record 2 has for two fields: A = Joe and B = Tom

After the error record 1 is A = Joe and B = John
And Record 2 is A = Joe and B = Tom.

In other words, after the error, the data from another record is
transferred into the current record, replacing its data, in one or two
fields.

Given the frequency with which this happens, are you sure you want to work
out an automated solution? Would it be easier (i.e., less work) just to
clean it up periodically? Or to prevent it from happening in the first
place by how your users access the data?

Now that you see more specifically what I'm talking about, you see how it's
a big problem. The user is entering data; an error occurs for whatever
reason; and data that they've already entered (and which they may not be
looking at) is changed. Now, hopefully they'll review all fields again
before leaving the record; but they may not. Or they may have been in the
record for a quick thing in one field, and won't be looking at other fields
that may have already been entered. Plus the fact that these fields are on a
tab control makes the chances of them missing a change like this even
greater.

Neil

 

[Jeff Boyce]

Neil

I haven't run across that issue before.

Have you tried searching on-line?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Neil]

Neil

I haven't run across that issue before.

Have you tried searching on-line?

Yes; couldn't find anything.

 

[Jeff Boyce]

Neil

Let's hope one of the other newsgroup readers has run across this!

You may want to re-post, as folks sometimes don't follow down-thread to see
that an answer was not yet found...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rick Brandt]

Yes; couldn't find anything.

Does your SQL server table have a primary key defined or did you build a
local index for doing updates? What you are describing is exactly what can
happen if you define a local index for doing updates and choose a field or
combination of fields that are actually NOT unique in the table. Updates
end up affecting multiple rows on the server.

 

[Neil]

Does your SQL server table have a primary key defined or did you build a
local index for doing updates? What you are describing is exactly what
can happen if you define a local index for doing updates and choose a
field or combination of fields that are actually NOT unique in the table.
Updates end up affecting multiple rows on the server.

Yes, there's a primary key. This particular form consists of two tables
having a one-to-one relationship, the second table containing primarily the
rich text fields. The primary key of the second table is the foreign key of
first table.

Again, note that when this happens, it's only one or two fields that are
affected, not the entire record. And it only happens in conjunction with
some sort of error.

 

[Neil]

Thanks for your reply, Gordon. But, unfortunately, there is no code that
updates these fields. They're simply bound controls, and are managed by the
control and the ODBC driver.

Furthermore, the fact that the contents of the control are replaced by the
content of a previously saved record in that control indicates that it can't
be user error. These controls hold multiple sentences. For the user to be
able to retype exactly what was in another record, after already typing what
was in there originally, would be extremely unlikely.

I have code that copies the entire content of the record to a separate
history table whenever the form AfterUpdate event is run. Looking at the
history table, I see exactly what the user is talking about. There will be
multiple entries with the correct paragraph of text in the field, and then,
boom!, all of a sudden, out of nowhere, the contents of the field is
replaced, character-for-character, with the contents of another record for
the same field. If the user had done this, they would have to, after typing
the paragraph in the first place, all of a sudden decide to replace it wit
the wrong paragraph of text, and then replicate that other record's text
perfectly. Extremely unlikely.

Since this is the rich textbox control by FMS, and these types of controls
tend to get a little flaky at times, I'm still believing there's something
with the control itself. Since it happens infrequently, and so far only in
conjuction with an error, I see it as a hiccup within the control,
especially since, as noted above, I don't have any code that updates these
controls, and user cause is extremely unlikely.

The fact that no one responding to this thread has ever encountered anything
like this is comforting. But, at the same time, it leaves me with few ideas
as to how to address it. Right now I'm applying the KFC ("keeping fingers
crossed") approach. :-(

Thanks,

Neil

 

[Larry Linson]

Since this is the rich textbox control by FMS, and
these types of controls tend to get a little flaky at
times,

With all due respect, since this is an ActiveX Control by FMS, why are you
not pursuing the issue with FMS? My experience has been that (if you are
following their license provisions) they are good about providing support
and assistance.

Larry Linson
Microsoft Office Access MVP

 

[Neil]

With all due respect, since this is an ActiveX Control by FMS, why are you
not pursuing the issue with FMS? My experience has been that (if you are
following their license provisions) they are good about providing support
and assistance.

From first post in this thread:

"I've checked the FMS web site, and haven't seen anything about this. And
their technical support is pretty rudimentary. Not much help there. "

Their direct response to me was that it's something in my code. OK. If you
read this thread you'll see that that does not seem to be the case.

 

[Don S]

Neil,
Please see the thread "bug in form.recordset."
Also, please check out
http://groups.google.com/group/micr...47b59639843/3b707733ee8c4b1a#3b707733ee8c4b1a

Is your database replicated?

I am experiencing the same thing as described in the link. I am using
Access 2003, linked ODBC tables to a SQL 2005 server that is replicated.
When I make an entry in a subform, the database records the entry, but
Access displays data from a different record. Refreshing the record will
display the correct data in the subform. The problem did not show up until
I replicated this database, and stops if I tear down the replication and
remove the rowguid columns.

It seems clear that there is a bug in MS Access so I would like to hear from
the MVP's about the discussion of the bug mentioned in the link.

Thank you,
Don S.

 

[bobh]

Has anyone ever had experience with data moving from one record to another?
I have a form in which I'm using the FMS rich text control ("Total Access
Memo"). Several times when there has been an error (such as the user unable
to print, or other error), the user finds that data in one or two of the
rich text fields are actually data that had previously been saved in another
record. This has happened two or three times in the past few months. And,
while not a frequent problem, it is especially troubling when it happens.

I've checked the FMS web site, and haven't seen anything about this. And
their technical support is pretty rudimentary. Not much help there. So I was
wondering if anyone has ever experienced anything like this, either with or
without a rich text control.

I'm using an Access 2000 MDB with a SQL Server 7 back end, with ODBC linked
tables.

Thanks for any assistance!

Neil

Yes, back about 1 1/2 years ago I had an Access97 app that had a memo
field bound on a form that was used to enter updates/comments. The app
ran for almost 3 years and then the first occurance of the entire memo
field of one record was over written with the contents of another memo
field happened. This happened maybe 6 times over the course of the
next four months. This app was backup several times a day so I was
always able to get the memo field restored to it's correct state but,
I was never able to explain it or find a definate cause. I do however,
believe it was something that the user did, ie; a key combination or
sequence or something the user did that my vba coding did not cover.
That group went away and that app was no longer used so the issue went
away and I never got back to it. Good luck with your issue but I don't
think it's FMS, it's either an Access bug(with recordset or bookmark)
or the Users doing something not covered in code.
bobh.

 

[Jeff Boyce]

Good clue!

If the users inadvertently press <Ctrl>-<'>, Access copies the contents of
the previous record's field into the field with the focus.

If you are saying that this ALWAYS happens from an "older" record into a
"new" record, this might be one explanation...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Has anyone ever had experience with data moving from one record to
another?
I have a form in which I'm using the FMS rich text control ("Total Access
Memo"). Several times when there has been an error (such as the user
unable
to print, or other error), the user finds that data in one or two of the
rich text fields are actually data that had previously been saved in
another
record. This has happened two or three times in the past few months. And,
while not a frequent problem, it is especially troubling when it happens.

I've checked the FMS web site, and haven't seen anything about this. And
their technical support is pretty rudimentary. Not much help there. So I
was
wondering if anyone has ever experienced anything like this, either with
or
without a rich text control.

I'm using an Access 2000 MDB with a SQL Server 7 back end, with ODBC
linked
tables.

Thanks for any assistance!

Neil

Yes, back about 1 1/2 years ago I had an Access97 app that had a memo
field bound on a form that was used to enter updates/comments. The app
ran for almost 3 years and then the first occurance of the entire memo
field of one record was over written with the contents of another memo
field happened. This happened maybe 6 times over the course of the
next four months. This app was backup several times a day so I was
always able to get the memo field restored to it's correct state but,
I was never able to explain it or find a definate cause. I do however,
believe it was something that the user did, ie; a key combination or
sequence or something the user did that my vba coding did not cover.
That group went away and that app was no longer used so the issue went
away and I never got back to it. Good luck with your issue but I don't
think it's FMS, it's either an Access bug(with recordset or bookmark)
or the Users doing something not covered in code.
bobh.

 

[Neil]

Neil,
Please see the thread "bug in form.recordset."
Also, please check out
http://groups.google.com/group/micr...47b59639843/3b707733ee8c4b1a#3b707733ee8c4b1a

Is your database replicated?

I am experiencing the same thing as described in the link. I am using
Access 2003, linked ODBC tables to a SQL 2005 server that is replicated.
When I make an entry in a subform, the database records the entry, but
Access displays data from a different record. Refreshing the record will
display the correct data in the subform. The problem did not show up
until I replicated this database, and stops if I tear down the replication
and remove the rowguid columns.

It seems clear that there is a bug in MS Access so I would like to hear
from the MVP's about the discussion of the bug mentioned in the link.

Thank you,
Don S.

Thanks for the input, Don. No, my db isn't replicated. And, in my case, it's
not getting the wrong record in general; just in one or two fields. But,
just as the bugginess is within Access in your case, I believe it's within
the control or within Access' use of ActiveX controls in my case.

My purpose in posting here was to hopefully find a workaround, if people had
come across it in the past. Doesn't sound like a lot of people have, though.

Neil

 

[Neil]

Has anyone ever had experience with data moving from one record to
another?
I have a form in which I'm using the FMS rich text control ("Total Access
Memo"). Several times when there has been an error (such as the user
unable
to print, or other error), the user finds that data in one or two of the
rich text fields are actually data that had previously been saved in
another
record. This has happened two or three times in the past few months. And,
while not a frequent problem, it is especially troubling when it happens.

I've checked the FMS web site, and haven't seen anything about this. And
their technical support is pretty rudimentary. Not much help there. So I
was
wondering if anyone has ever experienced anything like this, either with
or
without a rich text control.

I'm using an Access 2000 MDB with a SQL Server 7 back end, with ODBC
linked
tables.

Thanks for any assistance!

Neil

Yes, back about 1 1/2 years ago I had an Access97 app that had a memo
field bound on a form that was used to enter updates/comments. The app
ran for almost 3 years and then the first occurance of the entire memo
field of one record was over written with the contents of another memo
field happened. This happened maybe 6 times over the course of the
next four months. This app was backup several times a day so I was
always able to get the memo field restored to it's correct state but,
I was never able to explain it or find a definate cause. I do however,
believe it was something that the user did, ie; a key combination or
sequence or something the user did that my vba coding did not cover.
That group went away and that app was no longer used so the issue went
away and I never got back to it. Good luck with your issue but I don't
think it's FMS, it's either an Access bug(with recordset or bookmark)
or the Users doing something not covered in code.
bobh.



Thanks for that. Good to know I'm not the only one experiencing it.

 

[Neil]

Yes, back about 1 1/2 years ago I had an Access97 app that had a memo
field bound on a form that was used to enter updates/comments. The app
ran for almost 3 years and then the first occurance of the entire memo
field of one record was over written with the contents of another memo
field happened. This happened maybe 6 times over the course of the
next four months. This app was backup several times a day so I was
always able to get the memo field restored to it's correct state but,
I was never able to explain it or find a definate cause. I do however,
believe it was something that the user did, ie; a key combination or
sequence or something the user did that my vba coding did not cover.
That group went away and that app was no longer used so the issue went
away and I never got back to it. Good luck with your issue but I don't
think it's FMS, it's either an Access bug(with recordset or bookmark)
or the Users doing something not covered in code.
bobh.

Good clue!

If the users inadvertently press <Ctrl>-<'>, Access copies the contents of
the previous record's field into the field with the focus.

If you are saying that this ALWAYS happens from an "older" record into a
"new" record, this might be one explanation...

Regards

Jeff Boyce
Microsoft Office/Access MVP

That's a great idea, Jeff. I got really excited when I read this (not as
excited as Chris Matthews listening to a Barak Obama speech; but excited,
nonetheless!). It makes perfect sense, especially since the data always
seems to be from the record they were just working on before the current
one. One small problem, though: with the form this happens in, the records
are in descending order, so the older one comes *after* the current one.

To test it, I created a record, put a value in the field, and the I created
another record (there's a New button for creating records, which creates
them in the table and requeries the form). I then pressed Ctrl+' in the
second record. But it didn't copy the other text, since it was after it,
just as I had suspected.

So that doesn't seem to be it. Any other ideas?

Sadly and solemnly disappointed (not as much as Hillary Clinton after seeing
her poll numbers drop; but disappointed nonetheless),

Neil

 

[Jeff Boyce]

Neil

The issue of which order the records SHOW in probably doesn't matter. You
can sort a recordset in many different ways.

Sorry, fresh out, but maybe someone else in here has seen this.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Neil]

Yeah, I'll have to check with the users to see if they resorted the records
before the problem happened. Usually they don't, so it's unlikely. But I'll
check.

Thanks for your help!

 

[Neil]

I previously posted about data shifting between records in my Access 2000
MDB with a SQL Server 7 back end, using ODBC linked tables. Every once in a
while, data from one record mysteriously appears in another record. This
incident happened again, this time adding a new wrinkle to the situation.

There are two tables -- TableA and TableB -- which have a one-to-one
relationship with each other, joined on TableA's autonumber primary key
field to TableB's long int primary key field.

And there are two forms that are used by the users. Form1 is bound to TableA
alone. Form2 is bound to TableA joined with TableB.

TableB is the table that has the problem with data shifting. However, in the
most recent episode of the data shifting, the record in question was only
edited using Form1 (which is only bound to TableA), and not Form2 (which is
bound to both TableA and TableB). Thus, it would not have been possible for
the data to shift through user intervention, or even though anything within
the form, but only through some other mechanism.

A record is created by the user clicking a New button and completing a few
fields. Code in the back end then creates the TableA record with the
user-provided data, getting the new autonumber value. It then creates a
sister record in TableB, using the new autonumber value as the PK for the
TableB record, and completing two fields in TableB that are required, based
on user-entered data.

In this case, as noted, after the record was created, according to the
history logs, the user only used Form1, which only accesses TableA. Yet
somehow the two fields that were completed by default when the TableB record
was created in the back end were changed to contain data from a different
record. Since TableB was never accessed by the user of either record, I
don't see how that could be possible except through some glitch in the back
end or ODBC driver.

TableB It contains 20 memo type fields ("text" type in SQL Server) that are
used to store RTF data, along with about 30 or so other fields. It could be
that the large number of memo fields is creating a problem?

Any thoughts or ideas would be appreciated.

Thanks!

Neil