Rst.Update does not update

[Sabine Oebbecke]

Hi there,

Acc 97 SR2

I hope there is someone out there able to help me with my weird
problem.

I try to update a field in a table with continuous numbers. My problem
is that the code that I run from a button in a form sometimes works,
sometimes it does not. So, I press the button, and the table is update
properly, then I press the button again and this time no calculation
seems to take place and no figures are added to „MyField". The number
of attempts can vary until the result is no update. Sometimes I can
run the code three times after the other and everything is fine, then
the fourth time the table is not updated, sometimes it works only
once, then pressing the button again and again brings no result.

The code is as follows:

Dim db As dao.Database
Dim rs As dao.Recordset
Dim intCounter As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM MyTable")

intCounter = 0

rs.MoveLast
rs.MoveFirst

Do While Not rs.EOF
rs.Edit
intCounter = intCounter + 1
rs!MyField = intCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Any input is highly appreciated!

Thanks,

Sabine

[Andrew Chanter]

Hi Sabine,

this is a weird one. I cant see anything wrong with your code. One thing
to check though. Maybe it is working but you are not realising it. You
need to ensure that you close Mytable and reopen it after each attempt, to
verify that you are looking at the latest data, otherwise your code my be
working but you are not getting to see the result.

If this isnt the case, try switching the following lines from

rs.Edit
intCounter = intCounter + 1
to
intCounter = intCounter + 1
rs.Edit
Its just a guess but this might work. You could also try recoding this in
ADO.

AJC
"Sabine Oebbecke" <Sa*************@unilever.com> wrote in message
news:5c*************************@posting.google.co m... Hi there,

Acc 97 SR2

I hope there is someone out there able to help me with my weird
problem.

I try to update a field in a table with continuous numbers. My problem
is that the code that I run from a button in a form sometimes works,
sometimes it does not. So, I press the button, and the table is update
properly, then I press the button again and this time no calculation
seems to take place and no figures are added to "MyField". The number
of attempts can vary until the result is no update. Sometimes I can
run the code three times after the other and everything is fine, then
the fourth time the table is not updated, sometimes it works only
once, then pressing the button again and again brings no result.

The code is as follows:

Dim db As dao.Database
Dim rs As dao.Recordset
Dim intCounter As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM MyTable")

intCounter = 0

rs.MoveLast
rs.MoveFirst

Do While Not rs.EOF
rs.Edit
intCounter = intCounter + 1
rs!MyField = intCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Any input is highly appreciated!

Thanks,

Sabine

[Andrew Chanter]

Another thing, your statement

Set rs = db.OpenRecordset("SELECT * FROM MyTable")
doesnt include a sort order. If you dont have a primary key defined this
will open the table in random order.

AJC

"Sabine Oebbecke" <Sa*************@unilever.com> wrote in message
news:5c*************************@posting.google.co m... Hi there,

Acc 97 SR2

I hope there is someone out there able to help me with my weird
problem.

I try to update a field in a table with continuous numbers. My problem
is that the code that I run from a button in a form sometimes works,
sometimes it does not. So, I press the button, and the table is update
properly, then I press the button again and this time no calculation
seems to take place and no figures are added to "MyField". The number
of attempts can vary until the result is no update. Sometimes I can
run the code three times after the other and everything is fine, then
the fourth time the table is not updated, sometimes it works only
once, then pressing the button again and again brings no result.

The code is as follows:

Dim db As dao.Database
Dim rs As dao.Recordset
Dim intCounter As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM MyTable")

intCounter = 0

rs.MoveLast
rs.MoveFirst

Do While Not rs.EOF
rs.Edit
intCounter = intCounter + 1
rs!MyField = intCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Any input is highly appreciated!

Thanks,

Sabine

[Sabine]

Hi Andrew,

Many thanks for your input.

Maybe it is working but you are not realising it.
Well, I have checked "MyTable" by closing and reopening it each time I
pressed the button, and "MyField" is definitely empty at times.

I have also already tried switching the lines to
intCounter = intCounter + 1
rs.edit
but that also has got no effect.

I have now taken the code out of the form and included it into a module,
but the behaviour is still the same ... Sometimes "MyField" is updated,
sometimes it is empty.
try recoding this in ADO
I have to admit I wouldn't know how to do it at the moment ...
your statement
Set rs = db.OpenRecordset("SELECT * FROM MyTable")
doesnt include a sort order. If you dont have a primary
key defined this will open the table in random order.

