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
1 1789
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Nashat Wanly |
last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and
Visual C# .NET
View products that this article applies to.
This article was previously published under Q310070
For a Microsoft Visual Basic .NET version of this article, see 308049.
For a Microsoft Visual C++ .NET version of this article, see 310071.
For a Microsoft Visual J# .NET version of this article, see 320627.
This article refers to the following Microsoft .NET...
|
by: ImraneA |
last post by:
Hi there
I had pleasure of upsizing Access v97 db to Access v2K/SQL 2K.
Wish to provide some knowledge gained back to community - hopefully
help others.
1.Question how do you test stored procedure from SQL Server vs MS
Access point of view ?
|
by: CSDunn |
last post by:
Hello,
I have a situation with MS Access 2000 in which I need to display report
data in spreadsheet orientation (much like a datasheet view for a form). If
you think of the report in terms of what a spreadsheet might show, the
column names will actually be dynamic, based on data from a SQL Server 2000
database. The row data will also come from the same database. So in this
case, I will have a main report and a subreport. I've already tried...
|
by: Neil |
last post by:
Hello,
I have a SQL Server 2000 database with an Access 97 front end.
I want to run stored procedures, (not nessessarily ones which return
records either - action type queries for adding new records etc), from
access and retreive some result from the procedure which would be used
programatically in VBA so that users don't get nasty looking ODBC
errors. Could anyone suggest what I need to do? I'm aware I could use
a pass through query to...
|
by: Noloader |
last post by:
Hello,
Access XP, SQL Server 2000
Is it possible to hide a SP under Queries in Access, yet still be able
to Execute it from Access? (Similar to hiding Tables, then using
Views)
We hooked up a custom form to accept the input parameters (MS Feature
Request!) for the Stored Procedure. We had two problems with MS's
| |
by: Wim van Rosmalen |
last post by:
Hi folks,
I've upgraded MS-Access 2002 to a MS-Access Project (adp), so now I have to
deal with more sophisticated queries (may I call them so?) like stored
procedures. I have a form with a combobox for selections and a textbox to
enter a certain value. Let us say I call the combobox @select and the
textbox @find. The combobox always shows the first of the items to select.
Now I want to return a message if nothing is found, or if...
|
by: Sreeneet |
last post by:
Hi all,
I want to call a stored procedure which is written in SQL Server from
an ms-access query. It is having some parameters also and the stored
procedure will return some records.
Is there any way to do this?
Any help?
|
by: Hank |
last post by:
We have just recently migrated the data from our Access 2000
backend to Postgres. All forms and reports seem to run correctly but,
in many cases, very slowly. We do not want to switch over until we
can speed things up. We would like to start implementing Stored
Procedures so we can do Server-Side processing.
Can anyone recommend a book that would help us learn how to
use sprocs or pass-through queries? I apologize if my terminology...
|
by: adserte |
last post by:
I have a security related question.
I was wondering how i can set up security so that for a table:
a user can read all data in the table but only update and delete their
own data (there is a username field in the table where the user enters
their username)
I am using access 2003 and linking tables odbc from sql server with
windows security.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |