Linking Spreadsheets in Microsoft Access

This is part of a tutorial on viewing QuickBooks transactions while QuickBooks is closed. The tutorial begins here.

Tutorial: Part 2

The method in Part 1 used spreadsheets to store and access QuickBooks data. QuickBooks makes exporting spreadsheets fast and painless; however, accessing, filtering and searching spreadsheets leaves room for improvement. We can improve on this method by using a database.

A database lets you combine tables to search all data at once. Remember our three tables?

2010
2011
2012
A database can combine these tables through VBA or queries into one big table for fast, easy searches.

Since spreadsheet data changes, we need a quick way to update it in the database and that solution is Linking. Linking lets you export spreadsheets from QuickBooks when it's open and refresh the spreadsheets when it's closed.

Any database will do but this tutorial uses Microsoft Access in examples.


These steps were recorded using Microsoft Access 2002. Your version of Microsoft Access may differ in appearance but the basic steps remains the same.







Only refresh the linked tables when you need the data. In other words, when you change transactions in QuickBooks, export the memorized spreadsheet but do not open the database and refresh the link at that time. Close QuickBooks and only refresh the links when you open the database to view current information.

For example, perhaps on Monday you needed to change a memo or make a note in a 2011 transaction. You open the 2011 QuickBooks file and make the change. Before closing QuickBooks, export the memorized spreadsheet and do nothing else.

Then on Friday, you need to see something in the 2011 QuickBooks transaction while you are working in the 2012 QuickBooks file. You open the database, refresh the linked table and view the most current transaction data.

Maybe you are an accountant or bookkeeper working in the ABC QuickBooks file. Before closing QuickBooks, export the spreadsheet and do nothing else. Later in the week, you are working in the XYZ file when the ABC client calls with a question. Open the database, refresh the linked table and view the current data without closing XYZ QuickBooks. Hang up and continue working without losing where you were.

We can improve on the manual linking method by writing a bit of code and creating a button on a form to refresh the linked tables and this is explained in the next post.

Continue to the next post or keep checking back if it is not written yet.

How to View QuickBooks Transactions with QuickBooks Closed

Tutorial: Part 1

Do you:
  • have more than one QuickBooks file?
  • archive QuickBooks files occasionally?
  • ever start a new company file to speed performance?
  • store client files?
  • frequently close one file and open another for yourself or clients?

The solution is:

Store QuickBooks transactions in date chunks in another application so you can access them when QuickBooks is closed.

In addition to transactions, this solution also works for accounts, vendors, customers, items, etc. Therefore, we will refer to this information as:
  • source data: residing in QuickBooks files
  • stored data: exported from QuickBooks and stored in another application



How the Solution Works for Historical Data

Since historical data rarely changes, you can rely on stored data as long as you refresh it when you change the source data.

For example, you may store transactions in blocks by year and have three tables:
  1. 2010
  2. 2011
  3. 2012
You may frequently access these tables with QuickBooks closed. To change the data, however, you must open QuickBooks. After changing the data (transactions, account names, items, etc) and before closing QuickBooks, refresh the table so it remains accurate until the next time you open QuickBooks.

To review:

The only data you need to refresh is data you change in the open QuickBooks file.
You cannot change stored data while QuickBooks is closed, therefore it remains accurate until you open QuickBooks and change it. The only exception is when using ODB connections and certain file permissions but even then you can only change one file and all others must be opened to change data.
After changing data and before closing QuickBooks, refresh the stored data.


Multiple Company Files

The same principle applies to multiple company files. You cannot change any company's QuickBooks data unless the file is open. Before closing the file, refresh the stored data and it remains accurate when QuickBooks is closed.

Store multiple company files by date but also include a higher level of identifier: Company Name. Your list of stored tables might look something like this:

ABC Company 2009
ABC Company 2010
ABC Company 2011
XYZ Company 2009
XYZ Company 2010
XYZ Company 2011

Accessing Paperless Receipts

Another benefit of stored data is access to paperless receipts with QuickBooks closed. If you use the The Link Principle (Amazon, Barnes & Noble) to link transactions to paperless receipts, you can view any receipt in seconds from the data in your stored transaction list.


How to Store QuickBooks Transactions

A variety of methods exist and we'll begin with the easiest but least effective and end with the most effective that uses VBA. Copy and paste VBA is included so you need not know VBA to use the method.

Method #1:

The simplest way to store QuickBooks transactions is to export to spreadsheets. QuickBooks provides this feature for you. To include all transactions, use the Custom Transaction Detail report, unless you only want specific transactions like checks, bills or credit cards or variations of combinations of these.

