Hi all,
I've been reading up on this over the last week but am struggling to find the root of the issue. I have two queries, an insert and an update, that I am trying to run using the Execute method so I don't have to disable warnings. I can get the queries to run fine using Docmd.OpenQuery but I would rather have customized messageboxes and only click 'OK' once for the process. Right now I get Error 3061: Too few parameters, Expected 1 as the error and I think it has to do with what needs to be listed after the execute command for each query but I don't know enough about this process nor can I find a lot that explains the execute function, can anyone help? Both queries work as required when run either form a macro or using Docmd.OpenQuery, I just get the two notifications for each one.
VBA and SQL for each query are below:
Code:
Option Compare DatabaseOption Explicit
Private Sub cmdRCVLine_Click()
Dim dbs As DAO.Database
Dim lngRowsAffected As Long
Dim Answer As Integer
Set dbs = CurrentDb()
CurrentDb.Execute "qryReceiving"
CurrentDb.Execute "qryrcvdate"
'-------------------Message Affected ----------------
MsgBox CStr(dbs.RecordsAffected) & " records has been Processed ", vbOKOnly + vbInformation, "Affected Rows"
End Sub
SQL for INSERT query:
Code:
INSERT INTO tblTransactions ( MaterialID, TransactionQTY, TransactionTypeID )SELECT tblPOLines.MaterialID, tblPOLines.POLineQTY, "1" AS TransTypeID
FROM tblPOLines
WHERE ((([tblPOLines]![POLineID])=[Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID]));
SQL for UPDATE query:
Code:
UPDATE tblPOLines SET tblPOLines.POLineClosedDate = Date()WHERE ((([tblPOLines]![POLineID])=[Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID]));