How to Insert Non-Insertable Custom Fields into Existing QuickBooks Invoices from Microsoft Access using QODBC

Annaliese DellSec-Treas
QODBC and tech tips
This process can be altered to use any database and ODBC because it is a principle and process. However, the sample code in this article uses:

      1. Microsoft Access and DAO
      2. QODBC
      3. QuickBooks

The sample code demonstrates the process and lets you see the it at work.

Problem #1:  Insertion

Not all InvoiceLine fields are insertable. To confirm this for yourself:

      1. open QuickBooks
      2. open VBDemo that came with QODBC
      3. establish a connection to QuickBooks in VBDemo
      4. type:
            sp_columns invoiceline      
      5. click the Query button
      6. scroll down to the customfields
      7. use the horizontal scroll bar to scroll across to the Queryable, Updateable and Insertable columns.
      8. note that some of the custom fields are updateable but not insertable

Question: How do you insert data into non-insertable fields?

Answer: Insert followed by update using the following process:

      1. retrieve the TxnID of the Invoice you want to add lines to
      2. use the Invoice TxnID to insert data to insertable fields
      3. insert only one invoice line at a time
      3. before inserting the next invoice line, use the same TxnID to retrieve all the invoicelines into a query
      4. retrieve the last record from the query--this is the last inserted InvoiceLine
      6. use the InvoiceLineTxnLineID from that last record to update the non-insertable yet updateable fields of the InvoiceLine

Problem #2:  Confirmation

You cannot retrieve the last inserted InvoiceLine, only the last inserted Invoice.

The unique identifiers for invoices and invoicelines are:

      Invoice:         TxnID
      InvoiceLine:  InvoiceLineTxnLineID

The QODBC command sp_LastInsertID can only be used with TxnID and not InvoiceLineTxnLineID.

Question: How do you retrieve the last inserted invoiceline to confirm the insertion?

Answer: The following assumes you:

      1. use a form to input data for inserting InvoiceLines
      2. the form's recordsource is a table

To facilitate confirmation, use the same field names for your table that QODBC uses.

In other words, create a table using QODBC field names.

To get the proper field names, type the following into VBDemo:

      sp_columns invoiceline

