Solved Stop Users from adding on second subform if #1 is not filled

[Pat Hartman]

I do not check whether really there is a new record or not on the specialty box, that's the issue I have, see the new picture I attached..

So, follow my directions on how to use the BeforeInsert event of the second form.

You also have three more problems. One the others mentioned and two they didn't.

1. The relationship between specialties and doctors is m-m, NOT 1-m. I'm going to attach a database with a sample m-m relationship.
2. You don't have RI declared or enforced.
3. You don't have the specialityID FK defined as required. This will be more clear when you create an actual m-m. In the junction table, the two FKs are both required AND, in some cases (this is one of them), you should have a unique index that contains the two FKs so that the combination of SpecialtyID and DoctorID can only occur ONCE in the junction table.

If you post the existing tables, someone will clean up the tables and perhaps even fix your form - although, I suggest that you review the forms in my sample. A m-m relationship can be viewed or updated from either direction. It also doesn't matter whether you use a subform or a popup form to enter the data into the junction table. My example shows both. One method will make more sense to you than the other for any given situation.

Many-to-many example | Access World Forums (access-programmers.co.uk)

 

[mloucel]

Are you saying a doctor only has a single specialty? If so you simply store that specialty in the doctor record. But I doubt very much if that makes sense in any medical scenario.

no dear friend;

maybe I am not being clear, and my apologies if that is the case.

For this particular database only and for this particular need, I must save the record the way it is.

your point is well taken, but in my database for the need of an authorization this must be done.

I cannot save the data as you describe, this will create a few duplicates..

the idea is to have Only 1 record for each SPECIALTY

then for each specialty as many Doctors as EACH specialty may have..

ex:
Optometry:
-- Dr1
-- Dr2
-- DR3
-- DR4

Gastroenterology:
-- Dr1
-- Dr2
-- DR3
-- DR4
-- DR5

Pulmonology:
-- Dr1
-- Dr2
-- DR3

and so on..., each SPECIALITY must be UNIQUE, then have the EU can choose whatever doctor suits the needs, in OUR case the address of the patient determines which doctor the patient will go to, the EU can choose from the "Range" of doctors WE have in that specific SPECIALITY available to US..

 

[gemma-the-husky]

no dear friend;

maybe I am not being clear, and my apologies if that is the case.

For this particular database only and for this particular need, I must save the record the way it is.

your point is well taken, but in my database for the need of an authorization this must be done.

I cannot save the data as you describe, this will create a few duplicates..

the idea is to have Only 1 record for each SPECIALTY

then for each specialty as many Doctors as EACH specialty may have..

ex:
Optometry:
-- Dr1
-- Dr2
-- DR3
-- DR4

Gastroenterology:
-- Dr1
-- Dr2
-- DR3
-- DR4
-- DR5

Pulmonology:
-- Dr1
-- Dr2
-- DR3

and so on..., each SPECIALITY must be UNIQUE, then have the EU can choose whatever doctor suits the needs, in OUR case the address of the patient determines which doctor the patient will go to, the EU can choose from the "Range" of doctors WE have in that specific SPECIALITY available to US..

But this is many to many. Your doctor 1 has three specialties. That's many to many.

The problem is your interface needs reconsidering in order to achieve what you want. If you don't have a skills table showing that doctor 1 is skilled in O, G and P, but doctor 4 is only skilled in O and G, then that's why you are finding it hard.

You add a new skill/competency by storing the doctor and the specialty in this skills table, and then your form will show the new doctor as available to satisfy the required competency. No orphans, and no duplicates.

 

[Gasman]

Seriously, I doubt one doctor only has one speciality.?
Imagine teachers/lecturers scenario.
Each could be capable of teaching certain/various subjects?
Perhaps it is just a GUI issue, but I would also guess many to many as well.

 

[mloucel]

But this is many to many. Your doctor 1 has three specialties. That's many to many.

The problem is your interface needs reconsidering in order to achieve what you want. If you don't have a skills table showing that doctor 1 is skilled in O, G and P, but doctor 4 is only skilled in O and G, then that's why you are finding it hard.

You add a new skill/competency by storing the doctor and the specialty in this skills table, and then your form will show the new doctor as available to satisfy the required competency. No orphans, and no duplicates.

Sorry that was just an BAD example

ex:
Optometry:
-- Dr Pete
-- Dr Rick
-- Dr Scott
-- Dr Mag

Gastroenterology:
-- Dr Gas
-- Dr Oil
-- Dr Can
-- Dr Key
-- Dr Wrench

Pulmonology:
-- Dr Smokey
-- Dr Marlboro
-- Dr Kill

 

[mloucel]

Seriously, I doubt one doctor only has one speciality.?
Imagine teachers/lecturers scenario.
Each could be capable of teaching certain/various subjects?
Perhaps it is just a GUI issue, but I would also guess many to many as well.

Nup.. not for this particular, I guess there is a serious confusion on what the database will accomplish, I guess everyone is more fixed on the wrong place..

The final IDEA:

1 Speciality MUST BE UNIQUE NO MATTER WHAT..

2 Specialist DOCTOR MUST be ASSIGNED to a particular SPECIALTY NO MATTER WHAT..

3 A doctor cannot be an Endocrinologist and a Dentist, or an Ophthalmologist CANNOT/MUST NOT be an OPTOMETRIST

YES this can happen in the real LIFE..

NO for this particular Data Base that will be the RULE and MUST happen.

 

[Gasman]

Yes, most people are thinking of real life. :-(

 

[GPGeorge]

Rule 2 applies to your business and only to your business. I doubt that in other organizations a doctor can be restricted to a single specialty.

In fact, a brief internet search turns up more than one counter-example:

You can impose the rule on your organization and allow each doctor one and only one specialty. That's your business rule.

 

[Pat Hartman]

this will create a few duplicates..

When you make rules that deviate from reality, you are almost certainly misunderstanding the requirement and will end up making major modifications later.

Is the only information you have for a doctor his name? If not, then you really need to create the data entry forms differently. You have one form that creates doctors. There will be a combo where you can choose the doctor's speciality.

Then the view you are showing will NEVER be used for data entry, only for viewing. If you insist on using your current layout, then you need to expand the doctor subform to include ALL required fields. You CANNOT be sloppy and add just a name without the doctor's address and contact information at a minimum.

You also have to make the Speciality FK in the doctor's record required and make sure its default is nu

 

[gemma-the-husky]

Then all you need to do is store a Speciality cross reference in a doctor's record. I presume some doctors can be GPs with no specialty at all, although it doesn't really matter.

 

[mloucel]

Rule 2 applies to your business and only to your business. I doubt that in other organizations a doctor can be restricted to a single specialty.

In fact, a brief internet search turns up more than one counter-example:

View attachment 114031

You can impose the rule on your organization and allow each doctor one and only one specialty. That's your business rule.

Yes George, but like I said because of the aplication this particular segment MUST be like that.
Literally the Authorizations person does not care if the doctor can perform various practices, if that is the case
Dr. Peter Hong, MD will be in THIS PARTICULAR SEGMENT.
Head and Neck Surgery
- Dr Peter Wong, MD
- Dr Michael Scott MD
ENT & Allergy
- Dr Robert One ENT
- Dr Peter Hong MD
- Dr Millary Joy ENT

As you can see Dr. Hong will be represented in 2 DIFFERENT PRACTICES and that is OK, we have never found a case though.. but yes it may happen.
But for this case, this is the way it should be.

 

[mloucel]

When you make rules that deviate from reality, you are almost certainly misunderstanding the requirement and will end up making major modifications later.

Is the only information you have for a doctor his name? If not, then you really need to create the data entry forms differently. You have one form that creates doctors. There will be a combo where you can choose the doctor's speciality.

Then the view you are showing will NEVER be used for data entry, only for viewing. If you insist on using your current layout, then you need to expand the doctor subform to include ALL required fields. You CANNOT be sloppy and add just a name without the doctor's address and contact information at a minimum.

You also have to make the Speciality FK in the doctor's record required and make sure its default is nu

Hey Pat.
Probably since No One has the FULL spectrum of this database, everyone is going THE CORRECT WAY of doing things IF THIS WAS A DIFFERENT TYPE OF DATABASE AND REQUIEREMENT.

This is NOT what everyone thinks it is..
Hence I posted the name of the Database, and the purpose of such..

Authorizations
it is not a part of any other system, is by itself.
the only thing this department does is Authorizations.
Let me try to explain briefly what it is maybe then the WHY will be a bit more clear.

Patient comes for a visit.
Dr. Determines he needs to see a specialist, say an ENT [Ear Nose Throat Doctor]
EU (End User) enters BASIC info of the patient
Chooses the Specialty called ENT
see what Doctors serve as ENT and picks one based on the Dr. Phone Number area code.
He then enter a bit more info
does a follow up
enter some dates related to the Authorization
once the patient goes and there is note from the ENT then the Authorization is marked COMPLETED.

is that easy, of course, to make it happen there are a few caveats that I have to sort in the software.
One of them was the problem I found.
User MUST have an easy way to search for the Specialty, if that specialty does not exist, then enter the new specialty and be able to enter which doctor(s) serve that specialty.
The form as is was literally what My boss loved and what he wanted, I WAS THE ONE THAT FOUND THE PROBLEM, and right before he did or the program goes live, I decided to fix the issue and since I couldn't I ask the question, that now has created this issue here in the forum.

My concern is not if the doctor can perform 2, 3 or more specialties I only care that His name shows in the Specialty he is needed at the moment we need to send the patient to him/her, and most important, MY BOSS.

This is his baby, He made me create it in excel a few years ago and it has been working, until we have too many lines in excel, then I was almost LET GO, I decided to either go back to programming or look for another Job, that at my age is surely not the best option.
So here I am, learning to do Access from a background from FoxBase, Cobol, and a few other things.

I guess most everyone is very Smart and my questions and the way I do stuff makes no sense, but I have no time to go back to school and learn, I had to do it with what I know, and a few books, youtube, this forum and now I found Rick Rost in you tube as well, and waiting for the best.

I was talking to someone else in another forum, they gave me another Idea, and I will try that tomorrow, if that one works and my boss likes it then problem solved.
I think I wasted all of your time, and no one was able to find a viable solution, maybe there is no solution at all, then the best move is to move on, like I said before:
There is more than 1 way to skin a cat.

Respectfully:

Maurice.

 

[gemma-the-husky]

I think the truth is that you are looking at a particularly limiting way of dealing with this, whereas a generalised solution would still work but would have other benefits.

Your "solution" is likely to lead to problems in the future, because the truth is you surely do have a many to many relationship. I just don't see how you can only allocate a doctor to a single specialism. That means you need to have at least as many doctors available as problems that need to be dealt with. That surely doesn't make sense.

You mention that the USER can determine there is a gap that needs filling .... So how is the user qualified to assess the various doctors' abilities?

I don't think you are wasting much of our time, as we are just talking generally about systems in general, but I'm sure we do have a good understanding of the nature of relationships. Many users think they have 1 to 1 or 1 to many relationships, but often often the truth is that it's a many to many relationship that often just displays as 1 to many in most cases - but the correct system will deal with the problem as a many to many.

Get a full list of all the specialties you need to deal with and you will most likely see that there are a couple of specialities for which you only have a single doctor.

That's many to many. One doctors has multiple specialties, and each specialty can have multiple doctors. Your system just needs to manage the network of these details.

 

[Pat Hartman]

@mloucel You've done a lot of explaining away something the rest of us know is wrong but I gave you the solution all the way back in POST # 4. Did you ever try it? You have to use the BeforeInsert event of the doctor subform to validate the current record of the first subform. If that row has an entry, then the assumption is that this doctor will belong to that speciality. Others have also provided solutions that take more work/more code. Use one of them if you don't like mine but you HAVE a solution.

I know you are new to Access. The solution I gave you using the BeforeInsert event in #4 (we're up to #21 for some reason) IS THE SOLUTION to your initial question regardless of whether you fix the schema to be correct and allow a doctor to have multiple specialities or not. Your reply when I suggested it instead of doing it was to say you didn't do that. Well, do it because that is how you will know that a record has been selected in the speciality table. Maybe you don't understand that when you reference a continuous or DS view form, you are referencing the current record, not all records, not the first record or the last record but the CURRENT record. That is the record that has the focus.

The current solution with the 1-m relationship will require you to duplicate a doctor if you ever recognize that he actually has multiple specialities or ignore him as an option because you can't add him twice to the doctor's table. You really need to understand this flaw and point it out to the person who created the problem in the original design.

Sometimes, there are alternative solutions such as how to ensure that the doctor is related to a speciality. I gave you the simplest one. You were given at least two others(not showing the form unless the specialty form has a current record, locking the doctor form until the specialty form has a current record). But there are rarely alternative solutions to using the real world to model your schema. When you ignore reality, as you are, you will eventually run into a problem. But, then you can come back and we'll tell you how to fix it. Never forget that the longer you let a bad design fester, the more objects and data you need to fix later.

Good luck. Please try the solution you understand best and let us know if your observed problem is resolved.

 

[mloucel]

@mloucel You've done a lot of explaining away something the rest of us know is wrong but I gave you the solution all the way back in POST # 4. Did you ever try it? You have to use the BeforeInsert event of the doctor subform to validate the current record of the first subform. If that row has an entry, then the assumption is that this doctor will belong to that speciality. Others have also provided solutions that take more work/more code. Use one of them if you don't like mine but you HAVE a solution.

I know you are new to Access. The solution I gave you using the BeforeInsert event in #4 (we're up to #21 for some reason) IS THE SOLUTION to your initial question regardless of whether you fix the schema to be correct and allow a doctor to have multiple specialities or not. Your reply when I suggested it instead of doing it was to say you didn't do that. Well, do it because that is how you will know that a record has been selected in the speciality table. Maybe you don't understand that when you reference a continuous or DS view form, you are referencing the current record, not all records, not the first record or the last record but the CURRENT record. That is the record that has the focus.

The current solution with the 1-m relationship will require you to duplicate a doctor if you ever recognize that he actually has multiple specialities or ignore him as an option because you can't add him twice to the doctor's table. You really need to understand this flaw and point it out to the person who created the problem in the original design.

Sometimes, there are alternative solutions such as how to ensure that the doctor is related to a speciality. I gave you the simplest one. You were given at least two others(not showing the form unless the specialty form has a current record, locking the doctor form until the specialty form has a current record). But there are rarely alternative solutions to using the real world to model your schema. When you ignore reality, as you are, you will eventually run into a problem. But, then you can come back and we'll tell you how to fix it. Never forget that the longer you let a bad design fester, the more objects and data you need to fix later.

Good luck. Please try the solution you understand best and let us know if your observed problem is resolved.

Believe it or not, very soon I will be able to post the final solution, not the way I thought but close enough, I am on vacation now, but as soon as I am back I will do it al let everyone know.
I am very thankful to everyone, for their suggestions, and I understand your thoughts, but unless you know my application is understandable why you cannot comprehend the WHY, has to be the way it is.

Maurice.

 

[mloucel]

I have finalized the form and I want to thank:

@Pat Hartman
@gemma-the-husky
@Gasman
@GPGeorge

and very special mention to @mike60smart whom gave me the final touch to my success in the form, if anyone is interested I am posting the link to this specific part of the Database already finalized, contains coding from various sources including my own, I truly understand there maybe a better way or approach, but is not what I like or need, but what my boss wanted and needed, this will fit perfectly to the final database as HE envisions and wants, of course if anyone has an opinion on how the code can be made better and more reliable I am more than open to hear and or apply your suggestions.

here is the link to this particular part of my program:
Final Version