June 7, 2018 at 11:23 am
Right now under company information profile when I click save button it goes through this code:
Dim RS As Recordset
Dim strSQL As String
strSQL = "SELECT tblWorkOrder.WorkOrderID, tblCompany.PreferredInvoiceMethod " & _
"FROM (tblCompany INNER JOIN tblWorkOrder ON tblCompany.CompanyID = tblWorkOrder.BuyerID) INNER JOIN tblSelectedInvoices ON tblWorkOrder.WorkOrderID = tblSelectedInvoices.WorkOrderID " & _
"GROUP BY tblWorkOrder.WorkOrderID, tblCompany.PreferredInvoiceMethod, tblSelectedInvoices.ShipmentNumber;" Set RS = CurrentDb.OpenRecordset(strSQL)
RS.MoveFirst
While RS.EOF = False
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSelectedInvoices SET [InvoiceMethod]='" & RS.Fields(1).Value & "' WHERE WorkOrderID=" & RS.Fields(0).Value
RS.MoveNext
Wend
The problem with this code is that it is a slow process and the more data is entered the slower it becomes. How do I combine the select above with the update below so whatever it gets value from the select it updates InvoiceMethod with the tblCompany.PreferredInvoiceMethod and it uses the tblWorkOrder.WorkOrderID as the where condition for the update?
Thanks in advance.
June 7, 2018 at 11:32 am
You could simply update in a set-based way without going row by row.
UPDATE i SET
[InvoiceMethod]=c.PreferredInvoiceMethod
FROM tblCompany AS c
JOIN tblWorkOrder AS o ON c.CompanyID = o.BuyerID
JOIN tblSelectedInvoices AS i ON o.WorkOrderID = i.WorkOrderID;
June 7, 2018 at 11:34 am
By the way, you shouldn't be updating the whole table every time. Use a WHERE clause to limit the work that needs to be done.
June 7, 2018 at 11:49 am
Luis Cazares - Thursday, June 7, 2018 11:34 AMBy the way, you shouldn't be updating the whole table every time. Use a WHERE clause to limit the work that needs to be done.
If you read my post I did actually. See my code again and you will find that I did just that.
June 7, 2018 at 11:51 am
Luis Cazares - Thursday, June 7, 2018 11:32 AMYou could simply update in a set-based way without going row by row.
UPDATE i SET
[InvoiceMethod]=c.PreferredInvoiceMethod
FROM tblCompany AS c
JOIN tblWorkOrder AS o ON c.CompanyID = o.BuyerID
JOIN tblSelectedInvoices AS i ON o.WorkOrderID = i.WorkOrderID;
IT WORKED! THANK YOU SO MUCH!! - Salute -
June 7, 2018 at 12:01 pm
I don't see where you're filtering out only the rows that need to be updated.
This query should do it, although it assumes none of the invoice method values are NULL.
I added DISTINCT because your query was using GROUP BY, the UPDATE won't work if there are duplicate rows in the CTE.
WITH InvoiceMethod AS (
SELECT DISTINCT wo.WorkOrderID, c.PreferredInvoiceMethod
FROM tblWorkOrder wo
INNER JOIN tblCompany c ON c.CompanyID = wo.BuyerID )
UPDATE inv SET InvoiceMethod = inv.PreferredInvoiceMethod
FROM tblSelectedInvoices inv
INNER JOIN InvoiceMethod m ON m.WorkOrderID = inv.WorkOrderID
WHERE inv.InvoiceMethod <> inv.PreferredInvoiceMethod;
June 7, 2018 at 12:23 pm
Luis Cazares - Thursday, June 7, 2018 11:32 AMYou could simply update in a set-based way without going row by row.
UPDATE i SET
[InvoiceMethod]=c.PreferredInvoiceMethod
FROM tblCompany AS c
JOIN tblWorkOrder AS o ON c.CompanyID = o.BuyerID
JOIN tblSelectedInvoices AS i ON o.WorkOrderID = i.WorkOrderID;
Hey! This actually worked in Microsoft SQL side but it is not working in the access side. I am trying to store it as a procedure in Microsoft SQL and call it from access. However, I have unable to call it successfully in access. Any advice on this? Thanks in advance.
June 7, 2018 at 12:25 pm
xboxown - Thursday, June 7, 2018 11:49 AMLuis Cazares - Thursday, June 7, 2018 11:34 AMBy the way, you shouldn't be updating the whole table every time. Use a WHERE clause to limit the work that needs to be done.If you read my post I did actually. See my code again and you will find that I did just that.
No, you're using the WHERE to go row by row, but that doesn't limit the amount of rows being updated. Scott gave a possible solution, but there should be a better rule than just update all that are different.
June 7, 2018 at 2:09 pm
xboxown - Thursday, June 7, 2018 12:23 PMLuis Cazares - Thursday, June 7, 2018 11:32 AMYou could simply update in a set-based way without going row by row.
UPDATE i SET
[InvoiceMethod]=c.PreferredInvoiceMethod
FROM tblCompany AS c
JOIN tblWorkOrder AS o ON c.CompanyID = o.BuyerID
JOIN tblSelectedInvoices AS i ON o.WorkOrderID = i.WorkOrderID;Hey! This actually worked in Microsoft SQL side but it is not working in the access side. I am trying to store it as a procedure in Microsoft SQL and call it from access. However, I have unable to call it successfully in access. Any advice on this? Thanks in advance.
I believe to use this code directly or call the stored procedure through Access you'd have to use a pass through query.
June 8, 2018 at 6:40 am
Chris Harshman - Thursday, June 7, 2018 2:09 PMxboxown - Thursday, June 7, 2018 12:23 PMLuis Cazares - Thursday, June 7, 2018 11:32 AMYou could simply update in a set-based way without going row by row.
UPDATE i SET
[InvoiceMethod]=c.PreferredInvoiceMethod
FROM tblCompany AS c
JOIN tblWorkOrder AS o ON c.CompanyID = o.BuyerID
JOIN tblSelectedInvoices AS i ON o.WorkOrderID = i.WorkOrderID;Hey! This actually worked in Microsoft SQL side but it is not working in the access side. I am trying to store it as a procedure in Microsoft SQL and call it from access. However, I have unable to call it successfully in access. Any advice on this? Thanks in advance.
I believe to use this code directly or call the stored procedure through Access you'd have to use a pass through query.
Additionally, you can do this from within VBA, by using the necessary objects and then putting that query into a String that is passed as the command to SQL Server. You will need to be sure that the ADO objects for your version of .Net are included in the References. ADO stands for ActiveX Data Objects.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply