Solved Form_BeforeUpdate error 2115

[mloucel]

Hello All:
I've been trying to fix a very small routine I had IN A FORM CALLED [ PatientEditF ].
I was instructed to correct my code to do it under the " Form_BeforeUpdate " for my routine checking my data.
I did, and I rebuilt the whole form, ground up.

Now I am receiving the Runtime error 2115.

I have searched here [AWF] and some other [stackoverflow], etc, where they discuss SIMILAR issues to mine but no matter what I try, I cannot figure out how to fix the issue.

I have the form and the code attached.

in the original form I have 3 buttons, at the end, Search [for a new record to edit], Save, and quit.

IN MY TEST DB, to avoid a large DB I got rid of the search button and code, since that worked fine and was not needed to replicate the problem; ALSO the FORM has ENABLED the NAVIGATION BUTTONS, so that you can move from record to record to test, ALL RECORDS ARE FAKE.

At this point I cannot save any data, when I use the BeforeUpdate I have tried to check for all possible errors [Blank fields, Changed Fields] which the end user can actually do, when I hit the save button the BeforeUpdate checks and if everything is correct, should ask the user if he/she wants to save the data and return to the form withe the saved data, but I get the 2115 , then I changed the line in me.dirty=false in the beforeupdate to REM, letting the button do the save, and I got into a LOOP, and finally the "You can't save this record" error and of course the record is not saved.

Now I tested the QUIT button, make any change to the data, BeforeUpdate checks, display the message, and answer NO to save the data, and the button works, no data saved, now I test YES, back again to 2115, I cannot change this time the line "Me.Dirty = False" because in my view defies the purpose.

I'm sorry if you laugh of such D...b error but I don't really know what to do.

Any help will be appreciated.

Maurice.

 

[Pat Hartman]

1. NEVER undo something the user typed unless you are not going to allow him to save anything. This will only leave the users cursing your offspring for eternity. This is the most unfriendly thing you can do when coding behind a data entry form. If a user forgets to enter data in a single field, you punish him by removing everything he typed in ALL controls. Not nice.
2. If the user backspaces to clear data, this leaves ZLS which are different from nulls. Your validation needs to account for this so I changed it.
3. I don't know what you were trying to do with the .OldValue checks so I just deleted them. They are probably what was clearing the record.
4. I don't know why you didn't just use Me.SomeField.SetFocus but I left the code as you wrote it. The expectation is that the user will just type over what was in the field. You may just be making correcting the error harder by not making the text selected.
5. I removed the code that prompts for a save. The users will find this amusing for about 5 minutes and then be annoyed with it. All you are doing is training the users to ignore your prompts since they have no meaning. There is a better way to force them to use your save button if that is what you really want to do. If you give them a save button and they don't use it, then I don't have a problem with the prompt. But, if you have a save button and you want to train them to use the save button to save, then you need some additional code so you can distinguish between a save caused by Access rather than one that was caused by pressing the save button so you know when to complain.

 

[mloucel]

1. NEVER undo something the user typed unless you are not going to allow him to save anything. This will only leave the users cursing your offspring for eternity. This is the most unfriendly thing you can do when coding behind a data entry form. If a user forgets to enter data in a single field, you punish him by removing everything he typed in ALL controls. Not nice.
2. If the user backspaces to clear data, this leaves ZLS which are different from nulls. Your validation needs to account for this so I changed it.
3. I don't know what you were trying to do with the .OldValue checks so I just deleted them. They are probably what was clearing the record.
4. I don't know why you didn't just use Me.SomeField.SetFocus but I left the code as you wrote it. The expectation is that the user will just type over what was in the field. You may just be making correcting the error harder by not making the text selected.
5. I removed the code that prompts for a save. The users will find this amusing for about 5 minutes and then be annoyed with it. All you are doing is training the users to ignore your prompts since they have no meaning. There is a better way to force them to use your save button if that is what you really want to do. If you give them a save button and they don't use it, then I don't have a problem with the prompt. But, if you have a save button and you want to train them to use the save button to save, then you need some additional code so you can distinguish between a save caused by Access rather than one that was caused by pressing the save button so you know when to complain.

Hi Pat, thanks for taking the time to correct me, I'm learning.
#1, I got it as a Failsafe, we are dealing with Patient names, if the EU does something wrong and then remembers they should have never touch the name, people have a 3 seconds memory and after they are doomed, I have had that headache IN EXCEL above all, which is so simple, some users have made some bad mistakes I have to backup the WS every 4 hours.
#2 I tried back space and thou it doesn't save and checks for blanks, the EU will panic when they see a blank space, and they will go back to make mistakes, but if somehow I am able to put the OLD value back If there was any, then I solve the problem.
#3 The Old.value was my way to check when the EU changes either the name or any value, Since it is a matter of making sure they do not make mistakes [Already I know them] by letting them know that the program detected a change can and will force them to say YES or NO, so if the yes is applied they are responsible for the name change [This will only be used by Supervisors, but yes I have less hair from them as well]
#4 the extra code was to make sure when the program returns to that value that the WHOLE field IS NOT selected but the cursor placed at the beginning of the field, so if they inadvertently press any key the damage will be minimum.
#5 I got your point here and yes I like it as well, but I need to return a prompt or I need to find a way to check when things go wrong, how can I prevent the user to make the mistake a bigger mistake.

