SP finished

I am executing a store procedure from an Access Adp using vb code on a form to update a number of rows. After the update I want to requery the form to display the updated data. Problem is the requery occurs before the update is completed. What is the best method to get the app to wait until the stored procedure fiinished the update?


Code on the form

Set cn = Application.CurrentProject.Connection
'(@Order nvarchar(255), @Material nvarchar(255),@Comments nvarchar(255), @Assign nvarchar(255), @Editor nvarchar(255)   )

strTmp = Nz(Forms.frm_AppendComments.Comments, "")

strTmp = " @Order='" & Me.order & _
                  "', @Item='" & Forms.frm_AppendComments.item & _
                  "', @Comments='" & Forms.frm_AppendComments.Comments & _
                  "', @ArrivalDate='" & Forms.frm_AppendComments.ArrivalDate & _
                  "', @Editor='" & FindNetUserName & _
                  "', @Assign='" & Forms.frm_AppendComments.Assign & _
                  "', @Action='" & Forms.frm_AppendComments.action & _
                  "', @PurchFlag='" & Forms.frm_AppendComments.OptPurch & _
                  "', @ProdFlag='" & Forms.frm_AppendComments.OptProd & _
                  "'"
cn.Execute "exec sp_MassChangeStatus2" & strTmp

me.requery


Store procedure


ALTER PROCEDURE sp_MassChangeStatus2
(@Order nvarchar(255), @Item nvarchar(255),@Comments nvarchar(255),@ArrivalDate datetime,@Editor nvarchar(255), @Assign nvarchar(255),  @Action nvarchar(15),  @PurchFlag int ,@ProdFlag int  )


AS

if @Action = 'OverWrite'

Begin
if @PurchFlag = -1
UPDATE    dbo.tbl_ZMOA_Status2
SET              PurchComments =left (@comments,255) , PurchAssign = @assign, PurchEditor = @Editor, PurchDateEdited = getdate()
FROM         dbo.tbl_ZMOA_Status2
WHERE     (OrderPONumber = @Order) AND (OrderPONumbeItem = @Item)

if @ProdFlag = -1
UPDATE    dbo.tbl_ZMOA_Status2
SET            ProdComments =left (@comments,255),ProdAssign = @assign,ProdEditor = @Editor, ProdDateEdited = getdate()
FROM         dbo.tbl_ZMOA_Status2
WHERE     (OrderPONumber = @Order) AND (OrderPONumbeItem = @Item)
End


if @Action = 'Append'

Begin
if @PurchFlag = -1
UPDATE    dbo.tbl_ZMOA_Status2
SET              PurchComments =left (@comments + ' *** ' + isnull(PurchComments,''),255), PurchAssign = @assign, PurchEditor = @Editor, PurchDateEdited = getdate()
FROM         dbo.tbl_ZMOA_Status2
WHERE     (OrderPONumber = @Order) AND (OrderPONumbeItem = @Item)

if @ProdFlag =-1
UPDATE    dbo.tbl_ZMOA_Status2
SET            ProdComments =left (@comments + ' *** ' +  isnull(ProdComments,''),255),ProdAssign = @assign,ProdEditor = @Editor, ProdDateEdited = getdate()

FROM         dbo.tbl_ZMOA_Status2
WHERE     (OrderPONumber = @Order) AND (OrderPONumbeItem = @Item)
End

if  isdate(@ArrivalDate) = 1
UPDATE    dbo.tbl_ZMOA_Status2
SET            ArrivalDate= @ArrivalDate, ArrivalEditor = @Editor,ArrivalDateEdited = getdate()
FROM         dbo.tbl_ZMOA_Status2
WHERE     (OrderPONumber = @Order) AND (OrderPONumbeItem = @Item)


Start Free Trial