How to Migrate Transactions from Microsoft Access to QuickBooks Part 3

This is a 3 part tutorial that begins here.

Test The Function

If you run into problems, test the function like this.

Create a copy of your QuickBooks® file to use as a test file.

Create a new table in your database with these fields exactly as they are written. Do not mispell, add characters, add fields, etc. These are the only fields you need for a test.

AccountRefFullName must be Data Type Text.

Memo must be Data Type Memo.

ExpenseLineAccountRefFullName Data Type must be Text.

Name Table Fields

Change to Datasheet View and enter values.

Do not use the values below!!!

For AccountRefFullName, use the name of a checking account in your test QuickBooks® file. To keep things simple, do not use a subaccount of another bank account. Use a parent bank account for this test. Type the exact name exactly as it appears in your QuickBooks® file.

For ExpenseLineAccountRefFullName, type the name of one of your expense accounts. Again, do not use a subaccount. Keep this test simple and use a parent expense account and type the name exactly as it appears in your QuickBooks® file.

Close the table. Name the table: test.

Open the Visual Basic Editor and type fncWriteData in the Immediate Window.

Type test as the name of the Microsoft® Access table you wish to transfer.

Type checkexpenseline as the name of the QuickBooks® table you wish to write to.

Give QODBC enough time to connect to QuickBooks® and write the check. Initial connections take a few extra seconds but thereafter writing is quick.

Open the bank account whose name you entered in the test table and look for the check. It will not have a payee because we did not include one so it should be easy to find.

Open the check. Notice the memo you entered in the test table.

If you followed these instructions to the letter but are still having problems migrating data from your Microsoft® access table, the problem must lie in your database table.

To recap problem areas:

  1. Make sure AccountRefFullName,VendorRefFullName and CustomerRefFullName values exactly match those in your QuickBooks® file. For example, ABCStore and ABC Store with a space are not the same.
  2. Do not use ExpenseLineAccountRefListID, AccountRefListID,VendorRefListID and CustomerRefListID. Stay away from ListIDs unless you are absolutely positive you are using the right ones for the corresponding names.
  3. Do not use BillableStatus. Alternatively, write a function that excludes BillableStatus from SQL when Expense Line Account types do not allow it. For example, you cannot select a Billable Status for a payment against a credit card. It is not available. Trying to insert it will cause an error.
  4. Make sure your table field sizes match QuickBooks® field sizes. For example, RefNumber only provides 11 characters. QODBC will not truncate RefNumber but instead cause an error for too many characters.
  5. Make sure you are including all required fields. For example, to find required fields for checks, type sp_columns checkexpenseline into VBDemo and scroll over to the Required_On_Insert column.
  6. Make sure the values for required fields are not Null. For example, you may have included the required field AccountRefFullName and that is good. However, if any of your records have a Null value in that field, an error will occur.
  7. Make sure you are only using Insertable fields. Use VBDemo and sp_columns checkexpenseline (or the table you are writing to) and scroll to the Insertable column. A typical mistake is using the field Amount when you should instead use the field ExpenseLineAmount.
The function prints each SQL statement in the Immediate Window of the Visual Basic Editor. The last SQL statement will be the one that caused the error. Copy it and paste it inot VBDemo for more information on the error.

Let me know how this works for you.

How to Migrate Transactions from Microsoft Access to QuickBooks Part 2

This is a two part tutorial that begins here.

Step 4: Do Not Use Billable Status

Some account types do not allow Billable Status so do not use that field.

For example, if you write a check as a credit card payment, Billable Status is not allowed. If you try to insert Billable Status to a credit card payment check, an error stops the code and the data is not inserted.

To confirm this, create a test check to pay against a credit card and try to select a Billable Status. You cannot.

If you need to migrate Billable Status, include a function that adds or removes that field and value in your SQL according to the Account Type in the CheckExpenseLine.

ASAP I will post this function. Contact me if you need it sooner.

Step 3: Entities and Accounts Must Already Exist

You must make sure Vendors, Customers and Accounts already exist in your QuickBooks® file.


For example, if your table contains a Vendor named ABCStore but this vendor does not exist in your QuickBooks® file, an error will occur and the data will not be inserted.

Additionally, if your table contains a Vendor named ABCStore but your QuickBooks® file calls this vendor: ABC Store (with a space between the ABC and Store), an error will occur because that is the same as that Vendor not existing in QuickBooks®.


The same goes for accounts. You must match the account names exactly as they appear in your QuickBooks® file.

