can you explain this loop ? (1 Viewer)

[smig]

Can you explain this loop, in the attached video ?
Error in open db

The path for the strFileName is wrong

I run this routine only.
I made it as a SUB to run it directly. Not calling it

Here is the VBA code

Public Sub TEST_XXX()

On Error GoTo errHere


Dim strFileName As String
Dim strFilePassword As String
Dim sngLatestVersion As Single

MsgBox "_TEST_XXX - 1"

pbDataPath = GetSetting("Tal Fuchs", pbAppFileName, "DataPath")

strFileName = pbDataPath & "\XXX" & pbDataFileName
strFilePassword = pbDataFilePass

MsgBox "strFileName - " & strFileName

Set DataDB = OpenDatabase(strFileName, False, False, "MS Access; PWD=" & strFilePassword & "")

MsgBox "_TEST_XXX - 2"

strSQL = "SELECT * " & _
    "FROM [dbVersions] " & _
    "ORDER BY [dbVersion] DESC "

MsgBox "_TEST_XXX - 3"

Set rs = DataDB.OpenRecordset(strSQL)

MsgBox "_TEST_XXX - 4"

With rs
    If .RecordCount > 0 Then
        .MoveFirst
        sngLatestVersion = .Fields("dbVersion")
        .Close
    Else
        sngLatestVersion = 0
    End If
End With

MsgBox "_sngLatestVersion = " & sngLatestVersion

MsgBox "_TEST_XXX - 5"


ExitHere:
    MsgBox "_TEST_XXX - ExitHere"
  
    Set rs = Nothing
    Set DataDB = Nothing
  
  
    pbErr = Err
    pbErrDescription = Err.Description
    MsgBox "Error - " & pbErr & "-" & pbErrDescription
  
    Exit Sub

errHere:
    pbErr = Err
    pbErrDescription = Err.Description
    Resume errHandler
  
errHandler:
    Set rs = Nothing
    Set DataDB = Nothing
    MsgBox "_TEST_XXX - errHere"
    MsgBox "Error - " & pbErr & "-" & pbErrDescription
  
    MsgBox "_TEST_XXX - errHere 2"
    MsgBox Err
    Call pbCleanAfterError

End Sub

This one is to explain thread 320778 more clearly

 

[The_Doc_Man]

You've got an error outside of this code that is re-calling it. According to these time-hacks from the video...

0:03 - shows filename
0:05 - "errHere"
0:06 - Error 3024 (cannot find named file)
0:07 - Test XXX #1
0:08 - shows filename
0:10 - Test XXX #2
0:11 - Test XXX #3
0:12 - Test XXX #4
0:13 - Test XXX #5
0:14 - Exit Here
0:14 - Err 0

The loop is not inside this routine that I can see. It hit all of your waypoints in linear (i.e. no loop) order and thus did not loop internally.

Put a breakpoint in this code. When it breaks, using the VBA page, open the View >> Call Stack panel. You should be able to see the name of the routine that called this one. Double-click that caller entry. The VBA code screen will shift to that call. Put a breakpoint at the next instruction there. Use the single-step feature to get to there.

The "Err 0" merely means that the Err Object has been released. I'm not sure, but I think the "Resume" did that.

The only other thing I can see that is an unknown here is that you call that pbCleanAfterError routine. What does it do? Show us. Because I don't see anything here that would cause an error. HOWEVER, it should be noted that once you dismiss the prior error using a Resume verb, your prior declaration of an error handler is back in force. So if there is an error in your cleanup routine, it could trigger a loop.

 

[smig]

You've got an error outside of this code that is re-calling it. According to these time-hacks from the video...

0:03 - shows filename
0:05 - "errHere"
0:06 - Error 3024 (cannot find named file)
0:07 - Test XXX #1
0:08 - shows filename
0:10 - Test XXX #2
0:11 - Test XXX #3
0:12 - Test XXX #4
0:13 - Test XXX #5
0:14 - Exit Here
0:14 - Err 0

The loop is not inside this routine that I can see. It hit all of your waypoints in linear (i.e. no loop) order and thus did not loop internally.

