Solved

ADO AddNew on an empty recordset with Access 2002 db

Posted on 2004-04-06
9
919 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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

777 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