Avoid prompt to save form upon form close in MS Access

Question

I have a form with save and cancel button.

When save is pressed and some required data was not provided, it prompts a message that data cannot be saved and I do some vba cosmetics to change the border of the controls that needed to be filled up. I change border colors in red and make it thicker.

My problem is when this changes was triggered and the user decided not to submit the form data, upon click on cancel button, message pops asking the user if he wants to save changes made on the form which obviously I do not want that.

I saw some suggestion in this post MS Access - Prevent prompt to save form

but reading on the documentation of the suggested answer, to me is not ideal to tun off all errors.

I also found another possible solution using

DoCmd.Close acForm, "myform", acSaveNo

But it seems acSaveNo only applies to data changes not on the controls property changes.

Is there any better way to avoid prompting form save and automatically discard any changes and close the form?

Thanks

EDIT: My code on changing form control appearance

Public Function InvalidBox(ByRef theBox As Control)
    theBox.BorderStyle = 1
    theBox.BorderColor = RGB(255, 0, 0)
    theBox.BorderWidth = 2
End Function

On my cancel button I have this code

DoCmd.Close acForm, "myform", acSaveNo

Please add your code that changes the form, and for the Cancel button. I'm surprised you get the "save changes" prompt, because when I make changes in Form view with VBA, and close the form, I don't get the prompt. Only for changes in Design view. — Andre
@Andre you're right. it looks like something is messing with my access. Did a reboot and open it again. Form is no longer prompting to save. Thanks — Wayne

Andre Andre · Accepted Answer · 2016-02-11T12:50:57

Solution was: normally Access doesn't ask "Do you want to save the changes" for changes made in Form view. Only in Design view.

So it is perfectly normal to change layout properties in a form at runtime, without any problems when closing the form.

Why the "save changes" prompt appeared here is unknown, but a reboot solved it.

Avoid prompt to save form upon form close in MS Access

2 Answers