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

ID: Q166294

The information in this article applies to:

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:

NOTE: This example contains information about editing ASP files. It assumes that you are familiar with Active Server, Visual Basic Scripting, and editing HTML files. Microsoft Access Product Support professionals do not support modification of any HTML, HTX, IDC, or ASP files.

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:

    Q159682 "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:

Q165359 Dynamic Combo Box to Filter Access Data in IDC Format 


Keywords          : OtpOthr IntAsp 
Version           : 97
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: May 17, 1999