HOWTO: Create a VI 1.0 Data Form Wizard with VI 6.0: Add Filter

ID: Q195340

The information in this article applies to:

Microsoft Visual InterDev, version 6.0

SUMMARY

This article builds on Knowledge Base article Q192665 "HOWTO: Create a Visual InterDev 1.0 Data Form Wizard with VI 6.0." The sample included in this article assumes that you have created the sample found in article Q192665.

IMPORTANT: Before continuing with this article, be sure to first complete the sample application created in the following article from the Microsoft Knowledge Base:

Q192665 HOWTO: Create a Visual InterDev 1.0 Data Form Wizard with VI 6.0

MORE INFORMATION

In article Q192665, you built a Visual InterDev 6.0 version of the Visual InterDev 1.0 Data Form Wizard using the Visual InterDev 6.0 Design-Time Controls (DTCs). Using the DTCs, you were able to build a powerful application in little time that required almost no code to be written by hand. However, the application that was created did not possess the ability to filter recordsets. Filtering gives the user the ability to reduce the number of displayed records according to specified criteria (for example, Name = 'Smith').

To add filtering capability to your application, you will be writing considerably more "by hand" code. You will write functions to generate a filter statement (for example, ProductType = 'Tent' AND ProductName LIKE 'S%') from your input objects. You will then write functions to apply this filter, as well as code to ensure that the filter persists from page to page.

To enhance the sample application with filtering capability, you will be completing the following steps:

Add Filter Input Fields.

Add Action Buttons.

Create Persistent Filter Property.

Add Functions to Create Filter Statement.

Add Functions to Apply Filter.

Add Filter Mode to Form Manager.

Before You Begin

As stated earlier, ensure that you have completed the application from Knowledge Base article Q192665 before continuing with this sample application. Since this article builds on that sample application, open the previously created application in Visual InterDev 6.0 before continuing.

1) Add Filter Input Fields

In order to provide input fields to allow the user to supply their filter parameters, you will add unbound input objects for each of your data-bound objects.

Step 1:

Locate your data-bound text fields and place the cursor after the first data-bound text field (txtProductCode). Double-click Textbox in the Toolbox to insert a new Textbox DTC. Right-click the inserted Textbox DTC and select Properties. Name the Textbox "txtFilterProductCode." Click OK. Repeat the process for both the txtProductName (txtFilterProductName) and txtUnitPrice (txtFilterUnitPrice) Textbox DTCs.

Step 2:

Place your cursor after the Listbox DTC (lstProductType) and double-click Listbox in the toolbar to insert a Listbox DTC. Right-click the Listbox DTC and select Properties. Name the Listbox "lstFilterProductType".

Step 3:

Select the Lookup tab and set the List Source to a Recordset with a Row source of rstProductType, a Bound column of ProductType, and a List field of ProductType. Click OK.

It is important to note that the Listbox provided for filtering will be populated with the same list of items as the bound Listbox used in your Form View. Unfortunately, there is no option in the list to select "none"; that is, when you filter you will always select a ProductType to filter on. However, you will often want to exclude the ProductType from the filter. To remedy this, you need to provide a "blank" option for the filter Listbox. You have several choices for how to do this. For this sample, you will modify the recordset generated to include a blank row as the first option.

Step 4:

Right-click the Recordset DTC rstProductType and select Properties. Update the SQL Statement to the following then click OK:


   Select '' As ProductType From Products
   UNION
   Select Distinct ProductType From Products ORDER BY ProductType

Step 5:

Locate the bound OptionGroup "optOnSale." Place your cursor after the OptionGroup and double-click OptionGroup in the Toolbox to insert an OptionGroup DTC. Right-click the new OptionGroup DTC and select Properties. Name the OptionGroup DTC "optFilterOnSale."

The OptionGroup DTC has an issue similar to the ListBox DTC in that you need to provide a "blank" option to not filter on the OnSale field. You also need to consider the conversion of the values "true" and "false" to values that are valid for the filter. In this case, with an Access Database Yes/No field, true maps to -1 and "false maps to 0.

Step 6:

Select the Lookup tab. Set the List source to Static list with the following values in the list then click OK:


   Bound Value   Display
   -----------   -------
                 Both
   -1            Yes
    0             No

Save your changes.

2) Add Action Buttons

You will be adding five new buttons to your page to enable the filtering capability:


   Filter      - Switch to "Filter" view (which will be defined later in
                 the FormManager).
   Cancel      - Exit Filter view without making changes to the filter.
   Apply       - Exit Filter view and apply the filter.
   All Records - Clear the filter.

Step 1:

Place your cursor after the Requery button. Double-click Button in the Toolbox to add a Button DTC. Right-click the Button DTC and select Properties. Name the Button DTC "btnFilter" and set the Caption to "Filter." Click OK. Add three more Button DTCs with the following values:


   Name=btnCancel, Caption=Cancel
   Name=btnApply, Caption=Apply
   Name=btnAllRecords, Caption=All Records

Save your changes.

3) Create Persistent Filter Property

