Updating invoices in Quickbooks using QODBC
I have about 400 invoices for which I need to update the InvoiceLineRate and Memo columns in the InvoiceLine table. I have the data in an Excel spreadsheet with the new information, but am not sure how to update all of the respective invoices in one operation. I have experimented with using the VB Demo, MS Query, MS Access to run a SQL statement, for example
UPDATE InvoiceLine SET InvoiceLineRate = 63.36, Memo = 'Includes Adjustment of $66.64' WHERE RefNumber = '2013157'
It works fine, but I need to know how to change them all at one time, if possible. I have created a linked table in MS Access, and it appears that my best bet may be to somehow update that table from the Excel file, but I don't know how to do that.
Mark
UPDATE InvoiceLine SET InvoiceLineRate = 63.36, Memo = 'Includes Adjustment of $66.64' WHERE RefNumber = '2013157'
It works fine, but I need to know how to change them all at one time, if possible. I have created a linked table in MS Access, and it appears that my best bet may be to somehow update that table from the Excel file, but I don't know how to do that.
Mark
Note that gustav's suggestion would update EVERY InvoiceLine, not just specific invoices. From what I read, you want to update a sub-set of invoicelines ...
If so, you'd need a WHERE clause to tell QODBC which invoices to work with. I'm not sure if QODBC supports the IN keyword, but if it does:
UPDATE InvoiceLine SET InvoiceLineRate = 63.36, Memo = 'Includes Adjustment of $66.64' WHERE Invoice IN ('123', '1234', '12345').
If not, you'd have to loop through each of your invoices, and send out that UPDATE statement for each Invoice, with the RefNumber value included in each WHERE clause.
If so, you'd need a WHERE clause to tell QODBC which invoices to work with. I'm not sure if QODBC supports the IN keyword, but if it does:
UPDATE InvoiceLine SET InvoiceLineRate = 63.36, Memo = 'Includes Adjustment of $66.64' WHERE Invoice IN ('123', '1234', '12345').
If not, you'd have to loop through each of your invoices, and send out that UPDATE statement for each Invoice, with the RefNumber value included in each WHERE clause.
As long as the spreadsheet contains the reference numberof the invoice, you would import the spreadsheet. You have to do this because joining to the spreadsheet would make the query not updateable. then create a query that joins the QuickBooks table to the imported spreadsheet and updates the matching rows. You won't need a where clause since the join will control which records are updated.
Don't forget to back up your quickbooks first because doing bulk updates is dangerous when you don't know exactly what you are doing.
Don't forget to back up your quickbooks first because doing bulk updates is dangerous when you don't know exactly what you are doing.
ASKER
The invoice updates generally have different amounts, so I either need to loop through them as Scott proposes, or import/join the table as Pat suggests. Pat, I assume that what you mean by "import" is to import the spreadsheet into MS Access and then do a join with the table in Access that is linked with QB. Either way, I need some directions. It's been too long since I've done much with Access and I've not had much experience with VB macros. Please give me the actual steps to take.
Thanks,
Mark
Thanks,
Mark
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Pat,
With some experimentation, I believe I got it to work using your instructions. The query is running now and will probably take a while. I'll report back with the results.
Mark
With some experimentation, I believe I got it to work using your instructions. The query is running now and will probably take a while. I'll report back with the results.
Mark
Great. Let us know what happens.
In the future, you might want to test with only a few records so you don't have to wait hours to see what happened.
In the future, you might want to test with only a few records so you don't have to wait hours to see what happened.
ASKER
Yes, it worked! Thanks.
That makes it a happy new year:)
UPDATE InvoiceLine SET InvoiceLineRate = 63.36, Memo = 'Includes Adjustment of $66.64'
DO make a backup first, please.
/gustav