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

Our company enjoyed QuickBooks® invoices for years; however, that changed in 2001 through no fault of Intuit®. We still use QuickBooks® to store invoice lines but we export that data into QuickBooks®  through a user-friendly invoice entry program.

First, the issues and then how we resolved them.

Invoice Unit Name Issues

You may not face the exact same issues; however, your particular needs may result in similar issues.

Invoices consist of invoice lines. Invoice lines consists of units, quantities and prices. The Rural Utilities Services division of USDA (RUS) dictates our unit names. However:
  1. customers sometimes use different names than RUS
  2. customers sometimes use different names than other customers
  3. engineers sometimes use different names than RUS or than the customer for whom they work
  4. foremen often use field terms that differ from RUS, customer or engineer
If you think this is confusing, imagine the person doing the invoicing.

Cross-Referencing Unit Names

The data entry tech was responsible for cross-referencing. This was accomplished from:
  1. memorization
  2. manual cross-referencing
To add to the confusion, RUS changed their unit names in 2001. Not all customers adopted the new names and some of the customers adoped some of the new names but not all of them.

Relying on the data entry tech was no longer an option.

Solving Unit Name Issues

Your needs may differ but the following solutions illustrate some of the possibilities when implementing a customized invoice entry program.

QODBC is an ODBC (Open Database Connection) driver for QuickBooks® . QODBC lets you read and write QuickBooks® data from a database using VBA (Visual Basic for Applications).

Most of the codes on this site use QODBC. Its versatility is only matched by its ease of use. If you know VBA, the hop to QODBC is a short one.

Using QODBC, a new invoice entry program was developed in Microsoft® Access that:
  1. Lets users cross-reference unit names at data-entry time and on-the-fly.
  2. Calculates quantities by a user-input multiplier on-the-fly. For example: Engineers enter wire in number of feet. The user multiplies this by the number of wires in a span. The number of wires in a span varies from job to job and even from sheet to sheet and pole to pole.
  3. Verbally warns the user of possible errors using typical error scenario algorithms. For example, under no circumstance would a span of wire consist of less than ten feet. If the user enters less than ten feet, a warning stops the program and asks if the footage is correct. Similarly, if the user enters an install unit on a removal invoice, a warning notifies the user that the unit class does not match the invoice class.
  4. Automatically fills in repeated fields for user defined groups. For example, each pole may require up to a dozen units. Each unit is one invoice line. Previously, the user typed the pole number on each invoice line. The new program lets the user enter the pole number once. The program then adds the pole number to subsequent invoice lines until the user inputs a new pole number.
  5. Reduces data entry error by allowing the user to visually follow the engineer's work plan as the database reads the data out loud back to the user. This lets the user confirm and correct entries before exporting them into QuickBooks®. Listening to spoken words while reading along is significantly faster than continually looking back and forth between screen and paper.

The Results

This extensibility through QODBC and Microsoft® Access has reduced both data entry time and user error.

One advantage is that units, prices, totals and invoices remain in QuickBooks®, keeping all bookkeeping entries in one application. The database is simply a user-friendly tool for entering invoices faster, easier and specific to company needs.

Meeting Your Specific Needs

Think about your invoicing. Is there a capability QuickBooks® does not provide that you would like to add? If you do not know Visual Basic, you might consider hiring a student or database specialist to create an industry-specific invoice program for you.

If you are short on funds with which to hire a programmer, you may know enough Visual Basic to write your own program in Microsoft® Access using QODBC.

Look over the codes on this site. You may decide you can do-it-yourself like I did and have a customized invoice system for your company.


Popular Posts