In order for your filter to continue to be applied while a user navigates the recordset, you need to ensure that it persists from page to page. You have several options to accomplish this. For this sample, you will use the PageObject DTC, which provides the capability to create a Property for the page. Once a Property is created, the PageObject DTC automatically creates getProperty() and setProperty() methods to enable us to set the property and retrieve it from page to page. For your sample application, you will create a property called "Filter." Once it is created, the PageObject will be expanded to have two new methods: getFilter() and setFilter().

Step 1:

Select the PageObject DTC, pageDFW. Right-click the PageObject DTC and select Properties. Click the Properties tab.

Step 2:

Enter a new property with the following values then click Close:


   Name   Lifetime Client Server
   ------ -------- ------ ----------
   Filter Page     Read   Read/Write

Now that you have a persistent filter property, you need to display the filter on the page for the user to see. To do this, you will expand the heading table to contain a second row, and you will ad a Label DTC to hold the filter value.

Step 3:

Locate the heading table (before the Grid DTC) and add the following Table Row HTML before the end table tag (</TABLE>).


   <TR>
     <TD>

     </TD>
     <TD></TD>
   </TR>

Step 4:

Drag a Label DTC into the first cell of this new table row. Right- click the Label DTC and select Properties. Name the Label DTC "lblCurrentFilter" and clear the default caption from the Field/expression field leaving it blank. Click the Format tab and select "Data contains HTML." Click OK.

Save your changes.

4) Add Functions to Create Filter Statement

At this point, you will need to write a bit of code on your own; that is, without a DTC. The following is the VBScript code that you will use for your sample. You will create three subroutines for this purpose:

FormatFilter - Takes as input a field name, the value of the filter input object associated with the field, and an appropriate delimiter for the type of data (single-quotes for character values, an empty string for integer values, and so forth). FormatFilter creates the filter statement from these values (for example, ProductName LIKE 'S%' AND ProductType = 'Tent'). Note that the Textbox values are accessed using their "value" property and Listbox and OptionGroup values are accessed using their "getValue()" methods).

SetFilter - Calls FormatFilter to generate the filter statement, then sets the PageObject Filter property, sets the message displayed to the user, and calls ApplyFilter (ApplyFilter is defined in the next step) to apply the filter statement to the ADO Recordset. Note that SetFilter closes the recordset before applying the filter and reopens it after. This is to avoid errors that will occur if the current bookmark is greater than the number of records in the filtered resultset.

ClearFilter - Clears the PageObject Filter property, resets the message displayed to the user, and calls ApplyFilter (ApplyFilter is defined in the next step) to clear the filter property of the ADO Recordset.

Step 1:

Insert the following code after "<SCRIPT ID=serverEventHandlersVBS LANGUAGE=vbscript RUNAT=Server>" in the heading of the page.


   Dim strFilter

   Sub FormatFilter(strFld, strInp, strDlm)
     strInput = Trim(strInp)

     If strInp <> "" Then

       If strFilter <> "" Then strFilter = strFilter & " AND "

       If UCase(Left(strInput,5)) = "LIKE " Then
         strFilter = strFilter & strFld & " " & strInp
       Else
         strFilter = strFilter & strFld & " = " & strDlm & strInp & strDlm
       End If

     End If
   End Sub

   Sub SetFilter()
     FormatFilter "ProductCode", txtFilterProductCode.value, "'"
     FormatFilter "ProductName", txtFilterProductName.value, "'"
     FormatFilter "ProductType", lstFilterProductType.getValue(), "'"
     FormatFilter "UnitPrice", txtFilterUnitPrice.value, "'"
     FormatFilter "OnSale", optFilterOnSale.getValue(), ""

     pageDFW.setFilter(strFilter)

     lblCurrentFilter.setCaption("Current Filter: " & strFilter)

     rstProducts.close
     ApplyFilter
     rstProducts.open
   End Sub

   Sub ClearFilter()
     pageDFW.setFilter("")

     lblCurrentFilter.setCaption("Current Filter: [None]")

     ApplyFilter
   End Sub

Save your changes.

5) Add Functions to Apply Filter

You need to add two functions to apply the filter: One that actually applies the filter and a second that ensures the filter will continue to be applied while the recordset is being navigated (using the onshow event of the PageObject). Here are the functions:

ApplyFilter - Since the Recordset DTC does not expose the Filter property of the underlying ADO Recordset, ApplyFilter uses the getRecordSource() and setRecordSource() to retrieve the underlying ADO Recordset, apply the Filter, and set the source of the Recordset DTC to the filtered ADO Recordset.