After inerting a new line into an existing invoice and before inserting the next line:

      1. use the InvoiceLineTxnLineID you retrieved in steps 5 & 6 above to retrieve all the values from the newly inserted InvoiceLine
      2. now you have both a:
           a. query containing the last inserted InvoiceLine data
           b. table (form's recordsource) containing the same data
      3. declare a boolean variable with a value of True
      4. iterate through the table and query fields, matching field names and comparing values
      5. the first time values do not match, set the boolean to False
      6. notify user of confirmation results by:
            a. message box
            b. text to speech announcement
            c . checkmark on form

Sample Code:

This is only a sample code to let you see invoice lines being inserted into QuickBooks® with custom fields.

In a real-life scenario, you would create the new invoice line from scratch in your database using a form and proper field names.

To avoid all this creation and give you quick results to see the custom field insert at work, the sample code retrieves a test invoice from QuickBooks® so it has the proper field names to work with.

How to Use the Sample Code

      1. paste the code into a new module
      2. create a new QuickBooks® file - do not use your real QuickBooks file
      3. create a test invoice with only one invoice line
      4. in the Reference Number of the invoice type: TestInvoice
      5. date the invoice for today
      6. use at least one custom field in your invoice and enter some data
      7. close and save the invoice
      8. re-open the test invoice so you can watch the new invoice line being inserted
      9. type fncInvoiceLineTable into the Immediate Window of the Visual Basic Editor    
     10. the code creates a new table from some fields and values from the test invoice - we created this table from an existing invoice line to save you the trouble of creating a new table using QODBC fields.
     11. the code opens the table
     12. change some of the custom field values in the open table
     13. Important! Do not change the TxnID
     14. Important! Do not exceed 11 characters for the RefNumber
     15. save and CLOSE the table
     16. you must CLOSE the table
     17. type ExportInvoiceLines into the Immediate Window of the Visual Basic Editor    
     18. watch the new line being added to the invoice in QuickBooks
     19. wait for the Confirmation message box to appear
     20. notes will appear in the Immediate Window of the Visual Basic Editor to give you an idea of what the code is doing


      1. Not all InvoiceLine fields are insertable.
      2. To insert non-insertable InvoiceLine fields, you must first insert the InvoiceLine and then Update the non-insertable yet Updateable fields.
      3. Always confirm the insert completed by comparing the database data to the InvoiceLine data and notify the user of the confirmation status.

Sub ExportInvoiceLines()
                      Dim rs As DAO.Recordset, db As DAO.Database, strSQL As String, strTxnID As String
                      Set db = CurrentDb
                      '********** INSERT INVOICE LINE  **********
                      'Open the recordset that contains the InvoiceLine data you are inserting
                      Set rs = db.OpenRecordset("tblInvoiceLine_RL")
                         With rs
                            strTxnID = rs!TxnID
                               'iterate through the table fields
                               For x = 0 To rs.Fields.Count - 1
                                  'do not use null values or customfield values
                                  If IsNull(rs.Fields(x).Value) = False And _
                                     InStr(1, rs.Fields(x).name, "customfield", vbTextCompare) = 0 And _
                                     rs.Fields(x).name <> "InvoiceLineTxnLineID" Then
                                     Debug.Print rs.Fields(x).name & ": " & rs.Fields(x).Type
                                     'Debug.Print rs.Fields(x).name & ": " & rs.Fields(x).FieldSize
                                        'add fields to the field string for the sql
                                        strFields = strFields & Chr(34) & rs.Fields(x).name & Chr(34) & ", "
                                           'add values to the value string
                                           If rs.Fields(x).Type = 20 Then
                                              'do not enclose number values in quotes
                                              strValues = strValues & Nz(rs.Fields(x).Value, "") & ","
                                           ElseIf rs.Fields(x).Type = 8 Then
                                              strValues = strValues & "{d '" & Year(rs.Fields(x).Value) & "-" & Right("00" & Month(rs.Fields(x).Value), 2) & "-" & Right("00" & Day(rs.Fields(x).Value), 2) & "'}" & ","
                                              'enclose text values in quotes
                                              strValues = strValues & "'" & Replace(rs.Fields(x).Value, "'", "`") & "',"
                                          End If
                                  End If
                               Next x
                          End With
                      'add the SaveToCache field and value
                      strFields = strFields & Chr(34) & "FQSaveToCache" & Chr(34)
                      strValues = strValues & "0"
                      'add  the beginning of the SQL plus the fields and values and TxnID of the invoice
                      strSQL = "INSERT INTO INVOICELINE (" & strFields & ") VALUES (" & strValues & ") "
                      Dim qd As DAO.QueryDef, q As String
                      q = "qryTemp"
                      'call a function to delete existing queries or manually delete it if it already exists
                      Call fncDeleteQuery(q)
                      'create a QODBC query
                      Set qd = db.CreateQueryDef(q)
                      qd.Connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"
                      qd.ReturnsRecords = False
                      qd.sql = strSQL
                            Debug.Print "Insert Query:"
                            Debug.Print qd.sql
                      '********** UPDATE CUSTOM FIELDS **********
                      Dim strInvoiceLineTxnLineID As String
                      'change the query to return records this time
                      qd.ReturnsRecords = True
                      qd.ODBCTimeout = 30
                      'Retrieve the InvoiceLines from the Invoice using the TxnID
                      qd.sql = "select txnid,InvoiceLineTxnLineID FROM INVOICELINE WHERE TXNID='" & strTxnID & "'"
                      'get the InvoiceLineTxnLineID from the last record in the query
                      Set rs = db.OpenRecordset(q)
                      strInvoiceLineTxnLineID = rs!InvoiceLineTxnLineID
                      'change the recordset to the table again to get the values you want to update
                      Set rs = db.OpenRecordset("tblInvoiceLine_RL")
                      'change the query to update this time
                      qd.ReturnsRecords = False
                      qd.sql = "update invoiceline set CustomFieldInvoiceLineCUSTOM2='" & rs!CustomFieldInvoiceLineCUSTOM2 & "'" & _
                              " where txnid='" & strTxnID & "' and invoicelinetxnlineid='" & strInvoiceLineTxnLineID & "'"
                            Debug.Print "Update Query:"
                            Debug.Print qd.sql
                      '************* CONFIRM THE INSERT ************
                      'declare a boolean to hold the value of True or False for confirmation
                      Dim blnConfirmed As Boolean
                      'set the boolean's value to true and it will only change if values do not match
                      blnConfirmed = True
                      'change the query to return records
                      170     qd.ReturnsRecords = True
                      180     qd.ODBCTimeout = 30
                      'select the fields you need that match the fields in your form's table and put them into a confirmation table
                      190     qd.sql = "select InvoiceLineTxnLineID,InvoiceLineItemRefFullName," & _
                      "InvoiceLineQuantity,CustomFieldInvoiceLineCUSTOM2 FROM INVOICELINE WHERE TXNID='" & strTxnID & _
                      "' and invoicelinetxnlineid='" & strInvoiceLineTxnLineID & "'"
                            Debug.Print "Confirmation Query:"
                            Debug.Print qd.sql
                      'create a table from the returned records
                      DoCmd.SetWarnings False
                      DoCmd.RunSQL "SELECT * INTO tblConfirmInvoiceLines_RL FROM " & q
                      DoCmd.SetWarnings True
                      'rs is already set to the form's recordsource table: "tblInvoiceLine_RL"
                      'create rs2 recordset and set it to the confirmation table you created above
                      Dim rs2 As DAO.Recordset, y As Integer
                      Set rs2 = db.OpenRecordset("tblConfirmInvoiceLines_RL")
                      'move to the last record because that will be that last inserted invoiceline
                      Debug.Print "Compare Values"
                      'iterate through the fields of each recordset
                      For x = 0 To rs.Fields.Count - 1
                         For y = 0 To rs2.Fields.Count - 1
                            'if the field names match, compare the values but do not compare invoicelinetxnlineid
                            If rs.Fields(x).name = rs2.Fields(y).name And rs.Fields(x).name <> "invoicelinetxnlineid" Then
                                  If rs.Fields(x).Value <> rs2.Fields(y).Value Then
                                        Debug.Print rs.Fields(x).name & ": " & rs.Fields(x).Value & " <> " & rs2.Fields(y).Value
                                        blnConfirmed = False
                                        Debug.Print rs.Fields(x).name & ": " & rs.Fields(x).Value & " = " & rs2.Fields(y).Value
                                  End If
                            End If 'rs.Fields(x).name = rs2.Fields(y).name
                         Next y
                      Next x
                      'let user know confirmation results
                      If blnConfirmed = False Then
                         MsgBox "Insert incomplete."
                         MsgBox "Insert confirmed."
                      End If
                      Set qd = Nothing
                      Set rs = Nothing
                      Set rs2 = Nothing
                      Set db = Nothing
                      End Sub
                      Function fncInvoiceLineTable()
                      On Error GoTo fncInvoiceLineTable_err
                      Dim db As DAO.Database, rs As DAO.Recordset, qd As DAO.QueryDef, q As String, qbDate As String
                      'delete this query if it already exists
                      Set db = CurrentDb
                      qbDate = "{d '" & Year(Now) & "-" & Right("00" & Month(Now), 2) & "-" & Right("00" & Day(Now), 2) & "'}"
                      q = "qryTemp"
                      Set qd = db.CreateQueryDef(q)
                      qd.Connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"
                      qd.ReturnsRecords = True
                      qd.ODBCTimeout = 60
                      qd.sql = "select TxnID,TxnDate,RefNumber,InvoiceLineQuantity,CustomFieldInvoiceLineOther1,CustomFieldInvoiceLineOther2," & _
                      "CustomFieldInvoiceLineCustom2,InvoiceLineItemRefFullName from InvoiceLine where " & _
                      " RefNumber='TestInvoice' and txnDate=" & qbDate
                      Debug.Print qd.sql
                      DoCmd.SetWarnings False
                      DoCmd.RunSQL "select * into tblInvoiceLine_RL from " & q
                      DoCmd.SetWarnings True
                      Set qd = Nothing
                      Set db = Nothing
                      DoCmd.OpenTable "tblInvoiceLine_RL"
                      Exit Function
                      Debug.Print Err.Number & ": " & Err.Description
                      If Err.Description = "Object 'qryTemp' already exists." Then
                      DoCmd.DeleteObject acQuery, "qryTemp"
                      End If
                      End Function

Open in new window

Real Life

You would use this process with an invoice entry program in a database. You would probably collect data for the new invoice line through a form. The form's recordsource would be a table that uses the same field names as QODBC. This makes coding and comparing data easier.

Your form would have a lookup combo or list box to find the Invoice you want to add invoice lines to. Your form would then import the invoice data, at least the TxnID, because the TxnID tells QuickBooks which Invoice you want to add lines to. The only information you need about an Invoice to write SQL to insert invoicelines is the Invoice TxnID.

Let me know how this works for you or if you have any questions by filling out one of my feedback forms ( or leaving a note.

Our Microsoft Access Invoice Entry program uses this method to add invoice lines to invoices.
Annaliese DellSec-Treas
QODBC and tech tips

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.