dcsimg
Results 1 to 33 of 33
  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2014
    Posts
    261

    Resolved [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. #2
    Frenzied Member
    Join Date
    May 2006
    Location
    some place in the cloud
    Posts
    1,886

    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. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2014
    Posts
    261

    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. #4
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,287

    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. #5
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    557

    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. #6
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,287

    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. #7
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,287

    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. #8
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    557

    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. #9
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,287

    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. #10
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,314

    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. #11
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,287

    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. #12
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,314

    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. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2014
    Posts
    261

    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. #14
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    557

    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. #15
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    17,273

    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
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  16. #16
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,287

    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. #17
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    17,273

    Re: Run-Time Error '3021' No Current Record Found

    Quote Originally Posted by Elroy View Post
    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
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  18. #18
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    557

    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. #19
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    557

    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. #20
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,287

    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. #21
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    17,273

    Re: Run-Time Error '3021' No Current Record Found

    Quote Originally Posted by Navion View Post
    ... 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
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  22. #22
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,287

    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. #23
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    557

    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. #24
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,314

    Re: Run-Time Error '3021' No Current Record Found

    Quote Originally Posted by LaVolpe View Post
    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. #25
    Frenzied Member
    Join Date
    May 2006
    Location
    some place in the cloud
    Posts
    1,886

    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. #26
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    557

    Re: Run-Time Error '3021' No Current Record Found

    Quote Originally Posted by jggtz View Post
    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. #27
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,287

    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. #28
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,287

    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. #29
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,314

    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. #30

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2014
    Posts
    261

    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. #31
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    557

    Re: Run-Time Error '3021' No Current Record Found

    Quote Originally Posted by Elroy View Post
    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. #32
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,314

    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. #33
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,287

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.