rstProducts_onbeforeopen - SetFilter and ClearFilter both call ApplyFilter to apply the changes to the current filter. However, SetFilter and ClearFilter are called only when a user clicks btnApply or btnAllRecords, respectively (you'll create this relationship in a later step when you modify the FormManager DTC). If a user is simply navigating a filtered recordset, you need to ensure that the filter continues to be applied. You accomplish this by calling ApplyFilter in the onbeforeopen event of the Recordset DTC, rstProducts.

pageDFW_onshow - In the onshow event of the PageObject, you call ClearFilter to initialize the PageObject Filter property and the message displayed to the user. Our PageObject onshow event is also where you will decide whether to show btnAllRecords. The reason for this is that btnAllRecords is not shown or hidden per Form mode, rather it is available in all modes, but only if a filter exists.

Step 1:

Add the following code after "<SCRIPT ID=serverEventHandlersVBS LANGUAGE=vbscript RUNAT=Server>" in the heading of the page:


   Sub ApplyFilter()
     If Not IsNull(pageDFW.getFilter()) Then
       Set tempRS = rstProducts.getRecordSource()

       tempRS.Filter = pageDFW.getFilter()

       rstProducts.setRecordSource(tempRS)
     End If

   End Sub

   Sub rstProducts_onbeforeopen()
     ApplyFilter
   End Sub

   Sub pageDFW_onshow()
     If IsNull(pageDFW.getFilter()) Then
       ClearFilter
     End If

     If pageDFW.getFilter() = "" Then
       btnAllRecords.hide
     Else
       btnAllRecords.show
     End If

   End Sub

6) Add Filter Mode to Form Manager

Now that you've added a number of new controls to the page, you need to create your Filter mode in the FormManager (fmgrDFW), as well as which controls will be available in Filter mode. You also need to update the FormView and ListView modes to hide the new controls, which will not be available to the user except in Filter mode.

Step 1:

Right-click the FormManager DTC (fmgrDFW) and select Properties. In the New mode field, type "Filter" and click ">" to add the Filter mode.

Step 2:

In the Form Mode section, select Filter. In the "Actions Performed For Mode" section, add the following:


   Object               Member   Value
   -------------------- -------- --------
   btnApply             show     ()
   btnCancel            show     ()
   btnView              show     ()
   btnView              value    "List View"
   txtFilterProductCode show     ()
   txtFilterProductName show     ()
   txtFilterUnitPrice   show     ()
   lstFilterProductType show     ()
   optFilterOnSale      show     ()
   lblProductCode       show     ()
   lblProductName       show     ()
   lblProductType       show     ()
   lblUnitPrice         show     ()
   lblOnSale            show     ()
   btnDelete            hide     ()
   btnFilter            hide     ()
   btnNew               hide     ()
   btnRequery           hide     ()
   btnUpdate            hide     ()
   grdProducts          hide     ()
   lstProductType       hide     ()
   navProducts          hide     ()
   optOnSale            hide     ()
   txtProductCode       hide     ()
   txtProductName       hide     ()
   txtUnitPrice         hide     ()

Step 3:

In the Form Mode section, select ListView. In the Actions Performed For Mode section, add the following to the list already there:


   Object               Member   Value
   -------------------- -------- --------
   btnFilter            hide     ()
   btnApply             hide     ()
   btnCancel            hide     ()
   txtFilterProductCode hide     ()
   txtFilterProductName hide     ()
   txtFilterUnitPrice   hide     ()
   lstFilterProductType hide     ()
   optFilterOnSale      hide     ()
   btnView              show     ()

Step 4:

In the Form Mode section, select FormView. In the "Actions Performed For Mode" section, add the following to the list already there:


   Object               Member   Value
   -------------------- -------- --------
   btnFilter            show     ()
   btnApply             hide     ()
   btnCancel            hide     ()
   txtFilterProductCode hide     ()
   txtFilterProductName hide     ()
   txtFilterUnitPrice   hide     ()
   lstFilterProductType hide     ()
   optFilterOnSale      hide     ()
   btnView              show     ()

Step 5:

Select the Action tab and add the following to the current list:


   Current Mode  Object        Event    Next Mode
   ------------- ----------    -------- ----------
   FormView      btnFilter     onclick  Filter
   Filter        btnView       onclick  ListView
   Filter        btnCancel     onclick  FormView
   Filter        btnApply      onclick  FormView
   Filter        btnAllRecords onclick  FormView
   FormView      btnAllRecords onclick  FormView
   ListView      btnAllRecords onclick  ListView

Step 6:

Select the row that has a Current Mode of Filter and an Object of btnApply. In the Actions Performed Before Transition section enter the following then click Close:


   Object        Member    Value
   ------------- --------- --------

                 SetFilter ()

Step 7:

Select the row that has a Current Mode of Filter and an Object of btnAllRecords. In the Actions Performed Before Transition section enter the following:


   Object        Member      Value
   ------------- ---------   --------

                 ClearFilter ()

Repeat Step 7 for Current Mode of FormView and Object of btnAllRecords as well as for CurrentMode of ListView and Object of btnAllRecords. Click Close.

Save your changes.

You have now added filtering capability to your Data Form Wizard application.

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

Q192665 HOWTO: Create a Visual InterDev 1.0 Data Form Wizard with VI

Keywords : kbVisID600 kbGrpASP
Version : WINDOWS:6.0
Platform : WINDOWS
Issue type : kbhowto