For example, let us say your table account name is Liability Insurance. The matching QuickBooks® account name is the same so you should have no problem, right? Not necessarily. You must match the Full Name. If Liability Insurance is a subaccount of Insurance, the fullname will be: Insurance:Liability Insurance.

Use VBDemo to look up your account names if necessary or their ListIDs by typing this into VBDemo:

     select listid,fullname from account

You may use either the ListID or the FullName. You do not need both.

You cannot use the Name field from the Account table because the line tables (CheckExpenseLine, CheckItemLine, BillExpenseLine, etc.) do not contain that field. The line tables only contain the AccountRefFullName field.

How to Use the Code

1.  Use a copy of your QuickBooks® file until you are satisfied this works for you.

2.  Copy and paste the code into a new module.

3.  Make sure QuickBooks® is open and you have granted QODBC permission to access it.

4.  Match table field names and remove billable status.

5.  Make sure you have included all the necessary data. For example, if you do not include PayeeRefFullName or PayeeRefListID for CheckExpenseLine transactions, your checks will have no Payee.

6. Write to the LINE tables: CheckExpenseLine, InvoiceLine, DepositLine. Use VBDemo to find the corresponding line table for the table you want to write to.

7. Open the Visual Basic Editor and the Immediate window to see the SQL statements as they are written. This gives you excellent examples of the SQL and how to construct it. Additionally, if you run into trouble, you can copy the SQL from the Immediate window and paste it directly into VBDemo for more information on any errors. What kind of errors? Errors like maybe you included a non-insertable field by mistake (Amount is the most common) or your vendor does not exist in QuickBooks or you included BillableStatus on a credit card payment. The code works but you must have followed the instructions on field names, insertable fields and BillableStatus.

8. To watch the code in action, enter some checks with your check register open so you can see them being written.

9. Type fncWriteData into the Immediate Window of the Visual Basic Editor.

10. You will be prompted for the name of the table from your database that you want to migrate.

11. You will be prompted for the QuickBooks® table name to write to. Remember, the corresponding LINE table of the transaction type you are migrating.

Let me know how this works for you.

I use this with QuickBooks 2009 Premier Contractor, Access 2002 and QODBC Version 9.

Continue to Part 3 for help on testing the function if you run into problems:

How to Migrate Transactions from Microsoft Access to QuickBooks Part 1

This tutorial provides a simple function to migrate data from Microsoft® Access to QuickBooks® using QODBC, an Open Database Connection for QuickBooks®.

You do not need to be a programming expert or even know SQL to use this tutorial because the SQL is written for you. Just copy and paste, follow instructions, and migrate away.

The video below shows the migration process as checks are inserted programmatically from  Microsoft® Access to QuickBooks®. The speed remains consistent as data is pulled from the database and inserted into the check register. The code can also be used with:

credit card charges
credit card credits
bill credits
checks (other than payroll checks which require sp_reports)

That is how fast it really works.

You do not need QuickBooks® SDK for this function. All you need is Microsoft® Access, QuickBooks® and QODBC.

This function uses SQL (Structured Query Language) to read from and write to QuickBooks®. An example would be:

      select * from account

The above SQL would be used in a Pass-Through query to extract all the accounts and their fields and values from QuickBooks®. If you did not understand that sentence do not worry! The function at the end of the tutorial is ready to use. It creates the Pass-Through query for you.

To migrate data from another bookkeeping application, import the transactions into Microsoft® Access tables for exporting to QuickBooks®.

The pre-written code uses DAO, so you need to select the DAO reference in your database. If you do not know how to select references, see the post: Chapter 01: Part 2: How to Create a New Module and Select References on my other site: The Generic Database. (

Step 1: Copy Your Tables

After importing transactions into Microsoft® Access, you need to match field names to QuickBooks® field names. Your computer is not a magician nor is it a mind reader. You must tell it what fields to insert data to. Copy the imported tables and change the copies so you still have the original data.

For this particular code, you will need separate tables for each transaction type such as:


Therefore, when exporting transactions from your old application into your database, export them by transaction type so each table contains only one transaction type. In other words, export only checks and then export only bills, etc.

Step 2: Match Field Names

In your copied tables, rename fields to match QuickBooks® field names. The code iterates through the recordset using the recordset field names for SQL. Do not worry if you do not understand this, just know you must match your table field names to QuickBooks® fields names.

For example: the transaction date field of your table must be named: TxnDate. The check number or invoice number must be named: RefNumber. Do not expect your computer to know that transaction date is supposed to be inserted into the TxnDate field.

Match your table fields by looking them up with VBDemo or build yourself a quick and simple form that provides QuickBooks® field names from the tutorial here on VBQuick: QuickBooks Tables in Access w/ QODBC Form at:
To change field names, open the copied tables in design view.

Step 2: Single Line Items

This code migrates single line items - in other words, checks with only one expense line or one item line, invoices with only one invoice line.

ASAP I will post code to migrate multiple line transactions. This is a quick example and quick start to let you see how simple migration can be.

What are Line Items?

When you look at a check in QuickBooks®, the colored area (top of the check) contains data stored in the Check table.

The bottom of the check (check stub) contains data stored in the  CheckExpenseLine table.

When you run a QuickBooks® Custom Transaction Detail report for checks and view the Debit and Credit columns, the bottom entries (from above) appear in the Debit column.

When you select the Expenses tab, the data is stored in the CheckExpenseLine table.

When you select the Items tab, the data is stored in the CheckItemLine table.

To insert checks into QuickBooks®, insert into the CheckExpenseLine or CheckItemLine table. The Check table automatically updates.

This applies to all the transaction tables. Some examples:

      This insert          automatically updates      this table
      CheckExpenseLine                                         Check
      CheckItemLine                                               Check
      BillExpenseLine                                               Bill
      CreditCardChargeExpenseLine                       CreditCardCharge
      InvoiceLine                                                     Invoice
      DepositLine                                                    Deposit

Step 3: Use Only Insertable Fields in Your Table

Your table fields must be insertable. Look them up with VBDemo or create the form referenced above to avoid repeatedly opening VBDemo to check field names and insertable values.

For example, let us say your check table has a field named Amount for the amount of the check. Amount is a field in both the Check and the CheckExpenseLine tables. However, Amount is not insertable in either table.

Insert amounts into checks using the table CheckExpenseLine and the field ExpenseLineAmount. QuickBooks® adds the line amounts and inserts the sum into the Amount field. You cannot change this. You must rename your field named Amount to ExpenseLineAmount so the code uses the proper field name.

If you try to insert a non-insertable field an error stops the code and the data is not inserted.

Open your copied tables in Design view and:
      1. delete non-insertable fields
      2. rename fields to match QuickBooks® fields


QODBC sp_LastInsertID invoiceline

Here is a tidbit I learned about sp_lastinsertid.

First, an explanation of sp_lastinsertid. This is a QODBC stored command that returns the last TxnID or ListID of the last insert done in QuickBooks®.

I use this command to verify data was written. For example, if I insert a check into QuickBooks®, my code would do the following:
  1. create a database table to hold data for a check I wish to insert into QuickBooks®
  2. run a query to insert the table data into QuickBooks® (the check)
  3. run a query to retrieve the TxnID of the last check inserted into QuickBooks®
  4. use the retrieved TxnID in a query to retrieve the check data
  5. compare that check data to the table data
  6. declare a boolean variable with a value of true
  7. iterate through the retrieved data comparing it to the table data 
  8. change the boolean variable to false the first time field values do not equal

An announcement from the computer finalizes the transaction:
  1. text to speech "Insert confirmed." or;
  2. green check mark on the form or;
  3. message box "Insert confirmed."
If all this sounds complicated, remember two things:
  1. Program once and you're done.
  2. Computers are fast! All this takes only seconds to complete.

What I Learned Today

Sometimes it becomes necessary to add a line to a transaction. I need to occassionally add lines to existing invoices. This is accomplished with a query like:

INSERT INTO INVOICELINE ("TxnID","InvoiceLineItemRefListID","InvoiceLineItemRefFullName", "InvoiceLineQuantity", "InvoiceLineRate","InvoiceLineClassRefListID", "InvoiceLineAmount", "FQSaveToCache") VALUES ('5A4D-1330608845','800000DC-1233356154','I P2.R.2',2,80,'8000005F-1233356210',160,0)

TxnID is the TxnID of the invoice not the invoice line. Remember, we are inserting this new line into an existing invoice so we need to know which invoice to put it in.

So, the SQL above inserts one line into an existing invoice. If this were a new invoice, TxnID would not be known and therefore not included in the SQL.

To confirm the line was inserted, I tried retrieving the lastinsertid with this SQL:

sp_lastinsertID invoiceline

This does not work because sp_lastinsertid only retrieves the last inserted ListID or TxnID.

InvoiceLine unique identifiers are InvoiceLineTxnLineID, not TxnID. Since the invoice already exists, you cannot retrieve its TxnID with sp_lastinsertid.

You must use another method to confirm insert.

Since you already know the TxnID of the invoice, you can use it to retrieve the lines from the invoice like this:

          "select * from invoice where TxnID='5A4D-1330608845' "

The above SQL returns all the invoice lines for that invoice. Then:
  1. assign true to a boolean variable
  2. create a recordset for the returned data
  3. move to the last record in the recordset since that will be the last inserted invoice line
  4. interate through the fields comparing returned data to the table data
  5. change the boolean variable to false the first time field values do not equal
Now that wasn't so hard, was it?

How To Use QuickBooks to Cross Reference Items Part 2

This is a two-part tutorial - begin here.

Importing the Item Table from QuickBooks® Using QODBC

After adding a few additional item names to the QuickBooks® item table, you are ready to import the items into your database and design a combo box.

For the sake of brevity, the code here does not include:

      1. Error trapping. This code has been tested but error trapping is simply good programming. Include your own or use the error trapping routine:

      2. Additional functions: Use the ones from the links provided or use your own:
            a. fncDeleteQuery: deletes existing queries

            b. fExistTable: deletes existing tables

You will need the following references selected in your Microsoft® Access database:

      1. Visual Basic for Applications
      2. Microsoft Access 10
      3. Microsoft DAO

If you do not know how to do select references, see this post on The Generic Database:

      Chapter 01: Part 2: How to Create a New Module and Select References

VBA Code

The following code:

      1. selects all the data from the QuickBooks® Item table using an SQL query and QODBC
      2. executes another query to extract the QODBC query data and insert it into a table

Copy and paste the code below into a new or existing module via the Visual Basic Editor:

Function fncImportItems()
Dim db As DAO.Database, qd As DAO.QueryDef, q As String
q = "qryTempImportItems"
Call fncDeleteQuery(q)
Set db = CurrentDb
Set qd = db.CreateQueryDef(q)
qd.Connect = QODBCConnect
qd.ReturnsRecords = True
qd.sql = "select * from item"
DoCmd.SetWarnings False
DoCmd.RunSQL "select * into tbl_Item_RL from " & q
Set qd = Nothing
Set db = Nothing
DoCmd.SetWarnings True
End Function

Additional functions - add to a new module

The code on my sites often uses the following two functions. If you do not have them in your database, you will need them for this code to work:

      1. fncDeleteQuery(q): 
            VBA Access Function fncDeleteQuery Code Only

      2. fncQODBCConnect: 
            Function QODBCConnect

Test the Function: Import the Item Table

Type fncImportItems into the Immediate Window of the Visual Basic Editor. Give QODBC enough time to connect to QuickBooks® and import the item table. Tests took nine seconds to import 1,600 items on a new connection.


      1. The new table name includes the suffix _RL. This tells you the table can be deleted because the database contains code to reload it.

      2. Make sure you either:
            a. pasted the two additional functions into a module or;
            b. delete the query each time you run the code and replace the function call QODBCConnect with your QODBC connection string or;
      3. reference your own delete query function and QODBC connection string function

You may call this function from any module or form in your database. Make sure to close any forms, reports, etc. using the table: tbl_Item_RL to avoid a runtime error informing you the table is already in use.

*Note: Instead of closing forms, reports, etc. all using the same record source, you could pass unique table names from each form that calls fncImportItems. The positive aspect is that you only have to set one form's related record sources to "". The negative aspect is frequent compacting due to database bloat; especially with continued table refreshing.

Pass unique table names by replacing these two lines:

      Function fncImportItems()
      DoCmd.RunSQL "select * into tbl_Item_RL from " & q

with these two lines:

      Function fncImportItems(UniqueTableName as string)
      DoCmd.RunSQL "select * into " & UniqueTableName & " from " & q

Make sure to declare the UniqueTableName variable and name the table in all the call statements to fncImportItems from the form.

Importing Customers

You also need the Customer table to lookup customers in the Combo Box.

The following code:

      1. selects all data from the Customer table using and SQL query and QODBC
      2. executes another query that puts that data into a table

Copy and paste the following function into a module in your database:

Function fncCustomerTable()
Dim db As DAO.QueryDef, qd As DAO.QueryDef, q As String
q = "temp"
Call fncDeleteQuery(q)
Set qd = CurrentDb.CreateQueryDef(q)
qd.Connect = QODBCConnect
qd.ReturnsRecords = True
qd.ODBCTimeout = 60
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT * INTO [tbl_Customer_RL] FROM [" & q & "];"
Call fncDeleteQuery(q)
Set qd = Nothing
Set db = Nothing
DoCmd.SetWarnings True
End Function

Create a Simple Form with a Combo Box Lookup

Whatever the reason for your cross-reference needs, a Combo Box is ideal for filtering and choosing items.

If you use a Combo Box on an invoice form, link the Customer table AccountNumber field to the Item table Description field using SQL.

Create a blank form with these nine controls. Be sure to use the same exact names and check your spelling because the code uses these names. If you change the names of the controls, the code will not work. The labels do not matter.

      1. Combo Box named: cboItem. Label: "Items". Hit Cancel if the Combo Box Wizard appears.
      2. Text Box named: txtItem - label "Find Item" or something similear
      3. Text Box named: txtItemName - label "Item Name"
      4. Text Box named: txtItemListID - label "Item ListID"
      5. Text Box named: txtSalesPrice - label "Price" or "Sales Price" or something similar
      6. Text Box named: txtItemDescription - label "Item Description" - elongate this text box because the code replaces chr(10) with linefeeds so each customer appears on a different line. Set the text box 'Enter Key Behavior' (Other Tab on the Properties Window) to 'New Line in Field'
      8. Command Button named: cmdSelectNone - label "Select None" - to clear customer selections
      9. List Box named: lstCustomer - label "Customers" and elongate to make scrolling faster.

*Important: make sure the cboItem control Tab Index immediately follows the txtItem Tab Index.

The form below was altered to take up less screen space. You may design your form any way you like, any color, size, etc. so long as you do not change the names of the controls.

Notice the customer list contains duplicate customers. The reason is that the customer import function imports jobs which you can translate as contracts. Different contracts may use different item names when produced by different engineers. Therefore, using jobs instead of customers lets you add unique item names for each contact if needed.

VBA Code

To avoid tedious instructions, this code sets Combo and List Box properties. You may set these manually and delete the code settings.

Copy and paste the following code (lines between --- begin and --- end) into the form's code.

--- begin

Private Sub Form_Open(Cancel As Integer)
'set the row sources for the combo box and list box
End Sub

Private Sub cboItem_RS()
'declare variables for table, customer filter and account filter
'this assumes user is using the AccountName field
Dim t As String, sCustomer As String, sAccount As String
'assign a table name to t to use for storing imported item data
t = "tbl_Item_RL"
'check to see if the table exists.
'If it doesn't, call the function to import the items and create the table
If fExistTable(t) = False Then fncImportItems
'set the item combo box properties
'you may change these or remove them from the code and
'set them with the combo box properties sheet
cboItem.RowSourceType = "Table/Query"
cboItem.ColumnCount = 5
cboItem.ListWidth = 10000
cboItem.ColumnWidth = 4500
cboItem.ColumnWidths = "0;2000;1000;8000;0 "
cboItem.ListRows = 20
'create a string to filter by a) AccountNumber and b) the item text box
If lstCustomer.ItemsSelected.Count > 0 Then
Dim v As Variant
For Each v In lstCustomer.ItemsSelected
sAccount = Nz(lstCustomer.Column(2, v))
 If Len(sAccount) = 0 Then sAccount = "Nothing"
sCustomer = sCustomer & " [tbl_Item_RL]![Name]" & _
" & [tbl_Item_RL]![Description] like '*" & sAccount & "*' " & _
" and [tbl_Item_RL]![Name] & [tbl_Item_RL]![Description] like '*" & txtItem & "*' or "
Next v
sCustomer = " [tbl_Item_RL]![Name] & [tbl_Item_RL]![Description] like '*" & Nz(txtItem, "") & "*' "
End If
'remove the last 'or' from the customer filter string
If Len(sCustomer) > 0 Then sCustomer = " AND " & Left(sCustomer, (Len(sCustomer)) - 3)
'apply the sql string to the combo box row source
cboItem.RowSource = "select listid,name,SalesPrice,description,isactive from " & t & _
" where isactive=-1 " & sCustomer & " order by name"
End Sub

Private Sub lstcustomer_RS()
'declare variable for customer table
Dim t As String
'name the customer table
t = "tbl_Customer_RL"
 'check to see if the customer table exists and if not, create it
If fExistTable(t) = False Then fncCustomerTable
 'set the customer list box properties
'you may change these or remove them from the code and
'set them with the list box properties sheet
lstCustomer.RowSourceType = "Table/Query"
lstCustomer.ColumnCount = 4
lstCustomer.ColumnWidth = 8000
lstCustomer.ColumnWidths = "0;4000;3000;0"
 'set the customer list box row source
lstCustomer.RowSource = "select listid,fullname,accountnumber,isactive from " & t & " where isactive=-1 order by fullname "
End Sub

Private Sub cboItem_Click()
'call the lost focus sub in case user clicks but stays in the item combo box instead of tabbing out
End Sub

Private Sub cboItem_LostFocus()
'fill the information boxes with data
'alternative: set the form's record source to the item table
'if you do this, programmatically set the form's record source to "" before
're-creating the item table or you will receive the 'table in use' error message
'then use the find record method to display the item record
'be sure to set each text box's control source to its corresponding table field
txtItemListID = cboItem.Column(0, cboItem.ListIndex)
txtItemName = cboItem.Column(1, cboItem.ListIndex)
txtSalesPrice = cboItem.Column(2, cboItem.ListIndex)
txtItemDescription.EnterKeyBehavior = True
txtItemDescription = Replace(Nz(cboItem.Column(3, cboItem.ListIndex), ""), Chr(10), Chr(13) & Chr(10), 1)
End Sub

Private Sub cmdSelectNone_Click()
'declare a variant to move through the selected customers
Dim v As Variant
'move through the selected customers and unselect them
For Each v In lstCustomer.ItemsSelected
lstCustomer.Selected(v) = False
Next v
're-filter the item combo box
End Sub

Private Sub lstCustomer_Click()
'call the lost focus sub in case user clicks but stays in the item list box instead of tabbing out
End Sub

Private Sub txtItem_AfterUpdate()
're-filter the item combo box with the new information from the txtItem box
'drop the combo box down so the user doesn't have to
SendKeys "{F4}"
'if the combo box contains items, automatically select the first item
'user can change this if necessary but the convenience of automatic selection is user friendly
If cboItem.ListCount > 0 Then
cboItem = cboItem.Column(0, 0)
End If
End Sub

--- end

Copy and Paste this code into a module for fExistTable:

Function fExistTable(strTableName As String) As Boolean
Dim db As DAO.Database, i As Integer
Set db = CurrentDb
fExistTable = False
For i = 0 To db.TableDefs.Count - 1
If LCase(strTableName) = LCase(db.TableDefs(i).Name) Then
fExistTable = True
Exit For
End If
Next i
Set db = Nothing
End Function

Play with the New Form

Open the form. If you have not yet imported the Item and Customer tables, give the form enough time to import these two tables. Activate the Combo Box to see the list of Items and Descriptions.

Play with the form by entering items and descriptions, selecting and de-selecting customers and contracts.

Design the form any way you like.

Additional ideas:

1. Add buttons to refresh the Customer and Item tables. Make sure to set all control row sources using the tables to "" before calling the table functions.

2. Notice that the descriptions run together in the Combo Box, separated by small squares. These squares represent chr(10). Program your invoice form to use chr(10) to extract item names.

3. Program the database to remove Item names for a specific contract no longer in effect, thus saving data space and making the item list easier to scroll manually.

4. Join ListID from the Item Table to PriceLevelPerItemItemListID from the PriceLevelPerItemTable for using the QuickBooks custom price feature.

5. Add a combo or list box to filter customers by active status.

6. Create a simple form to notify the user that Microsoft® access is connecting to QuickBooks®. This helps during initial connections when there is a lag time and the user may wonder what is happening.

How to Use QuickBooks to Cross Reference Items Part 1

This is a two part tutorial that:

      1. examines reasons for cross-referencing item names
      2. provides a principle that can be applied with different applications
      3. provides copy and paste code that demonstrates the method using:
            a. QuickBooks®
            b. QODBC
            c. Microsoft® Access

Why Cross Reference Items?

Contractors generally work under awarded contracts and invoice from a list of items in their bookkeeping application.

Contracts generally list items, quantities and prices. However, contract item names often differ from the contractor's item names.

Let us examine a real-life example from RUS (Rural Utilities Services division of The United States Department of Agricultural). One of the RUS unit names is: LCN N1.2. However, some electric cooperatives, engineers and contractors still use the old unit name: N M5-19.

Creating two separate items in the invoice application makes comparing bids and jobs and creating invoices from staking sheets (or work orders) difficult.

The method of cross-referencing item names in QuickBooks® outlined in this post solved a real need in a real business and has been used and tested for many years. It works and is an integral part of any invoice extensibility program.

Invoice Extensibility

A previous article:

            Using QODBC to Create a Customized Data Entry Interface for QuickBooks Invoices

...explained how customized invoice programs can:

      1. reduce invoice entry time
      2. reduce human error in data entry
      3. offer item cross-referencing or item lookup capabilities

This post:

      (a) explains how to cross reference item names directly in QuickBooks® and;
      (b) outlines a process to use that method and;
      (c) provides copy and paste code for beginners

No matter what bookkeeping or database application you use, you will benefit from this post because it explains a principle. Copy and paste code demonstrates the method using QODBC and Microsoft® Access but you can still benefit from the information here without either.

If you do not use QODBC, you can apply this method using your favorite spreadsheet or database application because once you know a principle, application of that principle is almost limitless.

For example, you could use the QuickBooks® export-to-spreadsheet feature to import the same data used in this post into your favorite database application, eliminating the need for QODBC.
Alternatively, you could use another ODBC connection or even the QuickBooks® SDK itself to import data into your database or a customized program you wrote yourself.

Quick Introduction to QODBC

QODBC is an ODBC (Open Database Connection) driver that uses SQL (Structured Query Language) to read and write data between QuickBooks® and databases. You do not need the QuickBooks® SDK (Software Development Kit) to use QODBC. You only need an application that lets you write your own SQL, like Microsoft® Access, Microsoft® Excel and others.

If you do not know SQL, this post provides copy and paste code. Copy and paste codes for QODBC exist here ( and on other sites for beginners.

If you would like a tutorial on using Microsoft® Excel, please let me know. I often write posts when enough interest is generated.

QuickBooks® Item Names

When customers, engineers and supervisors use different names for the same item, the invoice entry technician needs a way cross reference directly in the invoice application.

For example, a typical RUS (Rural Utilities Service - an agency of USDA) construction unit LCN N1.2 may vary as follows:

      RUS:             LCN N1.2
      Contract 1:    N N1.2
      Contract 2:    N M5-19
      Field Term:    Neutral Upset

A quick way to cross reference on a database form is to use a text box for string entry and a combo box for item selection as follows:

      1. string is entered into text box
      2. combo box is filtered for:
            a) customer, contract, supervisor or engineer and;
            b) item string from text box
      3. user selects item from drop-down combo box

