Combo Box Query??

[Guest]

Hi,

Im having some trouble & wondering if anybody out there has any suggestions
on how to do this.....

I have a drop-down box in my web application that is populated with Hospital
Names from my Access 2000 database, I want to have another drop down that
displays the Departments in each hospital depending on the Hospital chosen in
the first drop down box...

Ive made two tables in my Access Database
Table1 is called Hospitals and contains fields Hospital and HospitalCode
Table2 is called HospitalDepartments and contains fields called HospitalCode
and Department.

I cant make HospitalCode a Primary Key in the HospitalDepartments table
because HospitalCode is repeated thus there are duplicates and it won't let
me call it the Primary Key.....

So does anybody have any suggestions.... all greatly appreciated!! : )

 

[Michel Walsh]

Hi,


In you form, in the second combo box event "GotFocus", add code that may
looks like:



Dim sql As string
sql="SELECT DISTINCT Department FROM HospitalDepartments"
If 0<>len(Me.FirstControl & vbNullString) Then
sql=sql & " WHERE Department=""" & Me.FirstControl & """"
End If

If Me.RowSource <> sql then
Me.RowSource=sql
End if



where FirstControl is to be replaced by the name of your first combo box
control. I also assume Department is a text field (by opposition to be
numerical).

A sql statement is produced, in text, with the required WHERE clause to
limit the records, if appropriate. That sql-text is supplied for the
RowSource of the actual second combo box, which requeries the combo box
list.




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks Michel, i tried your code, no errors... but nothing is appearing in
the second combo box i.e. those departments that are supposed to be related
to the chosen Hospital in the first combo box...also can I use the DISTINCT
functionality if some Hospitals contain the same Department Names?????

Any othe suggestions

 

[Michel Walsh]

Hi,



Note that if it is the hospital, not the department, that supplies the first
combo box, then it should be:

sql=sql & " WHERE Hospital=""" & Me.FirstControl & """"

rather than

sql=sql & " WHERE Department=""" & Me.FirstControl & """"



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Changed Department to Hospital as you suggested but no change.... the
following is a snipet of my code Combo Box 1 fills up with the Hospital Names
from my Database but the second combo box didnt take in any DEPARTMENT names
it reads in all department names but won't read in just the associated
hospital departments.


<TR>
<TD>
<P align=center><STRONG>Hospital</STRONG></P></TD>
<TD>
<SELECT NAME="hospitalCombo" SIZE="1" style=" FONT-WEIGHT: bold; " >

<%
Session("DatabasePath") = Server.Mappath("complete.mdb")
Set DataConnection = Server.CreateObject("ADODB.Connection")

DataConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Session("DatabasePath") & ";"

'Initialise the strSQL variable with an SQL statement to query the database


sql ="select * from Hospital"

Set recordSet = DataConnection.Execute(sql)


Response.Write("<OPTION VALUE="""" SELECTED></OPTION>")
while not recordSet.EOF
Response.Write("<OPTION ")
Response.Write("VALUE=""")
Response.Write(recordSet("Hospital"))
Response.Write(""">")
Response.Write(recordSet("Hospital"))
Response.Write(" ")
Response.Write("</OPTION>")
recordSet.MoveNext
wend
recordSet.close

DataConnection.Close
%>


</SELECT>
</TD></TR>

<TR>
<TD>
<P align=center><STRONG>Department</STRONG></P></TD>
<TD>
<SELECT NAME="DepartmentCombo" SIZE="1" style=" FONT-WEIGHT: bold; " >
<%

Session("DatabasePath") = Server.Mappath("complete.mdb")
Set DataConnection2 = Server.CreateObject("ADODB.Connection")

DataConnection2.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Session("DatabasePath") & ";"

'Initialise the strSQL variable with an SQL statement to query the database


sql ="select DISTINCT Department from HospitalDepartments"
If 0<>len(Me.hospitalCombo & vbNullString)Then sql=sql & " WHERE
Hospital=""" & Me.hospitalCombo & """" End If
If Me.RowSource <> sql then Me.RowSource=sql End if



Set recordSet2 = DataConnection2.Execute(sql)

Response.Write("<OPTION VALUE="""" SELECTED></OPTION>")
while not recordSet2.EOF
Response.Write("<OPTION ")
Response.Write("VALUE=""")
Response.Write(recordSet2("Department"))
Response.Write(""">")
Response.Write(recordSet2("Department"))
Response.Write(" ")
Response.Write("</OPTION>")
recordSet2.MoveNext
wend
recordSet2.close

DataConnection2.Close
%>

Hope this may shed some more light on the issue!! ; )

 

[Michel Walsh]

Hi,


...oh oh... I was somehow assuming you were using an Access' FORM. I
am not sure I can help within HTML context (or ASP, or anything similar)..


Vanderghast, Access MVP