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)>

 

[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!