How to pass parameter from form?

[zhongwei]

I'm trying to upsizing access2000 to SQL and make a access project,but I face some problem about transproting data from form to store procedure,eg:
in access 2000 we have Query like this:
select * from table1 where id=forms![formA]![text1]
after upsizing to sql it's like this:
ALTER PROCEDURE dbo.StoredProcedure1
(@idnumber int)
AS
select * from table1 where table1.id=@idnumber

how to pass the data from form to store procedure?I tried using input parameter in form's property sheet, but failed.is there any one who has experience about?Please help!Thank you very much!

 

[cmmrfrds]

You can put it in parameters

like
Return dates from functions
@beginDate=ReturnBegDate(),@endDate=ReturnEndDate()

or
Pick your date from the form
@beginDate=forms![formA]![text1]
,@endDate=forms![formA]![text2]

or
Just your dates from variables
yourbegindate,yourenddate

or your can use command text and equate to recordsource.
example from one of my forms.

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim param1 As Parameter, param2 As Parameter
begDate = #10/1/2001#
endDate = #10/31/2001#

' Connect
Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn
Set param1 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param1
param1.Value = begDate
Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = endDate

' Set up a command object for the stored procedure.
cmd.CommandText = "dbo.sp_employeeRevenueProgress"
cmd.CommandType = adCmdStoredProc

' Execute command to run stored procedure

'- example of recordset
''Set rst = cmd.Execute
'- example of recordsource
me.recordsource = cmd.excute

 

[zhongwei]

Hi,Cmmrfrds
Thanks for your reply. I'm trying to pick up data from form,but Where should I put these data:
@beginDate=forms![formA]![text1]
,@endDate=forms![formA]![text2]

Looking forward to hearing from you!
Thanks again.

 

[cmmrfrds]

Under the data tab for the Form, one of the properties is Parameters. That is where you put the parameters.

 

[randusoleis]

I was just faced with this same senario when trying to create a report that used values from an "OPEN FORM" to set criteria for the report.

This is what I did.

CREATE PROCEDURE StoredProcedure1
(@idnumber int)
AS
select *
from table1
where id = @idnumber

On the report I set the control source as the stored procedure.
On the INPUT PARAMETER property I made the folowing statement.... [FORMS]![FORMNAME]![FieldName]

The INPUT PARAMETER passes the value of the field name (ie: field name) to the declared variable of the stored procedure which in turn completes the SQL statement and produces my report.

Also unless you are querying more than 1 table in your stored procedure you do not have to use the table name in your WHERE clause...only the field name.

I am not very sure about all that code entering stuff so I try when ever possible to GUI it all in and let Access write the code for me. Call me old fashioned. Randusoleis.....

 

[randusoleis]

By the way....create the stored procedure in SQL Server not Access2000. The looks are more friendly. Randusoleis.....

 

[zhongwei]

Thanks randusolies & cmmrfrds
Actually as I mentioned in my first message,I already tried to pass parameter by using input parameter property,but i failed. every time a message box pop up to ask for the value of idnumber.
in the input parameter property I type in like:
@idnumber=forms![FORMA]![TEXT1]
I tried many times without success,and I check the spelling of form name and controls' name, everything is ok.I don't know where is the problem.
Any thoughts about it? I was stuck there.
Thanks for any help!

 

[randusoleis]

In the InputParamter property you no longer need to add the "@idnumber =" part because that is already in your stored procedure. Also make sure you place "FORMS" in brackets....[FORMS]![FORMName]![FieldName].
Randusoleis.....

 

[zhongwei]

Hi,randusoleis
following your instruction, I tryed ,still failed.
I'm confused that in store procedure,I just declare the parameter @idnumber int.
if in input parameter property I just type [forms]![formA]![text1],how they know each other ?how they're related?

I think I should put @idnumber=forms![formA]![text1] somewhere..... but,where?
pls give further instruction,thanks!

 

[randusoleis]

In my example I was using a stored procedure to produce a report. What are you using the sp for? Randusoleis.....

 

[cmmrfrds]

Just to back up and go over the processing flow a little bit. The way I normally set up getting criteria from the user and passing to a Form or Report is to start with a form that has the criteria and a button to execute another Form that will bring back the results.

Code Behind Button
DIM LinkCriteria as string, DocName as string
DocName = "MyReportForm"
LinkCriteria = Me.YourID
DoCmd.OpenForm DocName, , , , , , LinkCriteria

In MyReportForm
Public Myvariable

Also Myvariable will be the parameter on the data tab

In OnOpen Event of MyReportForm
Myvariable = Me.OpenArg

 

[zhongwei]

Hi,cmmrfrds & Randusoleis
After reading your message,I also check the online book about input parameter,then I redo a new access data project,
It worked !

Thank you very much!