Cross Referencing QuickBooks® Invoice Items in Microsoft® Access using QODBC
Published:
Browse All Articles > Cross Referencing QuickBooks® Invoice Items in Microsoft® Access using QODBC
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
****
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(UniqueTable
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
*** 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
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 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.
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)