To create the Custom Transacction Detail report, follow these steps (see video below):

  1. click Reports
  2. select Custom Reports
  3. select Transaction Detail
  4. select a Date Range
  5. select the Columns you want on your report
  6. click OK
  7. click Memorize
  8. name the Report so you can find it later
  9. save it in a Report Group or create a new Report Group
  10. click OK
  11. click Excel
  12. create a New Worksheet the first time; thereafter, if you are saving the same date range,
    select Update Existing Worksheet
  13. save the report
  14. if updating an existing worksheet, select the sheet from the drop-down box QuickBooks provides for you that lists all worksheets in the select workbook



How and where you store spreadsheets is up to you. You might store each company in a separate workbook and keep each date block (year, quarter, etc.) on a separate worksheet. The possibilities are many.

This is the easiest method as far as exporting; however, saving and accessing spreadsheets means remembering their locations. In addition, each date block will reside on at least its own worksheet if not its own workbook. You may need to open more than one workbook or worksheet to find the transaction you need. This is still faster than opening QuickBooks each time you need to view a transaction, especially if you have to close one file and open another.

We can improve on this method by using a database and this is covered in the next post.

Continue to the next post

Microsoft Access Database Error -2147352567: The data has been changed.

If you receive this message and you know no one else has changed your record, does your form contain unbound controls?

If you tried DoCmd.RunCommand acCmdSaveRecord to no avail, try requerying the form or subform.

If a subform triggers the error and requerying the subform doesn't work, try Me.Parent.Requery to requery the subform's parent form.




Pleasant Surprise! QODBC and QuickBooks 2012 Offer More Updateable Fields

If you have been to this site before, you probably know I use QODBC to write data from Microsoft Access to QuickBooks and to import data from QuickBooks into Microsoft Access.

I recently upgraded to QuickBooks 2012 from QuickBooks 2009 which also meant upgrading QODBC. I wish I'd upgraded sooner. What a pleasant surprise to discover previously non-updateable fields are now updateable.

For example, in the older versions of QuickBooks and QODBC, CheckExpenseLine memos were not updateable which meant moving back to QuickBooks from Microsoft Access to add or change wording in these memos. These are the memos on the check lines and not the checks.

DepositLineMemos are also updateable now.

If you use The Link Principle to link transactions to receipts and use QODBC, this is very good news because you can now automate your database to create the links for these previously non-updateable fields and write them to QuickBooks from your database.

If you are still composing memos like "ink for printer" or "fixed brakes on truck" and you scan receipts into the computer, you are missing a golden opportunity. For more on how to organize paperless records in seconds plus link them to QuickBooks, Quicken or other financial software, see The Link Principle (Amazon or Barnes & Noble).

QuickBooks ODBC Database Rules 1

Creating forms in Microsoft Access to use with QuickBooks and QODBC is fairly simple if you stick to a few basic rules. This series contains the basic rules and each post will explain one rule. Are you ready?

Rule One: Use QuickBooks field names in your database tables.

First we'll examine why this makes your life easier and then I'll give you a quick and easy way to apply this rule.

This is the easiest rule to follow and to implement as well. While this rule is easier to apply to a new database, you can still apply it to an existing database by changing your field names.



Why Use QuickBooks Field Names

Using QuickBooks field names makes programming easier because you do not need to map fields.

Mapping Fields Explained

Let us say you have a customer database you want to link to QuickBooks. You would like to import QuickBooks customers into your database as well as insert and update customers from your database into QuickBooks. If you use different field names than QuickBooks uses, you will have to tell your database which table fields go with which QuickBooks fields in all your code.

First, an explanation of how you import data. You import QuickBooks data with pass through queries like this:

      1. write a pass through query that returns records
      2. Write SQL to select data from the pass through query and place it in a database table

If your field names do not match QuickBooks field names, you will forever be writing complicated SQL. The pass through query will remain the same. The SQL that transfers data from the pass through query to your table is where the difficulty arises. If the field names differ between the pass through query and the table, you will have to tell your database which fields from the query go with which fields in the table.




Demonstration 1

Import the Customer Table

The pass through query could be as simple as: "select * from customer". This would simply select all data from the customer table.

The SQL that transfers the query results into the table is where you need to map fields.

For example, let us say your database address fields are:

      Address1
      Address2
      City
      State
      ZipCode

The SQL "select * into YOURDATABASETABLE from THEPASSTHROUGHQUERY" will work to create a new table. To test this, create a new database and select the following references:

      Visual Basic for Applications
      Microsoft Access Object Library
      Microsoft DAO Object Library

Paste the following code into a new module:



With QuickBooks open and QODBC installed on your computer and granted permission to access QuickBooks, type the following line into the Immediate Window of the Visual Basic Editor:

      fncNewTable

