Runtime error 3021 как исправить

• 
• VBForums
• Visual Basic
• Visual Basic 6 and Earlier
• [RESOLVED] Run-Time Error ‘3021’ No Current Record Found

1. Oct 20th, 2014, 12:59 PM

   
   

   #1

   

   Thread Starter

   
   Hyperactive Member
   

   

   ---

   [RESOLVED] Run-Time Error ‘3021’ No Current Record Found
   

   I’ve read through the other threads on this topic but they don’t seem to apply to my problem. I have a 1 row table that I’m trying to read into a recordset using VB6, but when I try to access any of the fields in the record, I get a runtime error 3021 «No current record».

   I thought that when you use the Set Recordset command the recordset is populated with the contents of the target table. But that’s not happening. Can someone tell me what I’m doing wrong? Any help is greatly appreciated. Here’s my code:

   Database: MS Access
   Field: mpSalesRankTier1
   Data type: Number
   Content: 10000
   Number of rows in table: 1

   Code:

   Option Explicit
   
   Public Sub modDeterminePrice()
   
   
       On Error GoTo ErrorRoutine
   
   
       Dim dbCtrlRec As Database
       Dim rsCtrlRec As Recordset
    
   
       Set dbCtrlRec = OpenDatabase(App.Path & "" & "LLSYS.mdb")
       Set rsCtrlRec = dbCtrlRec.OpenRecordset("SYS_CONTROL_RECORD")
       
       MsgBox rsCtrlRec.Fields("mpSalesRankTier1")   '<- Failure occurs here
   
   
   Exit Sub
       
   ErrorRoutine:
   
       If Err.Number > 0 Then
           Call gscErrRtn.gscErrRtn
       End If
   
   End Sub

   Last edited by vb6coder14; Oct 20th, 2014 at 01:03 PM.

   ---

2. Oct 20th, 2014, 01:43 PM

   
   

   #2

   Re: Run-Time Error ‘3021’ No Current Record Found
   

   It looks like you are using DAO
   Then:
   1 Reference the DAO library
   2 Change the lines where you declare and where you open Recordset

   Code:

    Option Explicit
   
   Public Sub modDeterminePrice()
   
   
       On Error GoTo ErrorRoutine
   
   
       Dim dbCtrlRec As DAO.Database
       Dim rsCtrlRec As DAO.Recordset
    
   
       Set dbCtrlRec = OpenDatabase(App.Path & "" & "LLSYS.mdb")
       Set rsCtrlRec = dbCtrlRec.OpenRecordset("SYS_CONTROL_RECORD", dbOpenTable)
       
       MsgBox rsCtrlRec.Fields("mpSalesRankTier1")   '<- Failure occurs here
   
   
   Exit Sub
       
   ErrorRoutine:
   
       If Err.Number > 0 Then
           Call gscErrRtn.gscErrRtn
       End If
   
   End Sub

   JG

   … If your problem is fixed don’t forget to mark your threads as resolved using the Thread Tools menu …

   ---

3. Oct 20th, 2014, 02:57 PM

   
   

   #3

   

   Thread Starter

   
   Hyperactive Member
   

   

   ---

   Re: Run-Time Error ‘3021’ No Current Record Found
   

   jggtz, I made all of the changes you suggested and no dice. I’m still getting the same problem.

   ---

4. Oct 20th, 2014, 03:32 PM

   
   

   #4

   Re: Run-Time Error ‘3021’ No Current Record Found
   

   I happen to still use the DAO often. vb6coder14, with the DAO, it’s possible to have the recordset sitting on a non-record, and that’s exactly the case when you initially open a recordset.

   All that’s necessary is that you execute a rsCtrlRec.MoveFirst command before you try and read the fields.

   Be careful though. That will also throw an error if there are no records in the table. If you also want to avoid that, do something like «If rsCtrlRec.RecordCount > 0 Then …»

   Take Care,
   Elroy

   ---

5. Oct 20th, 2014, 03:42 PM

   
   

   #5

   Re: Run-Time Error ‘3021’ No Current Record Found
   

   It’s been a while since I have done DAO, so these are only observations, but here it goes..

   1- dim rsCtrlRec as RecordSet is valid if your DAO reference is set correctly. (no need for DAO.)

   2- Set rsCtrlRec = dbCtrlRec.OpenRecordset(«SYS_CONTROL_RECORD») is ok too

   3- I always use indexed recordset, and by habit, I always include the line :

   rsCtrlRec.MoveFirst

   to avoid BOF conditions

   4- Make sure you have records :

   msgbox rsCtrlRec.RecordCount

   5- check the position of the record pointer with rsCtrlRec.BOF and rsCtrlRec.EOF when encountering problems such as you have…. For example, if you have no records, after opening the recordset :

   rsCtrlRec.MoveFirst
   if rsCtrlRec.BOF then
   rem there are no records
   end if

   6- and last, but it should have been the first point mentioned, are you sure there is not typo in your field name, and that the field indeed does exist.. you can get the value of a field by its index number instead of a literal

   msgbox rsCtrlRec.Fields(0)

   will return the value for the first field in the table, no matter what it’s literal name is… this will also indicate that you have indeed at least one field defined if no error is returned.

   ---

6. Oct 20th, 2014, 04:03 PM

   
   

   #6

   Re: Run-Time Error ‘3021’ No Current Record Found
   

   Navion, you can use .MoveFirst, .MoveNext, .MovePrev, and .MoveLast without an index. There’s just no guarantee of the order of the records.

   With no index, you just don’t get the use of .Seek… which is where the real power is.

   ---

7. Oct 20th, 2014, 04:16 PM

   
   

   #7

   Re: Run-Time Error ‘3021’ No Current Record Found
   

   vb6coder14, here’s the DAO help file, in case you don’t have it. As Navion hinted, also be sure to learn the meaning of .BOF and .EOF. If you’re just using an .MDB style database, I’ve found the DAO to be an incredibly powerful tool. Elegant, bulletproof, and simple.

   dao360.chm

   ---

8. Oct 20th, 2014, 04:25 PM

   
   

   #8

   Re: Run-Time Error ‘3021’ No Current Record Found
   

   Yeah sure… Except for a generic SQL handler program I have written way back then (i still use it and it never required any updating ever since) that used non indexed tables, all the rest of my work uses indexes and since my code rarely fails if ever , I keep methods that have proven their worth , even if things could be done some other ways.

   Old habits die hard. For example, I never use :

   Dim a as String

   It will always be

   Dim a$

   for me although I am not real strong on Option Explicit why bother and be nagged countless times. I can keep track of variables on my own loll

   When you put too much restrictions on coding, it becomes VB 2010 and I don’t wish that anyone

   ---

9. Oct 20th, 2014, 04:39 PM

   
   

   #9

   Re: Run-Time Error ‘3021’ No Current Record Found
   

   *laughs heartily*

   Hey Navion, I’m totally with you. I mostly work alone these days, but I’ve had to fire programmers in the past who wouldn’t leave unbroken code alone. If it ain’t broke, DON’T FIX IT! There’s another «code sharing» thread on here where I made a similar point. The sharing of .BAS (or other) files in multiple .VBP project is a monumentally bad idea in my opinion. It’s the same issue, someone «fixing» code for one project that breaks another.

   I’ll have to admit that I go back to the a!, a$, and a# days, but I have managed to wean myself into the new «as string» etc format. The only similar habit that I won’t break is Left$(s, 4), Mid$(s, 2, 5), etc. I do use variants and even like them, but I do stay away from them whenever possible. (Just noting that Left(s,4) returns a variant whereas Left$(s,4) returns a string.

   Oh geez, I’m a HUGE advocate of Option Explicit. SOOO many times before we had it, I can remember beating my head against the wall, when it was just a variable name typo. In fact, I’ve long advocated that there should also be an Option TypeExplicit that applied to Redim as well as functions. I mean, if I want a variant or a late bound object, I’ll declare it as such.

   I’ll agree that they should stay the meta-commands (Option …), but they do make finding bugs easier.

   Take Care,
   Elroy

   ---

10. Oct 20th, 2014, 04:40 PM

    
    

    #10

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Its been a while since I have used DAO but I do not remember ever needing to issue a movefirst to get to an active record when a recordset is first opened.

    Of course there is no code there that checks to see if any records were returned so my guess would be that movefirst would also throw an error as I suspect the problem is that the recordset is empty

    ---

