How to create a JavaScript Jump Menu using a Dropdown list generated from an Access Database

This article assumes that you have already imported your database into your web, and that FrontPage has created the fpdb folder, put the database in that folder, and created the global.asa file, the _fpclass folder, and the database connection. Please review How to create a FrontPage Database Connection for more information.

For this tutorial, we will use a fictitious database with one table, called “websites”, with two fields, website_name and website_url.

Create a new page and save it as dropdown.asp.

On the Insert menu, point to Form, and select Form.

Delete the Reset button.

Position your cursor within the form, to the left of the Submit button.

On the Insert menu, point to Database, and select websites.

In step 1 of the wizard, select your database connection. Click Next.

In step 2 of the wizard, select your record source. Click Next.

In step 3 of the wizard, select Edit List and remove all of the fields, except website_name, the field you want to show in the dropdown list and website_url, the URL you want the user to jump to (note: the URL must be in http://www.example.com format). Click Next.

In Step 4, choose Dropdown List – one record per item. Display values from the field you are displaying (website_name), Submit values from the field containing the URL (website_url) . Click Next. Click Finish.

Switch to Code View, find this statement:

<select NAME=" website_url " SIZE="1">

and add

id="setit"

so the statement looks like this:

<select NAME=" website_url " id="setit" SIZE="1">

While still in Code View, find this statement:

<input type="submit" value="Submit" name="B1">

Change the value from “Submit” to “Go” (this is the text that gets displayed on the button)

and add

onclick="window.open(setit.options[setit.selectedIndex].value)"

So the statement looks like this:

<input type="submit" value="Go" onclick="window.open(setit.options[setit.selectedIndex].value)" name="B1">

Save the page while you are still in Code View and preview in browser.

<%
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/fpdb/mydatabase.mdb")

FUNCTION dropDownMenu(tableName,dbFieldName,formFieldName,sortBy,selectItem)
ddSQL = "SELECT " & dbFieldName & " FROM " & tableName & " ORDER BY " & dbFieldName & " ASC"
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
set rstemp=conntemp.execute(ddSQL)
IF rstemp.eof THEN
response.write "No records matched so cannot make menu."
Call CloseAll
ELSE
menuArray=rstemp.getrows
Call CloseAll
Response.write("<select size='1' name='" & formFieldName & "'>" & VbCrLf)
FOR m = 0 TO ubound(menuArray,2)
Response.write("<option")
IF menuArray(0,m) = selectItem THEN
Response.write(" selected")
END IF
Response.write(">" & menuArray(0,m) & "</option>" & VbCrLf)
NEXT
Response.write("</select>" & VbCrLf)
END IF
END FUNCTION

SUB CloseAll
'rstemp.close
'set rstemp=nothing
'conntemp.close
'set conntemp=nothing
END SUB

%>





(substitute your database name in the "myDSN = blah,blah" line.)

Put that code at the top of the page in HTML view. Now with that code, you should be able to use it *inside* the
DRW calling it like so:

<%call
dropDownMenu("myTableWithDropDownValues","FieldIWantTheValuesToComeFrom","TheNameOfTheDropdown","TheFieldToSortBy
",FP_FieldVal(fp_rs,"TheDRWFieldValueIWantPreSelected"))%>

Substitute that line where you'd normally use just the value. This must all be done on a dieted DRW.