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:
           
            fncWriteError
            http://www.thegenericdatabase.com/2011/09/03-function-fncwriteerror.html
    


      2. Additional functions: Use the ones from the links provided or use your own:
         
            a. fncDeleteQuery: deletes existing queries
               www.vbquick.com/2011/10/microsoft-access-delete-query-function.html

            b. fExistTable: deletes existing tables
                www.thegenericdatabase.com/2012/02/visual-basic-vba-function-fexisttable.html
.

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

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




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
            www.vbquick.com/2011/10/Microsoft-access-delete-query-function.html

      2. fncQODBCConnect: 
            Function QODBCConnect
            www.vbquick.com/2011/09/function-qodbcconnect.html



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.



NOTES:

      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
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 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
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 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
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()
're-filter 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

--- 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
db.TableDefs.Refresh
Application.RefreshDatabaseWindow
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.

Comments

Popular Posts