Access 2007 Form problem

[Brian McLaughlin]

Hi.
Probably a simple problem but i'm pulling my hair out!
I have 3 tables(Consulta nt name, hospital and medical department).
Consultant name is joined to the other two by their primary key. I want
to create a form with 3 drop down menus.
I've managed to do the first two (choose hospital first, then medical
department) but I want the third drop down menu(consultant name) to
refer to the choices made in the first two drop down boxes and limit the
names to only those sharing the same department and hospital. Can anyone
help me please?

*** Sent via Developersdex http://www.developersdex.com ***

[Salad]

Re: Access 2007 Form problem

Brian McLaughlin wrote:

Hi.
Probably a simple problem but i'm pulling my hair out!
I have 3 tables(Consulta nt name, hospital and medical department).
Consultant name is joined to the other two by their primary key. I want
to create a form with 3 drop down menus.
I've managed to do the first two (choose hospital first, then medical
department) but I want the third drop down menu(consultant name) to
refer to the choices made in the first two drop down boxes and limit the
names to only those sharing the same department and hospital. Can anyone
help me please?
>

Typically your first combo is the "master" combo. When you select
Hospitals, it then would requery the departments combo that exist for
that hospital (which, if there's no default, would be null) and since
there's no Dept selected, the consultant would be null. When you update
the Dept combo, the Consultant combo is requeried. Ex:
ComboHospital's AfterUpdate event
Me.ComboDept.Re query
Me.combodept = Null
Me.ComboConsult .Requery
Me.ComboConsult = Null

ComboDept's AfterUpdate event
Me.ComboConsult .Requery
Me.ComboConsult = Null

I will assume that the filter for the ComboConsult might be something like
Where [Hospital] = Me.ComboHospita l And Dept = Me.ComboDept
or something like that.

Une americaine a Paris

http://www.youtube.com/watch?v=IkpuAQIdoD4