?
Solved

ADO AddNew on an empty recordset with Access 2002 db

Posted on 2004-04-06
9
Medium Priority
?
930 Views
Last Modified: 2007-12-19
Can anyone shed any light on this error for me. I have serached MS, google etc etc but can find no info.

runtime error -2147217836 (80040e54)
Number of rows with pending changes exceeded the limit

It happens on the first addnew and I have fiddled with various formats (not addnew with parms) but everything else. Its a pain and I'd be grateful for any help!

Code see below:

With rsSecuritiesDB
        If Not .EOF Then
            .MoveFirst
            .Find "SecurityID='" & DataValue$(3) & "'"
        End If
         If .EOF Then 'NOT found
         'Add a share
            .AddNew
            No_Shares = No_Shares + 1
            If No_Shares >= Sha_Max Then Call Check_Sha_Max
            !PriceDate = Format$(Now, "dd/mm/yy") 'DataValue$(1) 'Date
            !Description = DataValue$(2) 'Description
            !LatestPrice = Trim$(DataValue$(4)) & Space$(15 - Len trim$(DataValue$(4))))
            !SecurityID = DataValue$(3)  'Symbol
            '!Seq = DataValue$(5)   'Old ID
            '!Type = "S"
            .Update
        Else
            !LatestPrice = Val(DataValue$(4))
            !PriceDate = Format$(Now, "dd/mm/yy")
            .Update
        End If
        End With
0
Comment
Question by:henrymarriott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 18

Expert Comment

by:Data-Man
ID: 10768755
I have found that using recordsets with the lockbatchoptimistic is a little easier.

Make all your changes and then issue the batchupdate method of the recordset.

Also, in ADO the .Update isn't required...at least I think isn't not.

how many records are you trying to update?

Mike
0
 

Author Comment

by:henrymarriott
ID: 10769572
Thanks for that , Mike. I will give it a go. I have tried various conbinations and you are quite right - update is not necessary if one is using AddNew - the next AddNew triggers the update etc. The stupid thing about this is that it is happening on the first record that it tries to update which makes me siuspect that the err msg is probably a red herring. Its 10pm now so I will try the batch update tomorrow! Thanks for your help. Will let u know what happens.
0
 

Author Comment

by:henrymarriott
ID: 10813734
Mike sorry to take so long to resolve this one. In fact I managed to fix the problem by putting in a .requery after the IF EOF (2nd one) and that seemed to solve it. The batch lock was obviously wrong though with the simple update but that did not fix the problem. I can only guess why it worked but if I added a record to the db via Access and then ran the vb code it worked. So I assumed it was something to do with an empty recordset hitting EOF or being closed - I must admit that I am finding ADO a great deal more pernickety than it sounds - good old Microsoft!!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 18

Expert Comment

by:Data-Man
ID: 10816610
I'm glad you got the problem solved. Are there any remaining issues?

Mike
0
 

Author Comment

by:henrymarriott
ID: 10821863
Plenty!! but I am getting thru them bit by bit. They are mainly issues of converting the client to a new way of thinking though! If you feel like working a bit harder for your points there is one small issue I do have with bound controls. If you can't face it just say so!

With a bound datalist control and a number of text boxes on a form   - say datalist1 and text1 to text5 - I should be able to bind the contents of the textboxes to the name selected in datalist1. I have copious examples of where the names in DataList1 come from one table and the values in the text boxes from another by setting boundcolumn etc. I cannot get it to work if ALL DATA comes from the same table!! Do I still need TWO ado data controls? Does my boundcolumn have to be a key field - presumably it does? Does DataList bind in exactly the same was as datacombo? I am trying to use no code at all and I reckon that by clicking on a name in datalist all the text boxes should update - am I wrong?
Settings are
Adodc1 works fine and points to a MEMBERS table
DataList1 displays the list of members names - no problem
DL settings are:
BoundCol = MemberID (Key)
Listfield = name
Datafield = name
Datasource and rowsource = adodc1
Textboxes initially display the details for the first member in the list when form is loaded. This never changes when I select a new name.
Settings are:
Datasource = adodc1
Datafield = name or initials or addr1 etc for each text box

If you can guide me thru that lot you will certainly earn your points!

Regards
henry

 

 
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10835208
Are you using VB against and Access Database?

If so, you should probably close out this one and then repost your question.  You will probably get lots of answers.  I'm a little swamped for the next couple of weeks.  I have twin boys on the way and my time is rather limited.

Thanks,
Mike
0
 

Author Comment

by:henrymarriott
ID: 10860993
OK, Mike, I will do that or I will have another go at solving it myself now that I have had a few days off - very best of luck with the twins - they won't give you much time off!!!

Best wishes
Henry
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10861066
Sounds like a plan.  Don't forget to close out this thread.

Mike
0
 
LVL 2

Accepted Solution

by:
Lunchy earned 0 total points
ID: 10863732
Closed, 250 points refunded.
Lunchy
Friendly Neighbourhood Community Support Admin
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question