If that make sense, I would much appreciate your advise, I rather take your words and advise, I understand is going to be difficult for me to change my ways and my users ways but I thinks is time for a change.

Please if you have time let me know what do you think of my answers.

Maurice.

 

[arnelgp]

might not be what you want.

 

[Pat Hartman]

Hi Marcel,
#1 erasing what the user typed is not a failsafe and is totally unnecessary. If an error exists, it exists. So, if the user attempts to save without correcting, the error is still there. Erasing data the user typed is a really bad idea except when you are never going to allow them to save anything. If the users are so bad at data entry that you feel the need to do this, then you probably need to switch to a logging method where the modified record is always logged after it is saved, along with the name of the person doing the update so you can keep track of who needs training. "Cancel = True" tells Access to leave the bad data alone but to not save the record. Presumably, the user will correct the error and press the save again. The same error will be reported again if the user didn't fix it.

#2 The esc key will return the previously saved value. Pressing the esc key twice will back out all changes to the current record. Give the users an undo button if they can't remember how to use the esc key. Or, give them an "Exit without saving" button.

#3 Comparing the current value to the old value does not identify mistakes. All the code is doing is checking whether or not the field contains a single character. That isn't validation. You don't even have any sanity checks on the date. DOB can't be in the future can it? How about a thousand years in the past?

#4 I get what the code does and I said that, but the entire point is that you are sending the user back to that control specifically because it is EMPTY so the code is doing essentially nothing since the field is always empty and so will naturally be sitting at the first position simply by setting focus to the control. The code is innocuous but is not necessary and so obfuscates the necessary code. Unnecessary code is not as bad as incorrect code but it adds unnecessary complication and makes people stop and try to figure out what you were trying to do. Then they become confused when they can't figure out why you thought it was necessary when they recognize that the code only runs when the control is empty.

#5 You are not actually validating anything so I'm not sure what mistake you are expecting to capture. If you are trying to force the user to use the button to save, then I will outline the method.

• Define a global boolean variable or TempVar. Name it bSavePressed.
• In the on Dirty event, set bSavePressed to False
• In the Save button's click event, set bSavePressed to True
• As the FIRST statement of the form's BeforeUpdate event, check the value of bSavePressed. If it is False, display an appropriate message and set focus to the Save button and exit. If the value of bSavePressed = True, then set the value to False and continue with the validation because the user pressed the save button so you know he wants to save the record.

OK, nothing you said changed my mind about any of my comments

 

[mloucel]

might not be what you want.

Yes... And I can see what you did, and I can see where my errors were, and thanks as well for leading me in the new use [for me] of the MMB, is simple, yet accomplish all the tasks I needed with failsafe, I actually tested the code with one of the users and just like I wanted she made a mistake and started asking me questions, She was kind of frustrated, pressed the QUIT button and problem solved, the Save button does exactly what they need, and at the end she was happy.
I am going to study a bit more the code and compare all my other routines, and see where can I make it better.
Thanks @arnelgp

 

[mloucel]

Hi Marcel,
#1 erasing what the user typed is not a failsafe and is totally unnecessary. If an error exists, it exists. So, if the user attempts to save without correcting, the error is still there. Erasing data the user typed is a really bad idea except when you are never going to allow them to save anything. If the users are so bad at data entry that you feel the need to do this, then you probably need to switch to a logging method where the modified record is always logged after it is saved, along with the name of the person doing the update so you can keep track of who needs training. "Cancel = True" tells Access to leave the bad data alone but to not save the record. Presumably, the user will correct the error and press the save again. The same error will be reported again if the user didn't fix it.

#2 The esc key will return the previously saved value. Pressing the esc key twice will back out all changes to the current record. Give the users an undo button if they can't remember how to use the esc key. Or, give them an "Exit without saving" button.

#3 Comparing the current value to the old value does not identify mistakes. All the code is doing is checking whether or not the field contains a single character. That isn't validation. You don't even have any sanity checks on the date. DOB can't be in the future can it? How about a thousand years in the past?

#4 I get what the code does and I said that, but the entire point is that you are sending the user back to that control specifically because it is EMPTY so the code is doing essentially nothing since the field is always empty and so will naturally be sitting at the first position simply by setting focus to the control. The code is innocuous but is not necessary and so obfuscates the necessary code. Unnecessary code is not as bad as incorrect code but it adds unnecessary complication and makes people stop and try to figure out what you were trying to do. Then they become confused when they can't figure out why you thought it was necessary when they recognize that the code only runs when the control is empty.

