Question: How do I use a button to print the results of a query?

Answer: The following tutorial demonstrates this. You may wish to print this article for best results.

Let's assume you're working with the sample tables provided with Paradox, e.g. the MAST Customer, Orders, and LineItems tables and that you have a query that selects a customer. Let's assume you open the Customer.rsl (which lists the orders by each customer) with the Answer table from your query (thus showing the orders for a given customer).

So, the goal is to drop a button on Custform.fsl that a) runs the query and b) previews the report with the results of that query. Here are the steps for doing so:

1. Make sure your working directory is set to the samples directory provided with Paradox, typically ..\Corel\Paradox\Samples.

2. Open CustForm.fsl in a Design Window (From the main Paradox menu, choose File | Open | Form, click the Edit radio button, select CustFrom.fsl, and then choose OK.)

3. When the form opens in Design mode, drop a button on the form. If you need to, drag the resulting button to a location that doesn't contain any text.

4. Select the Label of the button and then press F2 (Edit). Type Show Orders and then press F2. (If you have trouble seeing the selection handles, consider temporarily changing the color of the page object to White.)

5. Select the button and then choose Tools | Object Explorer (or press Ctrl+Space). The Object Explorer should appear.

   Take a moment to look at it; if you do not see an outline tree view and a set of tabs running down the right side of the Object Explorer, choose View | Both from the Object Explorer's menu.

6. Click the Events tab along the right side of the Object Explorer. This shows the events available for the currently selected object, which should be the button.

   (To verify this, make sure the selected object in the Object Tree is named something like #Button28.)

7. Scroll through the event list and then double-click the pushButton event. This opens a blank editor window with two lines of code.

   This is the pushButton() event. Code placed between the two existing lines gets executed each time the button is clicked.

8. Add the following code to the pushButton() event (note that you don't need to add the method or endMethod lines; they're provided for context. Also, the code is explained a little later:

    method pushButton(var eventInfo Event)
    var
       qry  Query           ; pointer to our query
       roi  ReportOpenInfo  ; controls report open options
       rpt  Report          ; pointer to our report
       str  String          ; holds current Customer ID
       tc   tCursor         ; holds results from query
                            ;  (better speed and validation)
    endVar

    const
       ANSTABLE = ":priv:answer.db"   ; temp table for results
       ERRTITLE = "Can't Show Orders" ; title of error dialogs
       STDERROR = "Use [>>] for details..." ; std. error msg.
    endConst

       ;// make sure we have a customer
       if Customer_No.isBlank() then
          beep()
          msgStop( ERRTITLE, "Reason: " +
                   "No customer selected.  Please " +
                   "select (or add) a customer." )
       else

          ;// select the current customer
          str = Customer_No.Value
          qry = Query

             Customer.db | Customer No |
                   Check | ~str        |

          endQuery

          ;// make sure we get results

          switch

             case not qry.executeQBE( tc ) :
                errorShow( ERRTITLE, STDERROR )
                qry.writeQBE( ":priv:badquery.qbe" )

             case tc.nRecords() = 0 :
                   msgStop( ERRTITLE, "Reason: No customers " +
                            "have " + str + " as their ID.  \n\n" +
                            "Have you saved your changes?" )


             case not tc.instantiateView( ANSTABLE ) :
                errorShow( ERRTITLE, STDERROR )

          otherwise : ; try to open the report
             try
                roi.MasterTable = ANSTABLE
                roi.Name = "customer.rsl"
                rpt.open( roi )
                rpt.designModified = FALSE
                rpt.wait()
             onFail
                errorShow( ERRTITLE, STDERROR )
             endTry

          endSwitch
       endIf

       ;// Clean up and exit
       ;// _ very_ important; do this always!
       if tc.isAssigned() then
          tc.close()
       endIf

    endMethod

9. Press F9 to verify that you don't have any syntax errors; if errors are reported, compare your code to the above listing. They probably result from typographic mistakes.

10. Choose File | Close to close and save your changes to the event.

11. Choose File | Save to save the changes to CustForm.fsl.

12. Press F8 to run the form and the click the button. You should see a report preview window showing the orders placed by Kauai Dive Shoppe (Customer No: 1221). When satisfied with the report, close it by clicking the little [x] box in the upper right corner.

That's the basic process. When reviewing the code itself, please keep the following points in mind:

1. This is only one way to implement this, one specifically designed to showcase several considerations and techniques. Another technique is described in Printing a Single Record.

2. The Var..EndVar block declares variables used by the code. A description of each variable is provided after the semi-colon (;), which tells Paradox that the rest of the line is a comment.

3. The Const..EndConst block declares constants (variables that cannot be changed). These let you define values that could change between uses (programs) but not while the program is running. For example, the ERRTITLE constant is set to "Can't Show Orders" because that's the purpose of the button. However, if we copied this button to an Orders form and reworked the code to print an Invoice, then we would change ERRTITLE to "Can't Print Invoice."

   This is helpful because it means that we only need to make this change once, instead of every place where the constant gets used.

4. The If..Else...Endif statement is a comparison statement; it lets you validate certain conditions and take action depending on the results of that comparison. The actual comparison is called an expression.

   In this case, we make certain that the current Customer record has a Customer Number, which we need for the query. If not, an error message is displayed.

5. If there is a customer number, we assign it to a string variable so it can be automatically placed in the query as a parameter. When defining queries like this, it's best to use string variables explicitly; otherwise, Paradox will automatically convert your value to a string and that may not give you the results you need.

6. The qry = Query...EndQuery block defines the query being run. In this example, we've demonstrated a couple of different techniques:

   1. The Check underneath the table name is a shortcut for placing checkmarks in every field.

   2. The ~str reference under the Customer No field name tells Paradox to place the value assigned to str into the query being run. In this use, ~str is considered a query variable.

   3. To adapt this code to a situation where you're working with a saved query, replace the qry = Query...EndQuery blocks with the following:

       qry.readFromFile( "MYQUERY.QBE" )
   

   8. Once the query is defined, we use Switch...endSwitch statement to perform and validate a number of steps. If any of the case expressions returns True, then Paradox runs the code inside the Case "branch" and ignores the following branches. The purpose of each branch is explained below.

   9. The "not qry.executeQBE( tc )" branch shows a couple of things:

      1. How to execute a query assigned to a query variable.

      2. How to run a query without making a physical Answer table. This is a performance enhancement that can significantly reduce the time needed to run the query. If the query is able to complete, then its results are assigned to tc, which is an in-memory table containing the query results.

      3. If the query cannot complete (for whatever reason) then executeQBE() returns False and the problems are placed on the error stack. The code inside this branch displays the error stack and saves the query to a text file in your private directory for debugging purposes. This lets you open a query with Notepad for debugging.

   10. The "tc.nRecords() = 0" branch verifies that records were returned by the query. That should always be the case in this example, however, it's worth showing because it's a very useful technique in this sort of situation.

   11. The "not tc.instantiateView()" branch creates a temporary table in your private directory containing the results of your query. In heavy-use network environments, this can be faster than relying on Paradox to create the Answer table for you.

       Please be aware that instantiateView() has an important subtlety associated with it. Once the temporary table is created, the tCursor variable points to the temporary table, not the original table. That's not an issue in this case, but it's worth mentioning if you use instantiateView() for other operations.

   12. Otherwise is a keyword associated with the Switch statement that tells Paradox what to do if none of the Case branches have been executed. In this (ahem) case, we're counting on that being the most common situation and use it to open the report.

   13. The roi variable is a special variable designed to provide control over a report opened with ObjectPAL. In this example, we use it to a) change the master table of the report, b) indicate the name of the report being run, and c) reset the report to an unmodified state.

       If we had not done the latter, the user would be prompted to save the changed report when closing it. Setting DesignModified to FALSE avoids this and ensures that the original report remains untouched.

   14. The clean up portion of the code illustrates an important concern regarding tCursor variables. Specifically, you should always formally close them before the end of your code. There are a few cases where tCursors are not closed by Paradox when they go out of scope (can no longer be used).

       These are not bugs in Paradox, per se, but situations where closing the tCursor may damage data. A common situation occurs when you use a tCursor to add a new record to a table, but accidentally enter a key violation. In this case, Paradox cannot post your new record, so the tCursor remains open. Unfortunately, the only way to deal with this situation is to exit and restart Paradox. At that point, Paradox cancels your changes. In turn, this illustrates the importance of always checking return values. (A future article will explore this issue in more detail.)

   If you made it this far, congratulations. You've just started programming with ObjectPAL. this particular example is a little more involved than many and we've barely touched on many important topics. However, we hope we've given enough information to a) start using ObjectPAL and b) use the Help files and ObjectPAL Tutorial effectively.