SQL Server Performance Forum – Threads Archive

Access ADP and Stored Procedure error

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

---

Hi,
It would have been better to post this question in vb forums because problem lies there. Anyways consider this: 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 IsNull(Me.Dept) Then
StrRS = StrRS & "@param1=NULL,"
else
p_Ref_DepartmentID = CInt(Me.Dept)
StrRS = StrRS & "@param1=" & p_Ref_DepartmentID & ","
End If If IsNull(Me.so) Then
StrRS = StrRS & "@SO_param=NULL"
ELSE
p_SO = CLng(Me.so)
StrRS = StrRS & "@SO_param=" & p_SO
End If 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 Also please tell us when do you call these vb subroutines, is it on clicking event of combo boxes or something else.

---

Hi ranjitjain. There is only two places where the subroutine get_subfrm_recs gets called. 1) It is called when a user click on the combo box named Dept; such as
Dept_AfterUpdate() event. 2) It is called when a user click on the combo box named SO; such as
SO_AfterUpdate() event. 3) Whichever order that the user click does not matter. If Dept or SO is
combo box is clicked, then the subroutine get_subfrm_recs gets called.

---

What is Microsoft sql server,Error:207

---

]]>