best way to stop accidental deletes of data from field (1 Viewer)

[hbrehmer]

Hi all,
I have an on going issue. I have some users who use two screens and occasionally will forget where their cursors are and accidentally change data or delete in a field. What is the best way to address this? I think adding a message script on the fields, asking if you want to change or delete, but I want some opinions before I go forward with this challenge.

Thanks!

Heidi

 

[bob fitz]

If it's a problem then that should help to overcome it. If multiple fields are legitimately changed sometimes, then it might be better to do the validation in the form's Before Update event rather than in each bound control

 

[theDBguy]

Hi. That's a tough one. Definitely, using a warning and prompting for confirmation would stop the accidental edits and deletions, but it could get annoying for non-accidental users.

 

[hbrehmer]

Exactly! I don't want the changes to become annoying to the user, but I have two employees that constantly delete the field, not realizing that they are doing it. It becomes frustrating to other employees who have to search for the record, but find that the field is blank. Ugh! That is why I need professional advice here.

 

[bob fitz]

Exactly! I don't want the changes to become annoying to the user, but I have two employees that constantly delete the field, not realizing that they are doing it. It becomes frustrating to other employees who have to search for the record, but find that the field is blank. Ugh! That is why I need professional advice here.

If your users "login" then you could tailor the validation to only run for certain users

 

[Gasman]

Exactly! I don't want the changes to become annoying to the user, but I have two employees that constantly delete the field, not realizing that they are doing it. It becomes frustrating to other employees who have to search for the record, but find that the field is blank. Ugh! That is why I need professional advice here.

Sounds like that field should not be cleared in the first place?

 

[hbrehmer]

Now that I'm thinking this through, I'm wondering if I make a separate form that is a view only for information, and have a Add/Edit form that is password protected that only authorized employees can change. Thoughts?

 

[bob fitz]

You could make it so that Null and ZLS can't be saved

 

[theDBguy]

Now that I'm thinking this through, I'm wondering if I make a separate form that is a view only for information, and have a Add/Edit form that is password protected that only authorized employees can change. Thoughts?

I would prefer using their network login (username) rather than a password.

 

[hbrehmer]

I would prefer using their network login (username) rather than a password.

I agree. That would be the simplest solution. I just haven't done that kind of programming yet. Not sure how to accomplish that. I'm still in the learning phase. I'll have to do more research on how to write that kind of code.

 

[hbrehmer]

So I thought I'd try having any change to the field trigger a message box and confirmation (surprising how many customers change their name these days). But I keep getting an error. Can someone help me?

Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please feel free to Remove this Comment
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

Private Sub CustomerName_AfterUpdate()
    Dim x As Integer
    Dim NewName As Integer
    Dim OrigName As Integer
    NewName = Me.CustomerName.Value  (Here is where the DeBug gets me)
    OrigName = [Forms]![frmCustomerMain]![CustomerName].OldValue  'Me.CustomerName.OldValue
    If NewName <> OrigName Then
        x = MsgBox("Are you sure you want to change Customer Name?", vbYesNo + vbQuestion)
        If x = vbYes Then
            DoCmd.Save
        Else
            CustomerName.SetFocus
            'Me.Undo
            CustomerName.Value = OrigName
            'Cancel = True
        End If
    End If
End Sub

 

[The_Doc_Man]

If you are in the situation where a user shouldn't have the ability to modify certain fields, there is a conceptually simple method. Tedious ... but simple.

Step 1: In the form's OnLoad routine, determine if the user is a restricted user based on their login name or whatever other method you decide. I had a table of expected usernames and one of the fields in that table was a user Role - just a code showing whether the user was a good guy or a dolt.

Step 2: For every control on that screen that these users should not change, set .Locked = TRUE. If this is a "good" user then you can decide to not lock the controls.

Step 3: The method to identify the controls to be treated this way is up to you, but if you have not yet employed the .Tag property, you can put a code word or other identifying value in the relevant .Tag slots and then in the OnLoad routine, search the controls in a For Each control loop and for those that are tagged, apply the lock as needed.

 

[The_Doc_Man]

Your post and mine crossed. Are you saying that a customer can change his own name? If you can get the domain name, that shouldn't happen very often if at all. This is why you would use an external trusted identification method.

 