This is intended. I want the code to update the table from the first
entered record to the last.
Well, looking forward to any other ideas which may help to get my update
problem solved.

Thanks again,

Sabine

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Sabine]

One more hint:

I have also tried with adding a 'MsgBox intCounter' to see whether the
code correctly counts the records, and according to the MsgBox
everything should be fine. It tells me '1', '2', '3', etc., yet
"MyField" is not updated, but empty ...

Rgds,
Sabine

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Wayne Morgan]

The code looks ok. As far as ADO goes, not in Access 97.

Have you done a compact and repair of the file lately? What is the data type
of MyField? Is there an Index on MyField? You say the field is sometimes
left blank. Does it get changed from numbers in the previous run to blanks
when you run the code a second time? Have you tried importing this into a
new mdb file and trying from there? You indicate that you have SR-2 for
Office, but do you also have Jet 3.51 SP3 installed?

--
Wayne Morgan
MS Access MVP
"Sabine" <sa****************@xxxunilever.com> wrote in message
news:42**********@127.0.0.1...

One more hint:

I have also tried with adding a 'MsgBox intCounter' to see whether the
code correctly counts the records, and according to the MsgBox
everything should be fine. It tells me '1', '2', '3', etc., yet
"MyField" is not updated, but empty ...

Rgds,
Sabine

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Sabine]

Hi Wayne,

The code looks ok. As far as ADO goes, not in Access 97. That was what I thought ...
Have you done a compact and repair of the file lately? Yes, today.
What is the data type of MyField? Integer.
Is there an Index on MyField? No.
You say the field is sometimes left blank. Does it get changed from numbers in the previous run to blanks when you run the code a second
time?
Yes, this is exactly what happens.
Have you tried importing this into a new mdb file and trying from there?
Well, I more often import the whole mdb (135 MB) into a new one. Also
did this at the beginning of this week, however, did not restrict this
to the tables, queries, forms connected to this problem. Do you think
it's worth a try ?
You indicate that you have SR-2 for Office, but do you also have Jet

3.51 SP3 installed?
Mmhhh, don't know. If you tell me how to check it, I will have a look.

Thanks!

Sabine

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Wayne Morgan]

Find msjet35.dll in the Windows\System directory. This may be WinNT\System32
or Windows\System32 depending on your version of Windows. If your install is
in a nonstandard directory, you'll have to adjust for that. Right click the
file and choose properties. Click on the version tab. The version should be
3.51.3328.0 or higher.

http://support.microsoft.com/default...b;en-us;172733

Try adding the line

DoEvents

after the Update line. It shouldn't matter, but won't hurt anything either.
I'm just curious if it makes a difference.

--
Wayne Morgan
MS Access MVP
"Sabine" <sa****************@xxxunilever.com> wrote in message
news:42**********@127.0.0.1...

Hi Wayne,

The code looks ok. As far as ADO goes, not in Access 97.

That was what I thought ...

Have you done a compact and repair of the file lately?

Yes, today.

What is the data type of MyField?

Integer.

Is there an Index on MyField?

No.

You say the field is sometimes left blank. Does it get changed from

numbers in the previous run to blanks when you run the code a second
time?
Yes, this is exactly what happens.

Have you tried importing this into a new mdb file and trying from

there?
Well, I more often import the whole mdb (135 MB) into a new one. Also
did this at the beginning of this week, however, did not restrict this
to the tables, queries, forms connected to this problem. Do you think
it's worth a try ?

You indicate that you have SR-2 for Office, but do you also have Jet

3.51 SP3 installed?
Mmhhh, don't know. If you tell me how to check it, I will have a look.

[Sabine]

I have now updated the msjet35.dll to V 3.51.3328.0.
Thanks for the link.

I have then also added the line DoEvents after the rs.Update, but no
success.

B U T ! It looks as if I found a solution : After I pressed the button
to run the code, I need to wait for 4 (!) seconds and when I then press
the button again, everything works fine. If I am too quick, "MyField" is
updated with blanks.

So it's a timing issue (with regard to your suggested DoEvent it looks
as if you already thought about this) and I have now to look for some
code which deactivates the button for a few seconds. You don't have a
snip of code for it by chance :-) ?

Regards,

Sabine

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Quintal]

Sabine <sa****************@xxxunilever.com> wrote in
news:42**********@127.0.0.1:

I have now updated the msjet35.dll to V 3.51.3328.0.
Thanks for the link.

