I am exporting invoices from Access 2007 to  QuickBooks QBi 2009/10 using QODBC v9.0.0.259.  The problem is that the item price  always reverts to the price held in Quickbooks.  It ignores what is contained in the export.  Any ideas would be greatly appreciated.  Thanks.

Paul

There's simply not enough detail here regarding the script or insert statements you are using to post these invoices to make any judgement calls on what's really happening, but Reckon have been busy changing what happens inside QuickBooks QBi 2009/10, see: InvoiceLine Rates and Amounts using QuickBooks QBi 2009/10 for an example. Try stepping through the insert statements using VB Demo to see what's really happening.

What should be happening is shown in the Australian Sample Tutorial example in: How can I create multiple lines in the Invoice, Purchase, Journal and other tables?  

Thanks Tom.  I have read your links and tried to use VB Demo but ran into a lexical element error.  My code is attached.  rsExportSales is an ADO recordset

rsExportSales.MoveFirst

Do Until rsExportSales.EOF

    iRecordID = rsExportSales!RecordID

    If rsExportSales!MYOBPrint = "P" Then

        iPrint = -1

    Else

        iPrint = 0

    End If

    strItemListID = rsExportSales![InvoiceLineItemRefListID]

    strProductName = rsExportSales![Product Name]

    dbQuantity = rsExportSales!Quantity

    dbPrice = rsExportSales!Price

    dbTotal = rsExportSales!Total

    strGST = rsExportSales!GST

    strSQL = "INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineQuantity," & _

    "InvoiceLineRate, InvoiceLineAmount, InvoiceLineTaxCodeRefFullName, FQSaveToCache)" & _

    "VALUES " & "('" & strItemListID & "', '" & strProductName & "', '" & dbQuantity & "', '" & _

    dbPrice & "', '" & dbTotal & "', '" & strGST & "', " & "1)"

    DoCmd.RunSQL strSQL     'Export Invoice Details

    rsExportSales.MoveNext  'Check if invoice Details finished and header needs to be exported

    If Not rsExportSales.EOF Then

          If rsExportSales!RecordID = iRecordID Then GoTo Section

    End If

    rsExportSales.MovePrevious      'Export invoice header

    strCustListID = rsExportSales!CustomerRefListID

    strCustName = rsExportSales![Co/Last Name]   

    dtDelDate = rsExportSales!Date

    Debug.Print dtDelDate, "Export Header"

    strDelAdd2 = rsExportSales![Del Addr 1]

    strDelSuburb = rsExportSales!Suburb

    strDelState = rsExportSales!State

    strDelPCode = rsExportSales!PCode

    strPONumber = rsExportSales![Customer PO]

    strInvoiceNo = Str(rsExportSales!RecordID)

    strSQL = "INSERT INTO Invoice (CustomerRefListID, CustomerRefFullName, TxnDate," & _

    "ShipAddressAddr1, ShipAddressAddr2, ShipAddressCity," & _

    "ShipAddressState, ShipAddressPostalCode," & _

    "IsPending, PONumber, ShipDate, Memo," & _

    "IsToBePrinted)" & _

    "VALUES " & "('" & strCustListID & "', '" & strCustName & "', '" & dtDelDate & "', '" & _

    strCustName & "', '" & strDelAdd2 & "', '" & strDelSuburb & "', '" & strDelState & "', '" & _

    strDelPCode & "', " & 0 & ", '" & strPONumber & "', '" & dtDelDate & "', '" & strInvoiceNo & "', " & iPrint & ")"

    DoCmd.RunSQL strSQL

    rsExportSales.MoveNext

Section:

Loop

INSERT INTO InvoiceLine (CustomerRefListID, InvoiceLineItemRefListID, InvoiceLineDesc,
InvoiceLineRate, InvoiceLineTaxAmount, InvoiceLineTaxCodeRefListID, FQSaveToCache)
VALUES ('1840000-1112685502', '1B0000-1151902315', '23007',
96, 9.6,'8000000A-1224053897', 0)

Using the above example as template please try a number of times with different InvoiceLineRate and  InvoiceLineTaxAmount values using VB Demo and advise the results.

Item Service.

Paul

Insert Statements and screen dumps for two invoices that have been exported to Quickbooks are included below.

Insert Statement1.

INSERT INTO InvoiceLine (CustomerRefListID, InvoiceLineItemRefListID, InvoiceLineDesc,
InvoiceLineQuantity, InvoiceLineRate, InvoiceLineTaxAmount, InvoiceLineTaxCodeRefListID, FQSaveToCache)
VALUES ('DC0000-1142837194', '40000-1142550723', 'SWB', 30,
12, 1.1,'80000004-1265057775', 0)

Insert Statement 2

INSERT INTO InvoiceLine (CustomerRefListID, InvoiceLineItemRefListID, InvoiceLineDesc,
InvoiceLineQuantity, InvoiceLineRate, InvoiceLineTaxAmount, InvoiceLineTaxCodeRefListID, FQSaveToCache)
VALUES ('DC0000-1142837194', '40000-1142550723', 'SWB', 30,
26, 2.36,'80000004-1265057775', 0)

Can I trouble you to also run this statement and post the resulting QuickBooks invoice please?

INSERT INTO InvoiceLine (CustomerRefListID, InvoiceLineItemRefListID, InvoiceLineDesc,
InvoiceLineQuantity, InvoiceLineRate, InvoiceLineTaxAmount, InvoiceLineTaxCodeRefListID, FQSaveToCache)
VALUES ('DC0000-1142837194', '40000-1142550723', 'SWB with QTY 20 and price at $15', 20,
15, 30.0,'80000004-1265057775', 0)

No trouble.  Thanks for the help.  Quicken invoice below.

The developers of QODBC are not having much luck trying to duplicate the problem using their copy of QuickBooks QBi 2009/10 Enterprise. They have requested a XML Trace of our last test insert operation:-

INSERT INTO InvoiceLine (CustomerRefListID, InvoiceLineItemRefListID, InvoiceLineDesc,
InvoiceLineQuantity, InvoiceLineRate, InvoiceLineTaxAmount, InvoiceLineTaxCodeRefListID, FQSaveToCache)
VALUES ('DC0000-1142837194', '40000-1142550723', 'SWB with QTY 20 and price at $15', 20,
15, 30.0,'80000004-1265057775', 0)

This will help determine where the bug is within the Reckon modified qbXML SDK 6.1.

See: How can I trace the XML that is sent to and from QuickBooks by QODBC? 

Tom, the text file with trace info is quite large.  Is there a way I can attach it?

Paul

I got your trace file, but the QODBC Software Engineer in USA wants a XML Trace (not a standard trace file). This requires the following special settings to be added to both of these odbcinst.ini files.

Please see: How can I trace the XML that is sent to and from QuickBooks by QODBC? for more.

