Solved Macro to avoid accidental record edits (1 Viewer)

[abnorth]

Hi!

I have some users that like to randomly click on things and delete text for no reason. This results in accidental changes to some of our data.

I’ve locked everything on the form that isn’t used for adding/editing records, but I would like to add a prompt when data is about to changed so that there are no accidental edits from random clicking.

I assume using BeforeUpdate would be the best way to go about this?

Using the macro builder I’ve managed to display an “Are you sure” message box with yes/no options to click. However, both options gives me an error 2046 that says that the command or action isn’t available now. If I delete the Else clause the yes option works perfectly with the data being changed, but I still can't figure out how to have the "no" option cancel any changes. What am I missing/doing wrong?

If possible, I would like to stay away from using VBA and just use the macro builder to accomplish my goal.

 

[Minty]

In VBA you would issue a

Me.Undo
Cancel = True
Exit Sub

as the No option.
I'm afraid I don't use Macro's so can't help much beyond that.

 

[Gasman]

Use two separate ifs?

 

[abnorth]

Use two separate ifs?

Hi Gasman,

Not sure what you mean. An If statement to display the message and another to undo?


On another note. Not having ever touched VBA before I looked up a tutorial on using beforeupdate to cancel unwanted changes and got it to work based on the example in the video. So I guess this is solved, but if anyone knows how to do it with macros I would still love to know for future reference! In general, I am not super comfortable just putting some code in when I don't understand the language or even where I am supposed to type what

 

[jdraw]

You could make use of data macro(s). See my posts in this thread re Macros.
The data macro has been used as an audit log ---any changes to a record can be logged, regardless if it was via form, query, vba or direct table manipulation.

 

[Gasman]

Not sure what you mean. An If statement to display the message and another to undo?

No an If for 6 <> MsgBox
Then an If for 6 = MsgBox

If fo no other reason that as a debugging technique.
As mentioned macroes and extremely hard to debug.
With VBA you can walk line by line and inspect the variable.

Best start learning VBA. It really is much easier than macroes.

 

[abnorth]

@jdraw Thanks! I'll take a look.
@Gasman Ah I see, that makes sense. I wouldn't mind learning VBA, it's just a matter of time (I'm a chemist who is supposed to be doing lab work, not managing a database, but no one else at my company can do it ). My guess is if I need much more functionality out of access, I will pick it up.