On Trapping Refresh Key and Escape Key. (1 Viewer)

[spenzer]

Hi!

I have bound forms all over my database and it's causing some significant unwanted updates every now and then; even when users does not intend to.

I have discovered that the two main culprits so far are the:

1. Escape key (when it can't detect dirty event.)
2. Refresh key (when user press it, whatever is in the form whether it's filled out or not, it will be updated automatically which is a major pain.)

I have already trapped my Escape Key and as of now, I intend to
trap Refresh key also.

My question:
A. Do trapping both of that keys on selected forms would somehow backfire on the integrity,function and overall stability of my database in the long run?

B. Is there any other way to secure entry and tighten up control over update and saving process while preventing accidental unwanted updates?

I can smell unbound forms, but i want answers for bound forms only if possible.

 

[Pat Hartman]

If you want to trap unintentional updates, you need to do it in the correct event - the form's BeforeUpdate event. The form's BeforeUpdate event is the single most important event for forms. The buck stops here. Nothing gets saved to the database before this event fires so if you want to trap unintentional updates, your code goes HERE. I don't like prompting the user unnecessarily because they become desensitized to messages and eventually blow through all of them - even the important ones. To get around prompting for every save, I create a form level variable. I unset it in the Current event of the form and I set it in the Click event of my SAVE button. Then I check it in the BeforeUpdate event of the form. If it is true, meaning that they pressed the save button, I let the update proceed. If it is false, I prompt them and ask if they really want to save. Then I Cancel the update and exit the procedure if they say No and continue with the form level edits if they say yes. I do the prompt before the edits because I don't want to raise error messages if they didn't really intend to save. It just confuses them.

 

[spenzer]

If you want to trap unintentional updates, you need to do it in the correct event - the form's BeforeUpdate event. The form's BeforeUpdate event is the single most important event for forms. The buck stops here. Nothing gets saved to the database before this event fires so if you want to trap unintentional updates, your code goes HERE. I don't like prompting the user unnecessarily because they become desensitized to messages and eventually blow through all of them - even the important ones. To get around prompting for every save, I create a form level variable. I unset it in the Current event of the form and I set it in the Click event of my SAVE button. Then I check it in the BeforeUpdate event of the form. If it is true, meaning that they pressed the save button, I let the update proceed. If it is false, I prompt them and ask if they really want to save. Then I Cancel the update and exit the procedure if they say No and continue with the form level edits if they say yes. I do the prompt before the edits because I don't want to raise error messages if they didn't really intend to save. It just confuses them.

Thank you! Very clear and sound advice.

During development I have always been thinking of a way to validate my data on a form which will prevent the form from closing if it did not pass the rule. So I decided to put all my validation rules on the On click event of a command button which is used to close the form. It worked well. But lately; I just found out these unwanted updates and the culprit is those two Keys and who knows maybe other things i don't know the trigger about.

So following your advice, I think I would need to copy all my existing validation rules in the On click event to the Before update event of the form. That way i get the best of both worlds; which is preventing accidental/unwanted updates and preventing forms from closing when they violate the rule.

 

[Pat Hartman]

Yes, by all means move your code to the correct event. I also suggest the form level variable to prevent annoying prompts to your user. I forgot to mention the Form's AfterUpdate event resets the variable so that if they make a change to a record they just changed, they get prompted or not depending on whether they push the save button or just navigate away from the record.

 

[spenzer]

Yes, by all means move your code to the correct event. I also suggest the form level variable to prevent annoying prompts to your user. I forgot to mention the Form's AfterUpdate event resets the variable so that if they make a change to a record they just changed, they get prompted or not depending on whether they push the save button or just navigate away from the record.

I am interested in your suggestion regarding form level variable but i don't quite grasp how to implement it, so I'm kind of wondering where to start experimenting on it.

What I have right now is a very simple kind of validation checking. I have all the rules set to both Before update event of the form and the same rule applies on the On click event of a command button which is used to close the form.

I don't have a dedicated save button in the form. Instead, I only have one command button named cmdClose which handles both the exit and automatic save of the data in the form when it passed all validation. Now, whenever there is a violated rule, as soon as the user pressed the cmdClose button; instead of closing the form, it will prompt for a warning message to whatever violation is committed and will setfocus on that offending control.

So prompt happens only when user is trying to close the form while committing violation to rules and not on control by control basis if that's what you are implying.

 

[Pat Hartman]

Form level variable:

Option Compare Database
Option Explicit
[COLOR="MediumTurquoise"]Public bSaveData As Boolean[/COLOR]
Private Sub Form_Open(Cancel As Integer)
...
End Sub

You don't need code in separate events. You only need code in the BeforeUpdate event. There are at least two other obvious ways to close a form - the "x" in the corner of the form (unless you have disabled it) and the "x" in the corner of the Access Window. Both will cause the Form's BeforeUpdate code to run but will not invoke your close button. If you want the button to both save and close, it would be best to give it a name that indicates those functions - "Save & Close". Given the dual nature of the button, you should have a second one that closes without saving otherwise you give your users a conundrum. How to exit without saving.

One way to handle the code in many places is to create a dedicated sub that just does the editing and returns True or False. You would then call this edit module from the BeforeUpdate event and you could call it elsewhere also.

The code in your save and Exit button would be something like:

If MyEdits() = True Then
    Me.Close
Else
    Msgbox "You may not close the form while there are edit errors.  Either fix the errors or press the Exit without Saving button.",vbOKOnly
    Exit Sub
End If

 

[gemma-the-husky]

i think the problem is that Access has a certain "user paradigm"

it would be better to design in accordance with that paradigm, than try to develop against it.

one point is that virtually any change to data in bound forms is saved in numerous ways, without requiring a specific save action. experienced Access users expect this behaviour. when you change data, it is immediately saved to the "shared" database, so that other users become aware of the change. this is a positive, not a negative feature.

it can happen in so many ways that preventing it is virtually impossible. the only reliable way is not to use bound forms, which is a much harder way to develop.

it is much better to ask users why they are changing data, if they do not mean to. and what is the "refresh" button you refer to. what does it do? what do you think it does?

 

[Pat Hartman]

it can happen in so many ways that preventing it is virtually impossible. the only reliable way is not to use bound forms, which is a much harder way to develop.

Preventing it is not virtually impossible. In fact, it is quite simple. You simply have to understand one single event. The one single event you must understand is the Form's BeforeUpdate event. No update gets past this event regardless of what the user does. Short of hitting the power off or forcing a system reboot, this event is the LAST event that gets executed BEFORE a record is saved. This is the ONLY event you need code in to stop a save. The code that stops a save is

Cancel = True

How hard is that?

 

[spenzer]

Form level variable:

Option Compare Database
Option Explicit
[COLOR="MediumTurquoise"]Public bSaveData As Boolean[/COLOR]
Private Sub Form_Open(Cancel As Integer)
...
End Sub

You don't need code in separate events. You only need code in the BeforeUpdate event. There are at least two other obvious ways to close a form - the "x" in the corner of the form (unless you have disabled it) and the "x" in the corner of the Access Window. Both will cause the Form's BeforeUpdate code to run but will not invoke your close button. If you want the button to both save and close, it would be best to give it a name that indicates those functions - "Save & Close". Given the dual nature of the button, you should have a second one that closes without saving otherwise you give your users a conundrum. How to exit without saving.

One way to handle the code in many places is to create a dedicated sub that just does the editing and returns True or False. You would then call this edit module from the BeforeUpdate event and you could call it elsewhere also.

The code in your save and Exit button would be something like:

If MyEdits() = True Then
    Me.Close
Else
    Msgbox "You may not close the form while there are edit errors.  Either fix the errors or press the Exit without Saving button.",vbOKOnly
    Exit Sub
End If

I'm still somewhat on a limbo with this. Although, I think I get the overall picture of what you are suggesting. "A centralized validation rule container that I can reuse in every part of the form by just calling it whenever it's needed." Which frankly sounds very nice and handy and powerful as well.

What I'm not sure is the implementation detail of it; in order to accomplish this, am I going to need to have high level or somewhat advanced vba programming skill? Because I've never wrote vba in class module or even a separate standard module. All I ever done is write vba directly on form's events.

 

[spenzer]

i think the problem is that Access has a certain "user paradigm"

it would be better to design in accordance with that paradigm, than try to develop against it.

one point is that virtually any change to data in bound forms is saved in numerous ways, without requiring a specific save action. experienced Access users expect this behaviour. when you change data, it is immediately saved to the "shared" database, so that other users become aware of the change. this is a positive, not a negative feature.

it can happen in so many ways that preventing it is virtually impossible. the only reliable way is not to use bound forms, which is a much harder way to develop.

it is much better to ask users why they are changing data, if they do not mean to. and what is the "refresh" button you refer to. what does it do? what do you think it does?

I think that the automatic saving feature in access when using bound forms is still a great feature. It certainly saves time and helps beginner-intermediate programmer develop and see results fast which will inspire to do more and more until we get to the next level. Or at least, that's what I'm experiencing right now. And I think, the security hole I'm experiencing right now is part of getting to that next level thing. Though, it's a truly a major pain.

To answer what the "refresh" button is, it is the F5 key in the keyboard that is used to refresh current window. But in access if you press that in a bound form when user already have started filling up the form; it will automatically save bypassing all validation rules that is not stored in before update event. If you ask me what is their purpose of doing it; up to this day, I'm still not sure why. But I don't have power users here, some users are not even tech or computer savvy so maybe some are experimenting on keys; again, I'm not sure. But I've warned them again and again not to press f5 key when access is open;apparently, someone has the habit of triggering it.

 

[Pat Hartman]

Once you get your code into the correct event, it won't matter what they do. Your code will trap the edit.