Cross Referencing QuickBooks® Invoice Items in Microsoft® Access using QODBC

Annaliese DellSec-Treas
QODBC and tech tips
Published:

Overview: This article:


      (a) explains one principle method to cross-reference invoice items in Quickbooks®
      (b) explores the reasons one might need to cross-reference invoice items
      (c) provides a sample process for creating a Microsft® Access cross-referencing combo box using QODBC

What is QODBC?


QODBC is an ODBC (Open Database Connection) driver for reading and writing QuickBooks® data using SQL (Structured Query Language). Anyone with fundamental VBA knowledge should find it easy. Copy and paste codes exist on VBQuick.com and other sites for beginners

Do I Need QODBC and Microsoft® Access to Cross Reference Invoice Items?


No. The article explains a princple. Any QuickBooks® connection and database may use this principle to produce the same results. QODBC is only one way to import data into the database. Microsoft Access is only one database to import into.

Previous Related Article


A previous article, Invoice Extensibility with QuickBooks®, Microsoft® Access and QODBC, explained how customized invoice programs can:

      1. speed invoice entry
      2. reduce user input error
      3. facilitate cross-referencing of items among customers

Why Cross Reference?


Cross referencing items among customers is generally required by contractors who:

      1. invoice the same items for all customers and;
      2. work for different customers that;
      3. use different item names among them and/or;
      4. use field terms for contract items and/or;
      5. receive work orders from engineers who use different item names

When customers, engineers and supervisors use different item names for the same item, the invoice entry technician needs a quick way cross reference on the fly.

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

Invoice entry improves when technicians can enter any item name from a list like the one above and bill the proper QuickBooks® item.

Focus of This Article


This article focuses on:

      a) Where to store the item list in QuickBooks®.
      b) How to build a combo box that cross references items.

This method can be tweaked to use with Excel, QuickBooks® IIF files or another ODBC connection to QuickBooks®.

The combo box can be altered for use with:

      1. invoice entry
      2. estimate entry
      3. price comparison

What You Need to Use The Sample Code:


      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

This principle applies to any system and version of the above applications and other applications as well.

The code in this article was tested using:

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

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

Where to Store Cross-References in QuickBooks®


One typical way to cross-reference items is to create an item table in Microsoft® Access and join that table to QuickBooks® items. However, that method requires two applications to store data.

The method explained in this article uses only one application to store data. The database merely examines and filters the data.

Why Store Cross-References in the QuickBooks® file?

     

      1. you are free to change database applications
      2. the data remains in QuickBooks® where it is less likely to become corrupted
      3. the data automatically transfers to future QuickBooks® files
      4. QuickBooks® reports can be altered to filter for cross referencing

To store all data in QuickBooks®, you need to understand QuickBooks® tables.

Understanding Invoice Tables


Three important invoice tables are:

      1. Invoice
      2. InvoiceLine
      3. Item

1. Invoice

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.

2. InvoiceLine

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

3. Item

You choose items from the Item table for each invoice line.

Item Descriptions


The Item table includes the field: Description.

The InvoiceLine table includes the field: InvoiceLineDesc.

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

When you select an Item for an invoice line, the Item Description appears on the invoice line.

When you type over that Item description, QuickBooks® stores what you type in the InvoiceLineDesc field of the InvoiceLine table.

Instead of typing over the item description, you may use VBA to store a description in the InvoiceLineDesc field of the InvoiceLine table.

Additionally, you may use VBA to extract information from the Description field of the Item table and store it in the InvoiceLineDesc field of the InvoiceLine table.

Remember:

      Item table: Description
      InvoiceLine table: InvoiceLineDescription

Below, VBA extracted the Description from the Item table for Cust5 and stored it in the InvoiceLineDesc field of the InvoiceLine table.

Item Table                                               InvoiceLine Table                  
--------------------------------------------------------------------------  
Item Name      Item Description              InvoiceLineDesc
--------------         ----------------------              -----------------------  
LCN VP1.01        Default: N VP1.01             N M5-6
                            Cust1: LCN VP1.01  
                            Cust2: N VP1.01
                            Cust3: Transformer Arrestor
                            Cust4: Arrestor
                            Cust5: N M5-6
                            Other : Arrestor on Transformer

You may use VBA to extract the correct item name and store it in the InvoiceLineDesc field of the InvoiceLine table by linking to the customer. Use the colon and carriage return characters to extract item names from the 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. Customer names can change. A customer name change would require changing the name for that customer in all item descriptions. A permanent customer name field stays the same even if the customer name changes.
      2. Customer names require more character space than abbreviated customer references; making human recognition outside the database environment easier.

If you use customer names instead of a designated Customer Field, you may need to program the database to change customer names in the Item table if names change in the future.

If you designate a Customer Field , you may (a) use the Customer Account Number field or (b) create a custom field.

To create a custom field in the customer table:

      1. double click a customer from the customer center customer list
      2. select the Additional Information tab on the edit customer window
      3. click the Define Fields button
      4. create a label
      5. check the customer box to use the label in the Customer table
      6. reference this label for database table linking

The sample code uses the Account Number field of the Customer table. If you intend to copy and paste the code, use the Account Number field.

*** Add Item Names ***


With a Customer Field designated in QuickBooks®, add unique customer item names using a customer reference. Type the customer reference (ie.: AccountNumber or abbreviated customer name) followed by a colon and the desired item name. Make sure each item has its own line. Follow the example in the table above for LCN VP1.01.