11. Oct 20th, 2014, 04:54 PM

    
    

    #11

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    You know what, DataMiser? You’re right. I guess I’ve just never thought to NOT do a .MoveFirst. I just tested it and the recordset opens on the first record. Actually, all of my tables DO have indices (including PrimaryKey indices), so I’m not sure what happens on tables without an index, but that’s interesting.

    ---

12. Oct 20th, 2014, 04:58 PM

    
    

    #12

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    I do not think the presence of or absence of indexes/primary keys would have any effect either.

    They will of course make your queries faster when using criteria that is indexed but as far as moving next, previous, first, last I would not expect there to be any difference.

    ---

13. Oct 20th, 2014, 05:00 PM

    
    

    #13

    

    Thread Starter

    
    Hyperactive Member
    

    

    ---

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    DataMiser is right. The recordset is empty. What threw me was my lack of understanding of the internal workings of a .MDB file. I actually manually keyed values into the database, but I didn’t hit the ENTER key. I went straight to SAVE. Consequently, although it appeared as if I had data, I really didn’t.

    It wasn’t until I physically went back into the database and hit the ENTER key when a 2nd record appeared, this one prefixed with an ‘*’. I’m going to assume that’s the EOF pointer. When I ran my code again, it worked just fine.

    Can someone confirm my interpretation of what happened, or am I still off base in my understanding?

    ---

14. Oct 20th, 2014, 05:01 PM

    
    

    #14

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Hey Elroy…

    I wrote a paper back in the early days of VB, when they introduced Variants as the default data , destroying old myths and urban legends about stronger typed variables from old time programmers (although I am one of those myself). I do like variants but I use Typed variables too a lot, most often than not actually.

    At some point, I got a bit lazy and started to use the variant versions of the common string functions too…. but not anymore… they are slower and introduce an element of uncertainty (in my mind anyway, no real rationale on that argument). I reverted back to the mid$ and Trim$ etc… I think readability is much improved.

    I have read your posts these past few days, and between the lines too. I think we both would get along

    ---

15. Oct 20th, 2014, 05:03 PM

    
    

    #15

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Before a MoveFirst is called, shouldn’t the recordset be checked for .EOF? I see that the posted code is using the generic ON ERROR GOTO [label], but checking for .EOF after returning the recordset can allow the option of using some default values vs. exiting the routine if .MoveFirst cause an error. Just thinking out loud

    ---

16. Oct 20th, 2014, 05:08 PM

    
    

    #16

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Yep, you’re right LaVolpe, but that’s why I mentioned the RecordCount property in post #4. If it’s got records, a MoveFirst will never fail.

    And a smile and nod to Navion. )) Are we all having fun now? LMAO

    ---

17. Oct 20th, 2014, 05:13 PM

    
    

    #17

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Originally Posted by Elroy
    

    Yep, you’re right LaVolpe, but that’s why I mentioned the RecordCount property in post #4. If it’s got records, a MoveFirst will never fail.

    A recordset, depending on type & sql provider, can have an initial value of -1 if I recall correctly

    ---

18. Oct 20th, 2014, 05:17 PM

    
    

    #18

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Yes, but for my own common use, I use indexed tables, a movefirst (that I don’t remember throwing an error on empty recordset), followed by a seek and a NoMatch that will take care of the EOF if any. Navigating down, of course, checking EOF is required.

    On error should not be really be used doing DAO, good coding should use BOF, EOF, NoMatch (index stuff). That does not leave much causes for errors

    ---

19. Oct 20th, 2014, 05:24 PM

    
    

    #19

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Indeed Elroy! Indeed! Almost a chat room atmosphere. But let’s not hijack the OP’s thread too much (for the record lolll)

    ---

20. Oct 20th, 2014, 05:28 PM

    
    

    #20

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Navion, I totally agree. I have my share of On Error Resume Next, and On Error Goto … in my code, but I very seldom use it when using DAO methods. I either get it right, or I debug it.

    Actually, I just looked around at some of my standard DAO procedures and did find this code:

    Code:

    Public Function DoubleVal(fld As DAO.Field, Optional dDefault As Double = 0) As Double
        If IsNull(fld) Then
            DoubleVal = dDefault
        Else
            On Error Resume Next
            DoubleVal = fld
            On Error GoTo 0
        End If
    End Function

    I did put error ignoring in there, but, truth be told, I’ve now got no idea why. However, going back to something I said earlier today, if it ain’t broken, don’t fix it.

    ---

