Is there a way to pass parameter to storeprocedure

[dwight1]

Hi,

I want to pass a parameter to stored procedure. I know we can do it using a report/form by passing the input parameters.

example:

1.step--sp/function (define a param)=>@appid
2.Step--report/form (inputparameters)=>(@appid=forms!formneed!control)
3.step--form =>(combo)

My requirement is to open a sp/function by using a parameter on the form that has a combo.
Eliminate step 2 from the above.

Is this possible?

Dwight

 

[cmmrfrds]

Do you want to execute the SP through vba code? Anyway, here is an example of executing an SP and returning the results to a recordset.

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As ADODB.Recordset, connString As String, bdate As String
Dim cmd As New ADODB.Command, parm1 As ADODB.Parameter
Set rs = New ADODB.Recordset
'--- connect to sql server
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

Set parm1 = cmd.CreateParameter(, adInteger, adParamInput)
cmd.Parameters.Append parm1
parm1.Value = "10255"
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic

Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.CustOrdersDetail"
cmd.ActiveConnection = cn
Set rs = cmd.Execute

cn.Close
Set cn = Nothing
Set cmd = Nothing

 

[dwight1]

Cmmrfrds,

Thank you. I have a small but different problem. I have a form that the user inputs. It generates autonumber field (custid) when the form closes.

I have a print button that opens a report based on the new record added by the user.

So, i want to requery the form, so that it generates a new id and reflects the new info in the report WITHOUT closing the form.

Docmd.requery(controlname)

what is this control name. I tried giving the form name but it does not like it.

Can you advise.

Thanks

Dwight

 

[cmmrfrds]

In the afterupdate event of the Form you can capture the new generated id. ADO will have put this id in the cust_id field bound to the Form. You can use this id as a filter when opening the Form again. You could save this id in a global variable before closing the Form. The filter is one of the parameters available when using the DoCmd.OpenForm. I hope this gives you enough to go on.

 

[dwight1]

We are on the same topic but different page here.

As you know in ADP the autonumber is generated when you are off the screen unlike Access were we have autonumber generated as u enter the data.

So i want, in short,have the new form (on which user enters the data) to refresh/requery such that the user can see the custid generated.

I want this refresh/requery command to be being the print button with the code.

Currently i am using the following code, the report opens but is blank (not able to grab or refresh/requery autogenerate the number).

Onclick Event

Dim stDocName As String
DoCmd.Save

stDocName = "CenturyAppPg2"
DoCmd.OpenReport stDocName, acViewPreview

stDocName = "CBApproval Request"
DoCmd.OpenReport stDocName, acPreview

Thanks

Dwight

 

[dwight1]

cont..i have given the parameter @custid in the sp, record source of the report.

 

[cmmrfrds]

Have you tried using a filter.

Private Sub Form_Open(Cancel As Integer)
Me.AllowFilters = True
Me.FilterOn = True
End Sub

Assuming cust_id is the field in your table and numeric.

Private Sub Form_AfterUpdate()
Me.Filter = "Cust_id = " & boundcustid
Me.Requery
End Sub

Otherwise, show exactly what you have on the Parameter tab.