How write a VBA and stored procedure code that handle optional parameter.

How write a VBA and stored procedure code that handle optional parameter.
Access Version: 2002
SQL Version : SQL 2000 Server

I have an ADP Access database and a form that has a combo box named Dept.
When a user click on the Dept combo box; if the value is not null then
run the stored procedure Get_SO_After_Dept_Update_1.
If however, the value is null then
run the stored procedure Get_SO_After_Dept_Update_2.

Everything work very well. Now how do I go about to modified the VBA code
and the Stored Procedure code so that I will only have one stored
procedure to deal with instead of two. Another word, whether the value in
the Dept combo box is null or not null the VBA and the stored
procedure code will run.


Combo Box Name : Dept
Control Source :
Row Source Type: Table/View/StoredProc
Row Source :

////////////////////////////////////////////////////////////////
VBA Code in an Access ADP Form

Private Sub Dept_AfterUpdate()
Dim iDept As Integer

If Not IsNull(Me.Dept) Then
'Get_SO_After_Dept_Update_1 is a stored procedure that resides in SQL 2000 Server
Me.so.RowSource = "Exec [Get_SO_After_Dept_Update_1]'" & Me.Dept.Value & "'"
Else
'Get_SO_After_Dept_Update_2 is a stored procedure that resides in SQL 2000 Server
Me.so.RowSource = "Exec [Get_SO_After_Dept_Update_2]"
End If
End Sub

/////////////////////////////////////////////////////////////////
Stored Procedure Get_SO_After_Dept_Update_1

CREATE PROCEDURE Get_SO_After_Dept_Update_1
@iDept int
AS SELECT DISTINCT [1_Job - Parent].SONumber, Ref_DepartmentID.ID
FROM dbo.[1_Job - Parent]
INNER JOIN dbo.Ref_DepartmentID
ON dbo.[1_Job - Parent].Department_Name = dbo.Ref_DepartmentID.DepartmentName
WHERE dbo.Ref_DepartmentID.ID = @iDept
GO

/////////////////////////////////////////////////////////////////
Stored Procedure Get_SO_After_Dept_Update_2

CREATE PROCEDURE Get_SO_After_Dept_Update_2
AS SELECT DISTINCT [1_Job - Parent].SONumber, Ref_DepartmentID.ID
FROM dbo.[1_Job - Parent]
INNER JOIN dbo.Ref_DepartmentID
ON dbo.[1_Job - Parent].Department_Name = dbo.Ref_DepartmentID.DepartmentName
GO
Start Free Trial