Adding Other and Default item names lets you include more descriptive strings when desired. Program the database to use the Default item name when the customer name field does not appear in the list.

You may also add item names used by engineers or foremen. 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 4,095 character limitation for the Item table Description field provides plenty of room for additional item names.

Warning: Once you use the Item table Description field for cross referencing, the entire list appears on invoice lines. Therefore, this method is best used when:

      1. You do not use QuickBooks to print invoices or;
      2. You set the Item table Description field to do not print so only the item name appears on each invoice line.

*Note: To use the sample code, designate the Account Number field of the Customer table. Once you understand how the code works, you may change this and re-program accordingly.

Importing QuickBooks® Items Using QODBC


With a few additional item names added to the QuickBooks® Item table, you may import the items into your database and design the combo box.

For the sake of article length, these codes do not include:

      1. Error trapping. Include your own or use the one at: www.thegenericdatabase.com/2011/09/03-function-fncwriteerror.html
      2. Additional functions. You may already have functions that delete existing queries or return booleans for objects, etc. If not, simply copy and paste the code from the links provided.

Make sure the following references are 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:

     www.thegenericdatabase.com/2011/09/03-references.html

The following code:

      1. creates a query that selects all the data from the QuickBooks® Item table using QODBC
      2. inserts the query data 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

Open in new window


****
Additional functions:

      1. fncDeleteQuery(q): deletes a query if it already exists and can be copied from: www.vbquick.com/2011/10/Microsoft®-access-delete-query-function.html
      1. fncQODBCConnect: puts the QODBC connection string into a function for quick and easy code writing and can be copied from: www.vbquick.com/2011/09/function-qodbcconnect.html

****

Test the function by typing fncImportItems into the Immediate Window of the Visual Basic Editor. Allow QODBC ample time to connect to QuickBooks® and retrieve the item table. Tests required nine seconds to retrieve 1,600 items on a new connection.


*******
NOTES:

The imported table name includes the suffix _RL to indicate this table is reloadable and may be deleted from the database without consequence and reloaded any time through VBA.

Make sure you either:

      1. pasted the two additional functions sited above or;
      2. manually delete the query and use your exact QODBC to QuickBooks® connection string in place of QODBCConnect or;
      3. reference your own delete query function and QODBC connection string

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: One way to avoid closing all forms, reports, etc. when re-writing tables is to pass unique table names to table-writing functions for each form, report, etc. The positive aspect is that you need only set one form's related record sources to "". The negative aspect is database bloat and the need for frequent compacting; especially with continued table refreshing.

You may 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 call statements from the form.
     


Importing Customers



You also need to import the Customer table to link the designated Customer Field to the item table.


The following code:

      1. creates a query that selects all data from the Customer table using QODBC
      2. executes SQL that extracts the query data and inserts it into a table


Copy and paste this 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
                      qd.sql = "SELECT * FROM CUSTOMER"
                      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

Open in new window


*** Create a Simple Form with a Cross Reference Item Combo Box ***


No matter the reason for your cross-reference requirements, you need a Combo Box from which to choose items.

When using a Combo Box on an invoice form, link the designated Customer Field (in this case, Account Number) to the Description field using SQL.

Create a blank form and add the following controls:

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.

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 into the form's code.

Private Sub Form_Open(Cancel As Integer)
                      'set the row sources for the combo box and list box
                           cboItem_RS
                           lstcustomer_RS
                      End Sub
                      
                      Private Sub cboItem_RS()
                      'declare variables for table, customer filter and account filter
                      'this assumes user is using the Account field
                           Dim t As String, sCustomer As String, sAccount As String
                      'assign a table name to the t variable 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 the designated Customer Field and the item lookup 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
                           Else
                                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"
                      cboItem.Requery
                      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
                           cboItem_LostFocus
                      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
                           txtItem_AfterUpdate
                      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
                           txtItem_AfterUpdate
                      End Sub
                      
                      Private Sub txtItem_AfterUpdate()
                      'refilter the item combo box with the new information from the txtItem box
                           cboItem_RS
                      'drop the combo box down so the user doesn't have to
                           cboItem.SetFocus
                           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

Open in new window


Copy and Paste this code into a module for fExistTable:

Function fExistTable(strTableName As String) As Boolean
                      120    Dim db As DAO.Database, i As Integer
                      140    Set db = CurrentDb
                      150    fExistTable = False
                      160    db.TableDefs.Refresh
                      170    Application.RefreshDatabaseWindow
                      180    For i = 0 To db.TableDefs.Count - 1
                      190        If LCase(strTableName) = LCase(db.TableDefs(i).Name) Then
                      200             fExistTable = True
                      210             Exit For
                      220        End If
                      230    Next i
                      240 Set db = Nothing
                      End Function

Open in new window


Open the form and activate the Combo Box to see the list of Items and Descriptions.

Play with the form by entering items and descriptions. Design the form any way you like.

For a tutorial with screenshots, see:

http://www.vbquick.com/2012/03/cross-reference-quickbooks-invoice-item.html

Additional ideas:


     1. Add buttons to the form to refresh the Customer and Item tables. Make sure to set all control row sources to "" before calling the create 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 these for extracting item names.

      3.  Add combo box to filter customers by active status.

      4.  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.
0
6,248 Views
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.