How to Insert Non-Insertable Custom Fields into Existing QuickBooks Invoices from Microsoft Access using QODBC
Published:
Browse All Articles > How to Insert Non-Insertable Custom Fields into Existing QuickBooks Invoices from Microsoft Access using QODBC
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.
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
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.
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.
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.
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 FunctionReal 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.
QODBC and tech tips
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.
Comments (0)