I've built a table in Access of invoice lines to be added to existing invoices. I have the TxnID and all the necessary xxxxListID fields etc., with the item, quantity, and values coming from other sources. So "all" I have to do now is feed these records, one by one, into an INSERT INTO . . . VALUES . . . statement. I'm an experienced Access user, but a VBA-by-the-seat-of-my-pants-because-I-can't-find-a-good-training-course user - as I suspect many others may be!
To begin with, so I can see the thing works, I'm using a form based on my table with a combo box to select one record, so I thought I'd be able to have the thing look like this:
DoCmd.RunSQL "INSERT INTO QB_InvoiceLine ( TxnID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, " & _
"InvoiceLineQuantity, InvoiceLineRate, InvoiceLineClassRefListID, InvoiceLineTaxCodeRefListID, " & _
"CustomFieldVFBatchNo, FQSaveToCache )" & _
"VALUES ('Me.TxnID', 'Me.RefNumber', 'Me.InvoiceLineItemRefListID', 'Me.InvoiceLineDesc', 'Me.InvoiceLineQuantity', " & _
"'Me.InvoiceLineRate', 'Me.InvoiceLineClassRefListID', 'Me.InvoiceLineTaxCodeRefListID', 'Me.CustomFieldVFBatchNo', 0)"
It tries to add a record, but fails - one field set to Null due to a type conversion failure.
Am I on totally the wrong track, or is it just some really annoying little thing I've missed?
You are on the right track. InvoiceLineRate is a decimal, but you are trying to insert it as a string, "'Me.InvoiceLineRate', should be "Me.InvoiceLineRate, without the single quotes.
Thank you for your usual prompt reply, but then it asks me for the value of Me.InvoiceLineRate and the insert fail with Runtime Error 3155 - Incorrectly built XML from Update start (#10045). I thought I already posted the first part of this reply, so forgive me if this is a repeat, but I didn't receive a notification that my post had been accepted.
I tried removing the quotes from the two numeric fields - Me.InvoiceLineQuantity and Me.InvoiceLineRate, but now it asks for the value of them, and when I give it values it then fails to insert - Runtime error 3155, Incorrectly built XML from Update Start (#10045).
I made the procedure display the two values in a message box - they're fine. If I take them out of the insert altogether I still get the same error, but with no prompts for values of any fields first. Since custom fields have been known to misbehave, I've tried removing the last field too, but still the same problem.
[QODBC] Incorrectly built XML from Update Start (#10045)
Most likely means that one of your ListID values used or the TxnID is not correct for the company file you are using. ListIDs and TxnIDs are unique to each QuickBooks company file.
I checked all my ListID's and TxnID - I can query on them and return the right record. I reduced to one connection - to the open QB file, deleted the OPT files (one in Program Files, one in Documents and Settings - eh? There seems to be a disparity between using 'Configure QODBC data source' and 'QODBC Setup screen') and switched off optimization, re-booted (on principle) and tried this in VBDemo:
INSERT INTO InvoiceLine ( TxnID,CustomerRefListID,RefNumber, InvoiceLineItemRefListID, InvoiceLineAmount,FQSaveToCache) VALUES ('11CC9-1157644406', '200000-1135954083', 'I13462', 'DC40000-1157657910', 235.89,0) It says 'Error parsing complete XML return string'
But if I remove the TxnID:
INSERT INTO InvoiceLine ( CustomerRefListID,RefNumber, InvoiceLineItemRefListID, InvoiceLineAmount,FQSaveToCache) VALUES ('200000-1135954083', 'I13462', 'DC40000-1157657910', 235.89,0) - the invoice is inserted correctly. But of course, this means I get another invoice, not another line on an existing invoice.
Do I remember reading somewhere on the forum that there's a problem with using TxnID on inserts on the UK version? If so, I'll have to do this a different way - hold the header data from the existing invoice in QB, delete it, then create a new invoice with the saved header data and my 'built' item lines.
When you add lines to an existing invoice, use the line detail columns only and not any of the header details as the Invoice header already exists and don't use FQSaveToCache. For example:
INSERT INTO "InvoiceLine" ("TxnID", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineTaxCodeRefListID") VALUES ('5CA9-1197764583', '250000-933272656', 'Bin Permit Renovations', 200.00000, 200.00, '20000-999022286')
You need to look at the bottom of both the Review QODBC Messages and Review SDK Messages log in the QODBC Setup screen for the real error message and post the last entry sections only so we can see what the problem is. We will also need to know the revision and update rev of the copy of QuickBooks you are using.