ms-accessvbado-whiledata-importdo-loops

Custom Import VBA issue


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

Solution

  • 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.