Mixed fields design suggestions (1 Viewer)

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.

First, a side comment just to get it out of the way. Don't make fields have names with spaces in them. It is far easier to type run-together names when coding in VBA as well as in SQL, since names with spaces ALWAYS need to be bracketed whereas names without spaces can drop the brackets in most cases. So three less characters every time (the space and two brackets).

OK, the main problem: The fact that you are potentially adding a doctor with a specific affiliation makes this a case where a pop-up form might be needed, since you have not only a write-in for the doctor but then you also have to select the affiliation to a facility. Obviously, once you select the [Facility Name], you have the name for doctor affiliation. Do you have a preset list of [Doctor Name] for the "no write-in" facility types where a [Facility Name] is only allowed to come from a fixed list? I.e. do you also have a list of known doctors for your hospitals? I have run across cases where a doctor has both hospital and private practice affiliations. If that can happen, you have a sudden many-to-many relationship, which would scream "junction table."

You say the logic of blocking arbitrary data entry for two types but allowing entry for the others is working for you, but I wonder whether a small pop-up form might give you better flexibility and control. I'm thinking along the lines of "isolation of function" so that you can avoid cross-contamination of functional requirements in your coding. Of course, that is using advice from that wise old sage, Julius Caesar, he of "Divide and Conquer" fame.

As to the "suburb" problem, since you are subject to that same complex logic, you might also need to consider a pop-up form.

Of course, any time you update an underlying data source for combos or list boxes, a control.Requery is required after the exit of the pop-up. But I infer that you either knew or have discovered that.

That discussion of [Facility Type] vs. [Facility Name] is a little bit confusing. Are you saying that once someone selects a facility type, there are some lists that cannot have an arbitrary name but for other types, a write-in [Facility Name] is permitted and is then permanently associated with that [Facility Type]? That is what I got out of it. (This is just a question for clarity.)

The_Doc_Man said:

First, a side comment just to get it out of the way. Don't make fields have names with spaces in them.

Click to expand...

Sorry this was autocorrect on the phone - the fields are named without spaces (didn't autocorrect them in the 4th paragraph for some reason)

The_Doc_Man said:

Do you have a preset list of [Doctor Name] for the "no write-in" facility types where a [Facility Name] is only allowed to come from a fixed list?

Click to expand...

No the [DoctorName] is only used for the "GP" facility type which is a 'write-in' field. It is not used for any of the first 3 fixed-list facility types. So it is always "write-in" but should include the option of previously entered doctors for that facility name.

The_Doc_Man said:

You say the logic of blocking arbitrary data entry for two types but allowing entry for the others is working for you, but I wonder whether a small pop-up form might give you better flexibility and control. I'm thinking along the lines of "isolation of function" so that you can avoid cross-contamination of functional requirements in your coding. Of course, that is using advice from that wise old sage, Julius Caesar, he of "Divide and Conquer" fame.

As to the "suburb" problem, since you are subject to that same complex logic, you might also need to consider a pop-up form.

Click to expand...

This was the purpose of the thread was to see if I am making this too complex or if there is a better way - the only concern I have at this stage with a pop up is the data entry becomes a bit slower. Right now it is type, tab, type, tab etc and a when I have watched users they tend to reach for the mouse when a pop up arrives, even when focus is set correctly. (need better users? )

The_Doc_Man said:

Of course, any time you update an underlying data source for combos or list boxes, a control.Requery is required after the exit of the pop-up. But I infer that you either knew or have discovered that.

Click to expand...

Yes and I hate to have to requery so often - the other issue is when I move a record the old row source stays for that control and I need to re-set the row source using the on current event. More code and more requeries

The_Doc_Man said:

That discussion of [Facility Type] vs. [Facility Name] is a little bit confusing. Are you saying that once someone selects a facility type, there are some lists that cannot have an arbitrary name but for other types, a write-in [Facility Name] is permitted and is then permanently associated with that [Facility Type]? That is what I got out of it. (This is just a question for clarity.)

Click to expand...

Yes that is correct. We have a set list of hospitals and community services. The user must select these when entering data (they are associated with a specific code that is used for stats). There is no variation allowed in this list. The GPs and doctors, however, are an ever evolving list and this is where the user can enter anything but I think they should at least be presented with a (possible) prior entry to select from to keep it as consistent as possible.

to see if I am making this too complex

Click to expand...

We might have trouble answering that question because it requires us to know a LOT about your problem. In ALL cases, though, the code must be as complex as the problem. EXACTLY as complex - because your goal is a 1-to-1 correspondence between the map and the territory, so to speak. Your goal is to model the problem in code so you cannot simplify unless the piece you work on is simple.

As to requerying so often... if you are doing a variant on cascading combo boxes, that frequent requery is a cost of doing business.

More code and more requeries

Click to expand...

But at least if you are doing that, it is because you have discovered that you needed to do so? Nobody ever said making a database app was easy. You have a LOT of hard work to determine what needs to be done. Once you have the problem scoped out, though, the implementation isn't so bad. Trust me - this phase where you are asking all sorts of "would it be better to ..." questions is normal. Aggravating - but normal.