How Vile Is "GoTo" To You?

[Micron]

Many posts on problems with using GoTo statements, but I didn't see a post for opinions of it. Long ago I read somewhere how utterly disdainful the use of GoTo was to direct code execution so I have avoided it like the plague. After a few years of this, I am rethinking my position. For example, let's say I have declared and SET several objects such as currentdb, rs, qdef's etc. If I decide to exit early because no records are returned, I should close/destroy objects beforehand. However, I don't see the sense in doing that again just before the end statement:



set db = currentdb
set rs = db.openrecordset("qryMyQuery", dbopendynaset)
set etc. etc
if rs.recordcount = 0 then

set db = nothing
rs.close
set rs = nothing
set etc. etc. = nothing
exit sub

end if
other code when not exiting early.....

exitHere:
set db = nothing
rs.close
set rs = nothing
set etc. etc. = nothing
exit sub

errHandler:
error stuff
resume exitHere

Why not
if rs.recordcount = 0 then goto exitHere and forget the first block of code that destroys the objects?

What are your thoughts on using GoTo?

[June7]

I only use GoTo for branching to exit or error handler, never to anywhere else in code. So in your example, code would be:

If rs.RecordCount = 0 Then
GoTo exitHere
End If

[Micron]

Since you are only testing for and taking one action, a one-liner is my habit: If rs.recordcount = 0 Then GoTo exitHere
but I suppose it's a moot point. Nothing wrong with your way either. I hope a few others weigh in too because I'm curious as to whether or not you and I are a small crowd. I've done it the other way for a few years now.

[June7]

Yes, one-liner is okay, although more practical code could be:

If rs.RecordCount <> 0 Then
'do all this stuff here
End If

Eliminates the GoTo exitHere line.

Main point is, the clean-up code is not repeated and is at the end of procedure.

[Rawb]

As a general rule, I won't use GoTo statements except as an error handler. And the only reason I use it there is because of VBA's poor support for traditional try/catch style error handling (you can do it, but it's messy).

While using it for a one-liner isn't the worst thing you can do, allowing them in code at all makes it easier to run into situations where "well, I'm really pressed for time and this is easier, so I'll use it here too." And that can cause things to spiral out of control really fast if you let it.

I have to work regularly with a database that shows the dangers of using GoTo indiscriminately: I've had to debug while loops, nested in for loops, nested in If statements, all with GoTo statements jumping execution in and out almost at random. It's kind of turned me into a hard-liner "NO GOTO STATEMENTS! EVER!" guy.

Once (and never again! ), I even tried rewriting one of those code blocks to no longer use GoTo statements. It ended up cutting the number of lines by about 10% (out of about 700 lines of code) and made things easier to read to boot.

[nick404]

To weigh in...
I have not been doing extensive vb programming, have only just began experimenting. One database I made used a button for a user to "save entry" and proceed to a clean entry form for more data input while "saving" what was just entered.

DoCmd.GoToRecord , , acNewRec

A week later when the database became populated with data, the button stopped working on the shared drive version and would no longer go to a new record. It just stayed on the same one (but worked just fine on my local copy which only had some dummy data). Not sure if this catalyzes intriguing discussion but just throwin' it out here.

[ItsMe]

The way I see it is, if I need three versions of exitHere (within the same sub) I should probably be using a separate sub procedure or function to take care of certain tasks. Having said that, I do use Goto.

In a perfect world I suppose I would try to replace Goto with a Call to a sub/function if the GoTo was not cleaning things up within the current scope of the sub procedure.

[Micron]

Overdoing anything is bad practice for sure. For me, I was so hell bent on not using it lest I left some sort of negative impression, that I took to destroying/closing everything as many times as needed rather than goto exitHere. So if there were 2 IF statements that could call for an exit sub, I'd do the clean up for each, and in the exitHere where I knew error handling might direct execution to. I just couldn't see anymore why I'd do that to show I wasn't a newbie dweeb because someone said that's who uses GoTo. As for calling a function to do this, I don't see it. You would not know how many objects you'd have to pass to it, and if you're going to write more than one to get around using GoTo, that just doesn't seem practical to me.

[ItsMe]

... As for calling a function to do this, I don't see it....

I don't think it is possible to call a function to close objects contained within another procedure. The objects would be out of scope and not be available to be cleaned up. You would probably get a Runtime error.

I was referring to times when I have used GoTo in order to skip over lines of code simply because I did not want them executed and in order to control the flow of statements. I suppose people would be inclined to point and laugh saying that I need to pay attention to the separation of concerns principle and do a better job of encapsulation. So, in a perfect world, I would create a function or sub procedure and call that function or sub procedure as needed, avoiding the use of GoTo.

[Micron]

I think you'd be right about such a function being out of scope (what was I thinking) but I mis-interpreted what you wrote. I am going to be using GoTo in favour of multiple clean-ups from now on.

[IrogSinta]

The example you gave is not a good one since, as June7 pointed out, you can revise your code to If rs.RecordCount <> 0 Then...
Do you have any other code examples where you think GoTo is the only possible way. Most of the time, all that's needed is a little bit of logical rearranging to avoid using GoTo. I tend to prefer writing code without using GoTo though I must admit I can recall 3 times in the past 20 years that I couldn't find a way around it which bummed me a bit because I like writing efficient and concise code.
Just my 2 cents :-)

