Subform Using a Stored Procedure RecordSource with Optional Parameters

  • Thread starter Thread starter Robert Robichaud
  • Start date Start date
R

Robert Robichaud

Would like to tie a subform to a SQL 2000 Stored Procedure.

The subform (OrderDetails) Record Source contains the name of the Stored
Procedure. The subform's Input Parameter points to 1 of 3 optional
parameters as @OrderID=frm.[Orders].OrderID where the form Orders is the
Parent form containing the OrderDetails subform. I expect this to return
all OrderDetails records with the same OrderID as the Orders form.

I can run the Stored Procedure in Query Analyzer but when I open the Orders
form, I'm prompted for each of the optional parameters. If I cancel the
prompts, the Orders form opens but the OrderDetails subform is blank.

Is this the correct way or is there an alternative?
 
Robert,

I wonder if the problem is with the optional parameters. Would you mind
posting the SP?

-Andy
 
Andy,

Here it is:

ALTER PROCEDURE spOrderDetailsSubform
@OrderID varchar(8) = NULL,
@Categories varchar(32) = NULL,
@ProductClass varchar(32) = NULL

AS

DECLARE @sqlstr as varchar(512)

SET @sqlstr = 'SELECT [OrderDetails Temp].*
FROM [OrderDetails Temp]
WHERE ((([OrderDetails Temp].OrderID)=' + @OrderID + ')'

IF (@Categories IS NOT NULL)
SET @sqlstr = @sqlstr + ' AND ([OrderDetails Temp].CategoryID IN
(' + @Categories + '))'

IF (@ProductClass IS NOT NULL)
SET @sqlstr = @sqlstr + ' AND ([OrderDetails Temp].ProductClass
IN (' + @ProductClass + '))'

SET @sqlstr = @sqlstr + ') ORDER BY [OrderDetails Temp].CategoryID DESC,
[OrderDetails Temp].ProductID;'

EXECUTE(@sqlstr)


Regards,

Robert


Andy Williams said:
Robert,

I wonder if the problem is with the optional parameters. Would you mind
posting the SP?

-Andy

Robert Robichaud said:
Would like to tie a subform to a SQL 2000 Stored Procedure.

The subform (OrderDetails) Record Source contains the name of the Stored
Procedure. The subform's Input Parameter points to 1 of 3 optional
parameters as @OrderID=frm.[Orders].OrderID where the form Orders is the
Parent form containing the OrderDetails subform. I expect this to return
all OrderDetails records with the same OrderID as the Orders form.

I can run the Stored Procedure in Query Analyzer but when I open the Orders
form, I'm prompted for each of the optional parameters. If I cancel the
prompts, the Orders form opens but the OrderDetails subform is blank.

Is this the correct way or is there an alternative?
Click to expand...
Click to expand...
 
Back
Top