Combo's [Edit from Record] help

[mloucel]

Hello Gurus;

I am almost finalizing my project, is not perfect but is mine thanks to the help of many of you, but I have an issue and I don't know what to do:
Here is the part of the form:

The user adds all those records in the ADD RECORD Form, that part is working fine.

Before those records were supposed to be grayed out IN THE EDIT RECORD FORM, so that the user couldn't change them, [per my boss] but the EU [End User] has a different Idea, and they are right many times they need to MODIFY for any reason and the way the boss wanted it was not right, so I had to re-do the form [at the LAST MINUTE] to:
1) get the Record from the DB [AuthorizationsT] and push the data to the combos
2) once the combos are fed allow the combos to change the record if the EU wants to do so.

this is were I am stuck..

I HAVE NEVER DONE ANYTHING LIKE THIS and I HAVE 0 KNOWLEDGE WHATSOEVER ON WHAT TO DO.

IPA record comes from a DB [AuthorizationsT] that contains the id of the record only (as it should), then I need to move that ID to the COMBO and display the correct name of the IPA, but once pushed to the combo, it should get the new record from the following SQL, if the user needs to do so:

SELECT IpaT.IpaID, IpaT.IpaName, IpaT.IpaActive, IpaT.IpaActive FROM IpaT
WHERE (((IpaT.IpaActive)=True))
ORDER BY IpaT.[IpaName];

HealthPlan MUST DO Exactly the same thing this as IPA does,get the current Record from [AuthorizationsT and push that to the combo], this is the SQL:

SELECT HealthPlanT.HealthPlanID, HealthPlanT.HealthPlan, HealthPlanT.HPActive, HealthPlanT.HPActive FROM HealthPlanT
WHERE (((HealthPlanT.HPActive)=True))
ORDER BY HealthPlanT.[HealthPlan];

Specialty and Specialist are CASCADING COMBOS.
they should fill as well their data at the beginning of the EDIT form [Private Sub Form_Load()], get whatever record is on the [AuthorizationsT] and fill the Specialty and Specialties combos with the current record and as well if the user needs to change the record, they must be able to do so and be able to save the new data to the [AuthorizationsT].

Here is the SQL for Specialty [Should be getting the CURRENT record from [AuthorizationsT and pushed to the Combo]

SELECT [SpecialtyT].[SpecialtyID], [SpecialtyT].[Specialty] FROM SpecialtyT
ORDER BY [Specialty];

Here is the SQL for Specialties [Should be getting the CURRENT record from [AuthorizationsT and pushed to the Combo]

SELECT qrySpecialtiesEdit.SpecialistsID, qrySpecialtiesEdit.DrName, qrySpecialtiesEdit.DrPhone
FROM qrySpecialtiesEdit
ORDER BY qrySpecialtiesEdit.[DrName];

This is the QUERY:

I know how to build the Cascade combo from "Private Sub Form_Load()" when I am doing the input of the NEW record in the NEW RECORD FORM, but here EDIT RECORD FORM, I am completely at lost, ALL this I have NO IDEA whatsoever what to do, and how to do it.

LITERALLY I DO NOT KNOW HOW TO BUILD THE CODE.

I am completely at lost here, I've tried to look for help, but I can't find anything that can come close to what I need, since I am:

1 Getting the data from a Record Database and push that to a combo that is generated from a query
2 Pushing that to a Combo that contains an ID only and display the correct data.
3 If the EU changes the record, save the new data [ID] to the AuthorizationsT from all the fields that were changed

I would much appreciate some help here, because at this point, I have been stressed for about 1 week not wanting to look for help, and is not stupid pride, is just that I wanted to exhaust all possibilities before asking for help.
If you can help me with some code or example DB I can use that would be great since my time is so short now, in case you need the FE and BE I can provide then, and I understand that many of you might CRINGE with my code but is what I have and I did it to the best of my abilities, I fully understand that many of you can do this in a heart bit, even with your eyes closed, but please do not forget, I have NEVER done anything like this and I have been learning ACCESS in a "AS WE GO" Basis.

Kindly help me!!
Maurice.

 

[MajP]

This is a bound form so can you show us the sql for the source query?

Since this is a bound form I really do not understand any of this.

The form opens up to some kind of PK. I assume it is AuthorizationID. That table or query includes foreign keys to
IPA, HealthPlan, Speiciality, and Specialist and the whole thing populates.
I do not understand this "pushing to combos." unless those combos are unbound. If they are bound they get their values from the source table.

 

[MajP]

If you have a development DB with notional data can you share it?
If not at least share the Form in a new db. If it is based on a query then at least post the sql.
Also post a screen capture of the relationship window.

 

[Pat Hartman]

1) get the Record from the DB [AuthorizationsT] and push the data to the combos
2) once the combos are fed allow the combos to change the record if the EU wants to do so.

There is something very wrong with this Marcel.
1. You don't need separate forms for add/view/change.
2. Without knowing the rules for changing, I can't give you specific instructions, BUT, a simple solution might be to set the AllowUpdates and AllowDeletes properties to False in the form's Current event when you are not on a "new, empty, record"

Private Sub Form_Current()
    If Me.NewRecord Then
        Me.AllowEdits = False
        Me.AllowDeletions = False
    Else
        Me.AllowEdits = True
        Me.AllowDeletions = True
    End If
End Sub

3. Then you can have a button that toggles the "allow" properties so the user can edit the visible record. You could prompt for a password if only some people are allowed to edit.

There are numerous reasons for not creating multiple forms that show the same data but the reason for not separating the add/edit function is you need to duplicate your validation and that is very wrong.

 

[mloucel]

@Pat Hartman and @MajP
Thanks

I FULLY ACKNOWLEDGE THAT I HAVE TONS OF ERRORS THAT WILL MAKE ALL OF YOU RUN SCARED
I FULLY ACKNOWLEDGE THAT I AM SURELY TRYING MY BEST TO THE BEST OF MY ABILITY
I AM NOT ASKING TO CORRECT ALL THE ERRORS [PRETTY MUCH THE WHOLE THING IS AN ERROR POSSIBLY]

I just need help in that minor particular area where I have issues, and of course if you have a better way to accomplish what I need to do, please do so, but let me know so I can learn.

The BE was easy to upload but the FE is too big, so I am simply giving a link to the file in my Google Drive:

Access - Google Drive

drive.google.com

The data contained in the BE is either FALSE or Public Knowledge generally we got it from the Internet and does not contain any data that may be considered "privileged"

Unless RUN it will ask for a password.

I am desperate at this point, and I need to make sure this works, I have just found another MAJOR error when I am saving a NEW authorization in the PatientT since AuthorizationID Should NOT be there since this is a UNIQUE record [PatientT] and will never save that info, but I will correct that.

Please, Please ANY help to correct this or to make this work will be greatly appreciated, honestly at this point I am desperate, I don't know what to do and my stress level is beyond stress, I know I understand that ANYONE needs to help me in any way, but I am really desperate I have spent almost 4 months making every change imaginable to conform to what my boss wants and I am in my last straw, they are a bit frustrated that may come with consequences.

So that is my situation, if anyone can help me, I would much appreciate it.

IF you want to try the APP running you may run the Admin user, the Temporary password for that is [ 3Lmege77* ]

Maurice.

 

[mloucel]

There is something very wrong with this Marcel.
1. You don't need separate forms for add/view/change.
2. Without knowing the rules for changing, I can't give you specific instructions, BUT, a simple solution might be to set the AllowUpdates and AllowDeletes properties to False in the form's Current event when you are not on a "new, empty, record"

Private Sub Form_Current()
    If Me.NewRecord Then
        Me.AllowEdits = False
        Me.AllowDeletions = False
    Else
        Me.AllowEdits = True
        Me.AllowDeletions = True
    End If
End Sub

3. Then you can have a button that toggles the "allow" properties so the user can edit the visible record. You could prompt for a password if only some people are allowed to edit.

There are numerous reasons for not creating multiple forms that show the same data but the reason for not separating the add/edit function is you need to duplicate your validation and that is very wrong.

I never knew this, I don't know if it is too late now..

 

[mloucel]

This is a bound form so can you show us the sql for the source query?

Since this is a bound form I really do not understand any of this.

The form opens up to some kind of PK. I assume it is AuthorizationID. That table or query includes foreign keys to
IPA, HealthPlan, Speiciality, and Specialist and the whole thing populates.
I do not understand this "pushing to combos." unless those combos are unbound. If they are bound they get their values from the source table.

is not on a query is bound to AuthorizationsT

 

