I like the automatic QBF facility that comes with all default Power Object applications. Is there any way to pass the selection criteria to a report?
Yes, there is, but it may not be intuitively obvious. In trying to use a form to specify selection conditions for a report through its QBF facility, you run into two obstacles. The first obstacle is that you do not have any way of getting to the selection clause created by use of the QBF facility in a form. The second obstacle is the limited way available to impose selection criteria on a report.
To overcome the first obstacle, you will need to use some code and possibly the help of a temporary database table. Although it is true that you cannot get access to the WHERE clause created by the QBF activity, you do have access to all the rows that were returned as a result of the QBF selection criteria. You will have to create a list of values that uniquely identify the rows in the recordset for the form.
The unique identifiers could be either the unique key value for the row or, if you are using an Oracle database, the ROWID for the row, which not only uniquely identifies the row, but is a very fast way to access rows. If you are going to use a list of ROWIDs, you will have to create a temporary table in the database with a single column to hold the ROWIDs. You will first make sure that you have all the rows in the database by using the
FetchAllRows() method for the recordset. You will then delete all the rows in the temporary table with an
EXEC SQL statement.
After clearing the table, you will walk through the recordset and write the ROWID to the temporary table. In the example below, I have used an
EXEC SQL statement to accomplish the insert. When you have completed writing all the values, you
COMMIT the inserts to the database.For this example, I am using a form called
frmMain and a temporary table called
TEMP_ROWID that contains a column called
TEMP_ROWID. The code for this is:
DIM nCount As Integer DIM nPointer As Integer DIM nROWID As Integer DIM sSQL As String frmMain.GetRecordset().FetchAllRows() EXEC SQL AT MLDATA delete from TEMP_ROWID nCount = frmMain.GetRecordset().GetRowCount() nPointer = 1 DO WHILE nPointer <= nCount nROWID = frmMain.GetRecordset().GetColVal("ROWID") sSQL = "insert into TEMP_ROWID (" & nROWID & ")" EXEC SQL AT MLDATA :sSQL nPointer = nPointer + 1 LOOP EXEC SQL AT MLDATA commit
You have all your ROWIDs safely in their temporary table. Your final step is to call the report and set the limitation on the rows for the report. The only way to impose a selection condition on a report is through the
DefaultCondition of the report. Set the
DefaultCondition of the report to
"ROWID = (SELECT TEMP_ROWID FROM TEMP_ROWID)" . The subSELECT will return the ROWIDs from the temporary table and limit the rows in the report to the same rows from the original table.You could accomplish the same effect by building a string of all the ROWIDs and setting the
DefaultCondition for the report to
"ROWID IN (" concatenated with the string of ROWID values and terminated with
")". This method might be a little more efficient, since it would not require a query to the database to fetch the ROWIDs, but I would be worried that you might have too many values and exceed the size limit for the