Solved

Display multiple fields in a drop down menu

Posted on 2014-11-07
14
100 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
  • 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now