Ron

[Micron]

I missed that in the second example, June7 reversed the test from =0 to <>. I never thought much about testing that the condition is the NOT what I expect or want it to be and directing flow based on that. So I now see the logic in what was written. Another simple situation I have is where a string should be "", otherwise one or more required controls have no data.
If svlist2 <> "" Then

MsgBox svList & svlist2, vbOKOnly, "Missing Information"
Exit Sub

End If

To write
If svList2="" then

do things
do more things

end if

means everything in between is dependent on the conditions being right rather than testing for failure in a compartmentalized way and bugging out then. I'm thinking that if there are multiple conditions that have to be tested, all will have to be nested. If I understand your meaning, you would do this:

If svList = "" then 'good, no required data missing

do some stuff
If intTest >0 Then

keep doing stuff
If a < b then

msgBox "So far, so good"
set rs = db.operecordset ("someQuery")
if rs.count<>0 then

select case rs.recordcount

case 1

msgbox "message if 1 record"

case is >1

msgbox "message if more than 1"

case moreCases

do moreCases stuff

end select

end if

end if

end if

end if

If that's how you do it, I don't say it is wrong, but I don't think I will adopt that approach. I will look for better examples tomorow.
Thanks.

[IrogSinta]

Well as much as I try to avoid GoTo, I try to avoid multiple nested If statements as well. This can be done by breaking the code up into smaller functions, or doing all the validations at the beginning and using IF..EndIf statements along with setting a flag variable. For instance you can set a Boolean variable such as IsValid or HasErrors during the validation and later check that flag to skip the rest of the process. For example:

    Dim sMessage As String

    If svList = "" Then
        sMessage = "Missing Information"
        
    ElseIf intTest <= 0 Then
        sMessage = "Number has to be greater than 0"
        
    ElseIf a < b Then
        sMessage = "a cannot be less than b"
        
    End If
    
    
    If sMessage <> "" Then
        MsgBox sMessage, , "Something Is Wrong"
        
    Else
        Set rs = db.operecordset("someQuery")
        If Not rs.EOF Then
    
            Select Case rs.RecordCount
                Case 1
                    MsgBox "message if 1 record"
                
                Case Is > 1
                    MsgBox "message if more than 1"
                
                Case moreCases
                    'do moreCases stuff
                    
            End Select
        End If
    End If

Having said this, I have no problem with using GoTo in order to avoid having to repeat code because I don't find it vile at all, but what I do really dislike is unnecessary code repetition.

[Micron]

My intention was to convey that these were independent tests, not alternate possibilities.

If svList = "" Then
sMessage = "Missing Information"

ElseIf intTest <= 0 Then
sMessage = "Number has to be greater than 0"

ElseIf a < b Then
sMessage = "a cannot be less than b"

End If etc. etc.

I think we are on the same page regarding repetition and the use of GoTo (at least now that I intend to use it only where it makes sense (i.e. avoids repeating clean up code, but not to direct flow otherwise).

[Missinglinq]

I'm amazed to read this thread, and all of the replies, and never once see the phrase 'SPAGHETTI CODE!' The phrase goes back to the late 70s/early 80s, about the time that programming languages (QuickBasic 4.5, which was the precursor to VBA, being one) became more structured and modular in nature. The objection to the extensive use of GoTo, especially in complex apps, was that it made following the flow of execution of code very, very difficult...and hence made debugging very, very difficult.

Using GoTo within a given Sub/Function, such as in going to an error-handler, doesn't present this problem, because you're dealing with a relatively small piece of real estate.


nick404: The GoTo being discussed, here, has nothing to do with

DoCmd.GoToRecord , , acNewRec

Is it possible the users' copies of Access haven't had the folder holding the app declared as a 'Trusted Location?' That would keep any code from executing.

Another reason that your Command Button works on your local copy, but doesn't on the distributed copies, could lie in this bit of your explanation:

"...the button stopped working on the shared drive version..."

Are your users sharing a single copy of the database, or sharing a single copy of the Front End, located on a shared drive? If so, this can cause an infinite number of strange, odd and curious problems, including the one you're experiencing. While the Back End properly goes on a shared drive, a copy of the Front End has to be on each user's hard drive.

If you still have problems with this you need to start a new thread on the subject.

Linq ;0)>