Access ADP and SP Did not Return Any Records

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

SQL Server Development (2000)

Access ADP and SP Did not Return Any Records

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2008-03-07 : 00:06:31

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:
"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_Selector_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

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 00:39:58

Have you tested the procedure to make sure it works with that 1st parameter passed?

Instead of trying to pass the parameter, have you code pop up message boxes that show you the intended SQL_Form result from you VBA code..

like this


MsgBox SQL_Form
'or below can be used to print to the immediate window in VB Editor.
'Debug.Print SQL_Form 
' Me.Q_FilteringQuery_subform.Form.RecordSource = SQL_Form
End Sub

Make sure the Statement looks like it should...or post what it does look like given the explained scenario

Poor planning on your part does not constitute an emergency on my part.

Lin100
Yak Posting Veteran

70 Posts

Posted - 2008-03-07 : 01:52:00

Hi dataguru1971. I tested with one parameter and it worked, but when I tested with two
parameter it gives me an error.
parameter 1: Department
parameter 2: SO_Number

Error Message 1
Enter a parameter value for SO_Number: _________

Error Message 2
Run-time error 8145. P1 is not a parameter for procedure Get_Records_For_Selector_Form

Debug.Print SQL_Form
gives me
Exec [Get_Records_For_Selector_Form_2] @Department = '5', @SO_Number = '10356'

The modified code for two parameters are below.
////////////////////////////////////////////////

Private Sub Activate_Stored_Procedure_To_Obtain_Records_For_Selector_Form()
Dim SQL_Form As String
Dim strStep As String

SQL_Form = "Exec [Get_Records_For_Selector_Form_2] "
strStep = ""

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

Debug.Print SQL_Form
Me.Q_FilteringQuery_subform.Form.RecordSource = SQL_Form
End Sub

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

CREATE PROCEDURE Get_Records_For_Selector_Form_2
@Department int = Null, @SO_Number int = 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 dbo.[1_Job - Parent]
ON Ref_DepartmentID.ID = dbo.[1_Job - Parent].DepartmentID
WHERE dbo.Ref_DepartmentID.ID = isnull(@Department, dbo.Ref_DepartmentID.ID)
AND SONumber = isnull(@SO_Number, dbo.[1_Job - Parent].SONumber)
ORDER BY RecordInitiateDate DESC
GO

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 02:02:08

My access is really rust, but I think it should be this syntax instead of the isNull like I mentioned before....it isn't really null as an item or non selection in combo boxes. it is an empty string which is diffferent


If not me.Dept.Text = "" then

Reason being, it will never pass TRUE for the result of isNull() if there is never a null.

Also, and again--a little rusty--but I also think the object reference has to be Me.Dept.Text (You have to specify the property of text to get that "value" out of the combo box and pass it.

Poor planning on your part does not constitute an emergency on my part.

Lin100
Yak Posting Veteran

70 Posts

Posted - 2008-03-10 : 19:12:41

Hi dataguru1971. I tried your suggestion, but it does not work.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-10 : 19:31:38

Doesn't work, and returns and error are two different things...

If there is an error message, post it. What does the code look like now? I will take a look and see what I can figure out.

Poor planning on your part does not constitute an emergency on my part.

Lin100
Yak Posting Veteran

70 Posts

Posted - 2008-03-14 : 19:16:17

Hi dataguru1971.

When I tried your suggestion I got an error message
"Run-Time error 2185. You can 't reference a property or method for a control unless the
control has the focus".
This message occured when I first select a value from a Dept combo box, while the
So combo box remained empty.

I have modified the VBA and stored procedure code to where only 2 parameters are
involved to make it more simple and so to narrow down the problem easier.

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

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

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

Private Sub Dept_AfterUpdate()
Call Activate_Stored_Procedure_To_Obtain_Rows_For_Combo_Boxes
End Sub

Private Sub SO_AfterUpdate()
Call Activate_Stored_Procedure_To_Obtain_Rows_For_Combo_Boxes
End Sub

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

Private Sub Activate_Stored_Procedure_To_Obtain_Rows_For_Combo_Boxes()
Dim SQL_Department As String
Dim SQL_SO As String
Dim SQL_Item As String
Dim SQL_Section As String
Dim strStep As String

SQL_Department = "Exec [Get_Department] "
SQL_SO = "Exec [Get_SO_Number] "
SQL_Section = "Exec [Get_Section_Number] "
SQL_Item = "Exec [Get_Item_Number] "
strStep = ""

If Not IsNull(Me.Dept) Then 'Department
SQL_Department = SQL_Department & strStep & " @Department = " & "'" & Me.Dept & "'"
SQL_SO = SQL_SO & strStep & " @Department = " & "'" & Me.Dept & "'"
SQL_Item = SQL_Item & strStep & " @Department = " & "'" & Me.Dept & "'"
SQL_Section = SQL_Section & strStep & " @Department = " & "'" & Me.Dept & "'"
strStep = ","
End If

If Not IsNull(Me.so) Then 'SO_Number
SQL_Department = SQL_Department & strStep & " @SO_Number = " & "'" & Me.so & "'"
SQL_SO = SQL_SO & strStep & " @SO_Number = " & "'" & Me.so & "'"
SQL_Item = SQL_Item & strStep & " @SO_Number = " & "'" & Me.so & "'"
SQL_Section = SQL_Section & strStep & " @SO_Number = " & "'" & Me.so & "'"
strStep = ","
End If

If IsNull(Me.Dept) And IsNull(Me.so) Then
Me.Dept.RowSource = "Exec [get_Dept_ID]" 'Obtain all Department rows.
get_subfrm_recs_2 'call procedure. Obtain all records.
Exit Sub
End If

Me.Dept.RowSource = SQL_Department 'Obtain the Department rows that matches the criteria.
Me.so.RowSource = SQL_SO 'Obtain the SO_Number rows that matches the criteria.
Me.Item.RowSource = SQL_Item 'Obtain the Item_Number rows that matches the criteria.
Me.Sectionno.RowSource = SQL_Section 'Obtain the Section_Number rows that matches the criteria.

Call Activate_Stored_Procedure_To_Obtain_Records_For_Selector_Form
End Sub

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

Private Sub Activate_Stored_Procedure_To_Obtain_Records_For_Selector_Form()
Dim SQL_Form As String
Dim strStep As String

SQL_Form = "Exec [Get_Records_For_Selector_Form_3] "
strStep = ""

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

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

Debug.Print "After ", SQL_Form
Me.Q_FilteringQuery_subform.Form.RecordSource = SQL_Form
End Sub

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-15 : 10:11:15

quote:

When I tried your suggestion I got an error message
"Run-Time error 2185. You can 't reference a property or method for a control unless the
control has the focus".
This message occured when I first select a value from a Dept combo box, while the
So combo box remained empty.

If you have forms, and embedded subforms, you have to preface the object reference with the Formname instead of "ME"


 Me.Dept.RowSource = SQL_Department 'Obtain the Department rows that matches the criteria.
Me.so.RowSource = SQL_SO 'Obtain the SO_Number rows that matches the criteria.
Me.Item.RowSource = SQL_Item 'Obtain the Item_Number rows that matches the criteria.
Me.Sectionno.RowSource = SQL_Section 'Obtain the Section_Number rows that matches the criteria.

This "Me" needs to reference the correct form. Not sure but if you are not "on" that form at the time, that is one of the errors that will fire in Access.

like you did here:

quote:

Me.Q_FilteringQuery_subform.Form.RecordSource = SQL_Form

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources