473,561 Members | 3,672 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Rst.Update does not update

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.OpenRecordse t("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
Nov 13 '05 #1
15 5646
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.goog le.com... 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.OpenRecordse t("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

Nov 13 '05 #2
Another thing, your statement
Set rs = db.OpenRecordse t("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.goog le.com... 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.OpenRecordse t("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

Nov 13 '05 #3
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.OpenRecordse t("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!
Nov 13 '05 #4
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!
Nov 13 '05 #5
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************ ****@xxxunileve r.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!

Nov 13 '05 #6
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!
Nov 13 '05 #7
Find msjet35.dll in the Windows\System directory. This may be WinNT\System32
or Windows\System3 2 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************ ****@xxxunileve r.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.

Nov 13 '05 #8
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!
Nov 13 '05 #9
Sabine <sa************ ****@xxxunileve r.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.
Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2342
by: John Moore | last post by:
Hi, I normally work with Java but I'm interested in using Python as well, particularly for little tasks like doing some massaging of data in a MySQL database. Below is my first attempt. I'm sure it's inelegantly written, but my main concern is that the UPDATE sql doesn't actually work, and I can't understand why. No error is returned, it's...
3
7024
by: Bill Clark | last post by:
I have about 20,000 records pulled from Excel that I need to update. What I need to do is run an update query that bascially says: If a field is null, update it with the previous record value of that same field. In some instances, it will have to go back a few records before it finds a value that is not null. Can this be done? Thanks ...
1
1598
by: Scott Emick | last post by:
I have the following datatables which are related: Transaction TransactionId-Sequence Orders OrderDetails when I process a daTransaction.update(dtTransaction) daTransactionIdSequence.update(dtTransactionIdSequence)
5
20822
by: dani kotlar | last post by:
I run the following code: this.carsBindingSource.EndEdit(); this.carsTableAdapter.Update( this.vehiclesDataSet.Cars ); in order to save changes in the dataset to the database, but the changes don't take place in the database. Am I missing something?
4
9341
by: kdpo | last post by:
Could someone tell where I can find out if it's true that during UPDFATE SQL Serve deletes data from table, and then inserts new one. Thanks -A
6
4094
JKing
by: JKing | last post by:
I'm using an update query to update a single field in a table. Table1 is part of my normalized table structure. Table2 is used solely as an import table for raw data which I sort and summarize through queries and VBA routines. Table I'm updating looks like this Table1 Code Name 1 Gym 2 Math 3 English Table I'm...
1
1203
by: muhremehr | last post by:
hi all can someone one help me why this code does nothing to my database ?!! Sub Main() Dim da As New AdventureWorks_DataDataSetTableAdapters.EmployeeTableAdapter() Dim employees As AdventureWorks_DataDataSet.EmployeeDataTable = _ da.GetData()
3
1082
by: mercea | last post by:
hi all, i have created a gridview with the select,delete and insert commands working properly. but the update command does not work. when i edit a column and click the update button, it generates no errors but does not save the changes. it just brings back the original values. i dont know wats missing. can anyone help me? this is part of my...
3
4293
by: asfmendes | last post by:
Hello everyone! I am creating a C# application for tablet pcs and I want to animate some strokes. I am moving the strokes and, after each move, I want to reflect that move in the screen. However, if I use the refresh(), I get a lot of flickering (since the whole screen is redrawn). I only need to redraw, at each step, a certain rectangle of the...
0
7647
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7570
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7859
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8088
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7618
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6210
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5187
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3617
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1181
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.