Microsoft KB Archive/195340

= How To Create a Visual InterDev 1.0 Data Form Wizard with Visual InterDev 6.0 Add Filter =

Article ID: 195340

Article Last Modified on 7/1/2004

-

APPLIES TO


 * Microsoft Visual InterDev 6.0 Standard Edition

-



This article was previously published under Q195340



SUMMARY
This article builds on Knowledge Base article 192665 "How To 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:

192665 How To Create a Visual InterDev 1.0 Data Form Wizard with VI 6.0



MORE INFORMATION
In article 192665, 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:


 * 1) Add Filter Input Fields.
 * 2) Add Action Buttons.
 * 3) Create Persistent Filter Property.
 * 4) Add Functions to Create Filter Statement.
 * 5) Add Functions to Apply Filter.
 * 6) Add Filter Mode to Form Manager.

Before You Begin
As stated earlier, ensure that you have completed the application from Knowledge Base article 192665 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 ().  

   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 "" 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)

ApplyFilter 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 - because the Recordset DTC does not expose the Filter property of the underlying ADO Recordset, ApplyFilter uses the getRecordSource to retrieve a reference to the underlying ADO Recordset and expose the Filter property.
 * 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

rstProducts.getRecordSource.Filter = pageDFW.getFilter

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:

<pre class="fixed_text">  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:

<pre class="fixed_text">  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:

<pre class="fixed_text">  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:

<pre class="fixed_text">  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:

<pre class="fixed_text">  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:

<pre class="fixed_text">  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.

<div class="references_section">