The code in this post uses QODBC to import the QuickBooks® item list into a Microsoft® Access database and fill and filter a combo box.

With a few tweaks, this method can use Microsoft® Excel, QuickBooks® IIF files or another ODBC connection to QuickBooks® to accomplish the same goal.

The combo box can be altered for use with:

      1. invoice entry
      2. estimate entry
      3. price comparison
      4. price level per item price lists

What You Need

To use the principle, you need only read and understand the information.

To use the code, you need:

      1. QODBC, QuickBooks® and Microsoft® Access properly installed and working on the system
      2. QuickBooks® has been granted permission to connect to Microsoft® Access using QODBC.
      3. The user knows how to create a form and controls and use the Visual Basic Editor

The code in this article was tested using:

      1. QuickBooks® Premier Contractor 2009
      2. Microsoft® Access 2002
      3. QODBC Version 9

It does not matter what versions of the above applications you are using because this is a principle.

If no invoice program exists in Microsoft® Access, this process may be used to simply look up QuickBooks® item names by customer, contract, engineer or field term.

Adding Cross-References to the QuickBooks File

Cross referencing items may be accomplished by creating an item table in Microsoft® Access and joining it to an imported QuickBooks® item table. However, that method requires:

      1. two applications to store data
      2. two tables to store data

This post explains how to store all the data in one application and one table and use the database only to filter and manipulate the data.

