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

 

(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).