• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

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
0
Malloy1446
Asked:
Malloy1446
  • 9
  • 5
1 Solution
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Response.Write VISNID&" -- "& city &", "&State& "</option>"

Open in new window

0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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

0
 
Malloy1446Author Commented:
Using the suggested line does not work. NO list is generated.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Malloy1446Author Commented:
Yes. The value to be submitted should be City.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Will you please view the source, copy all of the generated html code and paste it back here.  Also, are you sure you are getting a good recordset?
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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

0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
0
 
Malloy1446Author Commented:
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
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Thanks but can you give me the rendered html.

Run the page, view source, then copy paste.
0
 
Malloy1446Author Commented:
THis is the html generated from my original code.
Code3.txt
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Where is the output generated from what I gave you?
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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

0
 
Malloy1446Author Commented:
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!
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Rendering the html turns on a lot of light bulbs.

Glad it's working.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now