VBA and stored procedure code that handle optional

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

VBA and stored procedure code that handle optional

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2008-02-28 : 03:40:53

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 04:07:20

Try this:-

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

Private Sub Dept_AfterUpdate()
Dim iDept As Integer

'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 & "'"

/////////////////////////////////////////////////////////////////
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 OR @iDept IS NULL)
GO

I doubt whether you need that '' after EXEC

Lin100
Yak Posting Veteran

70 Posts

Posted - 2008-02-28 : 04:53:24

Thank you visakh16 for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources