Preventing alternate methods of saving unvalidated records

[Hank.School]

I have a form that does some checking in a Command_Click event to see if the associated record is valid to be updated. Everything works fine EXCEPT that the user can still save an unverified record by changing some of the fields and hitting SHIFT+ENTER.

I have looked around and it seems the best place to catch this is in the Form's BeforeUpdate sub and I was curious of the best way to handle this. Should I create a public variable to pass between the subs or is there another way to pass an allow/disallow condition to the BeforeUpdate sub from the Click sub? I try to minimize public variables if possible but if it is the only (or best) way to do it then so be it.

A Me.Undo command would be waiting in BeforeUpdate for anything that wasn't a validated update.

Thanks
Hank

 

[Ranman256]

edit the record in an unbound form. It copies data from the record.
When user clicks a Save button,
it Validates all fields, and if valid ,
runs an update query if it already exists,
or
runs an append query if its is new

 

[missinglinq]

I have a form that does some checking in a Command_Click event to see if the associated record is valid to be updated.

What, exactly, does the bolded part of the above mean, especially the associated record part?

Validation done before saving a Record does, indeed, usually go in the Form_BeforeUpdate event.

Linq ;0)>

 

[Pat Hartman]

edit the record in an unbound form

Why use Access if you are not going to use it's RAD features. The key to developing with Access is understanding form level events. Once you understand that Access takes managing the safety of your data personally and saves the record in a number of situations you can stop trying to second guess it and go to the heart of the matter. The Form's BeforeUpdate event is the LAST event to run before a record gets saved and it CANNOT be BYPASSED. That means that your Save button should NOT do your validation. It should simply save the record and possibly close the form. The BeforeUpdate event is where the validation should happen. That way, no matter what caused the record to be saved, you can intercept the save and validate the data. The instruction:
Cancel = True
is used to tell Access to not save the record. It leaves the record dirty and allows the user to correct his problem (presumably your validation code gave him a descriptive error message and put focus back into the control you want him to fix).

I generally don't use Undo since it punishes the user for making a mistake. I do use it in the case where the user is not authorized to make changes and I do use it in the case where I ask if he wants to continue editing or discard and he answers discard. But, those are the only two times I use it.

Some people want to force a user to press their save button to ensure that the save is conscious rather than accidental. In that case, you do need a variable and you need to set it in several places.
In the Current event of the form, set bSave to False
In the Click event of the save button, set bSave to True before issuing the save command.
In the BeforeUpdate event of the Form, check the bSave value. If it is true, proceed. If it is false, pop up a message asking if the user wants to save or discard. If he selects vbYes for save, then continue. Otherwise use Me.Undo, Cancel = True, and bSave = False before exiting.

 

[missinglinq]

...Why use Access if you are not going to use it's RAD features...

I have to agree with Pat...using Unbound Forms really does away with the basic function of Access, which is to facilitate Rapid Application Development, as he said! Using Unbound Forms should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and most situations don’t! You don't need Unbound Forms to

1. Do Data Validation
2. Prevent Duplicate Records
3. Do Formatting of Data before it's Saved
4. Decide whether or not to actually Save a New or Edited Record

which are the most common reasons given. Nor are they needed for another dozen reasons I've seen people give!

Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That’s because with Bound Forms the Access Gnomes do the vast majority of the heavy lifting; with Unbound Forms the developer has to write code for everything...even the most mundane tasks!

Bottom line is…with Bound Forms you end up writing code for a few specialized situations, such as #1-#4, as listed above…and with Unbound Forms you have to write code for virtually everything that needs to be done!

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

Don't misunderstand me...there are a few, specialized situations, where an Unbound Form is preferable...but anyone who routinely uses them for everything, has simply made a bad choice in deciding to work in Access.

Linq ;0)>

 

[Hank.School]

What, exactly, does the bolded part of the above mean, especially the associated record part?

Validation done before saving a Record does, indeed, usually go in the Form_BeforeUpdate event.

Linq ;0)>

The validation of the record being edited on the form (the associated record) is done in a command click event. I have always tended to put the majority of the processing code in command click events but I never realized (until recently) that closing the form in other ways would update the table anyway. From the searching I have done related to the issue, it seems that many people do the majority of the coding in a click event and then more of an error handler for the BeforeUpdate event.

I suppose I could move the entire validation process into the BeforeUpdate sub, is that the better method? I wasn't thinking along those lines since the button is a 'Done' button and is closing the form (if the data passes validation) or returning to the form if it is invalid. If I am in the BeforeUpdate event I am already past returning to the form unless I change it to a 'Save' button.

Thanks for the reply.

Hank

 

[Hank.School]

edit the record in an unbound form.

Thank you for the reply but I tend to agree with missinglinq and Pat... I almost never use unbounded forms. It seems to be working around the functionality of Access.

 

[Hank.School]

That means that your Save button should NOT do your validation. It should simply save the record and possibly close the form. The BeforeUpdate event is where the validation should happen. That way, no matter what caused the record to be saved, you can intercept the save and validate the data. The instruction:
Cancel = True
is used to tell Access to not save the record. It leaves the record dirty and allows the user to correct his problem (presumably your validation code gave him a descriptive error message and put focus back into the control you want him to fix).

Excellent point, this is exactly what I was looking for! Unfortunately, most of the beginner tutorials I run across simply show code going directly under a Click event and I have been treating validation like any other code related to the button's function. It isn't until you run across particulars like this that you realize the discrepancies between beginner tutorials and good programming practices.

I could easily modify the code with the variable (which was my first idea) but it just feels like the wrong way to do it. I am going to move the validation code to the BeforeUpdate where it makes more sense.

Thank you guys for the excellent information!

 

[Pat Hartman]

It is hard to separate the wheat from the chaff when you are starting out. The internet makes it just as easy to find bad advice as good. It's like getting your news from one outlet. You never see the bias if you don't read the same story written by people with opposite political views. Try to find several opinions on how to do something. The extra time will save work undoing bad advice.

There are occasions where it makes sense to put validation code in the BeforeUpdate event of a control - NEVER in the click event. The idea is that validating close to the point of data entry allows you to stop the user from proceeding. This makes sense if there are alternate logic paths through a form where fields are hidden or shown depending on values entered in prior fields. The problem is that you still need to repeat much of the validation in the Form's BeforeUpdate event because that's your last chance to make sure that everything you need has been entered. Plus for validation that requires multiple fields, the edits are really awkward unless you know that both fields should already be present. Since I never want to duplicate code, if I have to do the validation in two places, I make it a procedure or a function and simply call it in two places so if I have to change it, I only have to change it once.