[Gasman]

So I thought I'd try having any change to the field trigger a message box and confirmation (surprising how many customers change their name these days). But I keep getting an error. Can someone help me?

Private Sub CustomerName_AfterUpdate()
Dim x As Integer
Dim NewName As Integer
Dim OrigName As Integer
NewName = Me.CustomerName.Value (Here is where the DeBug gets me)
OrigName = [Forms]![frmCustomerMain]![CustomerName].OldValue 'Me.CustomerName.OldValue
If NewName <> OrigName Then
x = MsgBox("Are you sure you want to change Customer Name?", vbYesNo + vbQuestion)
If x = vbYes Then
DoCmd.Save
Else
CustomerName.SetFocus
'Me.Undo
CustomerName.Value = OrigName
'Cancel = True
End If
End If
End Sub

Yet you could not tell us the error?
I'll have a guess. Why would CustomerName be an integer?

 

[hbrehmer]

Your post and mine crossed. Are you saying that a customer can change his own name? If you can get the domain name, that shouldn't happen very often if at all. This is why you would use an external trusted identification method.

No, the customer can't change their name, but I am surprised how often we have to update a customer's name in our database. I guess businesses are sold frequently.

I like your method. I do have a table of users, but have not initiated a login process yet. I was copying the one from Tradewinds database, but with all the building I was doing, I got away from it thinking I could revisit it once I have approvals on the program from my superiors.

 

[hbrehmer]

Yet you could not tell us the error?
I'll have a guess. Why would CustomerName be an integer?

Aha! You put your finger on my problem. CustomerName is NOT an integer. Thank you!

 

[hbrehmer]

Thank you to everyone who gave me input. I have a lot of possibilities here. For now, I'm going to use the After_Update code until I get password protection set up.

You are awesome folks!

 

[Isaac]

Now that I'm thinking this through, I'm wondering if I make a separate form that is a view only for information, and have a Add/Edit form that is password protected that only authorized employees can change. Thoughts?

This is the approach I often take. I like separating different tasks like that. Unless of course, it's absolutely imperative to have both in one view.

 

[theDBguy]

I agree. That would be the simplest solution. I just haven't done that kind of programming yet. Not sure how to accomplish that. I'm still in the learning phase. I'll have to do more research on how to write that kind of code.

Hi. Sorry, just replying to this specific post. Maybe this will help; otherwise, please continue with your current approach. Good luck!

Code Snippets

 

[Pat Hartman]

Thank you to everyone who gave me input. I have a lot of possibilities here. For now, I'm going to use the After_Update code until I get password protection set up.

Sorry but this is the WRONG event. You need to use the BeforeUpdate event so that you can cancel the save and prevent the bad data from becoming permanent. If you use the control's BeforeUpdate event, you will STILL need code in the form's BeforeUpdate event because if a user doesn't dirty a control, its events do not run so you would always allow a new record to be added even if the CustomerName is empty because your code on the control didn't run. Unless, I want to give immediate feedback, I use the Form's BeforeUpdate event for all validation. That way, the validation is always in one place and I don't need to do it twice. Also, you should make fields that cannot validly be empty required and set their AllowZeroLengthString properties to No.

Private Sub Form_BeforeUpdate(Cancel)
    If Me.CustomerName & "" = "" Then
        msgbox "Customer Name is required.", vbOKOnly
        Me.CustomerName.SetFocus
        Cancel = True
        Exit Sub
    End If
    If  Me.CustomerName <> Nz(Me.CustomerName.OldValue,0) Then
        x = MsgBox("Are you sure you want to change Customer Name?", vbYesNo + vbQuestion)
        If x = vbYes Then
        Else
            Msgbox "Name was not saved.  Press Save again to save other changes.", vbOKOnly
            Me.CustomerName.SetFocus
            Me.CustomerName = Me.CustomerName.OldValue
            Cancel = True
            Exit Sub
        End If
    End If
End Sub

This code needs more work because as written, it is only validating the Customer Name.

PS, I'm a little confused since you named the field CustomerName rather than CustomerID. Is it text or numeric? This code assumes numeric because your code assumed numeric.
Always use "Me." when referencing form/report controls. It is more efficient and gives you intellisense.
When working with bound forms, understanding which event to use for what purpose is the key.