I have then also added the line DoEvents after the rs.Update,
but no success.

B U T ! It looks as if I found a solution : After I pressed
the button to run the code, I need to wait for 4 (!) seconds
and when I then press the button again, everything works fine.
If I am too quick, "MyField" is updated with blanks.

So it's a timing issue (with regard to your suggested DoEvent
it looks as if you already thought about this) and I have now
to look for some code which deactivates the button for a few
seconds. You don't have a snip of code for it by chance :-) ?

Regards,

Sabine

You don't need the rs.movelast and rs.movefirst statements in the
code from your original message. It could be that the code is
executing the loop before the recordset is ready to accept the
values.

Try removing those two lines.
--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

Sabine <sa****************@xxxunilever.com> wrote in
news:42**********@127.0.0.1:

I have now updated the msjet35.dll to V 3.51.3328.0.
Thanks for the link.

I have then also added the line DoEvents after the rs.Update,
but no success.

B U T ! It looks as if I found a solution : After I pressed
the button to run the code, I need to wait for 4 (!) seconds
and when I then press the button again, everything works fine.
If I am too quick, "MyField" is updated with blanks.

So it's a timing issue (with regard to your suggested DoEvent
it looks as if you already thought about this) and I have now
to look for some code which deactivates the button for a few
seconds. You don't have a snip of code for it by chance :-) ?

Regards,

Sabine

Another thought. You show no error handler. if it's resume next,
you will never see any message that might help to diagnose the
problem. comment it out if presesnt.

--
Bob Quintal

PA is y I've altered my email address.

[Wayne Morgan]

Yes, that is what I was thinking with the DoEvents. Since 4 seconds seems to
do it, let's try something else. There is a command to allow Access/Jet to
write all pending items to disk. Try this in place of the DoEvents.

DBEngine.Idle dbRefreshCache

--
Wayne Morgan
MS Access MVP
"Sabine" <sa****************@xxxunilever.com> wrote in message
news:42**********@127.0.0.1...

I have now updated the msjet35.dll to V 3.51.3328.0.
Thanks for the link.

I have then also added the line DoEvents after the rs.Update, but no
success.

B U T ! It looks as if I found a solution : After I pressed the button
to run the code, I need to wait for 4 (!) seconds and when I then press
the button again, everything works fine. If I am too quick, "MyField" is
updated with blanks.

So it's a timing issue (with regard to your suggested DoEvent it looks
as if you already thought about this) and I have now to look for some
code which deactivates the button for a few seconds. You don't have a
snip of code for it by chance :-) ?

Regards,

Sabine

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Sabine]

B i n g o , Wayne, this did the trick - runs perfectly now !

Thanks a lot for your help - much appreciated!

Regards,

Sabine

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Sabine]

Hi Bob,

Thanks for your input. As you can see from my latest reply to Wayne, the
line of code

DBEngine.Idle dbRefreshCache

which he suggested to include solved my problem.

However, I would be interested to know why I "don't need the rs.movelast
and rs.movefirst statements in the code." Because, whereever one looks
for examples, there is at least the "rs.movefirst" mentioned.

Rgds,
Sabine

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Quintal]

Sabine <sa****************@xxxunilever.com> wrote in
news:42**********@127.0.0.1:

Hi Bob,

Thanks for your input. As you can see from my latest reply to
Wayne, the line of code

DBEngine.Idle dbRefreshCache

which he suggested to include solved my problem.

However, I would be interested to know why I "don't need the
rs.movelast and rs.movefirst statements in the code." Because,
whereever one looks for examples, there is at least the
"rs.movefirst" mentioned.

Rgds,
Sabine

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

When using a recordset, the .recordcount property is not
guaranteed valid until the whole recordset has been read. if you
were using a for-next loop to go through the records

example:
for iCtr = 1 to rs.recordcount
!myfield = iCtr
mext

then before starting, a movelast would need doing to ensure that
the loop had the correct end point.

The .movefirst is required if you have done something with the
recordset since you opened it, like a .movelast, a .findfirst or
..findnext.

They are not necessary if you use a while loop instead, on a
newly opened recordset.In examples, I often put extra code to be
on the safe side. the statements in question are probably
remnants of example code. Sometimes good to have, but they slow
execution down if used where not necessary.

--
Bob Quintal

PA is y I've altered my email address.

[Sabine]

Thanks for the clarification, Bob.

I have amended my code accordingly.

Regards,

Sabine

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!