21. Oct 20th, 2014, 05:35 PM

    
    

    #21

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Originally Posted by Navion
    

    … a movefirst (that I don’t remember throwing an error on empty recordset), followed by a seek and a NoMatch that will take care of the EOF if any…

    Honestly, don’t mess with DAO much any longer, but with ADO… Per MSDN: «A call to either MoveFirst or MoveLast when the Recordset is empty (both BOF and EOF are True) generates an error.»

    To avoid that situation, my general iteration of the recordset kinda looks like this

    Code:

    ... assumption is recordset was returned. If EOF is false, should be on 1st record
       Do Until .EOF = True
          ... process
          .MoveNext
       Loop

    ---

22. Oct 20th, 2014, 05:39 PM

    
    

    #22

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Yep, I seldom let these conditions happen, but I believe that an empty recordset will set both .BOF and .EOF as true. And I think that’s the only time it happens. Also, I’m pretty sure that the DAO and the ADO are VERY similar (if not identical) regarding all of these relatively straightforward procedures.

    ---

23. Oct 20th, 2014, 05:46 PM

    
    

    #23

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Funny that you should mention that Elroy, I forgot to do it myself… another essential of DAO is IsNull, particularly with binary fields, they don’t like much being cast to empty strings, although .FieldSize does the job too.

    ---

24. Oct 20th, 2014, 05:55 PM

    
    

    #24

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Originally Posted by LaVolpe
    

    A recordset, depending on type & sql provider, can have an initial value of -1 if I recall correctly

    Yes, .Recordcount will return -1 in cases where recordcount is not supported. I can’t remember if this is the case with DAO but ADO will return -1 when using a server side cursor no matter how many records there may be.

    Checking for .eof is a more fool proof method.

    ---

25. Oct 20th, 2014, 06:40 PM

    
    

    #25

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    When using DAO .RecordCount will give you the result only after a .MoveLast method

    JG

    … If your problem is fixed don’t forget to mark your threads as resolved using the Thread Tools menu …

    ---

26. Oct 20th, 2014, 07:22 PM

    
    

    #26

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Originally Posted by jggtz
    

    When using DAO .RecordCount will give you the result only after a .MoveLast method

    Hummm good point, you are right about that. But if I remember correctly (I think I do, but maybe not), that does not apply to a recordset after an Index has been set.

    ---

27. Oct 20th, 2014, 08:16 PM

    
    

    #27

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Actually, it still applies after an index has been set. It’s actually the only major bug in the DAO that I’m aware of. And, the situation is worse than just needing a .MoveLast execution. It still may not work. One thing that is true though is that .RecordCount will always be greater than zero if there are records. Beyond that, it’s just completely untrustworthy. Here are two functions I use instead:

    Code:

    Public Function bHasRecords(rs As Recordset) As Boolean
        ' The RecordCount property is generally bad news.
        ' It doesn't always work right even with a MoveLast.
        ' It does seem to be reliable as a check of the existence of ANY records.
        ' But even here, sometimes it will report -1 as "no records" rather than zero.
        bHasRecords = rs.RecordCount > 0
    End Function
    
    Public Function lExactRecordCount(rs As Recordset) As Long
        ' Be careful with this.  You can NOT be in an "EDIT" or "UPDATE" mode to use this.
        ' However, it will attempt to preserve the active record.
        Dim Bookmark As String
        Dim l As Long
        '
        If (rs.BOF = False) And (rs.EOF = False) Then Bookmark = rs.Bookmark
        '
        ' Count the records.
        If bHasRecords(rs) Then
            rs.MoveFirst
            l = 1
            Do
                rs.MoveNext
                If rs.EOF Then Exit Do
                l = l + 1
            Loop
        End If
        '
        If Len(Bookmark) <> 0 Then rs.Bookmark = Bookmark
        lExactRecordCount = l
    End Function

    ---

