Knowledgebase: TroubleShooting
[QODBC-Desktop] Inserting invoice lines built from external data using MS Access
Posted by Jack - QODBC Support on 05 August 2014 12:42 PM

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 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?

Answer:

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.

Question:

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.

Answer:

[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.

Question:

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.

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

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.

 

(0 vote(s))
Helpful
Not helpful

Comments (2)
Zoe Davis
23 July 2017 03:38 PM
Good Day . I dont Know how to post a problem here but i am encountering a problem just like this
but the different part of it is that i dont have a existing invoice i need to add a new invoice using vb and will reflect it in the quickbooks
Jack
25 July 2017 03:09 AM
Hi Zoe,

Please refer below mentioned article for creating New Invoice using QODBC:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2389/0/how-to-create-invoices-using-qodbc
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2582/45/qodbc-desktop-how-to-create-invoices-through-ms-access-using-qodbc

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).