Access ADP and Stored Procedure error

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

Transact-SQL (2000)

Access ADP and Stored Procedure error

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2007-01-08 : 20:53:37

Access 2003 and SQL 2000 Server.

I have a form that have two combo boxes (Dept, and SO) and a subform (Q_FilteringQuery subform).
When I first clicked on the combo box named Dept, Access pop-up a dialog box asking me to enter
a value for a variable that was declared in the stored procedure (@SO_Param). This should not have
happened. next, I clicked the OK button without inputing the value for @SO_Param, and then I
select the combo box Dept. Then an error comes up

Run-Time error '8145'
P1 is not a parameter for procedure sp_getsubfrm_recs

The variable P1 was never declared anywhere in the VBA code nor was it ever declared in
the stored procedure.

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

Private Sub Dept_AfterUpdate()
Dim strsql As String
Dim iDept As Integer

If Not IsNull(Me.Dept) Then
iDept = CInt(Me.Dept)
strsql = "SELECT DISTINCT [1_Job - Parent].SONumber, Ref_DepartmentID.ID"
strsql = strsql & " FROM dbo.[1_Job - Parent] INNER JOIN"
strsql = strsql & " dbo.Ref_DepartmentID"
strsql = strsql & " ON dbo.[1_Job - Parent].Department_Name = dbo.Ref_DepartmentID.DepartmentName"
strsql = strsql & " WHERE dbo.Ref_DepartmentID.ID = " & iDept
Else
strsql = "SELECT DISTINCT [1_Job - Parent].SONumber, Ref_DepartmentID.ID"
strsql = strsql & " FROM dbo.[1_Job - Parent] INNER JOIN"
strsql = strsql & " dbo.Ref_DepartmentID"
strsql = strsql & " ON dbo.[1_Job - Parent].Department_Name = dbo.Ref_DepartmentID.DepartmentName"
End If

Me.so.RowSource = strsql

'call sub
get_subfrm_recs

End Sub

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

Private Sub SO_AfterUpdate()
Dim strsql As String
Dim LgSO As Long
Dim iDept As Integer

If Not IsNull(Me.Dept) Then
iDept = CInt(Me.Dept)
End If

If Not IsNull((Me.so)) Then
LgSO = CLng(Me.so)

strsql = "SELECT DISTINCT [1_Job - Parent].ItemNumber, Ref_DepartmentID.ID"
strsql = strsql & " FROM dbo.[1_Job - Parent] INNER JOIN"
strsql = strsql & " dbo.Ref_DepartmentID"
strsql = strsql & " ON dbo.[1_Job - Parent].Department_Name = dbo.Ref_DepartmentID.DepartmentName"
strsql = strsql & " WHERE dbo.Ref_DepartmentID.ID = " & iDept
strsql = strsql & " AND SONumber =" & LgSO
strsql = strsql & " ORDER BY ItemNumber"

Me.Item.RowSource = strsql
get_subfrm_recs 'call procedure
Else
strsql = "SELECT DISTINCT ItemNumber"
strsql = strsql & " FROM [1_Job - Parent] "
strsql = strsql & " ORDER BY ItemNumber"

Me.Item.RowSource = strsql
get_subfrm_recs 'call procedure
End If
End Sub

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

Private Sub get_subfrm_recs()
On Error GoTo Err_show

Dim p_Ref_DepartmentID As Integer
Dim p_SO As Long
Dim StrRS As String

StrRS = "Exec sp_get_subfrm_recs "

If Not IsNull(Me.Dept) Then
p_Ref_DepartmentID = CInt(Me.Dept)
End If

If Not IsNull(Me.so) Then
p_SO = CLng(Me.so)
End If

If Not IsNull(Me.Dept) And IsNull(Me.so) Then
StrRS = StrRS & "@param1 = " & p_Ref_DepartmentID & ","
Else
If Not IsNull(Me.Dept) And Not IsNull(Me.so) Then
StrRS = StrRS & "@param1 = " & p_Ref_DepartmentID & "," & " @SO_param = " & p_SO & ","
Else
If IsNull(Me.Dept) And Not IsNull(Me.so) Then
StrRS = StrRS & " @SO_param = " & p_SO & ","
End If
End If
End If

StrRS = Left(StrRS, Len(StrRS) - 1)

Me.Q_FilteringQuery_subform.Form.RecordSource = StrRS <--- ERROR. YELLOW HIGHLIGHT

If Me.Q_FilteringQuery_subform.Form.RecordsetClone.RecordCount = 0 Then
Me.Q_FilteringQuery_subform.Visible = False
Else
Me.Q_FilteringQuery_subform.Visible = True
End If

Exit Sub

Err_show:
MsgBox "Error: " & Err & " " & Err.Description
MsgBox "Error", Err.HelpFile, Err.HelpContext

If Err.Number <> 0 Then
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

End Sub

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

CREATE PROCEDURE sp_get_subfrm_recs
(@param1 Int = Null ,
@SO_Param Int = Null
)
As
SELECT DISTINCT
[1_Job - Parent].Department_Name, [1_Job - Parent].SONumber,
[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
FROM [1_Job - Parent] INNER JOIN Ref_DepartmentID
ON [1_Job - Parent].DepartmentID = Ref_DepartmentID.ID
WHERE [1_Job - Parent].DepartmentID = coalesce(@param1,[1_Job - Parent].DepartmentID)
AND [1_Job - Parent].SONumber = coalesce(@SO_Param,[1_Job - Parent].SONumber)
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources