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_FilteringQuer y_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_FilteringQue ry_subform".

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

Private Sub Activate_Stored _Procedure_To_O btain_Records_F or_Selector_For m()
Dim SQL_Form As String
Dim strStep As String

SQL_Form = "Exec [Get_Records_For _Selector_Form] "
strStep = ""

If Not IsNull(Forms!Se lector!From_Dat e) And Not IsNull(Forms!Se lector!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.Secti onno) Then 'Section_Number
SQL_Form = SQL_Form & strStep & " @Section_Number = " & "'" & Me.Sectionno & "'"
strStep = ","
End If

Me.Q_FilteringQ uery_subform.Fo rm.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_Nam e, [1_Job - Parent].ItemNumber, [1_Job - Parent].SectNumber, [1_Job - Parent].RecordInitiate Date, [1_Job - Parent].MechUser, [1_Job - Parent].ElecUser, [1_Job - Parent].GreenTagUser, [1_Job - Parent].GreenTagDate, Ref_DepartmentI D.ID
FROM Ref_DepartmentI D RIGHT JOIN [1_Job - Parent]
ON Ref_DepartmentI D.ID = [1_Job - Parent].DepartmentID
WHERE @From_Date >= isnull( @From_Date, dbo.[1_Job - Parent].RecordInitiate Date)
AND @To_Date <= isnull( @To_Date, dbo.[1_Job - Parent].RecordInitiate Date)
AND dbo.Ref_Departm entID.ID = isnull(@Departm ent, dbo.Ref_Departm entID.ID)
AND SONumber = isnull(@SO_Numb er, dbo.[1_Job - Parent].SONumber)
AND ItemNumber = isnull(@Item_Nu mber, dbo.[1_Job - Parent].ItemNumber)
AND SectNumber = isnull(@Section _Number, dbo.[1_Job - Parent].SectNumber)
ORDER BY [1_Job - Parent].RecordInitiate Date 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