Allow QODBC ample time to connect to QuickBooks. A new table is created in your database containing all the customer information in your QuickBooks file.

Open the new table in Design View and look at the field names. Do you see the database field names listed above? No. In fact, QuickBooks lists two types of addresses: one for billing and another for shipping.

Adding information to our customized field names (listed above) requires field mapping. This can be done with code. To illustrate, open the new table and add our custom field names to the table:

      Address1
      Address2
      City
      State
      ZipCode

Next, we'll map these fields to QuickBooks fields using code.



Demonstration 2

Field Mapping in Code

Paste the following code into a new module:





Type the following line into the Immediate Window of the Visual Basic Editor:

      fncAppendTable

Open the altered table and scroll across to the changed fields you created above. Your created fields contain QuickBooks data because the code Mapped the fields like this:

      rs!address1 = !BillAddressAddr2
      rs!address2 = !BillAddressAddr2
      rs!city = !BillAddressCity
      rs!state = !BillAddressState
      rs!zipcode = !BillAddressPostalCode

Imagine having to map fields for the nearly 50 fields in the customer table each time you write code to exchange information with QuickBooks. Imagine doing this for all the other QuickBooks tables.



Quick & Easy Blank Tables


As promised, I will give you a quick and easy way to create new tables for your database using QODBC. First we'll create blank tables for lists and then for transactions.



Blank Tables for Lists


Do not use this code for transaction tables like check, invoice, bill, etc. This code is for list tables like customer, vendor, account, etc. Continue below for code to produce blank transaction tables.

Paste the following code into a new module:




Copy and paste the following line into the Immediate Window of the Visual Basic Editor to create a new blank table:

      fncNewTableFields

Copy and paste the following line into the Immediate Window of the Visual Basic Editor to open the new table in Design View and see the fields:

      docmd.OpenTable "tblCustomers",acViewDesign

HINT: If you do not see the new table, close or collapse the Visual Basic Editor.

Your new table contains all the same fields as QuickBooks.

To create blank tables for other QuickBooks List tables:

      1. Select the text of the code in the Visual Basic Editor.
      2. Select Edit and then Replace from the Standard Toolbar menu of the Visual Basic Editor
            HINT: If you do not see the Standard Toolbar:
                  a. Select View from the menubar of the Visual Basic Editor
                  b. Scroll down and highlight Toolbars
                  c. Select Standard Toolbar from the Toolbars Menu
      3. Make sure the Selected Text radio button is selected on the Replace dialog box
      3. Replace "customer" with the name of the table you want to create. Some table names are:
            a. vendor
            b. entity
            c. class
            d. employee
            e. item
            f. fixed asset
            g. account
            h. jobtype
            i. pricelevel



Blank Tables for Transactions

To create blank tables for transaction tables, use a DateMacro in the code to avoid pulling all the table data into QuickBooks. If you do not use a DateMacro your code could hang or take an extremely long time depending on how many transactions your tables contain. You only want the field names so extracting information is unnecessary. Even if no records exist for today, QODBC will still create the blank table.




To create other list tables, replace check with these table names:

      1. checkexpenseline
      2. checkitemline
      3. invoice
      4. invoiceline
      5. pricelevelperitem
      6. bill
      7. billexpenseline
      8. billitemline
      9. deposit
     10. depositline



Summary

Your table field names should match QuickBooks field names to avoid field mapping each time you write code to exchange data between QuickBooks and Microsoft Access.

Use VBDemo (comes with QODBC) to see field names and properties for use in your code.

When creating forms for user input to insert new data into QuickBooks, use blank tables with QuickBooks fields names. You may quickly create blank tables using the functions above.

Instead of repeatedly opening VBDemo to see tables, fields and properties, you may desire a database form to access the information from inside your database. A tutorial here on VBQuick (QuickBooks Tables in Access w/ QODBC Form Part 1) takes you step-by-step to a finished form that does that for you:

http://www.vbquick.com/2011/10/quickbooks-microsoft-access-table-qodbc.html

Keeping checking back for the next post in this series for Rule #2.

As always, let me know how this works for you.


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.

Vendor/Customer

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®.


Account

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:
http://www.vbquick.com/2012/03/migrate-quickbooks-microsoft-access-2_23.html

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
invoices
deposits
bills
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. (http://www.thegenericdatabase.com/2011/09/03-references.html).


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:

      check
      bill
      invoice
      deposit
      creditcardcharge
      creditcardcredit
      billcredit

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: www.vbquick.com/2011/10/quickbooks-microsoft-access-table-qodbc.html.
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



Continue...

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:
           
            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.

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
            www.vbquick.com/2011/10/qodbc-quickbooks-invoice-access.html

...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 (VBQuick.com) 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