Microsoft KB Archive/166294

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

Article ID: 166294

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q166294



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



SUMMARY
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

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
 Start Microsoft Access 97 and open the sample database Northwind.mdb.  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]  On the Query menu, click Parameters.  Type the following in the Query Parameters dialog box, and then click OK.

     Parameter          Data Type [CustID]          Text  Save the qryOrdersWeb query and close it.</li></ol>

Creating and Exporting the OrdersWeb and CustList Forms
<ol> Use the AutoForm: Columnar Wizard to create a form based on the query qryOrdersWeb.</li> Click OK when prompted to enter a parameter value for CustID.</li> Save the form as OrdersWeb and then close it.</li>  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.

<pre class="fixed_text">      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 </li> Save and close the CustList form.</li> On the File menu, click Save As HTML. When the "Publish to the Web" Wizard appears, click Next on the opening screen.</li> On the "What do you want to publish?" screen, click the CustList form and the OrdersWeb form, and then click Next.</li> Click Next on the screen that prompts you to select a default template.</li> On the "What default format type do you want to create?" screen, click Dynamic ASP, and then click Next.</li> 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:

159682 "Data Source Name Not Found" Err Msg Opening Web Page</li> 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.</li> 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.</li></ol>

Customizing the ASP Files
<ol>  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 </li> Save and close CustList_1alx.asp.</li>  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:

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

<pre class="fixed_text">     If IsObject(Session("Form_OrdersWeb_rs")) and _ Request.QueryString("[CustID]").count=0 Then

</li> Save and close OrdersWeb_1.asp.</li> <li> 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:

<pre class="fixed_text">     If IsObject(Session("Form_OrdersWeb_rs")) Then

Change this line of code to match the following:

<pre class="fixed_text">     If IsObject(Session("Form_OrdersWeb_rs")) and _ Request.QueryString("[CustID]").count=0 Then

</li> <li>Save and close OrdersWeb_1alx.asp.</li></ol>

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://%3Cservername%3E/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.
 * 1) 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.

<div class="references_section">