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

[mloucel]

Hello All:
I have this simple form, is working fine or so I thought.

The form is a combination of 2 SubForms Sepicialty and Specialist
my problem is that when there is a NEW specialty the End User can click on the specialist subform and simply add Dr Names without even adding a specialty
creating what I call GHOST records

How can I stop the user from creating a new specialist and let him/her do it only when a specialty is actually created..??
SEE Picture #2 for more details on Linked master and Child fields locations.

Any Ideas will be apprecialted

 

[Gasman]

Perhaps search here?, or look at this thread?

Not Allow data entry in Subform if Form Header hasn't been filled out

Dear Experts, I have a form sourced on a table with general data: Autonumber (Request_Nr) Requestor Name Urgency Submitted_Date And a subform with Request Nr (child field to Form Autonumber) Material_Description Mtl_Code The requestor is supposed to fill out the header first and then...

www.access-programmers.co.uk

 

[mloucel]

Perhaps search here?, or look at this thread?

Not Allow data entry in Subform if Form Header hasn't been filled out

Dear Experts, I have a form sourced on a table with general data: Autonumber (Request_Nr) Requestor Name Urgency Submitted_Date And a subform with Request Nr (child field to Form Autonumber) Material_Description Mtl_Code The requestor is supposed to fill out the header first and then...

www.access-programmers.co.uk

Thanks I tried 2 different solutions there:
1) on the "On Enter Procedure" event of the second sub-form I tried just to check:

Private Sub SpecialistF_Enter()
    If Me.Specialty.Form.Dirty Then
        MsgBox "Form Specialty is Dirty"
    Else
        MsgBox "Form Specialty is NOT Dirty"
    End If
End Sub

but since BOTH are subforms and both are NOT unbound then no matter what the SpecialtyF is always NOT DIRTY
Test
- Without adding a new record just click on any existing record of Specialty then click the "specialist" sub-form the Event is tested NOT DIRTY as it should be.
- Now I create a NEW record on Specialty then click Specialist and the event is tested NOT DIRTY, of course it will be not dirty since the moment I move the mouse to the Specialist sub-form the specialty is saved and it worked.
- now I Click on NEW to create a new record but I DO NOT type anything, just simply move my mouse to the Specialist sub-form, but no luck
see picture:

 

[Pat Hartman]

Since these are "sibling" forms rather than "parent/child", I don't think the master/child links will work but you could try them. You have to qualify the master names with form!subform. But if that doesn't work, then

1. in the Current event of the Specialty subform requery the doctors subform
2. In the AfterUpdate event of the Specialty subform requery the doctors subform.

1. in the BeforeInsert event of the doctors subform, check the specialty subform for a value.

If Me.Parent.sfrmSpecialty!SpecialtyID & "" = "" Then
    Msgbox "Please Select a Specialty prior to entering a doctor",vbOKOnly
    Cancel = True
    Me.Undo
    Exit Sub
Else

Me.SpecialtyID = Me.Parent.sfrmSpecialty!SpecialtyID

End If

 

[Gasman]

You could make Specialist a subform of Speciality?
I would likely just had a combo for that data in the Specialist record?

 

[ebs17]

I think you have to approach it completely differently. What dependencies are there between specialty and specialist? Show the database schema.

I would expect a many-to-many relationship between specialty and specialist, and so the form would look completely different.

 

[GaP42]

I agree @ebs17 - a Specialist may be perform/ be engaged in multiple Specialties, and a Specialty may be performed by / engage multiple Specialists. The conceptualisation of the form I would presume needs to establish the records of Specialist to Specialty, with an isActive attribute (as a minimum).

 

[gemma-the-husky]

Your problem really is that a specialist is NOT a subform of the specialty.

What you really have is specialties, and specialists, two completely independent tables, and a junction table specialty-suppliers identifying the services that the specialists provide.

So given your form, selecting a specialty should cause the right hand box to update to show the specialists available for that specialty. If you try to enter a new specialist for the specialty, you need a way to either select an existing specialist who offers that service, or to first add the new specialist to the specialist-supplier table, and then select it. It's not an issue to have orphan specialists or orphan specialties. It just illustrates the way your procurement process is working.

