Custom Import VBA issue

Question

I use a custom made import function (skeleton below) to gather data from "Linked" text files (containing header/records/footer). The first field of the header will always contains 123, but I need to skip those records where the second field in the header begins with the letters "AC".

I tried to create a loop that, when it finds first field contains "123" and the second field starts with "AC" then to skip the records until it finds another "123", then assess again through the loop and only break out of the loop to write the records that don't contain "AC" in the second field of the header.

However I get "Compile error: Augment not optional" based on line Loop Until rs!Field1 = "123" And Left(rs!Field2) <> "AC", when trying the following and not quite sure how else to go about checking if the string in the second field starts with "AC". Thanks.

Public Function FormatTextFile()

Dim db As Database
Dim rs, rsa As Recordset
Dim cCount as double

Set db = CurrentDb
Set rs = db.OpenRecordset("Flow_20160316")

cCount = 1

Do

    Do While rs!Field1 = "123" And Left(rs!Field2, 2) = "AC"
        Debug.Print "Code Skipped on Record " & cCount
        cCount = cCOunt + 1
        rs.MoveNext
    Loop Until rs!Field1 = "123" And Left(rs!Field2) <> "AC"

Select Case rs!Field1

    Case Is = "123"
        'Code continues and writes some variables to tables'
        Case else
        Debug.Print "Code Skipped on Record " & cCount
    End select
    cCount = cCOunt + 1
rs.MoveNext

Loop until rs.eof

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function

Answer 1

Do / While / Until loops in VBA come in a variety of forms, there is an answer here on SO (but I can't find it now) that lists 7 versions.

(Edit: found it - Warning: don't go there if you don't have some spare time to waste)

But

Do While <condition>
    ' stuff
Loop Until <condition>

isn't allowed - it's a syntax error. The condition can appear only once.

Here is a decent overview: http://www.excelfunctions.net/VBA-Loops.html#DoWhileLoop

---

With that being said, your code with the nested loops seems overly complicated. Can't you simply do this?

Do While Not rs.EOF

    If rs!Field1 = "123" And Left(rs!Field2, 2) = "AC" Then
        Debug.Print "Code Skipped on Record " & cCount
    Else
        ' regular code
    End If

    cCount = cCount + 1
    rs.MoveNext

Loop

Note: by using Do While Not rs.EOF you avoid an error if rs is empty.