- Explained the relationship between QuickBooks®' items, prices and jobs.
- Offered ideas for altering the function.
- Examined reasons for importing the PriceLevelPerItem table.
- Provided real world applications for using the table.
- Supplied copy and paste code to:
- connect to QuickBooks® using QODBC
- import the custom prices from the Per Item Price Level table
- put the data into a Microsoft® Access table
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
- Select Tables from the Objects list of the Database Window
- 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
- 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®.
- From within QuickBooks®
- Copy the table
1. From Microsoft® Access Using VBA and QODBC
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:
- immediately following the = sign
- 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 & "'"
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.
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: