Running a Stored Procedure Using a Form Event



I have an Access Data Project (ADP) in which:

1) I want to run a stored procedure (that returns no records)
triggered by the AfterUpdate event of a form control

2) The value contained in the form control (after being updated) needs
to be passed to the stored procedure as a parameter.

What code do I need to put in the AfterUpdate form control event to
trigger the stored procedure? Whatelse do I need to do in order to
ensure that the form control value gets passed to the stored procedure
as a parameter?

Me.RecordSource "Exec strMyStoredProcedure" doesn't work because the
stored procedure is not intended to return any records that would act
as a record source.

Any ideas?

Norman Yuan

You can use an ADODB.Command object in the AfterUpdate event handler to
execute that SP in SQL Server. Something like:

Private Sub Form_AfterUpdate()

Dim cmd AS ADODB.Command
Set cmd=New ADODB.Command
Set cmd.ActiveConnection=CurrentProject.Connection

'Add necessary parameters here


End Sub

giorgio rancati

Hi Altemir,
try this

Method #1
Private Sub Form_AfterUpdate()

CurrentProject.Connection.[SpName] Me!ContolName

End Sub

Method #2
Private Sub Form_AfterUpdate()

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "SpName"
cmd.CommandTimeout = 300
cmd.Parameters.Append _
cmd.CreateParameter("@Param1", adInteger, adParamInput, ,

Set cmd = Nothing

End Sub

Vadim Rapp

A> What code do I need to put in the AfterUpdate form control event to
A> trigger the stored procedure?

currentproject.connection.execute "exec mystoredprocedure '" & control.value
& "'"

Vadim Rapp


Thank you Vadim for your excellent suggestion!! Very simple and to the
point! Works great.


uh why not docmd.runsql?

i mean.. i think that going through the Access error handler is
better-- more robust-- because cnn.execute doesn't always handle errors
like it should..


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question