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

Other Forums

MS Access

VBA and stored procedure code that handle optional

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2008-02-28 : 03:41:55

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

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-04 : 21:32:47

The value in the combo box in Access is not NULL, it is an empty string "" (two quotes)...there is a difference

You can also just modify the SQL procedure to have a default parameter (one that would not appear in the combo box)

in this case default = 0



CREATE PROCEDURE Get_SO_After_Dept_Update_1 (@iDept int = 0)
AS 

If @iDept >0
Begin
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
End

If @iDept = 0
Begin
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
end

Poor planning on your part does not constitute an emergency on my part.

Lin100
Yak Posting Veteran

70 Posts

Posted - 2008-03-05 : 03:38:04

Thank you dataguru1971 for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources