Loop without Do stumped

B

balt73

New member
Local time
Today, 10:49
Joined
Jan 14, 2025
Messages
2
Keep getting a compile error "Loop without Do and cannot find where the issue is. Appears everything matches up!


Dim blnError As Boolean
Dim strErrMessage As String

'On Error GoTo ErrHandler

Set db = CurrentDb
Set rstSource = db.OpenRecordset("SELECT * FROM [Parsed Messages] WHERE [Exported] = False ORDER BY [Message Date], [Message DTG]")

Do Until rstSource.EOF

blnError = False


Set rstTarget = db.OpenRecordset("SELECT * FROM [CVS CSA Inventory] WHERE [CVS CSA] = '" & rstSource![CSA Number] & "' AND [EIS TSR Type] = 'START'")

'SAMs will have the CSA when it is created/assigned
If Not IsNull(rstSource![CSA]) Then
If Left(rstSource![CSA], 2) = "EU" Then
rstTarget![EIS CSA] = rstSource![CSA]
End If

If Not rstTarget.EOF Then
rstTarget.Edit

'TSR
If rstSource![Message Type] = "TSR" Then
If rstSource![Message Date] <> #12:00:00 AM# Then
If IsNull(rstTarget![EIS TSR Date]) Or (rstSource![Message Date] > rstTarget![EIS TSR Date]) Or (rstSource![Message Date] = rstTarget![EIS TSR Date] And rstSource![Message DTG] > rstTarget![EIS TSR DTG]) Then
If rstTarget![EIS Status] < 4 Then rstTarget![EIS Status] = 4
If Not IsNull(rstSource![Message Date]) Then rstTarget![EIS TSR Date] = rstSource![Message Date]
If Not IsNull(rstSource![Message DTG]) Then rstTarget![EIS TSR DTG] = rstSource![Message DTG]
If Not IsNull(rstSource![Message Number]) Then rstTarget![EIS TSR Number] = rstSource![Message Number]
End If
End If
End If

'SAM
If rstSource![Message Type] = "SAM" Then
If rstSource![Message Date] <> #12:00:00 AM# Then
If rstTarget![EIS Status] < 6 Then
If rstTarget![EIS Status] < 5 Then rstTarget![EIS Status] = 5
End If

If Not IsNull(rstSource![SAM Award Date]) Then
If IsNull(rstTarget![EIS SAM Award Date]) Or rstSource![SAM Award Date] > rstTarget![EIS SAM Award Date] Then
If rstTarget![EIS Status] < 6 Then rstTarget![EIS Status] = 6
rstTarget![EIS SAM Award Date] = rstSource![SAM Award Date]
End If
End If

If Not IsNull(rstSource![SAM Completion Date]) Then
If IsNull(rstTarget![EIS SAM Completion Date]) Or rstSource![SAM Completion Date] > rstTarget![EIS SAM Completion Date] Then
If rstTarget![EIS Status] < 7 Then rstTarget![EIS Status] = 7
rstTarget![EIS SAM Completion Date] = rstSource![SAM Completion Date]
End If
End If
End If
End If

rstTarget.Update

rstSource.Edit
rstSource![Exported] = True
rstSource.Update
End If

rstSource.MoveNext
Loop (Compile Error here!)

rstSource.Close
db.Close

Set rstTarget = Nothing
Set rstSource = Nothing
Set db = Nothing

Exit Sub

ErrHandler:
MsgBox "Error: " & Err.Description, vbOKOnly, "Processing Error"

If Not rstSource.EOF Then rstSource.MoveNext
blnError = True
Resume Next
End Sub
 
Sort by date Sort by votes
Try posting between code tags. to keep the indentation. That is just a mess. :(
Then it should be obvious. Normally because you have not ended a structure correctly.

Edit:

Code: 
Sub Crap()
    Dim blnError As Boolean
    Dim strErrMessage As String

    'On Error GoTo ErrHandler

    Set db = CurrentDb
    Set rstSource = db.OpenRecordset("SELECT * FROM [Parsed Messages] WHERE [Exported] = False ORDER BY [Message Date], [Message DTG]")

    Do Until rstSource.EOF

        blnError = False


        Set rstTarget = db.OpenRecordset("SELECT * FROM [CVS CSA Inventory] WHERE [CVS CSA] = '" & rstSource![CSA Number] & "' AND [EIS TSR Type] = 'START'")

        'SAMs will have the CSA when it is created/assigned
        If Not IsNull(rstSource![CSA]) Then
            If Left(rstSource![CSA], 2) = "EU" Then
                rstTarget![EIS CSA] = rstSource![CSA]
            End If

            If Not rstTarget.EOF Then
                rstTarget.Edit

                'TSR
                If rstSource![Message Type] = "TSR" Then
                    If rstSource![Message Date] <> #12:00:00 AM# Then
                        If IsNull(rstTarget![EIS TSR Date]) Or (rstSource![Message Date] > rstTarget![EIS TSR Date]) Or (rstSource![Message Date] = rstTarget![EIS TSR Date] And rstSource![Message DTG] > rstTarget![EIS TSR DTG]) Then
                            If rstTarget![EIS Status] < 4 Then rstTarget![EIS Status] = 4
                            If Not IsNull(rstSource![Message Date]) Then rstTarget![EIS TSR Date] = rstSource![Message Date]
                            If Not IsNull(rstSource![Message DTG]) Then rstTarget![EIS TSR DTG] = rstSource![Message DTG]
                            If Not IsNull(rstSource![Message Number]) Then rstTarget![EIS TSR Number] = rstSource![Message Number]
                        End If
                    End If
                End If

                'SAM
                If rstSource![Message Type] = "SAM" Then
                    If rstSource![Message Date] <> #12:00:00 AM# Then
                        If rstTarget![EIS Status] < 6 Then
                            If rstTarget![EIS Status] < 5 Then rstTarget![EIS Status] = 5
                        End If

                        If Not IsNull(rstSource![SAM Award Date]) Then
                            If IsNull(rstTarget![EIS SAM Award Date]) Or rstSource![SAM Award Date] > rstTarget![EIS SAM Award Date] Then
                                If rstTarget![EIS Status] < 6 Then rstTarget![EIS Status] = 6
                                rstTarget![EIS SAM Award Date] = rstSource![SAM Award Date]
                            End If
                        End If

                        If Not IsNull(rstSource![SAM Completion Date]) Then
                            If IsNull(rstTarget![EIS SAM Completion Date]) Or rstSource![SAM Completion Date] > rstTarget![EIS SAM Completion Date] Then
                                If rstTarget![EIS Status] < 7 Then rstTarget![EIS Status] = 7
                                rstTarget![EIS SAM Completion Date] = rstSource![SAM Completion Date]
                            End If
                        End If
                    End If
                End If

                rstTarget.Update

                rstSource.Edit
                rstSource![Exported] = True
                rstSource.Update
            End If

            rstSource.MoveNext
        Loop (Compile Error here!)

        rstSource.Close
        db.Close

        Set rstTarget = Nothing
        Set rstSource = Nothing
        Set db = Nothing

        Exit Sub

ErrHandler:
        MsgBox "Error: " & Err.Description, vbOKOnly, "Processing Error"

        If Not rstSource.EOF Then rstSource.MoveNext
        blnError = True
        Resume Next
    End Sub

See how much easier it is to spot?
One way to avoid this is to enter the start and end words at the same time.

So
Do Until...
Loop
Then insert your code inbetween those, same with If and End If.
 
Last edited:
Upvote 0 Downvote
can't see it? Look at this sample:
Code: 
Public Sub LoopExample()
    Dim Check As Boolean, Counter As Long, Total As Long
    Check = True: Counter = 0: Total = 0 ' Initialize variables.
    Do ' Outer loop.
        Do While Counter < 20 ' Inner Loop
            Counter = Counter + 1 ' Increment Counter.
            If Counter Mod 10 = 0 Then ' Check in with the user on every multiple of 10.
                Check = (MsgBox("Keep going?", vbYesNo) = vbYes) ' Stop when user click's on No
                If Not Check Then Exit Do ' Exit inner loop.
            End If
        Loop
        Total = Total + Counter ' Exit Do Lands here.
        Counter = 0
    Loop Until Check = False ' Exit outer loop immediately.
    MsgBox "Counted to: " & Total
End Sub
 
Upvote 0 Downvote
Ok, thank you. Never posted before, I know it is something glaringly obvious that for some reason isn't jumping out. Going over it.
 
Upvote 0 Downvote
I believe you are missing an End If for If Not IsNull(rstSource![CSA]) Then
Look at the indentation.
 
Upvote 0 Downvote
The evaluation of the condition used to determine whether the code in the loop is to be executed in a Do Until vs a Do While Loop is located differently in the loop structures
 
Upvote 0 Downvote
Code: 
Sub Crap()
     Do Until rstSource.EOF
      
A:      If Not IsNull(rstSource![CSA]) Then
            
            If Left(rstSource![CSA], 2) = "EU" Then
            End If

B:            If Not rstTarget.EOF Then
                'TSR
                If rstSource![Message Type] = "TSR" Then
                    If rstSource![Message Date] <> #12:00:00 AM# Then
                        If IsNull(rstTarget![EIS TSR Date]) Or (rstSource![Message Date] > rstTarget![EIS TSR Date]) Or (rstSource![Message Date] = rstTarget![EIS TSR Date] And rstSource![Message DTG] > rstTarget![EIS TSR DTG]) Then
                        End If
                    End If
                End If

                'SAM
                If rstSource![Message Type] = "SAM" Then
                    If rstSource![Message Date] <> #12:00:00 AM# Then
                        If rstTarget![EIS Status] < 6 Then
                        End If

                        If Not IsNull(rstSource![SAM Award Date]) Then
                            If IsNull(rstTarget![EIS SAM Award Date]) Or rstSource![SAM Award Date] > rstTarget![EIS SAM Award Date] Then
                            End If
                        End If

                        If Not IsNull(rstSource![SAM Completion Date]) Then
                            If IsNull(rstTarget![EIS SAM Completion Date]) Or rstSource![SAM Completion Date] > rstTarget![EIS SAM Completion Date] Then
                            End If
                        End If
                    End If
                End If
B:            End If
A2:       (End If)
        Loop '(Compile Error here!)
Taking out all the other code I cannot find a closing end if, at block A2:

These error message get confusing because if there is a broken block within another block the error message will say the outer block. The problem is a missing end if not a loop without do.
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

K
'Next n' Looping rather than moving to the next record
Replies
16
Views
480
arnelgp
arnelgp
A
Solved Problems with code execution following email
Replies
19
Views
616
Pat Hartman
P
A
Solved Auto email error needed if attachment file doesn't exist or is named incorrectly
Replies
3
Views
398
theDBguy
theDBguy
P
Exit Do loop
Replies
9
Views
856
gemma-the-husky
gemma-the-husky
O
  • Solved
VBA Outlook SQL problem
2
Replies
20
Views
1,789
olxx
O

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom