ACC97: How to Create ASP Form That Can Filter Another ASP Form

• Microsoft Access 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

You can create an ASP form with a combo box that will dynamically filter the Microsoft Access data displayed on another ASP Form. One way to accomplish this is to export a Microsoft Access 97 form that is based on a parameter query. This creates an ASP form that uses criteria passed to it from an HTML form. However, instead of using the HTML form that is created automatically, you can export a second Microsoft Access 97 form to ASP format. Then you can use the second form to filter the first form. The ASP scripts that Microsoft Access generates for both forms need slight modifications to achieve the desired results.

WARNING: In order for the example in this article to work correctly, you must follow the steps in the example in the exact order in which they are presented.

This article contains a step-by-step example that creates an ASP form with a combo box containing a list of company names and a submit button. The button calls an ASP form that displays only the orders for the company that you selected in the first form's combo box.

MORE INFORMATION

The following example contains four sections:

• Creating the Parameter Query That the OrdersWeb Form Will Use
• Creating and Exporting the OrdersWeb and CustList Forms
• Customizing the ASP Files
• Testing the Query

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Creating the Parameter Query That the OrdersWeb Form Will Use

1. Start Microsoft Access 97 and open the sample database Northwind.mdb.

2. Create a new query in Design view based on the Orders table:

         Query: qryOrdersWeb
         ---------------------
         Type: Select Query
   

         Field: Orders.*
            Table: Orders
            Show: Yes
         Field: CustomerID
            Table: Orders
            Show: No
            Criteria: [CustID]
   
   

3. On the Query menu, click Parameters.

4. Type the following in the Query Parameters dialog box, and then click OK.

         Parameter          Data Type
         ----------------------------
         [CustID]           Text
   
   

5. Save the qryOrdersWeb query and close it.

Creating and Exporting the OrdersWeb and CustList Forms

1. Use the AutoForm: Columnar Wizard to create a form based on the query qryOrdersWeb.

2. Click OK when prompted to enter a parameter value for CustID.

3. Save the form as OrdersWeb and then close it.

4. Create a new form in Design view based on the Customers Table.

   NOTE: In the following sample, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating the RowSource for the combo box.

         Form: CustList
         ---------------------------------------------------------
         RecordSource: Customers
         NavigationButtons: No
   

         Command button:
            Name: CallOrdersWeb
            Caption: Display Orders
         Combo box:
            Name: CustomerFilter
            RowSource: SELECT [Customers].[CustomerID], _
                       [Customers].[CompanyName] FROM [Customers];
            ColumnCount: 2
            ColumnWidths: 0";1"
            BoundColumn: 1
   
   

5. Save and close the CustList form.

6. On the File menu, click Save As HTML. When the "Publish to the Web" Wizard appears, click Next on the opening screen.

7. On the "What do you want to publish?" screen, click the CustList form and the OrdersWeb form, and then click Next.

8. Click Next on the screen that prompts you to select a default template.

9. On the "What default format type do you want to create?" screen, click Dynamic ASP, and then click Next.

10. In the Data Source Name box of the "What are, or will be, the settings

        for the Internet database?" screen, enter the name of a System DSN
        on your Web server that points to the Northwind sample database.
    

        For more information on how to define a system DSN, search the Help
        index for "ODBC, setting up data sources," and see the following
        article in the Microsoft Knowledge Base:
    

           ARTICLE-ID: Q159682
           TITLE     : "Data Source Name Not Found" Err Msg Opening Web Page
    
    

11. In the Server URL box of that same screen, enter the URL that points to

        the Web Server location where your ASP files will be stored. For
        example, if you store the ASP files in the \ASPsamp folder on the
        \\PubTest server, type http://pubtest/aspsamp/ as your Server URL.
        Click Next.
    

12. On the "Where do you want to publish to?" screen, select the folder on

        your Web server indicated by the Server URL you typed in step 11. You
        must have Execute permission for this folder. Click Finish. The
        "Publish to the Web" Wizard creates five files: CustList_1.asp,
        CustList_1alx.asp, OrdersWeb_1.asp, OrdersWeb_1alx.asp, and
        OrdersWeb_1.HTML. OrdersWeb_1.HTML is not be used for this example,
        and you can delete it.
    

Customizing the ASP Files

1. Use Notepad or another text editor to open the CustList_1alx.asp file, and locate the following VB Script procedure:

         Sub CustomerFilter_AfterUpdate()
            call AddCtrlToList("CustomerFilter", "")
            call UpdateRefreshBtn()
         End Sub
   
      You must modify this procedure and add another Sub procedure that will
      handle the Click event of the CallOrdersWeb command button. Modify the
      code so that it looks like the following (you do not have to enter the
      lines that begin with apostrophes because they are comments):
   
         Sub CustomerFilter_AfterUpdate()
            call AddCtrlToList("CustomerFilter", "[CustID]")
            '[CustID] is the name of the parameter that will be passed to
            'OrdersWeb_1.asp.
            'The following line is commented out because this form does not
            'have navigation buttons.
            'call UpdateRefreshBtn()
         End Sub
   
         Sub CallOrdersWeb_Click()
            window.location.href = "OrdersWeb_1.asp?" & GetCtrlQueryString()
         End Sub
   
   

2. Save and close CustList_1alx.asp.

3. Use Notepad or another text editor to open the OrdersWeb_1.asp file. Near the top of the file you will find the following line of code:

         If IsObject(Session("Form_OrdersWeb_rs")) Then
   

   You must modify this line of code so that it checks to see if the [CustID] parameter is being passed because using the navigation buttons on the orders form will call OrdersWeb_1.asp without passing the [CustID] parameter. Change the line of code to the following:

         If IsObject(Session("Form_OrdersWeb_rs")) and _
            Request.QueryString("[CustID]").count=0 Then
   
   

4. Save and close OrdersWeb_1.asp.

5. Use Notepad or another text editor to open the OrdersWeb_1alx.asp file. Near the top of the file you will find the following line of code:

         If IsObject(Session("Form_OrdersWeb_rs")) Then
   

   Change this line of code to match the following:

         If IsObject(Session("Form_OrdersWeb_rs")) and _
            Request.QueryString("[CustID]").count=0 Then
   
   

6. Save and close OrdersWeb_1alx.asp.

Testing the Query

1. Start Microsoft Internet Explorer 3.0.

2. Type the Uniform Resource Locator (URL) in the address box of your Web browser to view CustList_1.asp. For example, if you saved your ASP files in a folder called Test in the wwwroot folder of your Web Server, type:

         http://<servername>/test/CustList_1.asp
   

   Note that the URL depends upon where your files are located on the Web Server and that Internet Explorer 3.0 with the HTML Layout Control is necessary to view forms exported to ASP.

3. The CustList_1.asp form opens in your Web browser with a combo box that contains company names and a Display Orders button. Select a company in the combo box, and then click the Display Orders button. The OrdersWeb_1.asp form displays the orders for the company that you selected.

   NOTE: If the CustList_1.asp form appears and works correctly, but the OrdersWeb_1.asp form opens to a blank page, check the [CustomerID] field in the query to see if the Show check box has been cleared. If it has not been cleared, you must clear it, and then rebuild the AutoForm OrdersWeb.

REFERENCES

For more information about exporting ASP files, search the Help Index for "ASP files," or ask the Microsoft Access 97 Office Assistant.

For more information on how to create and modify ASP files, please refer to your Microsoft ASP documentation.

For more information about creating a dynamic HTML combo box to filter data in an IDC query, please see the following article in the Microsoft Knowledge Base: