none
Updating recordset -- error 3021 - no current record RRS feed

  • Question

  • Fellow developers,  I am using Access 2010

    I inherited some code for in an existing app - for updating. Normally I write direct sql but current code is doing below. All of this in in Access 2010 vba. The mykeyid is captured after we do an insert to create a user records in the beginning of the app. This has been working flawleslly for weeks - hit about 100 times per day without error.

    Here is the code

    mysql = "Select * from myAgentTable where keyid =" & mykeyid

    set myrs = mydb.openrecordset(mysql, dbopdbOpenDynaset)

    With myrs

                .Edit
                .Fields("EndDate").Value =Now()

    End With

    myrs.Update 
    myrs.close

    mydb.close

    This code has been working flawlessly for weeks. Today, I saw too cases where my error handler bubled up a 3021. No current record.  And that is pretty much impossible. When the app opens we insert the record for the key, when they close we update that record using the identity value key.  There record IS in the db. I have solid error handles on "every" db call.

    Is there something wrong with they type of recordset we are using for this or is there another locking option we could try. As you know, finding somethign that happens rarely is very difficult becasue we cannot get this to happen all of the time.

    Thanks for any suggestions.

    MG

    Wednesday, August 31, 2016 4:18 PM

Answers

  • Hi Celieste,

    It has not occurred since, we have additional traps put in to see if the values is coming back as zero. So far, nothing yet.

    Monday, September 5, 2016 6:51 PM