The benefits of storing all the data in the QuickBooks® file are:

      1. you may use any database application or change database applications if desired
      2. QuickBooks® is less likely to become corrupted
      3. the data transfers automatically to future QuickBooks® files
      4. QuickBooks® reports can display and filter the data

To store all data in QuickBooks®, you need a method to add data to existing QuickBooks® items and that is the foundation of this principle.

Understanding QODBC and QuickBooks® Invoice Tables

The two invoice tables are:

      1. Invoice
      2. InvoiceLine

You write to the Invoice table to create an invoice. The Invoice table stores the customer, date, invoice number, due date, ie.: the invoice header information.

You write to the InvoiceLine table to create each individual line on the invoice.

You choose items for each InvoiceLine from the Item table (list of items).

The Item table includes the field: Description.

The InvoiceLine table includes the field: InvoiceLineDesc.

          Item Table                     InvoiceLine Table
          ----------                        ------------------
          Description                      InvoiceLineDesc

You enter descriptions into the item table through the New Item window in QuickBooks®.

The screenshot below highlights the description box where you enter descriptions and item names.

The descriptions that you type into the text box highlighted above:

      1. are stored in the Item table
      2. appear on the Invoice Line in QuickBooks® but are not stored in the InvoiceLine table

When you create an invoice in QuickBooks® and select an item, this description appears on the description line of the invoice.

