Solved What am I missing in my logic here? (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 10:12
Joined
Sep 21, 2011
Messages
14,038
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

Code:
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
 

Attachments

  • LTB Header.zip
    50.1 KB · Views: 470

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:12
Joined
May 21, 2018
Messages
8,463
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.
Code:
   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

Enthusiastic Amateur
Local time
Today, 10:12
Joined
Sep 21, 2011
Messages
14,038
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

You've got your good things, and you've got mine.
Local time
Today, 06:12
Joined
May 21, 2018
Messages
8,463
This has to do with logical and bitwise operators and "Short Circuiting". If interested why


There are advantages and disadvantages of short circuiting.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:12
Joined
Sep 21, 2011
Messages
14,038
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.

Code:
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

Immoderate Moderator
Staff member
Local time
Today, 05:12
Joined
Feb 28, 2001
Messages
26,996
VBA does not use short-circuiting of its logical operators because they are always treated as BITWISE operators.


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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:12
Joined
May 21, 2018
Messages
8,463
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

Immoderate Moderator
Staff member
Local time
Today, 05:12
Joined
Feb 28, 2001
Messages
26,996
@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.
 

Users who are viewing this thread

Top Bottom