Query-vrs-SQL using code

  • Thread starter Thread starter Steve Warren
  • Start date Start date
S

Steve Warren

I have a form with a cmd button when pressed opens and runs an update query.

DoCmd.OpenQuery "qryProductPurchaseOrderPONumber", acNormal, acEdit

My question is: Could I do away with the update Query and copy the SQL
statement into code, so I have less querys? Is it a better way?

My SQL statement is:

UPDATE (tblMFG INNER JOIN tblProduct ON tblMFG.MfgID =
tblProduct.PrductMfgID) INNER JOIN (tblCustomerOrder INNER JOIN
tblProductOrderDetail ON tblCustomerOrder.OrderID =
tblProductOrderDetail.OrderID) ON tblProduct.ProductID =
tblProductOrderDetail.ProductID SET tblProductOrderDetail.PoNo =
[Forms]![frmProductPurchaseOrder]![PONumberID], tblProductOrderDetail.PoDate
= Date()

WHERE (((tblProductOrderDetail.PoNo) Is Null) AND
((tblMFG.SupplierID)=[Forms]![frmProductPurchaseOrder]![POSupplierID]) AND
((tblProductOrderDetail.ProductWantToOrder)=Yes) AND
((tblCustomerOrder.OrderApprovedOffice)=Yes) AND
((tblCustomerOrder.OrderApprovedSalesPerson)=Yes));
 
Steve,

Yes, you could do as you suggest. You could use a string variable in
your code to hold the SQL of the query, and then use the Execute method
to run it. The skeleton of such a routine would be along these lines...

Dim strSQL As String
strSQL = "UPDATE .... "
CurrentDb.Execute strSQL, dbFailOnError

I believe this will be slower than DoCmd.OpenQuery, but not so much you
would notice.

You will need to adapt the SQL somewhat, for example replace
[Forms]![frmProductPurchaseOrder]![PONumberID] with Me.PONumberID
 
Back
Top