[Pat Hartman]

I never knew this, I don't know if it is too late now..

We try not to bother you with trivia at this stage in development but fixing this "knowledge gap" error will probably save you work and will certainly clean up the app. It is never too late to correct this type of mistake. The changes are local and made to the version of the form you choose to keep. If you keep the add form assuming that is where your validation is, then just rename the form you are discontinuing with a "zz" prefix as something like zzfrmEditForm. Assuming you have name autocorrect turned off, this won't break anything you don't want to break. If you have name autocorrect enabled, turn it off first. You don't want it to "fix" any references to frmEditForm to make them zzfrmEditForm. That will just make a different error for you later. You WANT any references to frmEditForm to break so you can manually change them to frmAdd or whatever you want to call the new form. ALWAYS rename objects you want to discontinue. All the "zz" stuff drops to the bottom and you can clean up every few weeks once you are sure you don't need the old version. Name autocorrect being on will interfere with this practice. If you decide you want to leave it on ( I don't), then YOU MUST MANUALLY find all references to the form you are discontinuing and fix them to point to the form you are keeping.

Some people like name autocorrect and I admit I occasionally make use of it. But, since I know how it works, I know when I can use it to my advantage and when it is more of a hinderance than a help. Therefore, I turn it on when I want it's help with propagating an object name change but for the most part, I leave it off. I find it most useful in the very early days of the development when the tables are still local and subject to some flux. Once the tables are split or upsized, I always turn Name Autocorrect off by default and only back on when I specifically want its help. People who don't have firm naming standards like the way it can "fix up object name changes". I have firm standards and so very rarely change an object name once the object is saved.

You are still feeling very unsure of yourself but as you can see, you have good instincts. You KNEW the boss was wrong. You just didn't know how to convince him that you were right. Even I still have occasional trouble with headstrong clients who think they know more than I do. Not sure why they would be paying me but we won't go there. When I KNOW I am right, I may implement the manager's request but I always do it "right" behind the scenes. You didn't know how to do it right so that puts you at something of a disadvantage when you were told to do it "wrong" so you were not in a position to protect yourself

Even though I get paid by the hour to do stuff, I realllllly don't like doing the same stuff twice regardless of how much it pads my billable hours when I have to redo something multiple times because the boss insisted on a poor solution.

And finally, I don't actually use the solution I suggested. I use the on Dirty event to determine if a user should be allowed to edit. The method I suggested to you prevents the edit and that will make the boss happier. My method technically allows it but my code undoes the edit and prevents Access from saving any changes. But, some users are disturbed by the fact that they can type into what they think should not be updateable. My choice is predicated on the fact that almost all my forms have combos or text boxes that are used for searching and when you set AllowEdits to false it interferes with the operation of ANY search features your form offers. If you run into this problem, I have a third solution which uses a code loop to lock the fields instead of the blanket lock performed by AllowEdits and using the code loop, you can ignore any controls you don't want to lock because they are unbound and used only for searching.

 

[MajP]

This thing is pretty locked down can add a record so I can log in. Looks like you encrypt the passwords and I cannot get the new user form to work.
Can you add me/us a Password
User: AccessWorld
Password: AccessWorld

Breaking in to it with shift by pass is not helpful because to many tempvars and methods set.

 

[mloucel]

This thing is pretty locked down can add a record so I can log in. Looks like you encrypt the passwords and I cannot get the new user form to work.
Can you add me/us a Password
User: AccessWorld
Password: AccessWorld

Breaking in to it with shift by pass is not helpful because to many tempvars and methods set.

User Admin
Pass: 3Lmege77* is temporary password

 

[mloucel]

We try not to bother you with trivia at this stage in development but fixing this "knowledge gap" error will probably save you work and will certainly clean up the app. It is never too late to correct this type of mistake. The changes are local and made to the version of the form you choose to keep. If you keep the add form assuming that is where your validation is, then just rename the form you are discontinuing with a "zz" prefix as something like zzfrmEditForm. Assuming you have name autocorrect turned off, this won't break anything you don't want to break. If you have name autocorrect enabled, turn it off first. You don't want it to "fix" any references to frmEditForm to make them zzfrmEditForm. That will just make a different error for you later. You WANT any references to frmEditForm to break so you can manually change them to frmAdd or whatever you want to call the new form. ALWAYS rename objects you want to discontinue. All the "zz" stuff drops to the bottom and you can clean up every few weeks once you are sure you don't need the old version. Name autocorrect being on will interfere with this practice. If you decide you want to leave it on ( I don't), then YOU MUST MANUALLY find all references to the form you are discontinuing and fix them to point to the form you are keeping.

I know you are only trying to help me and I am extremely grateful for that, I am TRULY sorry if you misunderstood me, I am truly expressing my guts at this point, and you guys in this forum are the only ones at this point that can UNSCREW my SCREWED App, on the contrary I am MAD at myself for not asking for more help to avoid this situation, I know you would have given me advise but I choose NOT TO ASK, believing I was going to do it.
I am beyond thankful with you Pat, your knowledge and guidance have pushed me to the limit of my imagination and I have learned a lot from you and from everybody else, you know I used to program in COBOL then FoxBase but I stop there, so all this is so new to me, and I am naturally struggling with the Frankenstein I created.
So PLEASE do not think for a moment that you or ANYONE ELSE bother me, to the contrary, I would be already out of a Job if it wasn't for your help.
Maurice.

 

[mike60smart]

@Pat Hartman and @MajP
Thanks

I FULLY ACKNOWLEDGE THAT I HAVE TONS OF ERRORS THAT WILL MAKE ALL OF YOU RUN SCARED
I FULLY ACKNOWLEDGE THAT I AM SURELY TRYING MY BEST TO THE BEST OF MY ABILITY
I AM NOT ASKING TO CORRECT ALL THE ERRORS [PRETTY MUCH THE WHOLE THING IS AN ERROR POSSIBLY]

I just need help in that minor particular area where I have issues, and of course if you have a better way to accomplish what I need to do, please do so, but let me know so I can learn.

The BE was easy to upload but the FE is too big, so I am simply giving a link to the file in my Google Drive:

Access - Google Drive

drive.google.com

The data contained in the BE is either FALSE or Public Knowledge generally we got it from the Internet and does not contain any data that may be considered "privileged"

Unless RUN it will ask for a password.

I am desperate at this point, and I need to make sure this works, I have just found another MAJOR error when I am saving a NEW authorization in the PatientT since AuthorizationID Should NOT be there since this is a UNIQUE record [PatientT] and will never save that info, but I will correct that.

Please, Please ANY help to correct this or to make this work will be greatly appreciated, honestly at this point I am desperate, I don't know what to do and my stress level is beyond stress, I know I understand that ANYONE needs to help me in any way, but I am really desperate I have spent almost 4 months making every change imaginable to conform to what my boss wants and I am in my last straw, they are a bit frustrated that may come with consequences.

So that is my situation, if anyone can help me, I would much appreciate it.

IF you want to try the APP running you may run the Admin user, the Temporary password for that is [ 3Lmege77* ]

Maurice.

What is the name of the Form that you have the problem with?

 

[mloucel]

What is the name of the Form that you have the problem with?

AuthorizationsEditF

@Pat Hartman has a suggestion I just simply are so stress and nervous I screwed up completely, and I am getting sick, this was supposed to be simple and it end up a nightmare for me.

ANY help will be much appreciated, the User Name and Temp Password to get into the App is in POST #5

 

[MajP]

You will have to describe the problem and what you want to happen. Everything looks like it works. Not sure what it is supposed to do.
I open the AuthorizationEditF
and to me it all seems to work so not sure what you want to happen.
The combos which are bound to the table all populate correctly. I can change the combos changing the underlying foreign keys. The cascade works for specialty specialist.

 

[mike60smart]

AuthorizationsEditF

@Pat Hartman has a suggestion I just simply are so stress and nervous I screwed up completely, and I am getting sick, this was supposed to be simple and it end up a nightmare for me.

ANY help will be much appreciated, the User Name and Temp Password to get into the App is in POST #5

The AuthorizationEditF Form opens. Do you want the unbound Controls which display as Grey Colour to be enabled to Edit??

 

[mloucel]

You will have to describe the problem and what you want to happen. Everything looks like it works. Not sure what it is supposed to do.
I open the AuthorizationEditF
and to me it all seems to work so not sure what you want to happen.
The combos which are bound to the table all populate correctly. I can change the combos changing the underlying foreign keys. The cascade works for specialty specialist.

Yes I am sorry.

The User has to do this:
1) for a new Authorization, He has to ADD [is a new DB] a new Patient [Menu Maintenance / Add Patients]
2) Once the patient is Added Goto Authorizations / ADD Authorization.
3) Once you Have an Authorization [Which will show in the bottom of the MENU] EU can access the Auth from there to EDIT or goto Authorizations SEARCH/EDIT, there he searches the Authorization and Edits..

