<

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

Published on
12,492 Points
6,492 Views
Last Modified:
Approved
Annaliese Dell
Find Annaliese on Amazon, Barnes & Noble and VBQuick.com.
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

Summary


      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
      .MoveFirst
      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) & "'}" & ","
                     Else
                        'enclose text values in quotes
                        strValues = strValues & "'" & Replace(rs.Fields(x).Value, "'", "`") & "',"
                    End If
            End If
         Next x
    End With
rs.Close
         
'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
      Debug.Print
qd.Execute


'********** 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)
rs.MoveLast
strInvoiceLineTxnLineID = rs!InvoiceLineTxnLineID

'change the recordset to the table again to get the values you want to update
Set rs = db.OpenRecordset("tblInvoiceLine_RL")
rs.MoveFirst

'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
      Debug.Print
qd.Execute


'************* 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
      Debug.Print

'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
rs2.MoveLast
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
            Else
                  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."
Else
   MsgBox "Insert confirmed."
End If


Set qd = Nothing
rs.Close
rs2.Close
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


fncInvoiceLineTable_exit:
DoCmd.SetWarnings True
Set qd = Nothing
Set db = Nothing
DoCmd.OpenTable "tblInvoiceLine_RL"
Exit Function
fncInvoiceLineTable_err:
Debug.Print Err.Number & ": " & Err.Description
If Err.Description = "Object 'qryTemp' already exists." Then
DoCmd.DeleteObject acQuery, "qryTemp"
Resume
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 (vbquick.com) or leaving a note.

Our Microsoft Access Invoice Entry program uses this method to add invoice lines to invoices.
0
Comment
0 Comments

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Join & Write a Comment

In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month