Unbound form field as parameter in StoredProcedure 1

[ProMan71]

I have created a form having one unbound "Date_beg" field and "Update" button. "Update" button runs a StoredProcedure "AppRec" , which appends records to a "tmp_records" table. I have set parameter "param1", which is prompted, when SP is run.

How can I use "Date_beg" field as "param1". Are there any other ways to solve this problem ?

Thanks in advance !

- Mark

 

[cmmrfrds]

The ADO Command object is typically used to manage stored procedures. Here is an example.

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, param3 As Parameter, param4 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 param3 = cmd.CreateParameter("Input", adSmallInt, adParamInput)
cmd.Parameters.Append param3
param3.Value = 3
Set param4 = cmd.CreateParameter("Input", adSmallInt, adParamInput)
cmd.Parameters.Append param4
param4.Value = 3

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

' Execute command to run stored procedure
''cmd.Execute

Set rst = cmd.Execute
'' Can return a recordset if desired.

 

[ProMan71]

Hi !

Code works fine. Thank you a lot !

- Mark

 

[chrissie1]

what also works if i remember it correctly (otherwise try the books online and look for "exec".

docmd.runsql "EXEC storedprocedure param1"

or

docmd.runsql "exec storedprocedure param1,param2"

and try to give a star when a tip helps you

Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[neecie]

Personally, I use a function for processing stored procedures

Public Function Exec_Select2(spName As String, ParamArray IDs()) As ADODB.Recordset
    Dim i As Integer
    Dim com1 As ADODB.Command
    Dim parame As ADODB.Parameter
    Set com1 = New ADODB.Command
    com1.ActiveConnection = CurrentProject.Connection
    com1.CommandText = spName
    com1.CommandType = adCmdStoredProc
    For i = 0 To UBound(IDs)
        Set parame = com1.CreateParameter(, adInteger, adParamInput, , IDs(i))
        com1.Parameters.Append parame
    Next i
    Set Exec_Select2 = com1.Execute
    Set com1 = Nothing
End Function
[\code]

You can also setup the function to take in an array of datatypes along with the data to avoid variants, but it incorprates alot more code