Results 1 to 6 of 6
  1. #1
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    78

    Do ... Loop Code error

    I have an AfterUpdate event to verify the account number entered is the correct number of digits. It includes a Do... Loop that is not functioning. When I debug, I get "Loop without Do". It worked fine until I changed the text of the prompt for a valid number. I changed it back and it's didn't fix the issue. Can you tell me what's wrong with the code? I'm pretty new to VBA, so be kind.

    Code:
    Private Sub ACDVPrsnID_AfterUpdate()
    
    Do
    '---validate accnt number is 10 digits---
        If Len(ACDVPrsnID) = 10 Then
            ACDVPrsnID = Format(ACDVPrsnID, "@@ @@@@ @@@@")
        ElseIf Len(ACDVPrsnID) = 12 Then
            ACDVPrsnID = Format(ACDVPrsnID, "@@@@@@@@@@@@")
        Else
        
            Me.ACDVPrsnID = Null
        '---Prompt for valid account number---
        Dim Message, Title, Default, MyValue
            Message = "*** ERROR: " & ACDVPrsnID & " is not a valid Account Number.  Enter a valid account number."  ' Set prompt.
            Title = "Account Number Error"    ' Set title.
            ACDVPrsnID = InputBox(Message, Title, Default)
            If MyValue = vbCancel Then
            GoTo Exit_ACDVPrsnID_AfterUpdate
    
        '---end prompt---
    End If
    
    Exit_ACDVPrsnID_AfterUpdate:
        Exit Sub
    Loop Until Len(ACDVPrsnID) = 10 Or Len(ACDVPrsnID) = 12
    
    End Sub


  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It looks like you are missing an "End If"

    Code:
    Private Sub ACDVPrsnID_AfterUpdate()
    
        Do
            '---validate accnt number is 10 digits---
            If Len(ACDVPrsnID) = 10 Then
                ACDVPrsnID = Format(ACDVPrsnID, "@@ @@@@ @@@@")
            ElseIf Len(ACDVPrsnID) = 12 Then
                ACDVPrsnID = Format(ACDVPrsnID, "@@@@@@@@@@@@")
            Else
    
                Me.ACDVPrsnID = Null
                '---Prompt for valid account number---
                Dim Message, Title, Default, MyValue
                Message = "*** ERROR: " & ACDVPrsnID & " is not a valid Account Number.  Enter a valid account number."  ' Set prompt.
                Title = "Account Number Error"    ' Set title.
                ACDVPrsnID = InputBox(Message, Title, Default)
                If MyValue = vbCancel Then
                    GoTo Exit_ACDVPrsnID_AfterUpdate
    
                    '---end prompt---
                End If   ' If MyValue = vbCancel Then
                
            End If   'If Len(ACDVPrsnID) = 10 Then   "<<<<<---- I added this end if
    
    Exit_ACDVPrsnID_AfterUpdate:
            Exit Sub
        Loop Until Len(ACDVPrsnID) = 10 Or Len(ACDVPrsnID) = 12
    
    End Sub
    Also, maybe the Exit Sub is in the wrong place or you are missing part of the error handling routine.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Data validation is normally done in BeforeUpdate event.

    Could just use Exit Do and eliminate the Exit_ branch.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Or maybe try this
    Code:
    Private Sub ACDVPrsnID_AfterUpdate()
        Dim Message, Title, Default, MyValue
    
        Do
            '---validate accnt number is 10 digits---
            If Len(ACDVPrsnID) = 10 Then
                ACDVPrsnID = Format(ACDVPrsnID, "@@ @@@@ @@@@")
            ElseIf Len(ACDVPrsnID) = 12 Then
                ACDVPrsnID = Format(ACDVPrsnID, "@@@@@@@@@@@@")
            Else
    
                Me.ACDVPrsnID = Null
                '---Prompt for valid account number---
                Message = "*** ERROR: " & ACDVPrsnID & " is not a valid Account Number.  Enter a valid account number."  ' Set prompt.
                Title = "Account Number Error"    ' Set title.
                ACDVPrsnID = InputBox(Message, Title, Default)
                If MyValue = vbCancel Then
                    '                GoTo Exit_ACDVPrsnID_AfterUpdate   '*********  commented out
                    Exit Do    'added
                    
                    '---end prompt---
                End If   ' If MyValue = vbCancel Then
            End If   'If Len(ACDVPrsnID) = 10 Then
    
        Loop Until Len(ACDVPrsnID) = 10 Or Len(ACDVPrsnID) = 12   '<<<--- Moved up
    
    '  Exit_ACDVPrsnID_AfterUpdate:
        '    Exit Sub
    
    End Sub

    Sorry. Trying to trace code without being aboe to execute it.

  5. #5
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    78
    ***Edit - Ssanfu, your second code works perfectly. THANK YOU!***

    Ok. I'll try these, but why does Access keep telling me I don't have a Do statement, when it's clearly there? Is it because of placement of the Do and Loop statements? I'm not even able to step through the code because it's not recognizing the Do.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Even though Access says it is an error with the Do loop, it is the missing End If that causes the error. But because the Do loop is the first the code sees, that is the error reported.
    With me, I typically forget an End If when working with nested If..End If structures. I end up counting the Ifs and the number of End Ifs, then have to figure out where the missing End If should be placed.


    Good luck with your project.....

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

Similar Threads

  1. Loop some butchered code.
    By Homegrownandy in forum Programming
    Replies: 6
    Last Post: 05-18-2017, 07:19 AM
  2. Replies: 12
    Last Post: 06-05-2015, 04:27 PM
  3. Adding loop to existing code
    By smithdam in forum Modules
    Replies: 21
    Last Post: 03-30-2015, 06:54 AM
  4. Replies: 13
    Last Post: 08-20-2014, 09:17 AM
  5. Replies: 8
    Last Post: 05-16-2011, 06:01 PM

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