Drop Down

Chris J · Feb 15, 2004

Wow, I am stuck. I am having fun trying different things. I am trying to create a Database Querry that has drop down choices. These choices are from a given field in my access database. How can I get the drop down menu to draw the choices from the field. Also note that I may have the same value more then once in that field, so I only want the Valuse to show up as a choice once. Also, what about making them Alphabetical? Thanks, CHRIS

Kathleen Anderson [MVP - FP] · Feb 15, 2004

Chris:
See this KB article to start:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q235554&GSSNB=1

To get the value to show only once, modify the SQL to SELECT DISTINCT. To
get them to show up alphabetical, under More Options, choose Ordering and
Sort on that field.

ChrisJ · Feb 15, 2004

Thanks for the REPLY!!!! Ok, please look at what I did at did at www.chrisbaseballcards.com and follow the link to BLANK (Playing around with that page). I just followed the steps for a lastname querry. See the drop down that says Bagwell like 50 times? I am unsure where I should put the SELECT DISTINCT. Also, I just want it so it is a drop down and they select from names in database. Then I could set one up with make and year etc. I will attach the HTML, as this may help. thanks, CHRI

<html><head><meta http-equiv="Content-Type" content="text/html; charset=windows-1252"><title>Blank</title><meta name="GENERATOR" content="Microsoft FrontPage 4.0"><meta name="ProgId" content="FrontPage.Editor.Document"><meta name="Microsoft Theme" content="twc-frame100 011, default"><meta name="Microsoft Border" content="none, default"></head><body><form method="POST" action="--WEBBOT-SELF--"><
fp_sQry="SELECT * FROM Products
fp_sDefault="
fp_sNoRecords="No records returned.
fp_sDataConn="Database1
fp_iMaxRecords=25
fp_iCommandType=
fp_iPageSize=
fp_fTableFormat=Fals
fp_fMenuFormat=Tru
fp_sMenuChoice="Last_Name
fp_sMenuValue="Last_Name
fp_iDisplayCols=
fp_fCustomQuery=Fals
BOTID=
fp_iRegion=BOTI
%><select NAME="Last_Name" SIZE="1"><option><%=FP_FieldHTML(fp_rs,"Last_Name")%></option></select><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></form><form BOTID="1" METHOD="POST" ACTION="blank.asp"><table BORDER="0"><tr><td>Last_Name</td><td><input TYPE="TEXT" NAME="Last_Name" VALUE="<%=Request("Last_Name")%>" size="20"></td></tr></table> <input TYPE="Submit"><input TYPE="Reset"> </form><table width="100%" border="1"><thead><tr><td>Last_Name</td><td>First_Name</td><td>Year</td><td>Make</td><td>Card_Number</td><td>Insert_Name</td><td>Serial_Number</td><td>Quantity</td></tr></thead><tbody><%
fp_sQry="SELECT * FROM Products WHERE (Last_Name = '::Last_Name::')"
fp_sDefault="Last_Name=Ankiel"
fp_sNoRecords="<tr><td colspan=8 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=8
fp_fCustomQuery=False
BOTID=1
fp_iRegion=BOTID
%><tr><td><%=FP_FieldVal(fp_rs,"Last_Name")%></td><td><%=FP_FieldVal(fp_rs,"First_Name")%></td><td><%=FP_FieldVal(fp_rs,"Year")%></td><td><%=FP_FieldVal(fp_rs,"Make")%></td><td><%=FP_FieldVal(fp_rs,"Card_Number")%></td><td><%=FP_FieldVal(fp_rs,"Insert_Name")%></td><td><%=FP_FieldVal(fp_rs,"Serial_Number")%></td><td><%=FP_FieldVal(fp_rs,"Quantity")%></td></tr></tbody></table></body></html>

Kathleen Anderson [MVP - FP] · Feb 15, 2004

Chris:
Switch to HTML/Code view and find this in the gray-colored code:
s-sql="SELECT * FROM Products" and change it to
s-sql="SELECT DISTINCT Last_Name FROM Products ORDER BY Last_Name"

Save the change while you're still in HTML/Code view.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

ChrisJ said:
Thanks for the REPLY!!!! Ok, please look at what I did at did at
www.chrisbaseballcards.com and follow the link to BLANK (Playing
around with that page). I just followed the steps for a lastname
querry. See the drop down that says Bagwell like 50 times? I am
unsure where I should put the SELECT DISTINCT. Also, I just want it
so it is a drop down and they select from names in database. Then I
could set one up with make and year etc. I will attach the HTML, as
this may help. thanks, CHRIS

<html><head><meta http-equiv="Content-Type" content="text/html;
charset=windows-1252"><title>Blank</title><meta name="GENERATOR"
content="Microsoft FrontPage 4.0"><meta name="ProgId"
content="FrontPage.Editor.Document"><meta name="Microsoft Theme"
content="twc-frame100 011, default"><meta name="Microsoft Border"
content="none, default"></head><body><form method="POST"
action="--WEBBOT-SELF--"><%
fp_sQry="SELECT * FROM Products"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Last_Name"
fp_sMenuValue="Last_Name"
fp_iDisplayCols=8
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%><select NAME="Last_Name" SIZE="1"><option><%=FP_FieldHTML(fp_rs,"Last_Name")%></option></select><input type="submit"
value="Submit" name="B1"><input type="reset" value="Reset"
name="B2"></form><form BOTID="1" METHOD="POST"
ACTION="blank.asp"><table
BORDER="0"><tr><td>Last_Name</td><td><input TYPE="TEXT"
NAME="Last_Name" VALUE="<%=Request("Last_Name")%>"
size="20"></td></tr></table> <input TYPE="Submit"><input
TYPE="Reset"> </form><table
width="100%"

border="1"> said:
Year</td><td>Make</td><td>Card_Number</td><td>Inser

t_Name said:
bot="DatabaseRegionStart" startspan
s-columnnames="ID,Last_Name,First_Name,Year,Make,Card_Number,Insert_Name,Ser
ial_Number,Quantity"
s-columntypes="3,202,202,202,202,202,202,202,202"
s-dataconnection="Database1" b-tableformat="TRUE"
b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE"
b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE"
b-listseparator="TRUE" i-ListFormat="0" b-makeform="TRUE"
s-recordsource="Products"
s-displaycolumns="Last_Name,First_Name,Year,Make,Card_Number,Insert_Name,Ser
ial_Number,Quantity"
s-criteria="[Last_Name] EQ {Last_Name} +" s-order s-sql="SELECT *
FROM Products WHERE (Last_Name = '::Last_Name::')"
b-procedure="FALSE" clientside SuggestedExt="asp"
s-DefaultFields="Last_Name=Ankiel" s-NoRecordsFound="No records
returned." i-MaxRecords="256" i-GroupSize="0" BOTID="1"
u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc"
u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY"
local_preview="<tr><td colspan=64
bgcolor="#FFFF00" align="left"
width="100%">Database Results regions will not
preview unless this page is fetched from a Web server with a web
browser. The following table row will repeat once for every record
returned by the query.</td></tr>"
preview="<tr><td colspan=64 bgcolor="#FFFF00"
align="left" width="100%">This is the start of a Database Results
region. The page must be fetched from a web server with a web browser
to display correctly; the current web is stored on your local disk or
network.</td></tr>" --><%
fp_sQry="SELECT * FROM Products WHERE (Last_Name = '::Last_Name::')"
fp_sDefault="Last_Name=Ankiel"
fp_sNoRecords="<tr><td colspan=8 align=left width=""100%"">No records
returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=8
fp_fCustomQuery=False
BOTID=1
fp_iRegion=BOTID
%><tr><td><%=FP_FieldVal(fp_rs,"Last_Name")%></td><td><%=FP_FieldVal(fp_rs,"First_Name")%></td><td><%=FP_FieldVal(fp_rs,"Year")%></td><td><%=FP_FieldVal(fp_rs,"Make")%></td><td><%=FP_FieldVal(fp_rs,"Card_Number")%></td><td><%=FP_FieldVal(fp_rs,"Insert_Name")%></td><td><%=FP_FieldVal(fp_rs,"Serial_Number")%></td><td><%=FP_FieldVal(fp_rs,"Quantity")%></td></tr></tbody></table></body></html>

ChrisJ · Feb 15, 2004

Ma'am, did what you said and it saved well. However, I get an error now. Please see the site, looks way off. Also, If I want to make a search field for Insert Name, Card # etc. I will have to do the smae in the SQL section for each search (ASSUMING I figure this out). Once again, I dont want that blank space to fill in. It should just be the drop down Menu based off of entries in the database. Please note still shows Bagwell like 50 times. Maybe I did it incorrectly. Let me know, thanks, CHRI

www.chrisbaseballcards.com Follow the "Blank" Lin

Code

<html><head><meta http-equiv="Content-Type" content="text/html; charset=windows-1252"><title>Blank</title><meta name="GENERATOR" content="Microsoft FrontPage 4.0"><meta name="ProgId" content="FrontPage.Editor.Document"><meta name="Microsoft Theme" content="twc-frame100 011, default"><meta name="Microsoft Border" content="none, default"></head><body><form method="POST" action="--WEBBOT-SELF--"><input TYPE="hidden" NAME="VTI-GROUP" VALUE="0"><
fp_sQry="SELECT * FROM Products
fp_sDefault="
fp_sNoRecords="No records returned.
fp_sDataConn="Database1
fp_iMaxRecords=25
fp_iCommandType=
fp_iPageSize=
fp_fTableFormat=Fals
fp_fMenuFormat=Tru
fp_sMenuChoice="Last_Name
fp_sMenuValue="Last_Name
fp_iDisplayCols=
fp_fCustomQuery=Fals
BOTID=
fp_iRegion=BOTI
%><select NAME="Last_Name" SIZE="1"><option><%=FP_FieldHTML(fp_rs,"Last_Name")%></option></select><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></form><form BOTID="1" METHOD="POST" ACTION="blank.asp"><table BORDER="0"><tr><td>Last_Name</td><td><input TYPE="TEXT" NAME="Last_Name" VALUE="<%=Request("Last_Name")%>" size="20"></td></tr></table> <input TYPE="Submit"><input TYPE="Reset"> </form><table width="100%" border="1"><thead><tr><td>Last_Name</td><td>First_Name</td><td>Year</td><td>Make</td><td>Card_Number</td><td>Insert_Name</td><td>Serial_Number</td><td>Quantity</td></tr></thead><tbody><%
fp_sQry="SELECT DISTINCT Last_Name FROM Products ORDER BY Last_Name"
fp_sDefault="Last_Name=Ankiel"
fp_sNoRecords="<tr><td colspan=8 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=8
fp_fCustomQuery=False
BOTID=1
fp_iRegion=BOTID
%><tr><td><%=FP_FieldVal(fp_rs,"Last_Name")%></td><td><%=FP_FieldVal(fp_rs,"First_Name")%></td><td><%=FP_FieldVal(fp_rs,"Year")%></td><td><%=FP_FieldVal(fp_rs,"Make")%></td><td><%=FP_FieldVal(fp_rs,"Card_Number")%></td><td><%=FP_FieldVal(fp_rs,"Insert_Name")%></td><td><%=FP_FieldVal(fp_rs,"Serial_Number")%></td><td><%=FP_FieldVal(fp_rs,"Quantity")%></td></tr></tbody></table></body></html>

Kathleen Anderson [MVP - FP] · Feb 15, 2004

Chris:
The only Database Region that should be on that page is the one containing
the Dropdown. Once someone makes a selection, it should pass off to (post
to) another page containing the results:
http://www.spiderwebwoman.com/resources/dbrwtipsandtricks.asp#return

Also, because you have more than 1 Database region on the page, I think you
changed the wrong one.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

ChrisJ said:
Ma'am, did what you said and it saved well. However, I get an error
now. Please see the site, looks way off. Also, If I want to make a
search field for Insert Name, Card # etc. I will have to do the smae
in the SQL section for each search (ASSUMING I figure this out).
Once again, I dont want that blank space to fill in. It should just
be the drop down Menu based off of entries in the database. Please
note still shows Bagwell like 50 times. Maybe I did it incorrectly.
Let me know, thanks, CHRIS

www.chrisbaseballcards.com Follow the "Blank" Link

Code:

<html><head><meta http-equiv="Content-Type" content="text/html;
charset=windows-1252"><title>Blank</title><meta name="GENERATOR"
content="Microsoft FrontPage 4.0"><meta name="ProgId"
content="FrontPage.Editor.Document"><meta name="Microsoft Theme"
content="twc-frame100 011, default"><meta name="Microsoft Border"
content="none, default"></head><body><form method="POST"
action="--WEBBOT-SELF--"><input TYPE="hidden" NAME="VTI-GROUP"
VALUE="0"><%
fp_sQry="SELECT * FROM Products"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Last_Name"
fp_sMenuValue="Last_Name"
fp_iDisplayCols=8
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%><select NAME="Last_Name" SIZE="1"><option><%=FP_FieldHTML(fp_rs,"Last_Name")%></option></select><input type="submit"
value="Submit" name="B1"><input type="reset" value="Reset"
name="B2"></form><form BOTID="1" METHOD="POST"
ACTION="blank.asp"><table
BORDER="0"><tr><td>Last_Name</td><td><input TYPE="TEXT"
NAME="Last_Name" VALUE="<%=Request("Last_Name")%>"
size="20"></td></tr></table> <input TYPE="Submit"><input
TYPE="Reset"> </form><table
width="100%"

border="1"> said:
Year</td><td>Make</td><td>Card_Number</td><td>Inser