When you type over this description, QuickBooks® stores that information in the InvoiceLine table. The information you typed only appears on that invoice on that line and no other invoice or line.

Just as you may change the item description for any invoice line, you may also change it with VBA. Further, you may use VBA to extract characters from the original Item description and store them in the InvoiceLine.

In other words, you may use VBA to extract the string N VP1.01 from the orginal description and store only that string in only that invoice line. You may also use VBA to store that string in all invoice lines where that particular Item appears. That is up to you and your needs.

How to accomplish this may be addressed in another post. This post deals only with creating a Combo Box for cross referencing. The Combo Box lets you enter the contract, customer, engineer or supervisor's item name to find the desired QuickBooks® item to invoice.

The table below illustrates extraction of an Item name for the contract CREC UH765. The item name N M5-6 may be inserted into the Invoice Line as the InvoiceLineDesc. It then replaces the Item table description: LCN VP1.01 but only on that specific invoice line.

Item Table                                                    InvoiceLine Table

Item Name                       Item Description                      InvoiceLineDesc
--------------                   -----------------                     -----------------------

LCN VP1.01                   Default: N VP1.01                   N M5-6
                                        Contract 1: LCN VP1.01
                                        ABC Elec: N VP1.01
                                        BWRec: Transformer Arrestor
                                        Cust4: Arrestor
                                        CREC UH765: N M5-6
                                        Other : Arrestor on Transformer

