QuickBooks PriceLevelPerItem Part 3: 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.



Review

The previous post:
  1. Explained the relationship between QuickBooks®' items, prices and jobs.
  2. Offered ideas for altering the function.
  3. Examined reasons for importing the PriceLevelPerItem table.
  4. Provided real world applications for using the table.
  5. Supplied copy and paste code to:
    1. connect to QuickBooks® using QODBC
    2. import the custom prices from the Per Item Price Level table
    3. put the data into a Microsoft® Access table
This Post

We will now examine the table and learn how to import one single Per Item Price Level.



Run the Function

Run the function by typing:
ReloadPriceLevelPerItemPrices
...into the Immediate Window of the Visual Basic Editor.

Give QODBC ample time to connect to QuickBooks® and for your function to create the table.


Opening the Table

Open the table in Design View to see the fields.

  1. Select Tables from the Objects list of the Database Window
  2. Click Design from the menubar of the Database Window







Design View lets you see the fields you selected.


Do not change this table. Refreshing the table using the code you wrote deletes any changes you make.



Acceptable Ways to Change Custom Prices

  1. Change custom prices from Microsoft® Access using VBA and QODBC to change the data in QuickBooks®. This differs from changing the data in the table. If you change the data in the table, the changes will not take effect in QuickBooks®.
  2. From within QuickBooks® 
  3. Copy the table


1. From Microsoft® Access Using VBA and QODBC

You can change custom prices from within Microsoft® Access using the QODBC read/write version. See QODBC documentation for how to accomplish this.

Request posts on subjects by filling out my Feedback Form (http://www.vbquick.com/p/feedback.html). I may post on subjects if enough requests come in. I have also helped others when issues intrigued me. The code for QODBC ProfitAndLossStandard Exclude Class (http://www.vbquick.com/2011/10/qodbc-quickbooks-report-profit-loss-vba.html) resulted from a how-to request on Experts Exchange. Occasionally, someone requests code I already have or that requires negligible alteration to qualify.

2. From Within QuickBooks®

QuickBooks® contains an interface for changing custom prices. This interface requires no special VBA programming or external connection.


3. Copy the Table

Contractors may desire to change custom prices temporarily for use in bid creation. Copy the table and work on the copy. This prevents overwriting bid work if someone inadvertently calls the table function.



How to Import a Single Price Level

Import a single price level using criteria on line 80 of the function. For example, QuickBooks® assigns each Per Item Price Level a unique and permanent ListID. You can use the ListID as criteria like this:

where ListID='80000001-1316369099' "



Then line 80 would look something like this:

80 qDef.sql = "select listid,name,PriceLevelPerItemItemRefListID,PriceLevelPerItemItemRefFullName,PriceLevelPerItemCustomPrice,isactive from pricelevelperitem where ListID='80000001-1316369099' "




You may then incorporate a listbox into your form that contains PriceLevels and their corresponding ListIDs. Users would then select a PriceLevel and the ListID would be sent to the Function in a string like below. This would then replace the first line of the function.
Function ReloadPriceLevels(strListID as string)


Then line 80 would look like this:
80 qDef.sql = "select listid,name,PriceLevelPerItemItemRefListID,PriceLevelPerItemItemRefFullName,PriceLevelPerItemCustomPrice,isactive from pricelevelperitem where ListID='" & strListID & "'"

Note: the apostrophes (enlarged and in red above) must surround strListID. The two apostrophes appear:
  1. immediately following the = sign
  2. after the last ampersand (&) and surrounded by quotation marks

These apostrophes appear in red above but will not appear in red when copied into your function.

Below they appear enlarged for your convenience. It is imperative that you do not include any spaces preceding or following these two apostrophes or your query will not work.


ListID='" & strListID & "'"



Summary

Contractors generally use different price levels for different contracts. QuickBooks® Per Item Price Levels let you enter different prices for each item manually.

Many uses exist for importing QuickBooks® Per Item Price Level custom prices into a Microsoft® Access database as outlined previously.

QODBC lets you read and write QuickBooks® Per Item prices from Microsoft® Access using VBA (Visual Basic).

The copy and paste code in this tutorial is only one example of how to use QODBC to exchange information with QuickBooks® through Microsoft® Access.

If you use this function, please let me know how it works for you. If you have any questions, leave a comment and I will do my best to help you.

Do you like this post? Please link back to this article by copying one of the codes below.

URL:

HTML link code:

BB (forum) link code:

2 comments:

  1. Hi there! I am trying to find a way for Quickbooks to read my Access Database. I've got a table with all of the items in it, along with a few fields to do a calculation with. I then have a query based on that table with a few calculated fields that calculate the price based on the fields of the table. We have 3 levels of pricing, and I am trying to get Quickbooks to reflect those 3 levels based on customer type. I made the 3 levels of pricing, and those can be assigned to customers. Is there a way to have Quickbooks read from those calculated fields?

    ReplyDelete
  2. I'm not sure I understand your question but QODBC can insert your calculated prices into QuickBooks. To insert into invoices, you insert invoice information such as Customer, PONumber, TxnDate etc. into the Invoice table. You insert the invoice line information such as items, quantities and prices into the InvoiceLine table using the TxnId of the Invoice you are inserting into.

    If you are talking about creating PriceLevelPerItem custom prices for each customer, you can do that too by inserting your calculated prices into the PriceLevelPerItem table.

    The details of how to accomplish the above would require a very long article.

    This article provides some insight into QuickBooks invoice tables: http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_10009-How-to-Insert-Non-Insertable-Custom-Fields-into-Existing-QuickBooks-Invoices-from-Microsoft-Access-using-QODBC.html

    Best wishes and let me know if this helps.

    ReplyDelete