All replies

  • What is dbopdbOpenDynaset?  Should it not be dbOpenDynaset ?

    Also, what is mydb set to exactly?  If it is to the Currentdb, you can't actually close it (not point in the mydb.close).  When asking for help related to a specific bit of code, try to show us the full procedure so we can see the declarations, error hanlder, ...


    -- Daniel Pineault, 2010-2015 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, August 31, 2016 4:55 PM
  • Fellow developers,  I am using Access 2010

    I inherited some code for in an existing app - for updating. Normally I write direct sql but current code is doing below. All of this in in Access 2010 vba. The mykeyid is captured after we do an insert to create a user records in the beginning of the app. This has been working flawleslly for weeks - hit about 100 times per day without error.

    Here is the code

    mysql = "Select * from myAgentTable where keyid =" & mykeyid

    set myrs = mydb.openrecordset(mysql, dbopdbOpenDynaset)

    With myrs

                .Edit
                .Fields("EndDate").Value =Now()

    End With

    myrs.Update 
    myrs.close

    mydb.close

    This code has been working flawlessly for weeks. Today, I saw too cases where my error handler bubled up a 3021. No current record.  And that is pretty much impossible. When the app opens we insert the record for the key, when they close we update that record using the identity value key.  There record IS in the db. I have solid error handles on "every" db call.

    Is there something wrong with they type of recordset we are using for this or is there another locking option we could try. As you know, finding somethign that happens rarely is very difficult becasue we cannot get this to happen all of the time.

    I don't think that can be an exact quote of your code, because there is no constant "dbopdbOpenDynaset" (unless you defined one by that name).  That aside, I wonder why you don't just execute an update query rather than opening a recordset; something like this:

    mydb.Execute "UPDATE myAgentTable SET EndDate = Now() WHERE keyid =" & mykeyid, dbFailOnError
    Now, as to why the record isn't found when you use the recordset method (assuming the issue with the recordset-type constant is just a transcription error), the only thing that occurs to me is that maybe your database object mydb is a different object from the one whereby you created the record, and maybe it uses a different connection.  If you assigned mydb from CurrentDb, then this should not be an issue.  But if you assigned it by OpenDatabase, then it could be, due to caching of updates.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, August 31, 2016 4:56 PM
  • Hi Daniel and Dirk,

    Thanks for your input.

    Ok. Sorry guys, I had a copy/paste mistake. Yes, my option is dbOpenDynaset

    Also, the dao connection is an external access db. There is nothing wrong with the connection.

    I have got error handlers checking the connection before I open the recordset so it is making a clean connection.

    There is nothing wrong with the connection. It uses Jet and it is shared dao connection.

    Again, this just happened twice and has been run 100's of times in the past week without a single flaw.

    Dirk, the reason I do not use direct SQL is that "existing code" that I inherited uses the existing approach and has been running fine on many other updates, inserts etc 100's of times without error, So, I was not going to rewrite this.

    I have to believe this is a caching issue. If you have any other ideas, let me know.

    Thanks again guys,

    MG

    Wednesday, August 31, 2016 5:45 PM
  • The error could occur if for some reason the variable mykeyid in the statement

      mysql = "Select * from myAgentTable where keyid =" & mykeyid

    has the wrong value, e.g. is 0 or NULL. Where ist this variable assigned its value? Perhaps something has gone wrong there. For example a Form_Open event may not run when opening a form that is already open.

    You should log the error to a table or file and include the values of all variables in your error handler.

    Matthias Kläy, Kläy Computing AG

    Wednesday, August 31, 2016 5:57 PM
  • Yes, it does sound like a caching issue.  To repeat my earlier question, how are you assigning mydb?  Are you using the DAO.OpenDatabase method, or are you using the application's CurrentDb method? 

    Contrasted with that, how was the record inserted in the database?  Was it done through a bound form to a linked table, or was it added by code, and did that code use a different database object -- in which case, how was that database object assigned?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, August 31, 2016 5:58 PM
  • Hi Matt,

    Nope, the value is seeded with an existing number. The record was crated with a rs.AddNew concept.

    We already checked that ;) and the record exists in the DB from original insert.

    MG

    Wednesday, August 31, 2016 6:28 PM
  • Hi Dirk,

    This app is a disconnectd concept. We only open the DB when needed and close it - not very often either. It is dao. The dao is opendatabase("N:\jjjjj\jjjj\mydb.accdb",'False)

    Again, this same connectin pattern is used throught the entire app and has yet to present this error on ANYTHING. hundreds of times over.

    Thanks

    MG

    Wednesday, August 31, 2016 6:30 PM
  • So when you create the record, you do something like:

        Set db = OpenDatabase(...)
        db.Execute "INSERT ..." 
        ' or else OpenRecordset, .AddNew, .Update, .Close
        db.Close

    ?

    And then when you later want to update the record, you open the database again, using the same connection string?

    Under those conditions, I'm less inclined to think it's a caching problem.

    Do you have error-handling in place to verify that the record was successfully added in the first place?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, August 31, 2016 6:46 PM
  • HI Dirk

    I do the same exact patten everywhere

    I open the db  with error handling for that.

    I use recordset with specific error handling for that

    I close the RS and close the db connection - "always". Have checked the code 3 times.

    I have error handling also when I close the rs and also close the db.

    Have tested 'every single one of them by hand", the all are working.

    If an open or, after the open, anything errors, I will get an error bubble up which I handle.

    Thnks

    MG

    Wednesday, August 31, 2016 7:12 PM
  • HI Dirk

    I do the same exact patten everywhere

    I open the db  with error handling for that.

    I use recordset with specific error handling for that

    I close the RS and close the db connection - "always". Have checked the code 3 times.

    I have error handling also when I close the rs and also close the db.

    Have tested 'every single one of them by hand", the all are working.

    If an open or, after the open, anything errors, I will get an error bubble up which I handle.

    At this point, I'm at a loss.  The only thing I can think of to do is to put in temporary verification code both at the point where the record is added -- to do a second lookup and make sure the record was really added successfully -- and at the point where you update it, to make sure that the recordset isn't empty after you've opened it.

    If it's happened twice recently, you have some hope that it will happen again and you can get a more complete picture.

    I didn't mention this before, but it occurs to me that you might do a compact/repair on the back-end database, on the off chance that there's some sort of corruption behind the apparently impossible behavior.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Wednesday, August 31, 2016 7:29 PM
  • HI Dirk,

    Thanks for the info. We are going to do a compact/repair on the backend db.

    There was no need to check to see if the record was added, we can see the record in the DB. It was added for that person. ;) and we can see that the enddate was not updated. So, the error handler is working perfectly. And I do not use on error resume next - all error handling is truly handled.

    So, we will compact and continue to test. If this thing happens again, I probably will rip out the rs.update logic for this one step and add simple t-sql to do the update and run that with db.exeucte(sql) command.

    Thanks again, Dirk

    MG

    Wednesday, August 31, 2016 7:36 PM
  • Good luck!

    I can only think of one other thing, which I think is what Matthias Kläy may have been getting at.  In the code you posted at the head of this thread, the key to look up is coming from a variable named "mykeyid".  If for some reason that variable doesn't have the right value, of course the lookup will probably fail.  Where does that variable come from?  Is it a local variable or a global one? 

    Did your error-handling code enable you to verify that, at the time of the original error 3021, it had the correct value?  If you were not able to verify that, I would certainly modify the code that does the lookup so that, if the recordset is empty, the value of mykeyid is recorded for later inspection.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, August 31, 2016 7:46 PM
  • HI Dirk,

    All of that we already have covered. Thanks for the info.

    Mark

    Thursday, September 1, 2016 10:21 AM
  • I too suspect that mykeyid is a global variable and does not contain what you think it does by the time you try to update the record you're trying to update.  Are you sure that your users are not encountering some other untrapped error causing mykeyid to lose its value? Perhaps an unaccounted for form error (i.e. one not trapped using the form's Error event)?

    -Bruce

    Thursday, September 1, 2016 6:26 PM
  • Hi Bruce,

    Thanks and thanks all.

    1 - yes this a global variable and it is ONLY updated once - "once". I did a find on the entire project.

    2 - The error handlers are solid but I am putting more info so if it does happen again, I will see that global variable value.

    3 - The code that I use to make the update is in the form unload. I am suspicious if the global variable is being corrupted because we are using it unload - highly doubtful. But regardless, I have moved thje update logic in to the close (before the unload event).

    4 - Again, this is very very random. Works flawlessly for me and I am building this thing - hours of launching and debugging and hit this code every single launch for the past 3 weeks.  I am building a logging even to capture said variable to see what is going on.

    Thursday, September 1, 2016 8:03 PM
  • An unhandled error may cause your global variable to reinitialize to 0 (I am assuming it is an integer or long integer value).  In situations like yours where I want to save some value for the duration of a user session within an application I'll stuff that value into a textbox on a hidden form which is much safer in terms of not losing its value on some unhandled error.  If I then want to write that value out to a database when the user closes the application I'll include code (like you did) in the close event of that hidden form.  When the application is closed, the hidden form is forced to close, and its close event code runs.

    -Bruce

    Thursday, September 1, 2016 8:37 PM
  • HI Bruce, I agree with you which is why I am adding a few other items. Yes it in a LONG variable type.MG

    Thursday, September 1, 2016 8:50 PM
  • Hi,

    Have you resolved your issue?

    As far as I am concerned, I would suggest you check if global variable is 0, outputting the value before the sql

    Monday, September 5, 2016 9:32 AM
    Moderator
  • Hi Celieste,

    It has not occurred since, we have additional traps put in to see if the values is coming back as zero. So far, nothing yet.

    Monday, September 5, 2016 6:51 PM