Put a breakpoint in this code. When it breaks, using the VBA page, open the View >> Call Stack panel. You should be able to see the name of the routine that called this one. Double-click that caller entry. The VBA code screen will shift to that call. Put a breakpoint at the next instruction there. Use the single-step feature to get to there.

The "Err 0" merely means that the Err Object has been released. I'm not sure, but I think the "Resume" did that.

The only other thing I can see that is an unknown here is that you call that pbCleanAfterError routine. What does it do? Show us. Because I don't see anything here that would cause an error. HOWEVER, it should be noted that once you dismiss the prior error using a Resume verb, your prior declaration of an error handler is back in force. So if there is an error in your cleanup routine, it could trigger a loop.

I run this routine only.
I made it as a SUB to run it directly. Not calling it

Also, it should Exit sub after not going back
0:14 - Exit Here
0:14 - Err 0

 

[The_Doc_Man]

There are a few suspicious things.

First, we have NO CLUE regarding what this does. You have dismissed this comment a couple of times in the other thread. But it is a black box that we cannot see.

Call pbCleanAfterError

Second, this segment tries to pull .RecordCount from a freshly opened recordset, but usually .RecordCount is not set until you have done something similar to a .MoveLast followed by a .MoveFirst - i.e. something to allow Access to count records. Just going in and immediately asking for a record count isn't going to cut it. BUT in all fairness, this segment SHOULDN'T get executed - ever - as long as there is a problem with the file name, because you would never get to the point of executing this if you can't open the database in question.

With rs
    If .RecordCount > 0 Then
        .MoveFirst
        sngLatestVersion = .Fields("dbVersion")
        .Close
    Else
        sngLatestVersion = 0
    End If
End With

Third, the video does not show the execution of this line:

MsgBox "_sngLatestVersion = " & sngLatestVersion

That long video sequence contains at least TWO errors. In the first segment, the attempt to open the DB after the MsgBox for "File Name" triggers the 3024 error, for which the flow in the video seems to be as expected, except that I never see "_TEST_XXX - errHere 2" - which really SHOULD be there. Therefore, the second error is something that would bypass those in-line message box outputs.

Fourth, you are using "rs" as a recordset, which is an undeclared variable, therefore in the absence of Option Explicit, it is a Variant. The same is true for "DataDB" which is also a Variant. FYI, since they are not declared locally and are not parameters, you don't have to set them to nothing in that final segment. When the code exits, they will cease to exist and will TRULY be Nothing anyway. If your "test bed" declares them externally, that would be different.

The logic flow at this point seems compromised because the code starts that loop - but every error flows through that final segment that should, but does not, show us the "errHere 2" message. Do yourself and us a favor. FOR THE PURPOSES OF TESTING, take out that call to your cleanup routine. If it behaves differently, you know where to look.

 

[smig]

Thanks for your time

But nothing can explain why it jump back to the start of the subroutine, just after it show the "_TEST_XXX - errHere" msg and the Error (Sec. 5) rather then showing "_TEST_XXX - errHere 2"

Also why will it go back to start, again, after it show the "ExitHere" and Err=0 (No error!!!) at sec. 15, rather then exiting
And again another loop on sec. 21


rs and DataDB are public variables
rs is DAO.recordset
DataDB is DAO.database

There are a few suspicious things.

First, we have NO CLUE regarding what this does. You have dismissed this comment a couple of times in the other thread. But it is a black box that we cannot see.

Call pbCleanAfterError

Second, this segment tries to pull .RecordCount from a freshly opened recordset, but usually .RecordCount is not set until you have done something similar to a .MoveLast followed by a .MoveFirst - i.e. something to allow Access to count records. Just going in and immediately asking for a record count isn't going to cut it. BUT in all fairness, this segment SHOULDN'T get executed - ever - as long as there is a problem with the file name, because you would never get to the point of executing this if you can't open the database in question.

With rs
    If .RecordCount > 0 Then
        .MoveFirst
        sngLatestVersion = .Fields("dbVersion")
        .Close
    Else
        sngLatestVersion = 0
    End If
End With

Third, the video does not show the execution of this line:

MsgBox "_sngLatestVersion = " & sngLatestVersion

That long video sequence contains at least TWO errors. In the first segment, the attempt to open the DB after the MsgBox for "File Name" triggers the 3024 error, for which the flow in the video seems to be as expected, except that I never see "_TEST_XXX - errHere 2" - which really SHOULD be there. Therefore, the second error is something that would bypass those in-line message box outputs.

Fourth, you are using "rs" as a recordset, which is an undeclared variable, therefore in the absence of Option Explicit, it is a Variant. The same is true for "DataDB" which is also a Variant. FYI, since they are not declared locally and are not parameters, you don't have to set them to nothing in that final segment. When the code exits, they will cease to exist and will TRULY be Nothing anyway. If your "test bed" declares them externally, that would be different.

The logic flow at this point seems compromised because the code starts that loop - but every error flows through that final segment that should, but does not, show us the "errHere 2" message. Do yourself and us a favor. FOR THE PURPOSES OF TESTING, take out that call to your cleanup routine. If it behaves differently, you know where to look.

 

[smig]

There are a few suspicious things.

First, we have NO CLUE regarding what this does. You have dismissed this comment a couple of times in the other thread. But it is a black box that we cannot see.

Call pbCleanAfterError

Second, this segment tries to pull .RecordCount from a freshly opened recordset, but usually .RecordCount is not set until you have done something similar to a .MoveLast followed by a .MoveFirst - i.e. something to allow Access to count records. Just going in and immediately asking for a record count isn't going to cut it. BUT in all fairness, this segment SHOULDN'T get executed - ever - as long as there is a problem with the file name, because you would never get to the point of executing this if you can't open the database in question.

With rs
    If .RecordCount > 0 Then
        .MoveFirst
        sngLatestVersion = .Fields("dbVersion")
        .Close
    Else
        sngLatestVersion = 0
    End If
End With

Third, the video does not show the execution of this line:

MsgBox "_sngLatestVersion = " & sngLatestVersion

That long video sequence contains at least TWO errors. In the first segment, the attempt to open the DB after the MsgBox for "File Name" triggers the 3024 error, for which the flow in the video seems to be as expected, except that I never see "_TEST_XXX - errHere 2" - which really SHOULD be there. Therefore, the second error is something that would bypass those in-line message box outputs.

Fourth, you are using "rs" as a recordset, which is an undeclared variable, therefore in the absence of Option Explicit, it is a Variant. The same is true for "DataDB" which is also a Variant. FYI, since they are not declared locally and are not parameters, you don't have to set them to nothing in that final segment. When the code exits, they will cease to exist and will TRULY be Nothing anyway. If your "test bed" declares them externally, that would be different.

The logic flow at this point seems compromised because the code starts that loop - but every error flows through that final segment that should, but does not, show us the "errHere 2" message. Do yourself and us a favor. FOR THE PURPOSES OF TESTING, take out that call to your cleanup routine. If it behaves differently, you know where to look.

You are correct that you need to do a .MoveLast to get the real .RecordCount number
but you can just use it with no moving just to make sure there are any records in the RecordSet

 

[The_Doc_Man]

Since you built a test-bed for your problem, you do not expect this sub IN THE TEST BED to have to do anything else. REMOVE THE FREAKIN' cleanup call for testing purposes and see if it does something different. How hard is it to comment out one line for test purposes? What is your problem here? If the cleanup routine is OK, fine. But PROVE that it is not the culprit by removing it from the mix to show that it is not responsible for whatever is happening.

From what I can tell from the logic flow showed by that video, your FIRST pass through the routine behaves correctly up to the point where you change from TRAP context to normal context based on the RESUME statement that jumps to label errHandler. But that path takes you through your cleanup routine.

I will also point out that there is another facet to be considered - that when you do the RESUME you not only revert to normal code context but per the rules of error routine declaration, you re-enable the same error handler. Which means if an error occurs in that context and the cleanup routine doesn't have its own handler, you WILL trigger a trap-loop.

but you can just use it with no moving just to make sure there are any records in the RecordSet

Are you sure about that? You don't get the message box that would reveal a putative version number. You should at least see 0, but in the video, I stepped through it in slow-motion and never saw that message.