The fundamentals of loops

[GraeagleBill]

I often times loop through DOA Recordsets with code structured like this:

Option Compare Database
Option Explicit


'blah blah blah


With rs
If .RecordCount > 0 Then
    .MoveFirst
    While Not .EOF
    'blah blah blah !fields
    .MoveNext
    Wend
End If


End With


'blah blah blah

Such loops never fail to process the last record before exiting the loop. That is, the loop does not terminate until the .MoveNext exhausts the recordset.

I don't often find occasions to read txt files, but recently such an occasion arose and so my natural tendency was to code a similarly structured loop to process a file. Upon discovering within the app that I was missing the last record from the file, I wrote a little test sub to investigate:


The text file has five (5) records within, 3 text lines with intervening blank lines. Unlike the code shown above, typically used to loop through DAO Recordsets, the loop reading the text file terminates the loop after reading in the last record rather than when an attempt is made to read another record. So, as one can see, the Debug.Print statement for the last record never fired.

So, puzzled with it all, I changed the sequence of statement events and below is the result. Is there some explanation for this behavior where loops behave differently when reading files versus recordsets?

[Welshgasman]

So you are expecting one record to a line?

[GraeagleBill]

Yes, I'm expecting 5 distinct records from the txt file.

[Welshgasman]

So post the file from notepad+ (with hidden characters), and see what is produced?
Sounds like you are not delimiting any records correctly?

https://www.google.com/search?q=what...obile&ie=UTF-8

[Welshgasman]

Dupe post in error.

[Micron]

Recordset EOF and BOF is true when you move beyond the last or first record. EOF with file is when you get to the end of the file, so not sure what you were expecting, especially since it looks like you have 2 blank lines in the output.

[GraeagleBill]

EOF with file is when you get to the end of the file

I wasn't expecting the EOF to fire until I attempted to read another record AFTER having processed the last record. With your explanation, it's clear my expectation was faulty given the sequence of the statements within the loop.

Thanks, lesson learned.

I think this is what Welshgasman was asking for:

[Welshgasman]

Ok,I was expecting crlf at the end of every line.
So apprently for text files, no need for last line, eof takes priority?

Easy enough to examine regardless?

[June7]

I tested your code with your sample file and all lines are output.

[Welshgasman]

There was a sample file?, I missed that on my phone?

[June7]

Not attached file but sample data in image.

[moke123]

I don't often find occasions to read txt files, but recently such an occasion arose

Not sure what your recent occasion entailed but just wanted to point out that FileSystemObject has a text stream object which is pretty handy.

Sub OpenTextFileRead(sTextFilePath As String)
    ' Must add reference to Tools > References > Microsoft Scripting Runtime
    ' (1) Open an instance of the FileSystemObject.
    ' (2) Open an instance of the FileSystemObject TextStream.
    ' (3) Read each line of text in the TextStream object.
    ' (4) Close the TextStream object.


    Dim fso As Scripting.FileSystemObject
    Dim tsTxtFile As Scripting.TextStream
    
    ' (1) Open an instance of the FileSystemObject.
    Set fso = New Scripting.FileSystemObject
    
    ' (2) Open an instance of the FileSystemObject TextStream.
    Set tsTxtFile = fso.OpenTextFile(sTextFilePath, ForReading, False, TristateMixed)
    
    With tsTxtFile
    
        ' (3) Read each line of text in the TextStream object.
        Do Until .AtEndOfStream
            ' ************************************
            ' insert your code here
            ' ************************************
            Debug.Print .Line & ": " & .ReadLine ' remove in production
        Loop


        ' (4) Close the TextStream object.
        .Close
        
    End With
    
    Set fso = Nothing


End Sub

5 test lines with a CrLf after each entry.

1: test line 1
2: 
3: test line 2
4: 
5: test line 3
6: 
7: test line 4
8: 
9: test line 5
10: