FrontPage Tips by Ward Cameron Enterprises
Take Your Web Site to New Levels

Using Multiple Select with a Drop-down Search Box

This tutorial is part of a larger discussion on creating search forms for your FrontPage Database website. Click here to read the full tutorial.

The process involves several steps

1. Searching begins with website design

2. Create a Progressive Search Form

3. Add an All Values option to your Search Form

4. Using Drop-down Menus with Multiple Select

5. Add a Text Search Field Using the DRW

6. Search Multiple Fields in the Same Table

7. Search Multiple Fields or Tables in the Same Database

In this tutorial, we'll show you how to use the Multiple Select option in a drop-down list to narrow down your search. So far, you've learned how to use a drop-down box in a search form AND how to add an All Values option to the drop-down box. Now, you'll learn how to use a little FrontPage Magic to allow users to select a single option OR to select several different options from the drop-down list and have the related values displayed on the results page.

Text vs. Numeric Values in Multiple Select

There is an easy way and a hard way to accomplish this. Your choice depends upon what type of value you are passing through the form. If you are passing a numeric value, such as an autonumber ID field, you can take the simple approach. Other field types require custom coding to accomplish the same result.

This is one more reason why EVERY table you create should use a unique autonumber field as the key. It just simplifies everything you do later.

We accomplish multiple selections using an SQL statement like the following:

SELECT * FROM Products WHERE ProductID IN(::ProductID::)

This tells FrontPage to return any records from the Products table where the ProductID is contained within the fields selected.

In your FrontPage form, you can select multiple values, each of which will be sent to your results page separated by a comma field.

Later, in the results page, our SQL statment needs to be able to determine the individual values and then return the correct records. For numeric values it's easy. That is because SQL coding can read number values without any help. Text values on the other hand, can only be understood if they are surrounded by an apostrophe.

So if you are sending several numeric ID values your form would send a value something like the following:

ProductID=(1,25,19)

A text value would similarly be sent as:

CustomerID=(ANATR,AROUT,BLAUS)

Unfortunately, for these values to be understood by your SQL statement they must be separated by apostrophies as in the following:

CustomerID=('ANATR','AROUT','BLAUS')

To get beyond this barrier, we need to perform a little FrontPage Magic. For this reason, using multiple select with text fields is a more complicated process.

Multiple Select With A Numeric Autonumber ID Field

The process begins with the form. On a blank page select Insert-Form-Form. This will place a blank form on your page. Now, select Insert-Database-Results.

Select the Northwind Connection and the Products Table. In Step 3, remove all fields from the list with the exception of ProductName and ProductID. Sort the list by ProductName. In Step 4 select Drop-down list with ProductName as the displayed field and ProductID as the field to be submitted.

Right click on the drop-down box to open the Drop-down Box Properties. Select the Allow Multiple Selections check box. The Drop-down Box Properties should resemble the following:

Click OK to close the Properties dialog. You should now enlarge the field by dragging the bottom down to lengthen it.

Right click on the Form and select Form Properties. Choose Send to Other and click the Options button. In the Options For Custom Form Handler screen type MultipleSelectNumberResults.asp in the Action field and Post as the Method.

Your form should resemble the one below. Save your form as MultipleSelectNumeric.asp

Now it is time to create your results page. Create another blank page and once again select Insert-Database Results. Select the Northwind Connection but in Step 2 select Custom Query and then Edit..

In the Custom Query window, type the following:

SELECT ProductID,ProductName,SupplierID,CategoryID FROM Products WHERE ProductID IN(::ProductID::)

Your screen should resemble the following:

This will select the ProductID, ProductName, SupplierID and CategoryID fields from the Products table IF their Product ID has been passed from the form.

Press OK to close the Custom Query dialog and then click next to continue through the Database Results Wizard. In Step 4 select Table as your formatting option. Save the page as MultipleSelectNumberResults.asp

Using Multiple Select for Text Fields

Creating the same result with a text field is not dramatically more difficult, although it required me to post many messages on discussion forums and experimenting with numerous coding samples.

The good news is that I finally stumbled on the solution through experimentation - and the better news is that it requires NO coding at all.

There is only one small difference when we need to work with a text value as opposed to a number value. The difference lies in the way that the query data must be passed. For text values, each value must be enclosed in single quotes, eg. 'value 1','value2'

Do the Hard work in Microsoft Access

Start the process in Microsoft Access. Create a new query and add the Customers Table. Drag the CompanyName and CustomerID field to the query design window.

Now, in the third column heading type the following:

CustID: "'" & CustomerID & "'"

This will create a third column value called CustID that adds a single quote around the field name. Save the query as qCustomerMultipleSelect.

Now, in your database form you'll place a Database Results Wizard that will use the Northwind Database connection and in step 2, connect to the query that you've created.

In Step 3, remove all fields from the list with the exception of CompanyName and CustID. Sort the list by CompanyName. In Step 4 select Drop-down list with CompanyName as the displayed field and CusttID as the field to be submitted.

As an Alternative, Use a Custom Query

If you want to stick with FrontPage, then you can duplicate the Access query by creating a custom query in Step 2 of the Database Results Wizard.

To accomplish this, you connect to the Northwind Database and in Step 2 select Custom Query. In the edit window type the following:

SELECT CustomerID, "'" & [CustomerID] & "'" AS CustID, CompanyName FROM Customers;

Your Edit window should look like the following:

Essentially, this does the same thing as creating the query in Access. I prefer the Access approach because it is both easier and less prone to errors. Typing custom SQL code makes it easy to make a minor typing error that can take a great deal of time to decipher.

Your form should look like this one (feel free to give it a try)

In our next tutorial, we'll show you how to search multiple tables Click here to read it now

Many thanks to Bud from http://frontpage.kmoraine.com/ and Texas Web Developers from http://www.TexasWebDevelopers.com for their assistance in helping me create this tutorial