There is where the problem is.
Before all of those fields where grayed out, now we figured out the boss was wrong and the EU DO NEED to edit those field IF they need to be changed, so that is where I need to populate those fields with the Data from AuthorizationsT but then be able to change those fields as well if that is the case and save the changed ones if any to the AuthorizationsT.

One of my Major headaches has been the last 2 combos since the last combo is a Cascading Combo Box coming from Specialty.

I hope this helps.

And thanks.

 

[mloucel]

The AuthorizationEditF Form opens. Do you want the unbound Controls which display as Grey Colour to be enabled to Edit??

Check the explain I gave @MajP in Post #16, so you can see where my problem resides, check the ORIGINAL data that is in the DB, or create a new record, when the AuthorizationsEditF populates the data on those fields is NOT the one saved in the original record.
AND they DO NOT need to be grayed out, that was before but my boss was WRONG, she wanted it like that but the EU was able to prove her Wrong, they need to be edited later on.

 

[mike60smart]

Check the explain I gave @MajP in Post #16, so you can see where my problem resides, check the ORIGINAL data that is in the DB, or create a new record, when the AuthorizationsEditF populates the data on those fields is NOT the one saved in the original record.
AND they DO NOT need to be grayed out, that was before but my boss was WRONG, she wanted it like that but the EU was able to prove her Wrong, they need to be edited later on.

