asked on

Display multiple fields in a drop down menu

I have an MS Access database. I need to create a drop down list generated from one table.
The fields I need to use are VISNID, City and State. Drop down list is sorted by VISNID then by city. I have the form working correctly with just the city. I can't seem to get the right syntax to include the VISNID (number) and State for the drop down options.

I want the drop down to display
VISN [VISNID field] -- [City field], [State field]
Code.txt

Scott Fell

Response.Write VISNID&" -- "& city &", "&State& "</option>"

Open in new window

Scott Fell

This is the entire code. Did you mean for the value to be the city?

<%
		Dim City, VISNID, State

		Response.Write ("<select name = 'city'>")
		Response.Write ("<option value=''>-- All --</option>")
			Set objRS = Server.CreateObject("ADODB.Recordset")


		sql = "SELECT DISTINCT City, FacilityStatus, VISNID FROM tblGeneral " & _ 
			  "WHERE tblGeneral.FacilityStatus= 'Open' "


				If Request.querystring("sort") = "" then
					sql = sql & "ORDER BY VISNID, City"
				Else
		  			sql = sql & "ORDER BY " & Request.querystring("sort")
		  		End If			
				
			objRS.Open sql, objConn

		While Not objRS.EOF
			city = objRS("City")
			VISNID = objRS("VISNID")
			State = objRS("State")
			Response.Write "<option value = '" & city & "'>"
                       ' Response.Write "VISNID"& VISNID&" -- "& city &", "&State& "</option>"
			Response.Write VISNID&" -- "& city &", "&State& "</option>"
			objRS.MoveNext
		Wend
		Response.Write ("</select>")
		objRS.Close
		%>

Open in new window

Malloy1446

ASKER

Using the suggested line does not work. NO list is generated.

Malloy1446

ASKER

Yes. The value to be submitted should be City.

ASKER CERTIFIED SOLUTION

Scott Fell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Fell

I added one line as an option that is commented out

 ' Response.Write "VISNID"& VISNID&" -- "& city &", "&State& "</option>"

Open in new window

Try just

Dim City, VISNID, State

		Response.Write ("<select name = 'city'>")
		Response.Write ("<option value=''>-- All --</option>")
			Set objRS = Server.CreateObject("ADODB.Recordset")


		sql = "SELECT DISTINCT City, FacilityStatus, VISNID FROM tblGeneral " & _ 
			  "WHERE tblGeneral.FacilityStatus= 'Open' "


				If Request.querystring("sort") = "" then
					sql = sql & "ORDER BY VISNID, City"
				Else
		  			sql = sql & "ORDER BY " & Request.querystring("sort")
		  		End If			
				
			objRS.Open sql, objConn

		While Not objRS.EOF
			city = objRS("City")
			VISNID = objRS("VISNID")
			State = objRS("State")
			Response.Write "<option value = '" & city & "'>"
             
			Response.Write VISNID&" -- "& city &", "&State& "</option>"
			objRS.MoveNext
		Wend
		Response.Write ("</select>")
		objRS.Close

Open in new window

Scott Fell

Let's try this too.

<%
		Dim City, VISNID, State

		Response.Write ("<select name = 'city'>")
		Response.Write ("<option value=''>-- All --</option>")
			Set objRS = Server.CreateObject("ADODB.Recordset")


		sql = "SELECT DISTINCT City, FacilityStatus, VISNID FROM tblGeneral " & _ 
			  "WHERE tblGeneral.FacilityStatus= 'Open' "


				If Request.querystring("sort") = "" then
					sql = sql & "ORDER BY VISNID, City"
				Else
		  			sql = sql & "ORDER BY " & Request.querystring("sort")
		  		End If			
				
			objRS.Open sql, objConn


		if not objRS.bof or not objRS.eof then
			do until objRS.EOF
				city = objRS("City")
				VISNID = objRS("VISNID")
				State = objRS("State")
				Response.Write "<option value = '" & city & "'>"
	             
				Response.Write VISNID&" -- "& city &", "&State& "</option>"
				objRS.MoveNext
			loop
			objRS.movenext
			Else
			response.write "<option value='0'>No Data</option>"
		end if
		Response.Write ("</select>")
		objRS.Close
		%>

Open in new window

Note, I changed from while/wend

Malloy1446

ASKER

recordset is generating a good drop down list with my original code. Only when I try to add the VISNID and State data do I have a problem.
Code2.txt

Scott Fell

Thanks but can you give me the rendered html.

Run the page, view source, then copy paste.

Malloy1446

ASKER

THis is the html generated from my original code.
Code3.txt

Scott Fell

Where is the output generated from what I gave you?

Scott Fell

Sorry, error in my last code with looping

<%
		Dim City, VISNID, State

		Response.Write ("<select name = 'city'>")
		Response.Write ("<option value=''>-- All --</option>")
			Set objRS = Server.CreateObject("ADODB.Recordset")


		sql = "SELECT DISTINCT City, FacilityStatus, VISNID FROM tblGeneral " & _ 
			  "WHERE tblGeneral.FacilityStatus= 'Open' "


				If Request.querystring("sort") = "" then
					sql = sql & "ORDER BY VISNID, City"
				Else
		  			sql = sql & "ORDER BY " & Request.querystring("sort")
		  		End If			
				
			objRS.Open sql, objConn


		if not objRS.bof or not objRS.eof then
			do until objRS.EOF
				city = objRS("City")
				VISNID = objRS("VISNID")
				State = objRS("State")
				Response.Write "<option value = '" & city & "'>"
	             
				Response.Write VISNID&" -- "& city &", "&State& "</option>"
			objRS.movenext
			loop
			
			Else
			response.write "<option value='0'>No Data</option>"
		end if
		Response.Write ("</select>")
		objRS.Close
		%>

Open in new window

Malloy1446

ASKER

Light bulb came on when I posted the HTLM. I must of typed your original suggestion incorrectly. the line that worked is

Response.Write "VISN " & VISNID & " -- " & city & ", " & State & "</option>"

Thank you!

Scott Fell

Rendering the html turns on a lot of light bulbs.

Glad it's working.