Solved Unable to Stop the bounded form from adding unwanted records

[MajP]

Not trying to muddy the waters here you have the OP well in hand.

For the rest of you, if you are not familiar what @arnelgp is suggesting you should add it to your quiver. I do not understand why @Pat Hartman is so dismissive of this approach. Many advanced Access developers do not understand that you can do transactions in DAO, and that opens up a lot of possibilities. The code that @arnelgp provides is easy to use and can do things way beyond validating a single record. His demo shows canceling a continuous form update. You could go further and cancel multiple child records.

So I find the below statement wrong

@arnel, The OP wants to prevent saving a record if it is incomplete. The only RELIABLE way to do that is to put the validation code in the form's BeforeUpdate event so the save can be cancelled if there is an error.

Clearly @arnelgp has shown the before update is not the only way

Close form without saving record?

Im very new so this could be a dumb question but here goes. I have a form which adds records to a database of donors. On the form i have a close form button with the code that follows - DoCmd.Close acForm, "Add Donor Form", acSaveNo whenever the close button is pressed it saves the record to...

www.access-programmers.co.uk

Not suggesting the OP should use this since it is a more advanced concept. I do recommend others get familiar for something else in the tool box. I have used this in some cases with great success.

 

[Pat Hartman]

I do not understand why @Pat Hartman is so dismissive of this approach.

Perhaps you didn't read the question. Let me repeat it for you.

Unable to Stop the bounded form from adding unwanted records​

Transactions have their places. Controlling the saving of a record on a bound form is not one of them. VALIDATION is the key to knowing whether or not a record is to be saved and wrapping your code in a transaction does nothing to further that. Not to mention the fact that when you are using a bound form, you are already wrapped in a transaction created by the Form class.

Writing code to replace that provided by Microsoft for a bound form goes against the entire reason for using a bound form. If you don't like the way an Access bound form works, you should be using a different platform. If you think you are smarter than MS and you can do it better,, then write as much redundant code as you want. Just remember, you loose built in functionality with this method such as refresh and filter.

If you feel the need to use unbound forms, then arnel's solution is excellent.

And despite the slickness of being able to circumvent Access and do it your way, the proposed transaction "solution" isn't a solution at all since it doesn't solve the problem. All it does is ask if the user wants to save. If he says yes, you've just saved another bad record which is exactly what the OP is trying to prevent. If there are rules for data, you can't get past validation code and validation code is reliable in the Form's BeforeUpdate event but not in other events. If there are no rules, then saving empty records isn't a problem.

Since you need the validation code whether you roll your own update or leave it to Access, rolling your own is redundant and presumptive.

I look at this differently from many of you. I've been developing applications for decades. I've written my million lines of code and I don't need the practice. That is what I love about Access. It removed the tedium of repeating the same code application after application (I created many templates over the years to mitigate this problem and even converted some to Access) and leaves me to code only what is necessary for the particular application I am developing. Newbees are awed by code because they don't understand how to write it but typically to an experienced developer, writing the code is the easy part of the development. That is why writing code in large projects is delegated to minions they call code monkeys. It is deemed too trivial for the important people to bother with.

 

[Pat Hartman]

As it is bounded form, as soon as the data is entered by the user, it was saved into the table.

That isn't quite how a bound form works. Access is designed to be helpful and data is what creating an Access application is all about. That means that Access leans toward saving all data whenever it thinks it needs to be saved. There is a pattern and you can figure it out but you don't need to. All you need to do is to understand the purpose of the BeforeUpdate event and use it to solve your problem

PS your English is very good so please don't be offended but a form is "bound". Bound is the past tense of the verb to bind. Bounded is the past tense of the verb to bound (as in a deer bounding across the field).