LISTING 2: VBA Stored Procedure Showing the Syntax for BEGIN…END Blocks and RETURN Keywords with an IF…ELSE Statement

Sub AcceptingAConditionalReturn()
Dim cmd1 As ADODB.Command
Dim rst1 As ADODB.Recordset
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim msg As String
Dim mytitle As String
Dim strState As String

'Instantiate a Command object pointing
'at a stored procedure
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandText = "spAuthorsInAState"
cmd1.CommandType = adCmdStoredProc

'Create two parameters: one for input to the
'stored procedure and another for a T-SQL
'RETURN value from the stored procedure
Set prm1 = cmd1.CreateParameter _
    ("Return", adInteger, adParamReturnValue)
cmd1.Parameters.Append prm1
Set prm2 = cmd1.CreateParameter _
    ("@mystate", adVarChar, adParamInput, 2)
cmd1.Parameters.Append prm2

'Assign a state abbreviation to one parameter
'before executing the Command object
msg = "Enter a two-letter state abbreviation"
mytitle = "Enter a state for authors"
strState = InputBox(msg, mytitle, "CA")
prm2.Value = strState
cmd1.Execute

'If the RETURN value from the stored procedure
'is 1, then open a form based on the return set
'from it; otherwise, display a message
'indicating there is no return set
Set rst1 = New ADODB.Recordset
If cmd1("RETURN") = 1 Then
    rst1.Open cmd1
    DoCmd.OpenForm "frmFromSQLServer", _
        acNormal
    Set Forms("frmFromSQLServer").Recordset = _
        rst1
'Close the recordset when no longer needed
    rst1.Close
Else
    MsgBox "No values returned"
End If

'Clean up
Set rst1 = Nothing
Set prm1 = Nothing
Set prm2 = Nothing
Set cmd1 = Nothing

End Sub