Problem with passing parameters to SQL procedure using VBA
- by Stefan Åstrand
Hi,
I am trying to pass @intDocumentNo and @intCustomerNo to a stored procedure using VBA but only @intCustomerNo is updated in dbo.tblOrderHead. I don't think the problem is with the procedure because if I enter the values manually it runs properly.
What am I doing wrong?
VBA Code:
Private Sub intCustomerNo_Click()
Dim cmdCommand As New ADODB.Command
With cmdCommand
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "uspSelectCustomer"
'@intDocumentNo
.Parameters("@intDocumentNo").Value = Forms![frmSalesOrder].[IntOrderNo]
'@intCustomerNo
.Parameters("@intCustomerNo").Value = Me![intCustomerNo]
.Execute
End With
DoCmd.Close
Forms![frmSalesOrder].Requery
End Sub
Procedure:
UPDATE dbo.tblOrderHead
SET dbo.tblOrderHead.intCustomerNo = @intCustomerNo ,
dbo.tblOrderHead.intPaymentCode = dbo.tblCustomer.intPaymentCode,
dbo.tblOrderHead.txtDeliveryCode = dbo.tblCustomer.txtDeliveryCode,
dbo.tblOrderHead.txtRegionCode = dbo.tblCustomer.txtRegionCode,
dbo.tblOrderHead.txtCurrencyCode = dbo.tblCustomer.txtCurrencyCode,
dbo.tblOrderHead.txtLanguageCode = dbo.tblCustomer.txtLanguageCode
FROM dbo.tblOrderHead
INNER JOIN dbo.tblCustomer ON dbo.tblOrderHead.intCustomerNo =
dbo.tblCustomer.intCustomerNo
AND dbo.tblOrderHead.intOrderNo = @intDocumentNo