(Which is what the two posts above have also said)

 

[mike60smart]

Thanks I tried 2 different solutions there:
1) on the "On Enter Procedure" event of the second sub-form I tried just to check:

Private Sub SpecialistF_Enter()
    If Me.Specialty.Form.Dirty Then
        MsgBox "Form Specialty is Dirty"
    Else
        MsgBox "Form Specialty is NOT Dirty"
    End If
End Sub

but since BOTH are subforms and both are NOT unbound then no matter what the SpecialtyF is always NOT DIRTY
Test
- Without adding a new record just click on any existing record of Specialty then click the "specialist" sub-form the Event is tested NOT DIRTY as it should be.
- Now I create a NEW record on Specialty then click Specialist and the event is tested NOT DIRTY, of course it will be not dirty since the moment I move the mouse to the Specialist sub-form the specialty is saved and it worked.
- now I Click on NEW to create a new record but I DO NOT type anything, just simply move my mouse to the Specialist sub-form, but no luck
see picture:View attachment 114021

Are you saying that a specific Dr has Many Specialities?

If this is the case then your table structure is wrong.

Can you upload a zipped copy of the database?

 

[mloucel]

You could make Specialist a subform of Speciality?
I would likely just had a combo for that data in the Specialist record?

I am trying to kill 2 birds with 1 stone..
The End User [EU] has the responsibility to enter BOTH
here is the caveat:
If there is a specialty there has to be specialists, so this is a Maintenance routine.
hence the reason, once they have all that info, the Program will eventually require that info {both} and then I will make use of that idea you just gave me.

 

[mloucel]

Your problem really is that a specialist is NOT a subform of the specialty.

What you really have is specialties, and specialists, two completely independent tables, and a junction table specialty-suppliers identifying the services that the specialists provide.

So given your form, selecting a specialty should cause the right hand box to update to show the specialists available for that specialty. If you try to enter a new specialist for the specialty, you need a way to either select an existing specialist who offers that service, or to first add the new specialist to the specialist-supplier table, and then select it. It's not an issue to have orphan specialists or orphan specialties. It just illustrates the way your procurement process is working.

(Which is what the two posts above have also said)

That's exactly what is happening right now.... [Answering @ebs17 as well]
This is a MAINTENANCE routine for the EU (End User)
So here the EU has to input the new Specialty and then what Doctors do attend that specialty [One to Many]
One Specialty ---->> Many Doctors
not the other way around
This routine SPECIFICALLY enters a specialty:
Optometry
Ginecology
Neurology
ETC...
then the other Side of the form MUST enter which doctors take care of that SPECIFIC Specialty.
During the course of business there will be times that a new doctor is needed so they can easily come here and enter a new one, of course you guys can see why there is an ACTIVE field, so that if a doctor no longer works for us they can easily DEACTIVATE [Not DELETE] the doctor and continue with their life.
On other side of the program they will be able to pick a specialty and then the list of doctors that takes care of that specific specialty will pop, @Gasman pretty much solve that for me with a comment in post #5.

So here in this issue I have:
DO I NEED TO STICK WITH THIS WAY OF ENTER INFO ???
NO
this is a way I found and it seemed at that time pretty good, but then I found out that the EU can actually enter SPECIALTIES when they hit the LAST record on SPECIALTY but the issue is that I do NOT check whether or not they actually enter a brand new record [SO A NEW RECORD MUST BE CREATED IN SPECIALTY TO BE ABLE TO ADD SPECIALTIES] but of course if they simply are in one of the OLD specialties, then they should be able to EDIT the doctors on that specialty.
THE FORM WORKS..
again
Except when a brand new specialty needs to be entered, then my program does not check weather there is effectively a brand new specialty or not and creates GHOST (I called it GHOST) records on the doctors side.

Somehow I think I have to find a way to STOP the SPECIALTY to ADD a new record unless they hit a button that says NEW specialty, then add that to the current specialty sub form and continue with their lives.

Maybe I have the wrong approach but I am not an expert as many of you are, hence, the reason I ask for help.
If there is a better approach to my dilemma then please guide me, I am just simply learning trying.

I was told already that I cannot attach that specific part of the database here [or any other] just for the ones that have asked me.

my apologies for extending this too much but it was easy this way than answer 1 by one.

 

[mloucel]

Since these are "sibling" forms rather than "parent/child", I don't think the master/child links will work but you could try them. You have to qualify the master names with form!subform. But if that doesn't work, then

1. in the Current event of the Specialty subform requery the doctors subform
2. In the AfterUpdate event of the Specialty subform requery the doctors subform.

1. in the BeforeInsert event of the doctors subform, check the specialty subform for a value.

If Me.Parent.sfrmSpecialty!SpecialtyID & "" = "" Then
    Msgbox "Please Select a Specialty prior to entering a doctor",vbOKOnly
    Cancel = True
    Me.Undo
    Exit Sub
Else

Me.SpecialtyID = Me.Parent.sfrmSpecialty!SpecialtyID

End If

Pat they are working as a parent Child if you go back to my initial post, you'll see how the picture #2 shows the link on the Specialists sub form, It works UP to the point where a NEW Specialty has to be entered then if for any reason the EU moves the mouse to the specialists 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..
Now I DO NOT have to do it this way or with this particular form if there is a better way, they I will try it.

Thanks for your help

.

 

[GPGeorge]

The problem lies, at least in part, in the table design.

However, having read this thread again, I can't figure out what your tables actually look like.
Please help us out.

Create a screen shot of the relationships window. Show only the tables relevant to this part of the database so that it is clear what they are. Also, please expand each table to show all of the fields in it. Make sure the relationships between the tables are clear.
Thank you.

I disagree with the claim that entering a specialty requires a specialist. That may be a requirement you have imposed on your application as a business rule, but there is absolutely no logical reason a new specialty can not be created before any individual has received training and has been certified for that specialty. Creating a specialty involves defining the attributes of that specialty. There is no way to require that a specialist must exist as a prerequisite for there being such a specialty. It's almost universally true, however, that the reverse is required. People can't be certified for a specialty unless the specialty exists. And that's what you are trying to handle with the two subforms, albeit unsuccessfully.

Let's construct a thought experiment to see how that might work.

You identify a new staff member you want to add, and you learn that this person's specialty is "Eyebrow transplant Surgery". Rare, indeed, and one you probably do not already have on staff.

So, you follow your rule, adding both the specialty and specialist. A few months later, you learn this person is a fraud. They were never board certified in "Eyebrow transplant Surgery". So, you fire them. Now what? Do you also have to delete the category? Does the rule that there must be a specialist to have a specialty mean deleting specialties for whom you no longer have a specialist on staff? Or do you start a search for a new, better vetted, candidate, anticipating that the role can remain in the meantime because it will be filled again?

The answer you choose helps determine the appropriate table design and business rules you want to enforce.

So, please do show us the existing tables in the relationship window.

Thank you.

 

[GPGeorge]

I also want to explore a second potential problem I see here. I need additional background to understand.

Do all of your staff have one and only one specialty? Is it possible for Dr. Wally to specialize in both internal medicine and infectious diseases? If Dr, Wally is hired into one role, does that exclude any other possible role? Permanently or temporarily?

 

[mloucel]

The problem lies, at least in part, in the table design.

However, having read this thread again, I can't figure out what your tables actually look like.
Please help us out.

Create a screen shot of the relationships window. Show only the tables relevant to this part of the database so that it is clear what they are. Also, please expand each table to show all of the fields in it. Make sure the relationships between the tables are clear.
Thank you.

I disagree with the claim that entering a specialty requires a specialist. That may be a requirement you have imposed on your application as a business rule, but there is absolutely no logical reason a new specialty can not be created before any individual has received training and has been certified for that specialty. Creating a specialty involves defining the attributes of that specialty. There is no way to require that a specialist must exist as a prerequisite for there being such a specialty. It's almost universally true, however, that the reverse is required. People can't be certified for a specialty unless the specialty exists. And that's what you are trying to handle with the two subforms, albeit unsuccessfully.

Let's construct a thought experiment to see how that might work.