Your XML Trace file clearly shows QuickBooks is overriding the values sent in the XML Input by QODBC with the values it has on file for the Item Service. This appears to be a qbXML SDK 6.1 bug introduced by Reckon:-

    XML Input:
        <?xml version="1.0" encoding="ISO-8859-1"?>
        <?qbxml version="6.1"?>
        <QBXML>
            <QBXMLMsgsRq onError = "continueOnError" responseData = "includeAll">
                <InvoiceAddRq requestID = "1">
                    <InvoiceAdd defMacro = "TxnID:9CD44B72-453F-46BC-9924-07EB49BA7743">
                        <CustomerRef>
                            <ListID>DC0000-1142837194</ListID>
                        </CustomerRef>
                        <InvoiceLineAdd>
                            <ItemRef>
                                <ListID>40000-1142550723</ListID>
                            </ItemRef>
                            <Desc>SWB with QTY 20 and price at $15</Desc>
                            <Quantity>20.00000</Quantity>
                            <Rate>15.00000</Rate>
                            <TaxAmount>30.00</TaxAmount>
                            <SalesTaxCodeRef>
                                <ListID>80000004-1265057775</ListID>
                            </SalesTaxCodeRef>
                        </InvoiceLineAdd>
                    </InvoiceAdd>
                </InvoiceAddRq>
            </QBXMLMsgsRq>
        </QBXML>
    XML Output:
        <?xml version="1.0" ?>
        <QBXML>
            <QBXMLMsgsRs>
                <InvoiceAddRs requestID="1" statusCode="0" statusSeverity="Info" statusMessage="Status OK">
                    <InvoiceRet>
                        <TxnID>DADA-1269409611</TxnID>
                        <TimeCreated>2010-03-24T16:46:51+11:00</TimeCreated>
                        <TimeModified>2010-03-24T16:46:51+11:00</TimeModified>
                        <EditSequence>1269409611</EditSequence>
                        <TxnNumber>10226</TxnNumber>
                        <CustomerRef>
                            <ListID>DC0000-1142837194</ListID>
                            <FullName>A Armstrong</FullName>
                        </CustomerRef>
                        <ARAccountRef>
                            <ListID>2F0000-1031006907</ListID>
                            <FullName>Accounts Receivable</FullName>
                        </ARAccountRef>
                        <TemplateRef>
                            <ListID>D0001-1217808312</ListID>
                            <FullName>Orara Account Invoice</FullName>
                        </TemplateRef>
                        <TxnDate>2010-03-24</TxnDate>
                        <RefNumber>10/439</RefNumber>
                        <BillAddress>
                            <Addr1>A Armstrong</Addr1>
                        </BillAddress>
                        <BillAddressBlock>
                            <Addr1>A Armstrong</Addr1>
                        </BillAddressBlock>
                        <IsPending>false</IsPending>
                        <IsFinanceCharge>false</IsFinanceCharge>
                        <DueDate>2010-03-24</DueDate>
                        <ShipDate>2010-03-24</ShipDate>
                        <Subtotal>167.27</Subtotal>
                        <SalesTaxPercentage>10.00</SalesTaxPercentage>
                        <SalesTaxTotal>16.73</SalesTaxTotal>
                        <AppliedAmount>0.00</AppliedAmount>
                        <BalanceRemaining>184.00</BalanceRemaining>
                        <IsPaid>false</IsPaid>
                        <IsToBePrinted>false</IsToBePrinted>
                        <IsToBeEmailed>false</IsToBeEmailed>
                        <InvoiceLineRet>
                            <TxnLineID>DADC-1269409611</TxnLineID>
                            <ItemRef>
                                <ListID>40000-1142550723</ListID>
                                <FullName>SWB</FullName>
                            </ItemRef>
                            <Desc>SWB with QTY 20 and price at $15</Desc>
                            <Quantity>20</Quantity>
                            <Rate>8.36364</Rate>
                            <Amount>167.27</Amount>
                            <SalesTaxCodeRef>
                                <ListID>80000004-1265057775</ListID>
                                <FullName>GST</FullName>
                            </SalesTaxCodeRef>
                        </InvoiceLineRet>
                    </InvoiceRet>
                </InvoiceAddRs>
            </QBXMLMsgsRs>
        </QBXML>

Tom,

Do you know if Quicken are planning to address the problem?  Is there anything that I can do?

Paul

Sorry, I haven't had much luck working with Reckon/Quicken in Sydney for many years now. It might also be worth trying using the TxnID and TxnLineID of the invoice line created by the last insert and trying to force the correct amount values after the insert like this:-
 
Update InvoiceLine set InvoiceLineQuantity= 20,InvoiceLineRate=15,InvoiceLineAmount=300, InvoiceLineTaxAmount=30
where TxnID = 'DADA-1269409611' and TxnLineID= 'DADC-1269409611'

and see whether the invoice line gets corrected or not? Let me know how it goes?

Thanks for the suggestion.  It works for single line invoices with DemoVB but with multiple line invoices, the other lines are deleted. My main problem is that with Access 2007 vba, I keep getting the following error message no matter what I try.

Error          3075         Syntax error (missing operator) in query expression '11WHERE TxnID ='DB8B-1270360935'

AND InvoiceLineTxnLineID = 'DB8D-1270360935''.

