Importing data based on drop down box selection??

[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!! : )

 

[Guest]

The primary key in your department table should be Hospital Code and
Department Code (If you don't have a department code, add it.) Then, I would
suggest that rather than two combo boxes, you have one multi column combo box
that has both hospital and department. You could use a query that joins the
two tables as the row source. This would be one less action the user would
have to take and simplify your coding.

 

[Guest]

Thanks for your suggestion Klatuu, you seem to know what your talking
about..I on the other hand am a bit behind... I have a Table called
HospitalDepartments and it contains fields called HospitalCode and
Department...you suggest making "The primary key in my department table
should be Hospital Code and Department Code (If you don't have a department
code, add it.) " ...So do you mean another column other than that of the two
I mentioned above??? Also how do you make two fields a primary key that exist
in the same table???

I also like your thoughts on one multi column combo box that has both
hospital and department....but again I'd be at a loss on how to create it and
the associated query.

If you have the time I'd be interested in knowing how it can be done...
Thanxs in advanced ; )

 

[Guest]

AQ,
You would not need another colum in your department table if the department
is a code, if it is descriptive text, then I would suggest a short code that
would be easier to use than a plain text description.
To set up a multi column primary key:
Open your table in design view.
Open the Indexes dialog box (the icon with the lightning bolt)
In the Index Name Column type "Primary"
In the Field Name Column type the name of your hospital code field
Under INdex Properties, Select Yes for Primary
Unique will then default to Yes and Ingnore Nulls to No - leave them there
In the Field Name Column in the row below the hospital code field name
type the name of the department field
You will notice the two little key symbols to the left of the fields you
have identified. Your primary key is set up.

Now, with that said, it is not necessary that the tables use the primary or
any other key. If you want to show the hospital name and department name,
but not necessarily the codes, you will need to build a query that has both
tables. Your left table should be Hospital and your right table should be
Departments. Join them together on Hospital code.

To set up the combo box, use the combo box wizard. It will walk you through
what you need to do. You will want the Hospital description and department
(description?)
and maybe both the hospital and department codes depending on what you will
need your combo box to return. When you get to the part where it asks to set
the column widths, you can make the columns you don't want to see invisible
by sliding the column separator until it disappears. It will still be there,
it just wont show in the drop down.
Your bound column will be what will be returned when you look at the control
vaue:
Me.cboHospDept
That will also be the value used to update your database if it is a bound
If you want to use other values later in your code, you can address it by
column number.
Me.cboHospDept.Column(0) will return the first column of the selected row
Me.cboHospDept.Coumn(1) will return the second column... etc.
Assuming you are using Option Base 0