Exiting other Subs within a Sub

[muttsylove]

Hi folks! I'm new to Access and VBA and just want to say that this site has TreMENdousLY aided in my understanding and coding development (cause I don't think I would've learned, gotten as far, and as quickly, as I have!)

Now moving along.. I have a question with regards to being able to exit other subs within a sub and whether there is an easy way to do this?

BACKGROUND: I have a MAIN sub which calls about 5 other subs. If the 1st sub that is called, fails, I have error handling to fail and exit the 1st sub. However, I would ALSO LIKE the 1st sub to be able to handle exiting the MAIN sub (to avoid having the MAIN sub running the other 4 remaining sub procedures).

TO NOTE: A co-worker of mine had suggested to change my 5 Subs into Functions and set it to Boolean. From there, set the function equal to True (to indicate success at the end of the process) or False in the appropriate places in the function. Thereafter, create "IF" statements within the MAIN sub to run a check on the function values.. [I hope I'm making sense!]

QUESTION: Though..I figured there Must( ) be a more efficient way (code-wise) to do this and was hoping you experts out there might be able to help?

If you happen to know of a better way, appreciate if you can provide a code sample as it would aid in my understanding!

FYI: If needed, below is a code snippet (written as Subs..)

Private Sub RunLoad()
On Error GoTo RunLoad

Call ValidateTempData
Call yadda
Call yadda1
Call yadda2
Call yadda3

Exit_RunLoad:
    Exit Sub

Err_RunLoad:
    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "RunLoad - FAILED"
    Resume Exit_RunLoad

End Sub

*******************************************************
Public Sub ValidateTempData()
On Error GoTo Err_ValidateTempData
Dim db As DAO.Database
Dim rsLookup As DAO.Recordset
Dim strSQLBadRecords As String

strSQLBadRecords = "SELECT [NAME1] " & _
                   "  FROM VALIDATE_DATA"


Set db = CurrentDb()
Set rsLookup = db.OpenRecordset(strSQLBadRecords)

If rsLookup.RecordCount <> 0 Then
    MsgBox "Bad records encountered in temp table."
    GoTo Exit_ValidateTempData [color=green]'EXIT THIS SUB. [b]But also can it handle exiting Sub Runload?[/color][/b]
Else
    Debug.Print "Stage: ValidateTempData - Completed"
End If

rsLookup.Close
Set rsLookup = Nothing
db.Close
Set db = Nothing


Exit_ValidateTempData:
    Exit Sub

Err_ValidateTempData:
    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "ValidateTempData - FAILED"
    Resume Exit_ValidateTempData

End Sub

FYI: Code snippet (written as a Sub with underlying Functions..)

Private Sub RunLoad()
On Error GoTo RunLoad

Call ValidateTempData
If ValidateTempData = True Then
	Call yadda
	If yadda = True Then
		Call yadda1
		[color=blue]If...and so forth and so on.....[/color]
	Else
		Msgbox "RunLoad-yadda1 Failed..."
	End If
Else
	Msgbox "RunLoad-ValidateTempData Failed..."
	Exit Sub
End If

Exit_RunLoad:
    Exit Sub

Err_RunLoad:
    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "RunLoad - FAILED"
    Resume Exit_RunLoad

End Sub

*******************************************************
[color=green]'Public Sub ValidateTempData()[/color]
Public Function ValidateTempData() As BOOLEAN
On Error GoTo Err_ValidateTempData
Dim db As DAO.Database
Dim rsLookup As DAO.Recordset
Dim strSQLBadRecords As String

strSQLBadRecords = "SELECT [NAME1] " & _
                   "  FROM VALIDATE_DATA"


Set db = CurrentDb()
Set rsLookup = db.OpenRecordset(strSQLBadRecords)

If rsLookup.RecordCount <> 0 Then
    MsgBox "Bad records encountered in temp table."
[color=green]'    GoTo Exit_ValidateTempData 'EXIT THIS SUB, *BUT ALSO* BE ABLE TO EXIT SUB RUNLOAD() [/color]
    ValidateTempData = False
    Exit Function
Else
    Debug.Print "Stage: ValidateTempData - Completed"
End If

rsLookup.Close
Set rsLookup = Nothing
db.Close
Set db = Nothing

ValidateTempData = True [color=green]'set to True upon success[/color]

Exit Function

[color=green]'Exit_ValidateTempData:
'    Exit Sub[/color]

Err_ValidateTempData:
    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "ValidateTempData - FAILED"
[color=green]'    Resume Exit_ValidateTempData

'End Sub[/color]
End Function

 

[MarkK]

Functions returning booleans will work, but another option is the use of error handlers and raising custom errors.
In your code, if yadda runs and completes without error then yadda1 will run, but if an unhandled error occurs in yadda, control is passed to the error handler in RunLoad. Within yadda, you can use syntax like...

Err.Raise 40000, "Your Source", "Your Description"

...to cause a custom error to occur, return rich information about what went wrong, and leave it up to the calling routine's error handler to decide how to proceed.

For more info: Read "Elements of runtime error handling" in Access help.

 

[muttsylove]

Thanks lagbolt for the details! Thanks also for the search string for the Access help. I shall read up on this some more!