My code is

    Dim strTxnID As String, strTxnLineID As String
    Dim dbQuantity As Double, dbprice as double
    Dim strSQL As String
    dbQuantity = 150
    dbprice = 18
    strTxnID = "DB8B-1270360935"                   

    strTxnLineID = "DB8D-1270360935"              

    strSQL = "Update InvoiceLine set InvoiceLineQuantity=" & dbQuantity & ",InvoiceLineRate=" & dbprice & _
    "where (((InvoiceLine.TxnID) = '" & strTxnID & "') and ((InvoiceLine.InvoiceLineTxnLineID)= '" & strTxnLineID & "'));"
   
    DoCmd.RunSQL strSQL 

Try:

    strSQL = "Update InvoiceLine set InvoiceLineQuantity=" & dbQuantity & ",InvoiceLineRate=" & dbprice _
    & "where (((InvoiceLine.TxnID) = '" & strTxnID & "') and ((InvoiceLine.InvoiceLineTxnLineID)= '" & strTxnLineID & "'));"

No luck unfortunately. Same error message.  I also tried reducing it to one line by deleting the update of LineQuantity.  Also, the update using VBDemo only worked after deleting the Tax quantity update.

If we can update price through VBDemo, surely it must be possible using Access vba?  Do you have any other suggestions, Tom?

Paul

This really is a syntax error at your end, try:

    strSQL = "Update InvoiceLine set InvoiceLineQuantity=" & dbQuantity & ",InvoiceLineRate=" & dbprice _
    & " where TxnID = 'DB8B-1270360935' and InvoiceLineTxnLineID = 'DB8D-1270360935';"

and work back from there.

Syntax error is now fixed with a space in front of WHERE.  The last? remaining difficulty is that updating the price with multiline invoices, deletes all other lines except for one.  This happens in both VB Demo and Access.  When I try to update it after each line is inserted (When the FQSaveToCache is set to 1), the line can not be found to update.

Any ideas greatly appreciated.

Paul

Update operations can only be done after all the "FQSaveToCache" cached invoice lines have been written with a FQSaveToCache set to 0 or header insert.

Normally I would do the Plan B approach as shown in: Using ClassRefListID when inserting invoice lines to put all the lines back into the invoice as a workaround. But who knows what the Reckon modified SDK is going to do?, however, because the TxnID tag acts like a update operation it's worth giving it a try to see what happens?

Thanks for the suggestions.  I followed Plan B as you mentioned but the 2nd line of the invoice deleted the first line, still leaving the invoice header details remaining. 

Paul

Thanks for your help Tom.  I assume that QODBC can not do anything more.

Paul

I see from the Release 10.00.00.269 Fix list that they have now appeared to have corrected the issue when updating line items sometimes it did not copy all non-deleted lines correctly causing them to be deleted also. It might be worth installing QODBC v10.00.00.269 and giving it a try again.

See: Release 10.00.00.269 Fixes for more.

Hi Diego,

I did eventually get it to work by first exporting the invoice and then updating the price.  However, I was using QB 09/10.  My code for updating the price is below.  Please ask if you would like more explanation.

Regards,

Paul

Sub UpdatePrice()
       
    Dim rs As dao.Recordset
    Dim dbInvoicing As dao.Database
    Set dbInvoicing = CurrentDb
    strSQL = "SELECT [Item Number], Price FROM [Export Sales] WHERE RecordID =" & iRecordID
    Set rs = dbInvoicing.OpenRecordset(strSQL)
   
    rs.MoveFirst
    i = 0
    strTxnID = DLast("[TxnID]", "InvoiceLine")
    Do Until rs.EOF
        i = i + 1
      
        strProductName = rs![Item Number]
        dbPrice = rs!Price
      
        Debug.Print dbPrice
       
        strSQL = "Update InvoiceLine set InvoiceLineRate=" & dbPrice & " WHERE TxnID ='" & strTxnID & "' AND InvoiceLineItemRefFullName = '" & strProductName & "';"
        DoCmd.RunSQL strSQL
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub