Combo Drop Down Menu

[natalieo]

Hi all ,

I am new here and my question may have been asked before . But I need help and advice .

I would like to know how get my drop down menu information that is pulling from an access database table to populate two fields which are join .

E.g I Have two table one name Model and one name Cat.

In my cat table i have the following fields CATID. MODELID, CAT

And in my model Table I have ModelID Model and CATID .

I want to be able to create a from where once I select a Model the corresponded CATID will automatically selected.

I hope this makes sence and someone out here can help a girl in a little pitfall.

Thanks in Advance

Natalie

[Frank]

What version of ASP-db are you using? Classic (ASP) or Tornado (Dotnet)?

Fk

[natalieo]

The classic asp

[Frank]

Seems like ModelID is the key to joint the tables. So, you'll hvae two asp files. The first one display the dbSelectbox of the ModelID. The second asp file takes the value selected from the dirst asp file and set a JOINed query via WHERE ModelID=xxx to display the grid containing the preferred fields of both tables. You can also try to use the template to fit the selectbox and grid on one screen.

Consider an upgrade to the DotNet version of ASP-db as there are many pre-programmed setup like SelectBoxForm which does this in one shot.

Frank

[Frank]

Looks like you are talking about drill down...the following example is a good and advanced one and should cover your case. If not then explain in more detail your application.

Frank

<h3><CENTER>Drill-Down Demo<BR>
<center><h3>Click on an Employee to see who they report to.<BR>
Click on an Order Number to see the Order Details. This is a five levels drill and response time is slow. The right way to do this is to use the "DynaList". </h3></center><p>
<CENTER><TABLE border=0><TR>

<TD> </TD><TD>
<%'
Set X=Server.CreateObject("ASP.db")
X.dbQuickProps = "10271;NWIND;Employees;Grid;;;;;100"
X.dbNavigationItem="none"
X.dbstatusbar=false
X.dbSQL="SELECT employeeID, FirstName, LastName, ReportsTo FROM employees"
X.dbSelectBox="5,<B>Employee (ID) Name</B>, Select Employee;(#employeeID#) #FirstName# #LastName#"
X.dbGridTableTag = "Border=1"
X.dbExportFlds="0,1,2,3"
X.ASPdb
%>
</TD>
<TH>

<%
if Session("ASPDB_10271_ReportsTo") <> "" then
Set X=Server.CreateObject("ASP.db")
X.dbQuickProps = "10272;NWIND;Employees;Form"
X.dbSQL="SELECT FirstName, LastName FROM employees where employeeID=" & session("ASPDB_10271_ReportsTo")
'X.dbOptions="Display=false"
X.dbFormDisplayFlds = "-1"
X.dbStatusBar=false
X.dbNavigation="none"
X.dbExportFlds="0,1"
X.ASPdb
ThisPerson = Session("ASPDB_10271_FirstName") & "&nbsp;" & Session("ASPDB_10271_LastName")
ReportsTo = Session("ASPDB_10272_FirstName") & "&nbsp;" & session("ASPDB_10272_LastName")
response.write(ThisPerson & " reports to: " & ReportsTo)
end if
%>

</TH></TR><TR><TD colspan=4><HR></TD></TR><TR valign=top><TD>

<%
Set X=Server.CreateObject("ASP.db")
X.dbDSN = "NWIND"
X.dbUnit=10273
X.dbMode="grid"
X.dbSQL="SELECT employeeID, OrderID FROM Orders where employeeID=" & session("ASPDB_10271_employeeID")
X.dbSelectBox="5,<B>Orders</B>; #OrderID#"
X.dbGridInc=999999
X.dbGridTableTag="Border=1"
X.dbNavigationItem="none"
X.dbExportFlds="OrderID"
X.dbstatusbar=false
X.dbFormTableTag = "Border=1"
X.ASPdb
%>
</TD>

<TD>
<%
Set X=Server.CreateObject("ASP.db")
s = zHead & ",<CENTER><b>Order Details</b></CENTER><BR>; "
'X.dbUserLocalText=s
X.dbDSN = "NWIND"
X.dbDBType="SQL"
X.dbUnit=10274
X.dbMode="form"
X.dbSQL="SELECT * FROM [Order details] where orderID=" & session("ASPDB_10273_orderID")
X.dbNavigationItem="none"
X.dbstatusbar=false
X.dbExportFlds="ProductID"
X.dbFormTableTag = "Border=1"
X.ASPdb
%>
</TD>

<TD>
<%
Set X=Server.CreateObject("ASP.db")
s = zHead & ",<CENTER><b>Product Details</b></CENTER><BR>; "
'X.dbUserLocalText=s
X.dbDSN = "NWIND"
X.dbDBType="SQL"
X.dbUnit=10275
X.dbMode="form"
X.dbSQL="SELECT * FROM Products where productID=" & session("ASPDB_10274_productID")
X.dbNavigationItem="none"
X.dbstatusbar=false
X.dbExportFlds="SupplierID"
X.dbFormTableTag = "Border=1"
X.ASPdb
%>
</TD>

<TD>
<%
Set X=Server.CreateObject("ASP.db")
s = zHead & ",<CENTER><b>Supplier Details</b></CENTER><BR>; "
'X.dbUserLocalText=s
X.dbDSN = "NWIND"
X.dbDBType="SQL"
X.dbUnit=10276
X.dbMode="form"
X.dbSQL="SELECT * FROM Suppliers where supplierID=" & session("ASPDB_10275_supplierID")
X.dbNavigationItem="none"
X.dbstatusbar=false
X.dbFormTableTag = "Border=1"
X.ASPdb
%>
</TD>
</TR></TABLE>

[Frank]

The following is a simple drill down example ->

<%
Response.write("form=" & request.form("aspDBGridSelectBoxItem_1"))
Set x = Server.CreateObject("ASP.db")
SQL="SELECT OrderID FROM ORDERS ORDER BY OrderID"
with x
.dbUnit = 1
.dbmdb = "NWIND.mdb"
.dbSQL = SQL
.dbMode = "grid"
.dbGridInc = 99999
.dbStatusBar="false"
.dbSelectBox = "1,Order ID,Select"
.aspdb
end with
%>

<%
reqf = request.form("aspDBGridSelectBoxItem_1")
if reqf <> "" then
arr = Split(reqf,"::")
arr(0) = Trim(arr(0))
response.write("<BR>reqf=" & arr(0))
Set y=Server.CreateObject("ASP.db")
SQL="SELECT * FROM [ORDER DETAILS] WHERE ORDERID = " & arr(0)

with y
.dbDSN = "NWIND"
.dbUnit=99
.dbMode="grid"
.dbSQL=SQL
.dbGridInc= 999999
.dbGridTableTag="Border=1"
.dbNavigationItem="none"
.dbstatusbar=false
.ASPdb
end with
end if
%>