Results 1 to 9 of 9
  1. #1
    Zaphod_B is offline Novice
    Windows XP Access 2002
    Join Date
    May 2011
    Posts
    5

    3021 error: no current record OR Loop without Do error

    Hi Everyone,

    I'm new here so I hope I posted this in the right section!

    I'm encountering an error with the code below. It works fine for all contacts
    except the last which it will not add to the table and gives the 3021 error:
    no current record.

    I have tried checking for EOF but then It gives a Loop without Do error...

    The line marked with *** is the one with the error according to access when
    the check for EOF is not in there and the script between []'s is what I added
    to check for EOF...

    What am I missing? Thank you!



    CODE:

    Code:
    Public Function combine_groepen()
    
    DoCmd.SetWarnings (False)
    
    Dim rst As DAO.Recordset
    Dim strCurrentContact As String
    
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM t_contact_categorie ORDER BY
    ContactID, GroepID")
    strCurrentContact = vbNullString
    
    
    strgroepen = vbNullString
    
    If Not (rst.BOF) Then
    Do
    
       
       Do [While Not rst.EOF]
       strgroepen = strgroepen & Nz(rst!GroepID, vbNullString) & ", "
       strCurrentContact = rst!ContactID
    
       
       rst.MoveNext
     
       
       ***Loop Until rst!ContactID <> strCurrentContact
      
    strgroepen = Left(strgroepen, Len(strgroepen) - 2)
    
    DoCmd.RunSQL "INSERT INTO t_contacts_inlinegrps ( ContactID, Groepen ) SELECT
    '" & strCurrentContact & "','" & strgroepen & "'"
    
    strgroepen = vbNullString
    
    Loop
    
    rst.Close
    Set rst = Nothing
    
    
    DoCmd.SetWarnings (True)
    
    End If
    End Function

  2. #2
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    you have 2 instructions in the same loop. 2 continuation instructions, that is. can't have that. TRY:
    Code:
    Do
     
        
       Do 'JUST A DO WILL BE FINE HERE
       strgroepen = strgroepen & Nz(rst!GroepID, vbNullString) & ", "
       strCurrentContact = rst!ContactID
     
        
       rst.MoveNext
      
        
       Loop Until rst!ContactID <> strCurrentContact 'OR JUST A LOOP HERE, NO EXTRA STUFF
    also, be wary of nexting the same looping statement twice in a row. confuses people. Try a 'DO' and then inside of that maybe a simple 'WHILE' statement.

  3. #3
    Zaphod_B is offline Novice
    Windows XP Access 2002
    Join Date
    May 2011
    Posts
    5
    Thank you ajetrumpet for your reply!

    However your suggestion didn't work, it still gives the same error.

    I'll just come clean and tell you that I got this piece of code from the internet, I do understand what it does no (after some searching and teaching myself on the internet) but couldn't have written it myself. Following your suggestion means deleting some of what I added. To end up with the original code I also removed the if statement that I added at the beginning of the code. So now I'm back at the code I got from the internet, with just the names of the tables etc different:


    Code:
    Public Function combine_groepen()
    
    'turn off warning dialog
    DoCmd.SetWarnings (False)
    
    Dim rst As DAO.Recordset
    Dim strCurrentContact As String
    
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM t_contact_categorie ORDER BY ContactID, GroepID")
    strCurrentContact = vbNullString
    
    'set groups back to null
    strgroepen = vbNullString
    
    
    Do
    
        
       Do 'JUST A DO WILL BE FINE HERE
       strgroepen = strgroepen & Nz(rst!GroepID, vbNullString) & ", "
       strCurrentContact = rst!ContactID
      
         
       rst.MoveNext
       
         
       Loop Until rst!ContactID <> strCurrentContact 'OR JUST A LOOP HERE, NO EXTRA STUFF
       
     strgroepen = Left(strgroepen, Len(strgroepen) - 2)
    DoCmd.RunSQL "INSERT INTO t_contacts_inlinegrps ( ContactID, Groepen ) SELECT '" & strCurrentContact & "','" & strgroepen & "'"
    
    
    'reset groups
    strgroepen = vbNullString
    
    Loop
    
    
    rst.Close
    Set rst = Nothing
    
    'turn dialogs back on
    DoCmd.SetWarnings (True)
    
    
    End Function
    The guy that wrote it admitted that this error was in there but said that 'this was easily fixable'. However he doesn't reply to questions on how to do this

    So I'm still stuck. Any other suggestions?

  4. #4
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    i have time today, if you want to upload your sample so I can see it being fixed myself.

    I do not like to read through code on this site, especially long procedures.

    by the way, this should teach you something about getting free code. When people post free stuff on the internet, the intention is to reel in business that involves more complex concepts. Most of them do not care whether it is spot on or not. At least, I wouldn't if I were doing it.

  5. #5
    Zaphod_B is offline Novice
    Windows XP Access 2002
    Join Date
    May 2011
    Posts
    5
    I'm sorry I didn't have time to react to your reply yesterday. I would be very gratefull if you could look at the code if you have some time these days...

    Here is a link to the file: http://bit.ly/jw0Wu9

  6. #6
    Zaphod_B is offline Novice
    Windows XP Access 2002
    Join Date
    May 2011
    Posts
    5
    Ok I figured it out, sort of... It seems to be a bug in access 2002 SP3. There are two problems, the first is that If rst.EOF Then Exit Do before the Loop statement doesn't work, and the second is that the error handler doesn't activate on this error. To get around this the on error event of the form need this code, I included the authors comments for clarity:

    Code:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
     If DataErr <> 3021 Then ' Error 3021 = " No current record"
        Response = acDataErrDisplay
     Else ' Error 3021 = " No current record"; Access 2002 has a bug on this type of error (It should only execute sub's error handler but it doesn't).
        Response = acDataErrContinue ' Ignore this error, but make sure you have error handler in offending sub routing.
     End If
    End Sub
    Last edited by Zaphod_B; 05-16-2011 at 09:07 AM. Reason: cleaning up the code

  7. #7
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    zap,

    do you need any more assistance on this??

  8. #8
    Zaphod_B is offline Novice
    Windows XP Access 2002
    Join Date
    May 2011
    Posts
    5
    No, thank you!

  9. #9
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    no problem, bud. Happy for ya! =)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form error "the current field must match..."
    By plavookins in forum Forms
    Replies: 0
    Last Post: 04-13-2011, 07:42 AM
  2. Runtime Error 3021
    By paddon in forum Programming
    Replies: 12
    Last Post: 03-14-2011, 12:14 PM
  3. Error: Loop without Do
    By eric.opperman1@gmail.com in forum Programming
    Replies: 4
    Last Post: 01-25-2011, 02:37 PM
  4. "No Current Record" error...not sure why it is there
    By jgelpi16 in forum Programming
    Replies: 1
    Last Post: 01-25-2011, 10:14 AM
  5. Current Date Error
    By McFly in forum Database Design
    Replies: 4
    Last Post: 02-04-2010, 09:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums