QODBC ProfitAndLossStandard Exclude Class Part 1

FREE TUTORIAL

Build-it-Yourself with
Copy & Paste Code

You cannot exclude Classes from a Profit and Loss Report in QuickBooks® but you can using QODBC and Microsoft® Access. With a bit of code, you can create the report with the click of a button.

The idea for this tutorial sprung from a request on Experts-Exchange where I participate and offer solutions pro bono for various issues, usually involving QuickBooks® and QODBC.

I never needed to exclude classes from a Profit & Loss Report but evidently this is a common request.

The issue intrigued me enough to write the code from scratch, create the form and offer it as a solution. The recipient was very happy with the results and it solved their problem.

This is a Two-Part Post. Be sure to click continue to go on and finish the database form and create the report.

You can also download the FREE completed Database from My GoogleDocs Page:

docs.google.com/file/d/0B6a0SM5mDWxGZGFmODE2MWYtZTAzOS00N2VlLWFiYzQtNDZhODRiOTZlMDQw/edit?pli=1

My download instructions reside on The Generic Database:

            How to Download & Open My Free Databases 
            www.thegenericdatabase.com/p/how-to-download-google-docs-databases.html


What This Code Does

  1. Creates a Pass-Thru query (sp_report ProfitAndLossStandard) using user input From and To dates
  2. Selects fields from this Pass-Thru query and concatenates the text and label fields as a 3rd field named Description to eliminate null fields and create a join field for the Report query and puts them into Table1
  3. Adds a Class Filter to the Pass-Thru query (sp_report ProfitAndLossStandard) using user selected Classes from the form listbox
  4. Selects the same fields from the 2nd Pass-Thru query as before, again concatenating the text and label fields as a 3rd field named Description to eliminate null fields and create a join field for the Report query and puts them into Table2
  5. Creates a Select query, joining the two tables by the Description field and creates an additional field that subtracts the Table2 amounts from the Table1 amounts.
  6. Opens a Report  for the user to design.
  7. Sets the new report's record source to qryProfitLoss.
The Report's record source (a select query) in Design View:






This code was tested with:
  1. Microsoft® Access 2002
  2. QuickBooks® Premier Contractor 2009
  3. QODBC Desktop Read Write 2009 Version 9.00.00.253



References

You will need the following references checked in Access:




First you need to create a form in Microsoft® Access that looks something like this:


Name the Form Controls

You must name the form controls as follows because the code uses these control names:
  1. TextBox 1: DateFrom
  2. TextBox 2: DateTo
  3. Command Button 1: cmdReloadClasses
  4. Command Button 2: cmdCreateReport
  5. ListBox: lstClass
Fix up the form any way you like. You may use input masks for the date text boxes (wise), adjust the size of the listbox, etc. Do not worry about the listbox columns or source, the code will do that.




Visual Basic Editor


To open the Visual Basic Editor:
  1. Select Modules from the Database Window
  2. Click Design




Double click the form you created from the Project Explorer. If you do not see the Project Explorer, select it from the View menu of the Visual Basic Editor menu bar.






Code

Besides the code below, you will need 3 additional functions. These additional functions reside here on VBQuick and my other site The Generic Database. At the end of this code, the links will appear to the 3 functions you need and you may get them and put them in a new module. You need them to run this code.

Paste this code into the code window of the Visual Basic Editor:


Option Explicit

Private Sub cmdCreateReport_Click()
      'if an error occurs, go to the error routine
100   On Error GoTo cmdCreateReport_Click_err
      'if the user didn't enter values for the dates,     
      'present a message box and exit     
110   If IsNull(DateFrom) = True Then
120       MsgBox "enter a From Date"
130       Exit Sub
140   End If
150   If IsNull(DateTo) = True Then
160       MsgBox "enter a To Date"
170       Exit Sub
180   End If
      'if the user didn't choose any classes to exlude     
      'present a message box and exit
185   If lstClass.ItemsSelected.Count = 0 Then
190      MsgBox "Select at least one Class to exclude."
195      Exit Sub
200   End If
        

      'go to the function that creates the two tables
205   sp_Report_Tables
      'declare variables for the Database, QueryDef and Query Name
210   Dim db As DAO.Database, qd As DAO.QueryDef, q As String
      'name the query
215   q = "qryProfitLoss"
      'function fncDeleteQuery deletes the query if it already exists
220   Call fncDeleteQuery(q)
      'set the database to the current database
230   Set db = CurrentDb
      'create the QueryDef
240   Set qd = db.CreateQueryDef(q)
      'make sure the query will return records
250   qd.ReturnsRecords = True
      'put the SQL statement into the query
260   qd.SQL = "SELECT Table1.text, Table1.label, Table1.amount AS AllClasses,Table2.amount AS SomeClasses, [allclasses]-[someclasses] AS [AllClasses-SomeClasses] FROM Table1 INNER JOIN Table2 ON Table1.Description = Table2.Description;"
      'this is the line to come back to in case an error occurred     
      'so you can set qd and db back to nothing before exiting
cmdCreateReport_Click_exit:
      'set dq and db back to nothing
270   Set qd = Nothing
280   Set db = Nothing
290   subOpenReport
Exit Sub
cmdCreateReport_Click_err: '
'fncWriteError display information about the error to the user and writes that information in the
'immediate window for the programmer
Call fncWriteError(Form.Name, "", "Private Sub cmdCreateReport_Click", Err.Number, Err.Description, Erl, "")
'go back to this line so you can set qd and db back to nothing before exiting
GoTo cmdCreateReport_Click_exit
End Sub



Private Sub cmdReloadClasses_Click()
      'if an error occurs go to the error line
100   On Error GoTo cmdReloadClasses_Click_err
      'before reloading classes, set the listbox rowsource to nothing     
      'if you do not do this you will get the "table already in use" error
110   lstClass.RowSource = ""
      'declare variables for the Database,QueryDef and Query Name
120   Dim db As DAO.Database, qd As DAO.QueryDef, q As String
      'name the query
130   q = "qry_temp_Classes"
      'this function deletes the query if it already exists
140   Call fncDeleteQuery(q)
      'set the database to the current database
150   Set db = CurrentDb
      'create the QueryDef
160   Set qd = db.CreateQueryDef(q)
      'the function "QODBCConnect" simply sets the QODBC connection string     
      'you can type out your connection string if you wish
170   qd.Connect = QODBCConnect
      'set the query to return records
180   qd.ReturnsRecords = True
      'put the required fields in the query     
      'you will want at least the ListID and the FullName     
      'you will choose the FullName from the listbox     
      'you will use the ListID in the query
190   qd.SQL = "select listid,fullname from class"
      'set warnings to false because you may be overwriting this table     
      'when you reload the classes     
      'if you do not set the warnings to false, you will get an error     
      'message about the table already existing
200   DoCmd.SetWarnings False
      'put the query results into a table
210   DoCmd.RunSQL "select * into tblClass from " & q
      'set the warnings back to true
220   DoCmd.SetWarnings True
      'set the new class table as the listbox rowsource
230   lstClass.RowSource = "Select * from tblclass order by fullname asc"
      'set the listbox column widths     
      'this is done just so I didn't have to explain how to set them on the form
240   lstClass.ColumnWidths = "0;3"
      'same with the column count
250   lstClass.ColumnCount = 2
      'set the listbox rowsource type to Table/Query
260   lstClass.RowSourceType = "Table/Query"
      'come back to this line after an error occurs so you can set     
      'qd and db back to nothing before exiting
cmdReloadClasses_Click_exit:
      'set qd and db back to nothing
270   Set qd = Nothing
280   Set db = Nothing
      'delete the temporary query
290   Call fncDeleteQuery(q)
Exit Sub
'error line to report error information
cmdReloadClasses_Click_err:
'fncWriteError displays information about the error to the user and writes that information in the
'immediate window for the programmer
Call fncWriteError(Form.Name, "", "Private Sub cmdReloadClasses_Click", Err.Number, Err.Description, Erl, "")
'go back to this line so you can set qd and db back to nothing before exiting
GoTo cmdReloadClasses_Click_exit
End Sub


Function ClassSQL() As String
      'if an error occurs, go to the error line
100   On Error GoTo ClassSQL_err
      'declare a variant variable to enumerate through the selected items in the listbox
110   Dim varItm As Variant
      'enumerate through the selected items in the listbox

120      For Each varItm In lstClass.ItemsSelected
            'add each selected item to the string variable ClassSQL           
            'this string will be attached to the second ProfitAndLossStandard query           
            'this is adding the ListIDs of the classes you wish to exclude
130         ClassSQL = ClassSQL & "'" & lstClass.ItemData(varItm) & "',"
140      Next varItm
      'add the parameter string to the list of class ListIDs
150   ClassSQL = "ClassFilterListIDs=" & Left(ClassSQL, Len(ClassSQL) - 1)
   
ClassSQL_exit:
Exit Function
ClassSQL_err:
'fncWriteError displays information about the error to the user and writes that information in the
'immediate window for the programmer
Call fncWriteError(Form.Name, "", "Function ClassSQL", Err.Number, Err.Description, Erl, "")
'go back to this line so you can set qd and db back to nothing before exiting
GoTo ClassSQL_exit
End Function


Private Sub sp_Report_Tables()
      'if an error occurs, go to the error line
100   On Error GoTo sp_Report_Tables_err
      'declare variables for the Database, QueryDef, Query Name and SQL string
110   Dim db As DAO.Database, qd As DAO.QueryDef, q As String, strSQL As String
      'name the query
120   q = "qry_Temp_SP_Report"
      'this function deletes the query if it already exists
130   Call fncDeleteQuery(q)
      'set the db as the current database
140   Set db = CurrentDb
      'create the QueryDef
150   Set qd = db.CreateQueryDef(q)
      'this function sets the QODBC connection string     
      'you may simply type your connection string after the = sign if you wish
      'find this function here
160   qd.Connect = QODBCConnect
      'set the query to return records
170   qd.ReturnsRecords = True
      'create the sql to query QuickBooks using the dates from the form     
      'this is for the first table that includes all classes and null classes     
      'returnrows='All' is important - do not leave it out
180   qd.SQL = "sp_report ProfitAndLossStandard show text,label,amount_1 as amount parameters datefrom=" & fncQBDate(DateFrom) & ",dateto=" & fncQBDate(DateTo) & " ,returnrows='All'"
      'set warnings to false because you will be overwriting this table all the time     
      'if you do not do this you will be notified that the table already exists
190   DoCmd.SetWarnings False
      'run SQL selecting the required fields from the pass thru query and putting them into table1     
      'notice you select text and label and then combine text and label in a third field named Description     
      'some rows of the text and label fields are null which would prevent joining in the report query     
      'adding text and label lets you use the Description field to join the two tables in a query
200   DoCmd.RunSQL "select text,label,text  & label as Description ,amount into Table1 from " & q
      'now you are using the same pass thru query but this time you are limiting     
      'the results with the ClassSQL created earlier
210   If IsNull(ClassSQL) = False Then qd.SQL = qd.SQL & "," & ClassSQL
      'put these query results into a second table
220   DoCmd.RunSQL "select text,label,text  & label as Description ,amount into Table2 from " & q
      'set the warnings back to true
230   DoCmd.SetWarnings True
sp_Report_Tables_exit:
250   Set qd = Nothing
270   Set db = Nothing
      'delete the temporary query
Call fncDeleteQuery(q)
Exit Sub
sp_Report_Tables_err:
'fncWriteError displays information about the error to the user and writes that information in the
'immediate window for the programmer
Call fncWriteError(Form.Name, "", "Private Sub sp_Report_Tables", Err.Number, Err.Description, Erl, "")
'go back to this line so you can set qd and db back to nothing before exiting
GoTo sp_Report_Tables_exit
End Sub


Private Sub subOpenReport()
'if an error occurs, go to the error line
On Error GoTo subOpenReport_err
'declare variables for the report
Dim obj As AccessObject, cp As Object, rpName As String
    Set cp = Application.CurrentProject
    ' Search for the report ProfitLoss in the AllReports collection.
100   For Each obj In cp.AllReports
         'if the ProfitLoss report is found, open it
110      If obj.Name = "ProfitLoss" Then
            'open the found report
120         DoCmd.OpenReport "ProfitLoss", acViewPreview
            'go to the exit line to set cp to nothing before exiting
130         GoTo subOpenReport_exit
140      End If
150   Next obj
       'if the report wasn't found, create one      
'declare a variable for a new report
160    Dim rp As Report
170    Set rp = CreateReport
180    DoCmd.Restore
       'put the new report name into the variable for renaming
190    rpName = rp.Name
       'save the open report
200    DoCmd.Save acReport, rp.Name
       'close the report to rename it
210    DoCmd.Close acReport, rp.Name
       'rename the report: ProfitLoss
220    DoCmd.Rename "ProfitLoss", acReport, rpName
       'open the report in design view
230    DoCmd.OpenReport "ProfitLoss", acViewDesign
       'set the record source for the report
240 Reports!ProfitLoss.Report.RecordSource = "qryProfitLoss"
       'save the report with the record source
250 DoCmd.Save acReport, "ProfitLoss"
       'ask user to design the report
260 MsgBox "Please design your report and save it."
'come here after an error occurs to set cp back to nothing before exiting
subOpenReport_exit:
'set cp to nothing before exiting
Set cp = Nothing
Exit Sub
subOpenReport_err:
'fncWriteError display information about the error to the user and writes that information in the
'immediate window for the programmer
Call fncWriteError(Form.Name, "", "Private Sub subOpenReport", Err.Number, Err.Description, Erl, "")
'go back to this line so you can set cp back to nothing before exiting
GoTo subOpenReport_exit
End Sub

Popular Posts