Solved

Display multiple fields in a drop down menu

Posted on 2014-11-07
14
105 Views
Last Modified: 2014-11-07
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
Comment
Question by:Malloy1446
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
14 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40429549
Response.Write VISNID&" -- "& city &", "&State& "</option>"

Open in new window

0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40429553
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
 

Author Comment

by:Malloy1446
ID: 40429564
Using the suggested line does not work. NO list is generated.
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:Malloy1446
ID: 40429568
Yes. The value to be submitted should be City.
0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
ID: 40429576
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40429580
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40429589
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
 

Author Comment

by:Malloy1446
ID: 40429590
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40429594
Thanks but can you give me the rendered html.

Run the page, view source, then copy paste.
0
 

Author Comment

by:Malloy1446
ID: 40429598
THis is the html generated from my original code.
Code3.txt
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40429601
Where is the output generated from what I gave you?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40429609
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
 

Author Comment

by:Malloy1446
ID: 40429612
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40429619
Rendering the html turns on a lot of light bulbs.

Glad it's working.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Powershell help for creating accounts 283 49
Change this SQL to get all nodes 3 38
compare date to getdate() 8 16
Need multiple Group By's 8 24
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question