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:
- 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.
Creating and Exporting the OrdersWeb and CustList Forms
- Use the AutoForm: Columnar Wizard to create a form based on the query
qryOrdersWeb.
- Click OK when prompted to enter a parameter value for CustID.
- Save the form as OrdersWeb and then close it.
- 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
- Save and close the CustList form.
- On the File menu, click Save As HTML. When the "Publish to the Web"
Wizard appears, click Next on the opening screen.
- On the "What do you want to publish?" screen, click the CustList form
and the OrdersWeb form, and then click Next.
- Click Next on the screen that prompts you to select a default
template.
- On the "What default format type do you want to create?" screen, click
Dynamic ASP, and then click Next.
- 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
- 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.
- 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
- 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
- Save and close CustList_1alx.asp.
- 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
- Save and close OrdersWeb_1.asp.
- 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
- Save and close OrdersWeb_1alx.asp.
Testing the Query
- Start Microsoft Internet Explorer 3.0.
- 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.
- 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
|