Access ADP and Stored Procedure Did not Return Any Records

[Lin100]

Access ADP and Stored Procedure Did not Return Any Records
Access 2002 and SQL 2000 Server

I have a form named "Selector", and it have four combo boxes and a subform named
Q_FilteringQuery_subform. When any of the combo box is selected, the code will
call a VBA procedure which will then activate the stored procedure named Get_Records_For_Selector_Form.
When I first select an item from the combo box Dept while all three other combo boxes are blank,
I got an error message:
Error Message "P1 is not a parameter for procedure Get_Records_For_Selector_Form"

Because of the error, no new records is displayed on the subform "Q_FilteringQuery_subform".

////////////////////////////////////////////////////////////////////////////////////////////////////////////

Private Sub Activate_Stored_Procedure_To_Obtain_Records_For_Se lector_Form()
Dim SQL_Form As String
Dim strStep As String

SQL_Form = "Exec [Get_Records_For_Selector_Form] "
strStep = ""

If Not IsNull(Forms!Selector!From_Date) And Not IsNull(Forms!Selector!To_Date) Then
SQL_Form = SQL_Form & strStep & " @From_Date = " & "'" & Me.From_Date & "'"
strStep = ","
SQL_Form = SQL_Form & strStep & " @To_Date = " & "'" & Me.To_Date & "'"
strStep = ","
End If

If Not IsNull(Me.Dept) Then 'Department
SQL_Form = SQL_Form & strStep & " @Department = " & "'" & Me.Dept & "'"
strStep = ","
End If

If Not IsNull(Me.so) Then 'SO_Number
SQL_Form = SQL_Form & strStep & " @SO_Number = " & "'" & Me.so & "'"
strStep = ","
End If

If Not IsNull(Me.Item) Then 'Item_Number
SQL_Form = SQL_Form & strStep & " @Item_Number = " & "'" & Me.Item & "'"
strStep = ","
End If

If Not IsNull(Me.Sectionno) Then 'Section_Number
SQL_Form = SQL_Form & strStep & " @Section_Number = " & "'" & Me.Sectionno & "'"
strStep = ","
End If

Me.Q_FilteringQuery_subform.Form.RecordSource = SQL_Form
End Sub

////////////////////////////////////////////////////////////////////////////////////////////////////////////

The stored procedure named Get_Records_For_Selector_Form
asked for 6 optional parameters.

CREATE PROCEDURE Get_Records_For_Selector_Form
@From_Date smalldatetime = Null, @To_Date smalldatetime = Null,
@Department int = Null, @SO_Number int = Null,
@Item_Number varchar(10) = Null,
@Section_Number nvarchar(3) = Null

AS SELECT DISTINCT [1_Job - Parent].SONumber, [1_Job - Parent].Department_Name, [1_Job - Parent].ItemNumber, [1_Job - Parent].SectNumber, [1_Job - Parent].RecordInitiateDate, [1_Job - Parent].MechUser, [1_Job - Parent].ElecUser, [1_Job - Parent].GreenTagUser, [1_Job - Parent].GreenTagDate, Ref_DepartmentID.ID
FROM Ref_DepartmentID RIGHT JOIN [1_Job - Parent]
ON Ref_DepartmentID.ID = [1_Job - Parent].DepartmentID
WHERE @From_Date >= isnull( @From_Date, dbo.[1_Job - Parent].RecordInitiateDate)
AND @To_Date <= isnull( @To_Date, dbo.[1_Job - Parent].RecordInitiateDate)
AND dbo.Ref_DepartmentID.ID = isnull(@Department, dbo.Ref_DepartmentID.ID)
AND SONumber = isnull(@SO_Number, dbo.[1_Job - Parent].SONumber)
AND ItemNumber = isnull(@Item_Number, dbo.[1_Job - Parent].ItemNumber)
AND SectNumber = isnull(@Section_Number, dbo.[1_Job - Parent].SectNumber)
ORDER BY [1_Job - Parent].RecordInitiateDate DESC
GO

[Stewart Ross]

Hi. All of what you have listed seems OK, at least as far as can be determined by checking the lines as written. As the error is occurring when you select a value from the department combo, could you supply the After Update code for this control? It may be that the After Update is doing something that would explain why you receive the message referring to P1 which does not appear in the code you have supplied.

-Stewart