#5 You are not actually validating anything so I'm not sure what mistake you are expecting to capture. If you are trying to force the user to use the button to save, then I will outline the method.

• Define a global boolean variable or TempVar. Name it bSavePressed.
• In the on Dirty event, set bSavePressed to False
• In the Save button's click event, set bSavePressed to True
• As the FIRST statement of the form's BeforeUpdate event, check the value of bSavePressed. If it is False, display an appropriate message and set focus to the Save button and exit. If the value of bSavePressed = True, then set the value to False and continue with the validation because the user pressed the save button so you know he wants to save the record.

OK, nothing you said changed my mind about any of my comments

Hello Pat..
Thanks so much for caring..
I have received a file from @arnelgp and it does exactly what I needed, your DB was awesome as well, I had to combine both examples and LEARN, but now has perfectly a failsafe code.
And I am sorry for confusing you more, the Idea on #1 was not erase what the user typed, but simply to make sure, that what they typed was right, if they then [at the message point] realize they screwed up, they have now a way to go back [failsafe].
This part of the program will be used only by 2 people in each office, and NOT always, it will be like a once in a moon, why?:
In our original EHR software, ladies in the Front Office [FO] enter that information, unfortunately for me, is the OLD "GIGO", that software is CLOSED so once they enter the name cannot be corrected, because is now associated with the rest of the chart, so in their logic, changing the name is catastrophic since it is "MARRIED" to the rest of the data.
This part of the software will correct that, by leaving the DEMOGRAPHICS [DEMO] of the patient in its own DB, so the rest of the Data associated with the name is safe.
I redirected all that data to see my DB and take DEMO from there instead of the DB they have.
Now if there is a mistake in the name say:
Mari Jones should be MARY Jones.
the Supervisor ONLY can actually come and CORRECT the name with all the failsafe capabilities, now you sort of gave me an idea that I have applied before and I have the code:
" then you probably need to switch to a logging method where the modified record is always logged after it is saved "
I will create a LOG database, where I will save all the changes, with the name and date/time of the User that did the changes, so if there is any issues later I can easily see who did what and I have a way to go back in time [I will have to use the utility and make the changes myself], but it will be a once in a lifetime thing.
Names and DOB and TELNO of the patients have been such a nightmare, we think that the EU will always do the right thing, but they don't , I have seen:
Bad Names
All uppercase
All lowercase
a few Upper and the rest lower
the last name where the FN should be
a period in the name or ln
I mean I think you get the point.
This program will correct those errors at the tie we found them, just as an example, [in another routine] when the EU enters the patient name, it doesn't really matter how they do it, FIRST there is a routine that will properly put the first letter of the name in Uppercase and the rest of the letters will be LOWERED, I am trying to expand that software routine to check for extra blank spaces, and we will never have names like McPato or Mc'Donalds but I am thinking in adding something like that as well [whenever I have time].

Maybe this will give you a better perspective of the WHY I needed the routine to work in this way.

One thing will be interesting to see and I believe is missing in every book or every place I found that teaches how to program in access or any other:
We learn by example, but hardly anyone tells me [ in my case ] , the reason why .. I learn the code, I learn the routines, I learn the words, the lingo, and a million things, but THE WHY..
and by WHY I mean, making a book [a video series will be better] and create a whole application, from bottom up, where you can teach the LOGIC of why this works better in this situation and not here or there, how do you manage an issue with the EU, the infamous WHAT IF SCENARIOS, That the EU can actually do and make mistakes.
See in this particular case you and @arnelgp have taught me the code and corrected my mistakes, you have even taken the time to make me re-think my OLD ways, but is just only the BeforeUpdate, but knowing the WHY and seeing the WHY applied, that makes a difference, is not just knowing how to use it, but WHY.

Thank you so much to both of you [ @Pat Hartman and @arnelgp ] for your patience and your guidance.

Maurice.

 

[Pat Hartman]

If you want to prevent the user from changing, the name. That is trivial. Either lock the name fields in the current event of the form OR add code into the name control's BeforeUpdate event that checks if this is a new record. If it is new, the data entry is allowed. If it is not new, THIS is the time you use the Me.controlname.Undo as well as the cancel = true

I prefer the validation code rather than the locking method.

 

[Pat Hartman]

Maybe this will give you a better perspective of the WHY I needed the routine to work in this way.

But the code isn't doing any actual validation. You can never identify bad names. You can identify duplicate names which may not be bad. You can make sure the first letter of each word is capitalized and only those letters but that isn't necessarily correct either. Depends on your language and local variants. You can't determine if FN/LN is swapped. You validate the things you can validate, and the rest has to be left to the users. If you want the user to have three choices - red, yellow, blue - you give him a combo that restricts the entry to the three choices but you can't prevent him from selecting blue when he meant red.