QuickBooks PriceLevelPerItem Part 2: Import into Microsoft Access Table Using QODBC

This is part of a tutorial on how to transfer the Per Item Price Level table from QuickBooks® into Microsoft® Access using QODBC without IIF files or spreadsheets. Click here to start at the beginning.

The Relationship Between Items, Prices and Jobs

The Design View query below illustrates the relationship between the following QuickBooks® tables:
  1. Customer
  2. PriceLevelPerItem
  3. Item
You need not understand these relationships to benefit from the copy and paste code included in this post.

These detailed explanations are for:
  1. the curious
  2. those who wish to alter the code
  3. devleopers interested in QODBC and its application
The relationships are as follows:
  1. the Customer (job) Table contains the ListID for its PriceLevel: PriceLevelRefListID
  2. the PriceLevelPerItem Table (center) contains ListIDs for its items: PriceLevelPerItemItemRefListID





The above query (design view) would return records similar to the results below that show:
  1. Customer Name from the Customer Table
  2. PriceLevel Name from the PriceLevelPerItem Table
  3. Item Name from the Item Table
  4. Item Description from the Item Table

Again, do not be concerned if you do not understand this. These explanations are for the those who desire a deeper understanding of QuickBooks® tables and how QODBC uses them.



Altering the Function

Depending on your needs, you may alter this function to import price level prices for:
  1. one price level at a time and reload the table every time you need to work on a different price level
  2. all price levels at once and reload the table only when PriceLevelPerItem records change



Real World Applications for the Imported PriceLevelPerItem Table

Reasons for importing QuickBooks® PriceLevelPerItem prices into Microsoft® Access vary but you may wish to use Microsoft® Access to:
  1. create an invoice entry program better suited to your needs
    1. Using QODBC to Create a Customized Data Entry Interface for QuickBooks Invoices (www.vbquick.com/2011/10/qodbc-quickbooks-invoice-access.html)
  2. create an invoice entry program that hides prices from the data entry technician
  3. compare previous job prices in a report for help in preparing a bid
  4. compare the difference between bid prices you are contemplating and previous jobs' prices
  5. apply prices from one job to another job's invoices to see how they would have affected profit
  6. use union queries
  7. use QuickBooks® unique identifiers (PriceLevelPerItem ListIDs are unique for each price) to link PriceLevelPerItem records to records in a database table.
You may think of other reasons to import prices from the PriceLevelPerItem table. Each business has different needs.

Please note that you will need to select the following references from the Visual Basic Editor:
  1. Visual Basic for Applications
  2. Microsoft® Access 10.0
  3. Microsoft® DAO

If you do not know How to Select References see The Generic Database:

            Chapter 01: Part 2: How to Create a New Module and Select References

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

Copy the function below, everything between the two lines of asterisks (do not include the asterisk lines).

Paste the function into a new module. If you do not know how to create a new module, the post directly above on How to Select References also contains instructions on creating a new module.

If you do not normally follow my posts and use my codes, you will find references to several other functions.

These functions save time and you should have them in all your databases. They are short. Put them in a separate module so you can quickly import them into other databases for your convenience.

If you do not already have them, copy and paste these also:


fncDeleteQuery:

fncDeleteTable:

fncWriteError:

QODBCConnect


The following function contains detailed explanations of each line. You do not need to understand the function. The explanations provide an excellent learning opportunity.



Copy Paste Function

*************
Function ReloadPriceLevelPerItemPrices()
        'if an error occurs, go to the error line that writes error information to
        'a message box for the user and the Immediate Window for the programmer
On Error GoTo ReloadPriceLevelPerItemPrices_err
        'declare variables for database, QueryDef and the query name
10      Dim db As DAO.Database, qDef As DAO.QueryDef, qName As String
        'assign the name of the query to the string variable
20      qName = "qryPT_PriceLevelPerItemPrices"
        'call a function that deletes the temporary query if it already exists
        'this query first checks to see if one with the same name exists and if it does, deletes it
        'if you try to delete a query that doesn't exist, Visual Basic will present
        'an error message telling you it cannot find the query
30      Call fncDeleteQuery(qName)
        'set the database - assumes you are working in the current database
40      Set db = CurrentDb
        'create the query using the name you assigned above
50      Set qDef = db.CreateQueryDef(qName)
        'QODBCConnect is a function that sets the QODBC connection string
        'this avoids having to remember it every time you need to use it
        'you can simply write "qDef.Connect = " and then type out your connection string if you prefer
60      qDef.Connect = QODBCConnect
        'set the query to return records
70      qDef.ReturnsRecords = True
        'create the sql string and put it into the query
        'you can simple select all fields with: qDef.sql="select * from pricelevelperitem"
        'the more fields you select, the longer the query will take
        'you are better off selecting only required fields
        'limiting the returned records is best done at the query level
        'rather than limiting them when you write the sql that puts the records into the table
        'QODBC takes longer to extract records from QuickBooks than
        'Microsft Access takes to extract records from the Pass Thru query
        'and put them into a table
80      qDef.sql = "select ListID,Name,PriceLevelPerItemItemRefListID,PriceLevelPerItemItemRefFullName, " & _
"PriceLevelPerItemCustomPrice,isActive from PriceLevelPerItem"
        'before you put the query records into a table, you may
        'use a function to delete the table if it exists
90      Call fncDeleteTable("tblPriceLevelPerItemPrices")
        'Turn off warnings so you don't get one for overwriting an existing table
        'You will frequently overwrite this table as well as the query that
        'supplies the data to it because whenever you add a new price or price level
        'in your QuickBooks® file, you will need to refresh the database table
        'you could simply create the pass thru query but if the user deletes it
        'will they know how to recreate it? best to create it programmatically
100     DoCmd.SetWarnings False
        'since you limited the pass thru query to only records that you require
        'go ahead and select them all for putting into the price level table
        'here the user will not know what is going on
        'you may do as I do and open a form telling the user you are extracting
        'data from QuickBooks® and then close the form when you are finished
110     DoCmd.RunSQL "select *  into tblPriceLevelPerItemPrices from " & qName
        'delete the pass thru query because you don't need it now
120     DoCmd.DeleteObject acQuery, qName
        'turn warnings back on
130     DoCmd.SetWarnings True
        'present the user with a message box to signal the price levels are refreshed
140     MsgBox "Finished."
        'use this line to return to from the error routine so if an error occurs
        'qDef and db will be set to nothing
ReloadPriceLevelPerItemPrices_exit:
        'you are finished with qDef and db so set them to nothing
        'if you do not set db to nothing, you may find Access running
        'in your task manager even after you close the database
150     Set qDef = Nothing
160     Set db = Nothing
        'exit the function - you are finished
Exit Function
        'at the beginning of this function, you instructed Visual Basic to come here
        'if an error occurs
        'fncWriteError presents the user with error information in a message box
        'and also writes the same error information in the Immediate Window
        'you may also write fncWriteError to put the error information into a table
ReloadPriceLevelPerItemPrices_err:
Call fncWriteError("", "Functions Prices", "Function ReloadPrices", Err.Number, Err.Description, Erl, "")
        'after writing the error information, return to the exit line to set qDef and db to nothing before
        'exiting the function
GoTo ReloadPriceLevelPerItemPrices_exit
End Function
***************

Continue to the next post

Comments

  1. Hi this is really interesting. Will it work with QB Enterprise?

    ReplyDelete
  2. I'm really not sure as I don't use QB Enterprise. Have you tried contacting QODBC? I'm sure they can answer your question.

    ReplyDelete

Post a Comment

Popular Posts