QODBC ProfitAndLossStandard Exclude Class Part 2
Continued from...Part 1
This is a Two-Part Post. You needed to complete the steps in the Part 1 before completing the steps outlined in this post.
Other Codes Required
The above code refers to the following functions that you will need.
Create a new module and add these functions to it:
Now that the code is installed, you need to set the two command buttons' On Click events.
Move to the report that is in Design View and save it, thereby saving your code. Keep the report in Design View.
- Double-click the Reload Classes button
- Select the Event tab from the Properites Window.
- Scroll down to the On Click event.
- Place the cursor in the text box next to On Click
- Click on the arrow that appears at the end of the text box
- Select [Event Procedure]
- Repeat these steps for the Query button.
Change to Form View by right-clicking on the form and selecting Form View.
Click the Reload Classes button to fill the listbox with QuickBooks® classes. This list will not be restricted to active classes but will also include inactive classes. The assumption is that you may wish to create reports for past periods in which classes are no longer active.
Wait for QODBC to connect to QuickBooks®, retrieve the classes, and fill the listbox.
If you try to create the report with no classes selected, you will see this message box:
If you try to create the report without entering the dates, you receive another message box:
With dates entered and classes selected, click the Create Report button.
A new report is created and named: "Profit Loss" and you are asked to design the report.
If you prefer to create the Report from AutoReport: Tabular:
- Close the new Profit Loss report and delete it from the Database Window.
- Select Reports from the Objects List in the Database Window
- Click New Report from the menu bar of the Database Window
- Select AutoReport: Tabular in the New Report Wizard
- Select qryProfitLoss as the report record source
When the report opens, right-click anywhere on the report and select Save As.
Make sure to name the report ProfitLoss because that is the report name the code uses to open the report.
Fix up the report any way you like, just be sure to save it as ProfitLoss.
Close the report and use the form to re-create it to confirm you saved it with the correct name and all is well.
To confirm that this report calculates the Profit & Loss Standard report excluding the classes you selected, go back to QuickBooks® and:
- Create a Profit & Loss Standard report using the same dates as your database report
- Create a 2nd Profit & Loss Standard report and modify it by selecting Multiple Classes from the Class Filter. Be sure to select the same classes that you selected for your database report.
- Subtract the Net Profit from the 2nd (modified) report from the Net Profit of the 1st (NOT modified) report. This amount should match the Net Profit from the AllClasses-SomeClasses column of your database report.
Two minus negative one equals three.
2 - (-1) = 3
To confirm this, go to the Immediate Window of the Visual Basic Editor and type:
The Immediate Window will display the answer: 3