Solved Macro to avoid accidental record edits (1 Viewer)

abnorth

New member
Local time
Today, 16:36
Joined
Oct 19, 2021
Messages
10
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?

AccessMacro.png


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

Minty

AWF VIP
Local time
Today, 21:36
Joined
Jul 26, 2013
Messages
10,330
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

Enthusiastic Amateur
Local time
Today, 21:36
Joined
Sep 21, 2011
Messages
13,890
Use two separate ifs?
 

abnorth

New member
Local time
Today, 16:36
Joined
Oct 19, 2021
Messages
10
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

Super Moderator
Staff member
Local time
Today, 16:36
Joined
Jan 23, 2006
Messages
15,340
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

Enthusiastic Amateur
Local time
Today, 21:36
Joined
Sep 21, 2011
Messages
13,890
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

New member
Local time
Today, 16:36
Joined
Oct 19, 2021
Messages
10
@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.
 

Users who are viewing this thread

Top Bottom