28. Oct 20th, 2014, 08:19 PM

    
    

    #28

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Just as a further comment, the only other «bug» I know of in the DAO is that the default timing settings sometimes cause users to have unwanted collisions in a multi-user environment. If asked, I’ll post a fix that I’ve used for years and is working flawlessly in many installations.

    In fact, after staring at some code, I’ll outline the problem. The Jet Engine has «read ahead» and «cached write» options. In a multi-user environment, both of these can cause problems. Here, I’ll go ahead and post it. The following turns the «read ahead» and the «cached write» options off so that all reads and writes to/from an MDB file are truly coming off the server disk.

    Code:

        DBEngine.SetOption dbExclusiveAsyncDelay, 2000
        DBEngine.SetOption dbSharedAsyncDelay, 0
        DBEngine.SetOption dbFlushTransactionTimeout, 500
        '
        DBEngine.SetOption dbUserCommitSync, "yes"
        DBEngine.SetOption dbImplicitCommitSync, "yes"
        DBEngine.SetOption dbLockRetry, 20
        DBEngine.SetOption dbPageTimeout, 5000
        DBEngine.SetOption dbMaxLocksPerFile, 9500
        DBEngine.SetOption dbLockDelay, 100
        DBEngine.SetOption dbRecycleLVs, 0

    I’ve got about five pages of comments also in the procedure that calls this. If anyone wants it, let me know.

    Last edited by Elroy; Oct 20th, 2014 at 08:24 PM.

    ---

29. Oct 20th, 2014, 08:21 PM

    
    

    #29

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    I had an issue once using DAO in VB5 on an Access DB where the .MoveLast triggered an error message something like «Statement to complex to process»

    It took a bit to find the root of the problem, surely a simple move last can not be to complicated and the query was rather basic.

    Turned out to be a null value in a QTY field where who ever created the DB decided that QTY should be a text field and allow null values.

    Still not sure why it threw that error message but placing a value of 0 in the null fields corrected the problem then I added a default value of «0» to prevent the problem from happening again.

    ---

30. Oct 20th, 2014, 08:23 PM

    
    

    #30

    

    Thread Starter

    
    Hyperactive Member
    

    

    ---

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    This has been a great discussion and I’ve certainly picked up several valuable tips from reading all of the responses. Thank you all very much for your input. I’m marking this thread resolved now.

    ---

31. Oct 20th, 2014, 11:24 PM

    
    

    #31

    Re: Run-Time Error ‘3021’ No Current Record Found
    

    Originally Posted by Elroy
    

    Actually, it still applies after an index has been set. It’s actually the only major bug in the DAO that I’m aware of. And, the situation is worse than just needing a .MoveLast execution. It still may not work. One thing that is true though is that .RecordCount will always be greater than zero if there are records. Beyond that, it’s just completely untrustworthy. Here are two functions I use instead:

    Again, only out of memories… I am pretty sure I have come across the problem before, and the DAO has a method to force its own reading of the whole dataset in order to return the proper RecordCount. Again not sure, but I will be browsing through some old code to see if i find it. Then as i finish writing these few words, certainty rises a notch of two because I remember testing the feature with very large number of records in a table. Will see.

    ---

32. Oct 21st, 2014, 12:17 AM

    
    

    #32

    Re: [RESOLVED] Run-Time Error ‘3021’ No Current Record Found
    

    I remember working with some large tables in VB5 and using the .MoveLast and .Recordcount to set the max value of a progress bar before looping through the data. I do not recall having saw any issues there but it was long ago.

    ---

33. Oct 21st, 2014, 08:01 AM

    
    

    #33

    Re: [RESOLVED] Run-Time Error ‘3021’ No Current Record Found
    

    I can absolutely promise that .MoveLast doesn’t always work to fix the .RecordCount problem. In my mind, I always thought it was an indexing thing. Say, you may have a PrimaryKey index on «Name». And then «AAAbernathy» is added first, and then «ZZZombie» added second, with 1000s of records subsequently added in between. In that case (through whatever btree or indexing method it internally uses), it comes nowhere close to actually «touching» and counting all the records to do a .MoveLast.

    But that’s all high supposition on my part. I just know .RecordCount isn’t accurate unless you «touch» every record in the database.

    And, long ago, I decided that, if I’m going to «touch» every record in the database, I’ll just count them myself.

    ---

• 
• VBForums
• Visual Basic
• Visual Basic 6 and Earlier
• [RESOLVED] Run-Time Error ‘3021’ No Current Record Found

Posting Permissions