You identify a new staff member you want to add, and you learn that this person's specialty is "Eyebrow transplant Surgery". Rare, indeed, and one you probably do not already have on staff.

So, you follow your rule, adding both the specialty and specialist. A few months later, you learn this person is a fraud. They were never board certified in "Eyebrow transplant Surgery". So, you fire them. Now what? Do you also have to delete the category? Does the rule that there must be a specialist to have a specialty mean deleting specialties for whom you no longer have a specialist on staff? Or do you start a search for a new, better vetted, candidate, anticipating that the role can remain in the meantime because it will be filled again?

The answer you choose helps determine the appropriate table design and business rules you want to enforce.

So, please do show us the existing tables in the relationship window.

Thank you.

The main table will keep records of what we call authorizations..
The reason why it has to be the way is designed:
Each authorization has many parts 1 of them is
What Specialty do we send the patient to: [Ophtalmology, Gynecology, Cardiology, etc]
once we know that then we decide which doctor within that Specific Specialty do we send the patient, that is determined by where the patient lives.
So 1 Specialty MUST be chosen first in order to fulfill the doctor.
This Routine Simply ADDS specialties as they are needed
then adds the doctors who takes care of that specialty.
Task:
- MAINTENANCE of specialty and Specialists
- Create a program that Adds Specialties in as need basis.
- create a program that adds specialists for that specialties in as need basis
-- a Specialty MUST be created first in order to ADD specialists.
-- we need to be able to enable or disable a specialist in as need basis.
THIS IS ONLY for this specific task..
in the main program once a Specialty is chosen, the EU should be able to pick from a list of doctors for that specific specialty. [ this is the easy part, 2 Combo Boxes, 1 that picks the Specialty, the Other one will populate the Specialists based on what the first pick is.]

there is Way more involved this is just 1 specific task.

Here is the screen shot of what you asked:
my database currently contains no verifiable data all is MOCK and trial, no real names of anything is used..
Thank you .
I Do not need to do things like this this was an Idea [BAD IDEA for what I assume]
But hey will learn something out of this.
I am learning as well so if I need to change the design then so be it..

 

[mike60smart]

The main table will keep records of what we call authorizations..
The reason why it has to be the way is designed:
Each authorization has many parts 1 of them is
What Specialty do we send the patient to: [Ophtalmology, Gynecology, Cardiology, etc]
once we know that then we decide which doctor within that Specific Specialty do we send the patient, that is determined by where the patient lives.
So 1 Specialty MUST be chosen first in order to fulfill the doctor.
This Routine Simply ADDS specialties as they are needed
then adds the doctors who takes care of that specialty.
Task:
- MAINTENANCE of specialty and Specialists
- Create a program that Adds Specialties in as need basis.
- create a program that adds specialists for that specialties in as need basis
-- a Specialty MUST be created first in order to ADD specialists.
-- we need to be able to enable or disable a specialist in as need basis.
THIS IS ONLY for this specific task..
in the main program once a Specialty is chosen, the EU should be able to pick from a list of doctors for that specific specialty. [ this is the easy part, 2 Combo Boxes, 1 that picks the Specialty, the Other one will populate the Specialists based on what the first pick is.]

there is Way more involved this is just 1 specific task.

Here is the screen shot of what you asked:
my database currently contains no verifiable data all is MOCK and trial, no real names of anything is used..
Thank you .
I Do not need to do things like this this was an Idea [BAD IDEA for what I assume]
But hey will learn something out of this.
I am learning as well so if I need to change the design then so be it..
View attachment 114029

Well as thought the table structure is wrong.

Can you upload a zipped copy of the database?

 

[mike60smart]

The main table will keep records of what we call authorizations..
The reason why it has to be the way is designed:
Each authorization has many parts 1 of them is
What Specialty do we send the patient to: [Ophtalmology, Gynecology, Cardiology, etc]
once we know that then we decide which doctor within that Specific Specialty do we send the patient, that is determined by where the patient lives.
So 1 Specialty MUST be chosen first in order to fulfill the doctor.
This Routine Simply ADDS specialties as they are needed
then adds the doctors who takes care of that specialty.
Task:
- MAINTENANCE of specialty and Specialists
- Create a program that Adds Specialties in as need basis.
- create a program that adds specialists for that specialties in as need basis
-- a Specialty MUST be created first in order to ADD specialists.
-- we need to be able to enable or disable a specialist in as need basis.
THIS IS ONLY for this specific task..
in the main program once a Specialty is chosen, the EU should be able to pick from a list of doctors for that specific specialty. [ this is the easy part, 2 Combo Boxes, 1 that picks the Specialty, the Other one will populate the Specialists based on what the first pick is.]

there is Way more involved this is just 1 specific task.

Here is the screen shot of what you asked:
my database currently contains no verifiable data all is MOCK and trial, no real names of anything is used..
Thank you .
I Do not need to do things like this this was an Idea [BAD IDEA for what I assume]
But hey will learn something out of this.
I am learning as well so if I need to change the design then so be it..
View attachment 114029

Can you upload a screenshot of the table that contains the SpecialityID showing all fields as well as the table name?

 

[gemma-the-husky]

That's exactly what is happening right now.... [Answering @ebs17 as well]
This is a MAINTENANCE routine for the EU (End User)
So here the EU has to input the new Specialty and then what Doctors do attend that specialty [One to Many]
One Specialty ---->> Many Doctors
not the other way around
This routine SPECIFICALLY enters a specialty:
Optometry
Ginecology
Neurology
ETC...
then the other Side of the form MUST enter which doctors take care of that SPECIFIC Specialty.
During the course of business there will be times that a new doctor is needed so they can easily come here and enter a new one, of course you guys can see why there is an ACTIVE field, so that if a doctor no longer works for us they can easily DEACTIVATE [Not DELETE] the doctor and continue with their life.
On other side of the program they will be able to pick a specialty and then the list of doctors that takes care of that specific specialty will pop, @Gasman pretty much solve that for me with a comment in post #5.

So here in this issue I have:
DO I NEED TO STICK WITH THIS WAY OF ENTER INFO ???
NO
this is a way I found and it seemed at that time pretty good, but then I found out that the EU can actually enter SPECIALTIES when they hit the LAST record on SPECIALTY but the issue is that I do NOT check whether or not they actually enter a brand new record [SO A NEW RECORD MUST BE CREATED IN SPECIALTY TO BE ABLE TO ADD SPECIALTIES] but of course if they simply are in one of the OLD specialties, then they should be able to EDIT the doctors on that specialty.
THE FORM WORKS..
again
Except when a brand new specialty needs to be entered, then my program does not check weather there is effectively a brand new specialty or not and creates GHOST (I called it GHOST) records on the doctors side.

Somehow I think I have to find a way to STOP the SPECIALTY to ADD a new record unless they hit a button that says NEW specialty, then add that to the current specialty sub form and continue with their lives.

Maybe I have the wrong approach but I am not an expert as many of you are, hence, the reason I ask for help.
If there is a better approach to my dilemma then please guide me, I am just simply learning trying.

I was told already that I cannot attach that specific part of the database here [or any other] just for the ones that have asked me.

my apologies for extending this too much but it was easy this way than answer 1 by one.

There's actually no MUST at all.
You have a form with the specialties
You have a form with the doctors.

You then have a third form which associates the doctors with their specialties. I would say do it that way rather than pick the doctors who have a given specialty, but you can do it either way. That's the point.

It's not one to many. A doctor can have multiple skills, and a given skill can be a "property" of many doctors. It's many to many. It's just a matter of taste which way you present it.you would probably have both. When you set up a doctor you record his specialties. When you select a required specialty, you can see the potential doctors with that skill.


.

 

[mloucel]

There's actually no MUST at all.
You have a form with the specialties
You have a form with the doctors.

You then have a third form which associates the doctors with their specialties. I would say do it that way rather than pick the doctors who have a given specialty, but you can do it either way. That's the point.

It's not one to many. A doctor can have multiple skills, and a given skill can be a "property" of many doctors. It's many to many. It's just a matter of taste which way you present it.you would probably have both. When you set up a doctor you record his specialties. When you select a required specialty, you can see the potential doctors with that skill.


.

I see your point, but for this particular case is NOT a many to many.
is always a 1 to many
1 specialty to MANY doctors, not the other way around.
like I said for THIS particular only.
My Database is called Authorizations and has nothing to do with doctor's skills, maybe that's where all the confusion is.
the authorization:
A patient comes to our clinic and sees a doctor.
this doctor determines that the patient needs to see a specialist and sends the order for an authorization.

The authorization dep then:
Creates a Pre Authorization (here my database WILL be opened)
EU enters Patient pertinent INFO and Doc ordering INFO as well..
EU search what type of AUTH is.. [ex..: an optometrist (very common)..
The database is the FIRST time the EU will use it..
He/She sees there is no OPTOMETRY, so he goes to the Maintenance Menu / Specialty/Specialists Sub Menu

Enters a NEW Specialty called OPTOMETRY,
Now Click the other "Window" and enters a doc's name and info, that suits this moment..
EU completes the rest of the authorization and some other stuff happens.

NOW..
The EU has another Auth.. another Optometrist
Same process except he determines he needs a different doctor NOT in the database, so he goes to Maintenance / etc..
Choses "OPTOMETRY" since it was entered already and a list of DOCTORS (1) shows on the other window..
He then click on the SPECIALIST window, on the NEW space and enters the NEW INFO..
Clicks SAVE and continues the Auth process..

that is in very simple steps what this particular routine should do..

Problem this PARTICULAR subroutine DOES NOT verifies that the EU has effectible entered a NEW SPECIALTY [WHEN NEEDED] in order to be able to correctly enter a NEW SPECIALIST INFO, thus creating what I call ghost records, in the specialist database.

Hope that clears this better.

Thanks so much and sorry for the confusion.

Maurice.

 

[gemma-the-husky]

I see your point, but for this particular case is NOT a many to many.
is always a 1 to many
1 specialty to MANY doctors, not the other way around.
like I said for THIS particular only.
My Database is called Authorizations and has nothing to do with doctor's skills, maybe that's where all the confusion is.
the authorization:
A patient comes to our clinic and sees a doctor.
this doctor determines that the patient needs to see a specialist and sends the order for an authorization.

The authorization dep then:
Creates a Pre Authorization (here my database WILL be opened)
EU enters Patient pertinent INFO and Doc ordering INFO as well..
EU search what type of AUTH is.. [ex..: an optometrist (very common)..
The database is the FIRST time the EU will use it..
He/She sees there is no OPTOMETRY, so he goes to the Maintenance Menu / Specialty/Specialists Sub Menu

Enters a NEW Specialty called OPTOMETRY,
Now Click the other "Window" and enters a doc's name and info, that suits this moment..
EU completes the rest of the authorization and some other stuff happens.

NOW..
The EU has another Auth.. another Optometrist
Same process except he determines he needs a different doctor NOT in the database, so he goes to Maintenance / etc..
Choses "OPTOMETRY" since it was entered already and a list of DOCTORS (1) shows on the other window..
He then click on the SPECIALIST window, on the NEW space and enters the NEW INFO..
Clicks SAVE and continues the Auth process..

that is in very simple steps what this particular routine should do..

Problem this PARTICULAR subroutine DOES NOT verifies that the EU has effectible entered a NEW SPECIALTY [WHEN NEEDED] in order to be able to correctly enter a NEW SPECIALIST INFO, thus creating what I call ghost records, in the specialist database.

Hope that clears this better.

Thanks so much and sorry for the confusion.

Maurice.

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.

I imagine your process could be improved.

In your scenario when you come to pick an optometry specialist you can't pick any doctor who already has a recorded specialty, as you say a doctor can only have one specialty. That's not what you mean at all, is it? A doctor can easily have multiple areas of competence, and the relationship is many to many.

You should have a doctor's table, a specialties table, and a doctor's skills table as a junction table between the two. This is the correct table design. How you present this to a database user is a completely different thing.

It sounds like you are trying to create both a doctor and the populate the doctors skillset at the same time, and you literally can't process both tables simultaneously. You have to create the doctor first,and then populate the skillset. You need an interface that makes it easy to do these tasks, but I don't think your interface can do that.