Question:

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 to 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 is set to Null due to a type conversion failure.
Am I on the wrong track, or is it just some annoying little thing I've missed?

Answer:

You are on the right track. InvoiceLineRate is a decimal, but if you try inserting it as a string, "'Me.InvoiceLineRate' should be "Me.InvoiceLineRate, without single quotes.

Question:

Thank you for your usual prompt reply, but then it asks me for my value of Me.InvoiceLineRate and the insert fail with Runtime Error 3155 - Incorrectly built XML from Update start (#10045). I thought I had already posted the first part of this reply, so forgive me if this is a repeat. 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 now asks for their value, 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, I still get the same error, but with no prompts for the values of any fields first. Since custom fields have been known to misbehave, I've tried removing the last area too, but I still have the same problem.

Answer:

[QODBC] Incorrectly built XML from Update Start (#10045)

It most likely means that one of your listed values or the TxnID is incorrect for the company file you are using. Listings and TxnIDs are unique to each QuickBooks company file.

Question:

I checked all my listings and TxnID - I can query them and return the correct 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 differently - 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.

Answer:

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')

It would be best if you looked at the bottom of the Review QODBC Messages and Review SDK Messages, logged in to the QODBC Setup screen for the actual error message, and posted the last entry sections only so we can see what the problem is. We will also need to know the revision and update of the copy of QuickBooks you are using.