I am trying to figure out the best way to design a table and form to achieve a result.
I have 3 fields which I need users to enter:
[Facility type] - one of 5 options: hospital, racf, community service, GP and Other.
I have a set list for the first 3 options which I want to restrict users to selecting. The last 2 options a user can enter anything but I want to store what is entered for a possible future match.
The 2nd field is [Facility name]. As mentioned above this is either a set list which must be selected or can be entered for future retrieval depending on first field.
The 3rd field is [doctor name]. This is only required if the facility type is GP and similarly a user can enter anything but should be provided with previously entered options linked to the facility name (2nd field) ie set doctors work at a practice but there can be more than 1.
The 3rd field was only just added and is where I am struggling. My current setup for the first 2 fields is:
I have 2 tables - a reference table with the 5 different facility types (tblFacilityTypes) and another (tblFacilities) with all the data (fields include ID, FacilityType, FacilityName, DoctorName and Suburb)
[Facility type] is a combo box with a form-level lookup of the reference table.
With the OnChange event of this combo box it sets the row source of the second combo box to the tblFacilities matching the facility type of the first combo. This second combo has a NotInList event which either prevents entry of the facility type if it is one of the first 3 or prompts the user to create a new entry if it is one of the latter facility types and then inserts that detail into the tblFacilities.
This has worked reasonably well until I now need to add the doctor name and link it to the facility name but only for GP facility types.
Do I just persist and run another row source setting and another NotInList event for the final combo box or is there a better way? One of the issues I have is that this then creates 2 new entries - one from the NotInList event from combo box 2 and another from the same event in combo box 3 (the first without the GP name). I still need the second combo box to insert a new entry as the 3rd combo is only used specifically for GPs and, as such, is optional.
Am I going about this the right way it do I need to split the facilities table to have a separate table for the doctor's names linked to the facility names? (One facility can have many doctors)
As a bonus I would actually like the suburb either stored if previously blank or retrieved if available from the tblFacilities (similarly the suburb is available and set for the first 3 types but not the last 2 types).
Any guidance would be appreciated.
I have 3 fields which I need users to enter:
[Facility type] - one of 5 options: hospital, racf, community service, GP and Other.
I have a set list for the first 3 options which I want to restrict users to selecting. The last 2 options a user can enter anything but I want to store what is entered for a possible future match.
The 2nd field is [Facility name]. As mentioned above this is either a set list which must be selected or can be entered for future retrieval depending on first field.
The 3rd field is [doctor name]. This is only required if the facility type is GP and similarly a user can enter anything but should be provided with previously entered options linked to the facility name (2nd field) ie set doctors work at a practice but there can be more than 1.
The 3rd field was only just added and is where I am struggling. My current setup for the first 2 fields is:
I have 2 tables - a reference table with the 5 different facility types (tblFacilityTypes) and another (tblFacilities) with all the data (fields include ID, FacilityType, FacilityName, DoctorName and Suburb)
[Facility type] is a combo box with a form-level lookup of the reference table.
With the OnChange event of this combo box it sets the row source of the second combo box to the tblFacilities matching the facility type of the first combo. This second combo has a NotInList event which either prevents entry of the facility type if it is one of the first 3 or prompts the user to create a new entry if it is one of the latter facility types and then inserts that detail into the tblFacilities.
This has worked reasonably well until I now need to add the doctor name and link it to the facility name but only for GP facility types.
Do I just persist and run another row source setting and another NotInList event for the final combo box or is there a better way? One of the issues I have is that this then creates 2 new entries - one from the NotInList event from combo box 2 and another from the same event in combo box 3 (the first without the GP name). I still need the second combo box to insert a new entry as the 3rd combo is only used specifically for GPs and, as such, is optional.
Am I going about this the right way it do I need to split the facilities table to have a separate table for the doctor's names linked to the facility names? (One facility can have many doctors)
As a bonus I would actually like the suburb either stored if previously blank or retrieved if available from the tblFacilities (similarly the suburb is available and set for the first 3 types but not the last 2 types).
Any guidance would be appreciated.