When programming for extraction, use the colon and carriage return characters to extract Item names from the item list.

Before doing this; however, you must designate a permanent Customer Field to use for cross-referencing.

Customer Field

You designate a specific field in the Customer table to use for cross referencing because:

      1. If a customer name changes, you would then have to change the name for that customer in all your item descriptions where it appears. A dedicated customer name field stays the same even if the customer name changes.
      2. Abbreviated customer references require less character space and are also easier to recognize.

If you use full customer names instead of a designated Customer Field, you may need to program the database in the future to change customer names in the Item table when they change. You may prefer doing this over using abbreviated customer names.

If you designate a Customer Field for cross referencing, you may use:

      1. the Customer Account Number field or;
      2. a custom field created for this purpose

To create a custom field in the customer table:

      1. open the Edit Customer window by double-clicking a customer in the customer center
      2. select the Additional Information tab
      3. click the Define Fields button
      4. create a new label
      5. make sure the customer box is checked
      6. reference this custom field in your database

This article uses the AccountNumber field from the Customer table in the following code. If you will copy and paste this code, use the AccountNumber field at least for now.

Adding Item Names

With a Customer Field designated in QuickBooks®, add unique customer item names using this new customer reference. Type the customer reference followed by a colon and the additional item name. Make sure each item has its own line. Follow the example above: New Item Window

If you add Other and Default item names, you can include more descriptive strings for your invoice lines. Program the invoice form to use the Default item name when the customer abbreviation is not on the list.

Engineer and supervisor item names can also be included. Precede the colon with the engineer's or foreman's name as follows:

          LCN VP1.01      Default: N VP1.01
                                     Cust1: LCN VP1.01
                                     Cust2: N VP1.01
                                     Cust3: Transformer Arrestor
                                     Cust4: Arrestor
                                     Cust5: N M5-6
                                     Other : Arrestor on Transformer
                                     J. Smith: Trans Arrestor
                                     O. Black: M5

The Description field of the QuickBooks® Item table provides 4,095 character spaces; plenty of room for additional item names.

Warning: Once you add item names to the Item Description field for cross referencing, the entire list appears on invoice lines in the description. See the illustration above:  Description On Invoice. Therefore, this method is best used when:

      1. You use another application than QuickBooks® to print invoices or;
      2. You set Item Description to do not print so only item names appear on invoice lines.

Next: Importing the Item Table from QuickBooks® Using QODBC