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