slowly converting all old 97 databases to 2003...

[sparks]

We are slowly converting all of our older access 97 databases to 2003.
One of them that has been running fine for 3 + years and has over 2000
records in is giving me a problem. It converted fine and seems to be
ok until LOL

I add a new person and enter data....when I try to close the form it
says this record has been modified by another user and can not be
saved...then the save==copy to clipboard===drop changes box comes up.

anyone have any idea what would get corrupted like this when I
converted?

I checked the relationships and they are there and the same as before
as far as I can tell.

thanks for any ideas
Jerry

[sparks]

Just found one thing and wanted to ask.
Some of the number fields are defined as bytes....
http://support.microsoft.com/kb/280730
says that Access is creating Null bit fields, which causes a type
mismatch.
and it mentions int and float fields.

I am changing them all to integer and see if this might be it.


On Thu, 12 Oct 2006 14:02:00 GMT, sparks <js******@swbell.netwrote:

>We are slowly converting all of our older access 97 databases to 2003.
One of them that has been running fine for 3 + years and has over 2000
records in is giving me a problem. It converted fine and seems to be
ok until LOL

I add a new person and enter data....when I try to close the form it
says this record has been modified by another user and can not be
saved...then the save==copy to clipboard===drop changes box comes up.

anyone have any idea what would get corrupted like this when I
converted?

I checked the relationships and they are there and the same as before
as far as I can tell.

thanks for any ideas
Jerry

[Albert D. Kallal]

You mean to say that in your first post you fogot to mentoin that you are
using sql server?

You don't think that was a important detal? You should also menton what
version of sql server.

When using sql server (or oracle, or sybase, or MySql), ms-access has to
"figure" out if hte reocrd has been changed. If you don't have a primary
key, and a timestamp field expsoed, then it makes things VERY difficlt for
ms-access to determine if the record has changed. In fact, when using
oracle, or (in your case, sql server), then I find sub-forms OFTEN do not
function correct untill you include a timestamp field in the sub-form.

Of course, if you not using sql server, then none of above advice about
timestamp fields applies, and you don't need them.

If you are not actually using sql server, then that kb aritcle (if you read
it) does not apply.

It would be *quite* surprising that just convering from a97 to a2003 would
cause that problem to rear its ugly head. 99% of the time (when not talking
about sql server), that write conflict probem is due to you running code, or
launch antoher form that mdoifyes the CURRENT data on the CURRENT form, and
that form has PENDING data. (that means, you run some code, or do somting in
code that mdoifyes the CURRENT reocd, but you do so with a reocordset, or
udpate query, or even luanch antoher form. (however, this problem would
normally NOT change due to upgradin to a2003).

If you ahve any code that creaes a new reocrdset, runs a udpate query, or
launchaas antoher form, you need to force a disk write of the current forms
data BEFORE you run this code.

go:

if me.dirty = True then
me.dirty = false
end if

....... your update code goes here (or code to launch other form..or
whatever)

I would double double double check your original code, and if we are NOT
talking about sql server, then the above is likely your problem (but,
strange that it did not exist in a97). and, if in fact you are talking about
sql server, then make sure a pk + timestamp field is exposed.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[sparks]

No I didn't mean sql I was just asking if something like that could
cause a 2003 conversion to mess up.
I can't find out what it is.
I did notice that its one 2 sub forms that causes the problem
and only if you change something on an existing value on one of
these 2 forms.
New records added do not have a problem.

I removed all the code behind these 2 subforms and its still doing it.
So I assume that its something else I am not seeing.

On Thu, 12 Oct 2006 14:37:56 GMT, "Albert D. Kallal"
<Pl*******************@msn.comwrote:

>You mean to say that in your first post you fogot to mentoin that you are
using sql server?

You don't think that was a important detal? You should also menton what
version of sql server.

When using sql server (or oracle, or sybase, or MySql), ms-access has to
"figure" out if hte reocrd has been changed. If you don't have a primary
key, and a timestamp field expsoed, then it makes things VERY difficlt for
ms-access to determine if the record has changed. In fact, when using
oracle, or (in your case, sql server), then I find sub-forms OFTEN do not
function correct untill you include a timestamp field in the sub-form.

Of course, if you not using sql server, then none of above advice about
timestamp fields applies, and you don't need them.

If you are not actually using sql server, then that kb aritcle (if you read
it) does not apply.

It would be *quite* surprising that just convering from a97 to a2003 would
cause that problem to rear its ugly head. 99% of the time (when not talking
about sql server), that write conflict probem is due to you running code, or
launch antoher form that mdoifyes the CURRENT data on the CURRENT form, and
that form has PENDING data. (that means, you run some code, or do somting in
code that mdoifyes the CURRENT reocd, but you do so with a reocordset, or
udpate query, or even luanch antoher form. (however, this problem would
normally NOT change due to upgradin to a2003).

If you ahve any code that creaes a new reocrdset, runs a udpate query, or
launchaas antoher form, you need to force a disk write of the current forms
data BEFORE you run this code.

go:

if me.dirty = True then
me.dirty = false
end if

...... your update code goes here (or code to launch other form..or
whatever)

I would double double double check your original code, and if we are NOT
talking about sql server, then the above is likely your problem (but,
strange that it did not exist in a97). and, if in fact you are talking about
sql server, then make sure a pk + timestamp field is exposed.

[Albert D. Kallal]

I removed all the code behind these 2 subforms and its still doing it.

So I assume that its something else I am not seeing.

If you don't have a pending writes problem (eg: in that form, if it runs
some update code, put in the code to force a disk write

either the if me.dirty.....

or use

me.refresh

(both will simply force a disk write)

So, if you have some update code running in that form, try the above.

This likely centers around some code you have that updates some values.
another possible fix is to put in a doevents to flush the event code stacked
up for running. In converting a very large a97 project to a2003, one form
did have a *slight* difference behaviour in the threading/execution of code.
A doevents fixed this.

So, I don't know why removing the sub-forms would fix anything, nor did I
suggest as such. (unless those sub-forms are bound to the SAME table as the
main form).

The areas to check here is any code that runs something that updates data in
the underlying table. If ANY code does this, the you need to force a disk
write BEFORE YOU run this type of code.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[sparks]

Well after about 4 hrs of trying everything I think I have fixed it.
on one of the forms I got the error...I did a copy to clipboard.
It was listing fields on another form.
I went and looked at that form and some of the code
did something like this
based on selections they did

If Q2C5Much Then Q2C5Much = Null
I changed it to

If Not IsNull(Q2C5Much) Then Q2C5Much.value = Q2C5Much.defaultvalue

this seems to have fixed it.
I assume I could have done this
If Not IsNull(Q2C5Much) Then Q2C5Much = Null

but I didn't care to try at that point....after 6 hrs of pulling my
hair out....I was tired and I think I have it licked...well only time
will tell...I can put in records fine and so far I have not seen
another fail to update on anything I have changed but give it time :)

there must be 2000 lines of code behind each form....WELL it seemed
like that much LOL

thanks big time for your help.

Jerry

On Fri, 13 Oct 2006 18:16:48 GMT, "Albert D. Kallal"
<Pl*******************@msn.comwrote: