Solved What am I missing in my logic here?

[Gasman]

Hi all,

As an intellectual exercise, I decided to see if I could solve this question with a recordset as I had originally suggested.

https://www.access-programmers.co.uk/forums/threads/fill-table-control.315132/page-2#post-1735501

I imported the Excel sheet into a table called LTSB and created the code below.
The code works as far as it updates the rows correctly, but errors with No current record - 3021 as I have reached EOF on the inner loop, but I cannot see why the test fails for EOF.?

I even tried Do While NOT rs.EOF and rs![Interval ID 1] <>"O", but still get No current record.?

So where is my logic failing please.?

The ID field is just there so I could use in a Watch window.

TIA

Sub CopyHeader()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String, strHeader As String
Dim iModified As Integer

strSQL = "Select ID, Field7, [Description 1], [Interval ID 1] FROM LTB"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)


Do Until rs.EOF
    strHeader = rs![Description 1]
    rs.Edit
    rs!Field7 = strHeader
    rs.Update
    iModified = iModified + 1
    rs.MoveNext

    Do Until rs.EOF Or rs![Interval ID 1] = "O"
        rs.Edit
        rs!Field7 = strHeader
        rs.Update
        iModified = iModified + 1
        rs.MoveNext
    Loop
        
Loop
rs.Close
MsgBox "Number of records updated was " & iModified
Set rs = Nothing
Set db = Nothing

End Sub

 

[MajP]

The End of file is not failing. It has to check both conditions of the OR so it also checks rs![Interval ID 1] = "O" which fails when it is at EOF.

   Do Until rs.EOF
      If rs![Interval ID 1] = "O" Then Exit Do
        rs.Edit
        rs!Field7 = strHeader
        rs.Update
        iModified = iModified + 1
        rs.MoveNext
    Loop

 

[Gasman]

I would have thought it would stop when it found the first condition to be True?, no need to check any other?

Ah well, that makes the code a little simpler as well.

Thank you @MajP

 

[MajP]

This has to do with logical and bitwise operators and "Short Circuiting". If interested why

Logical and Bitwise Operators - Visual Basic

Learn more about: Logical and Bitwise Operators in Visual Basic

docs.microsoft.com

There are advantages and disadvantages of short circuiting.

 

[Gasman]

Wow!, thank you @MajP

I've never heard of that, but sadly I tried the OrElse and that is not allowed in Access 2007 at least.?
At least I am aware of this now and I simplified the code a fair bit with no repetition.

Sub CopyHeader()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String, strHeader As String
Dim iModified As Integer

strSQL = "Select ID, Field7, [Description 1], [Interval ID 1] FROM LTB"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

Do While Not rs.EOF
    If rs![Interval ID 1] = "O" Then
        strHeader = rs![Description 1]
    End If

    rs.Edit
    rs!Field7 = strHeader
    rs.Update
    iModified = iModified + 1
    rs.MoveNext

Loop
rs.Close
MsgBox "Number of records updated was " & iModified
Set rs = Nothing
Set db = Nothing

End Sub

 

[The_Doc_Man]

VBA does not use short-circuiting of its logical operators because they are always treated as BITWISE operators.

Does VBA support short-circuit operators?

social.msdn.microsoft.com

If they are bitwise operators then they cannot skip use of the right-hand operand. MajP's article relates to VB, not VBA. There are a few differences between the languages and logical short-circuiting is one of them.

 

[Gasman]

Thank you @The_Doc_Man , at least I am aware of it now.

 

[MajP]

I tried the OrElse and that is not allowed in Access 2007 at least.?

That is a VB reference not a VBA reference. Those do not exist in VBA, but I thought the discussion was good because some languages support logical short circuiting.

 

[The_Doc_Man]

@MajP - actually, I recall that discussion occurring some years ago, I believe with Pat Hartman, about whether Access did any kind of expression short-circuiting at all. It was so long ago that I can't even remember which version of Access was being discussed, but definitely pre-Ac2003.