t_Name said:
bot="DatabaseRegionStart" startspan
s-columnnames="ID,Last_Name,First_Name,Year,Make,Card_Number,Insert_Name,Ser
ial_Number,Quantity"
s-columntypes="3,202,202,202,202,202,202,202,202"
s-dataconnection="Database1" b-tableformat="TRUE"
b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE"
b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE"
b-listseparator="TRUE" i-ListFormat="0" b-makeform="TRUE"
s-recordsource="Products"
s-displaycolumns="Last_Name,First_Name,Year,Make,Card_Number,Insert_Name,Ser
ial_Number,Quantity"
s-criteria="[Last_Name] EQ {Last_Name} +" s-order s-sql="SELECT
DISTINCT Last_Name FROM Products ORDER BY Last_Name"
b-procedure="FALSE" clientside SuggestedExt="asp"
s-DefaultFields="Last_Name=Ankiel" s-NoRecordsFound="No records
returned." i-MaxRecords="256" i-GroupSize="0" BOTID="1"
u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc"
u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY"
local_preview="<tr><td colspan=64
bgcolor="#FFFF00" align="left"
width="100%">Database Results regions will not
preview unless this page is fetched from a Web server with a web
browser. The following table row will repeat once for every record
returned by the query.</td></tr>"
preview="<tr><td colspan=64 bgcolor="#FFFF00"
align="left" width="100%">This is the start of a Database Results
region. The page must be fetched from a web server with a web browser
to display correctly; the current web is stored on your local disk or
network.</td></tr>" --><%
fp_sQry="SELECT DISTINCT Last_Name FROM Products ORDER BY Last_Name"
fp_sDefault="Last_Name=Ankiel"
fp_sNoRecords="<tr><td colspan=8 align=left width=""100%"">No records
returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=8
fp_fCustomQuery=False
BOTID=1
fp_iRegion=BOTID
%><tr><td><%=FP_FieldVal(fp_rs,"Last_Name")%></td><td><%=FP_FieldVal(fp_rs,"First_Name")%></td><td><%=FP_FieldVal(fp_rs,"Year")%></td><td><%=FP_FieldVal(fp_rs,"Make")%></td><td><%=FP_FieldVal(fp_rs,"Card_Number")%></td><td><%=FP_FieldVal(fp_rs,"Insert_Name")%></td><td><%=FP_FieldVal(fp_rs,"Serial_Number")%></td><td><%=FP_FieldVal(fp_rs,"Quantity")%></td></tr></tbody></table></body></html>

ChrisJ · Feb 16, 2004

Thanks to you, I AM LEARNING!!! Thank you, got it working with last names. Now if I want to add another drop down below my first one (say by Make), can I make it use the same reults area? Just add a search field (drop down menu). Also, if someone wants to just look at everything in the database, is there a way to do that? Thanks again! you have been a great help!!!! Chris

Kathleen Anderson [MVP - FP] · Feb 16, 2004

Chris:
You are doing GREAT!!

I have written a tutorial on how to how to filter a database query using two
drop down lists: http://www.spiderwebwoman.com/tutorials/doubledropdown.htm
Give it a shot

ChrisJ · Feb 16, 2004

Ah, that is great, but I just want seperate ones, so a person could search by year OR Player Or Make or Inset name (all of which get their drop down choices from the database) and use the same reults area. so they will only be able to choose one field, but that is still pretty nice! Please let me know. your link is sweet and informative, but not what I am trying to do. i just want several fields they can shoose from (one of them) and go! An also I would like them to be able to look at all cards to it would be a Check box saying see all cards (broken into say 1000 per page). Is this possible? So 2 questions really. LOL, Thanks, CHRIS

Kathleen Anderson [MVP - FP] · Feb 16, 2004

Chris:
I went back to your site and found that you've already done it. Good job!

To get a list of all the cards, you would insert another Database Results
Region with no selection criteria, and in the last step of the wizard,
define the number of records you want shown per page.

ChrisJ · Feb 18, 2004

Ill try it, thanks for all the help!!!!

How to make repeating drop-down lists?	2	Jul 3, 2022
Link a text box based on drop down box with four options MS Word 365	0	Aug 24, 2021
Font drop-down menu very slow	0	Dec 8, 2022
MS Project Center-Autolink column fields	0	Jul 6, 2022
Drop down list control value in vba	2	Mar 8, 2022
ASK Field Formatting	1	Jan 7, 2022
Can't save my Office documents (after computer rebuild and reauthorization)	0	Jun 10, 2022
Using a drop-down box to sort data based on seperate columns	2	Jul 5, 2013

Drop Down

Chris J

Kathleen Anderson [MVP - FP]

ChrisJ

Kathleen Anderson [MVP - FP]

ChrisJ

Kathleen Anderson [MVP - FP]

ChrisJ

Kathleen Anderson [MVP - FP]

ChrisJ

Kathleen Anderson [MVP - FP]

ChrisJ

Ask a Question

Similar Threads