Sorry but your explanation makes no sense to me at all.

 

[mloucel]

Sorry but your explanation makes no sense to me at all.

This is the flow of the App:

- EU enters a new patient using Menu Maintenance ADD Patients
- EU then uses Menu Add Authorization
- EU Enters an IPA, Health Plan and Specialty, and Specialist
-- Specialty Determines who are the Specialists within that Area so is a Cascade Combo, Specialist is according to what is the Specialty
- EU Saves the Record, we can QUIT for now.

= EU has to modify that Authorization
= EU goes to Menu Search / Edit and search for the Authorization using 1 of the provided fields
= EU then is in the Edit Authorizations Form.

This is where the problem is, if you remember what fields you pick when you enter the Authorization Originally [make sure are not the defaults], when I open the EDIT form, those records are not the ones displayed for IPA, HealthPlan, Specialty and Specialist.

I don't know how to populate those fields with the correct ORIGINAL records and then if the EU decides to change them, save those changes to the AuthorizationsT table.

I hope this is better.

 

[MajP]

This is where the problem is, if you remember what fields you pick when you enter the Authorization Originally [make sure are not the defaults], when I open the EDIT form, those records are not the ones displayed for IPA, HealthPlan, Specialty and Specialist.

The form is Bound to the authorization table. You open it to a specific authorization id

DoCmd.OpenForm "AuthorizationsEditF", , , "[AuthorizationID] =" & Me![AuthorizationID], OpenArgs:=2

So the displayed values are most certainly the ones displayed for IPA, HealthPlan, Speciality, and Specialists. How could they not be?
You open to a specific authorization which has foreign keys bound to IPA, HealthPlan, Specialtiy, Specialist.

I am so lost this appears to do exactly what you are expecting.
If the users change the combos they change the FKs and the authorization is update.

The only thing I can think is that you are just confusing yourself because at one time this was unbound. Then you were forced to bind it. You are thinking like it is unbound. You use to do this.

With Forms("AuthorizationsEditF")
.PatID = PatientID
.FN = PFirstName
.LN = PLastName
.PatDOB = PDOB
.PatPhone = PPhone
.DoctorName = DocName
.IPaNam = IpaName
.HealthPlanName = HealthPlan
.SpecialtyName = Specialty
.SpecialistName = DrName
End With

but now it is bound at least to some fields. So you cannot set values of IPA, Speciality, ...
Because you do not need to. You just move to the correct authorization and the magic of Access bound forms works.
If it was me I would bind all of those controls or and base the form off a query. Then it would show name, dob, etc. Just ensure you lock those uneditable fields.