Data Testing To Prevent Saving Bad Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I set up the following code listed below. The form is set up as a
multi-record form (Default View = Continuous Forms). The test is successful
on existing records preventing the user from going forward. When a new
record is added, the user can beat the system by clicking on the next line
and then clicking the save icon on the tool bar. Can the system be set up in
such a way that if the user adds a new record and the test fails, that he is
prevented from saving the record or even going to the next line by clicking
the mouse. Could the toolbar be disabled, preventing the user from being
able to save when the test fails ? Is any other code required in the
BeforeUpdate procedure to prevent the user from going forward until the error
is addressed ?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.HRnum) Then
Cancel = MsgBox("You must enter an HR number.", vbCritical, "Data
Validation Failure")
End
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.HRnum) Then
MsgBox "You must enter an HR number.", vbCritical, "Data Validation
Failure"
Cancel = True
End Sub

The value of vbCancel is 2
The value of True is -1
 
i tested your code on a Continuous form, and it ran consistently no matter
what i did to "save" either a new record or an edit to an existing record -
pressing Shift+Enter, or clicking the toolbar icon, or choosing menu Records
| Save record, or moving to another record - either next or previous, or
closing the form. this is the expected result.

are you sure that the new record is actually being saved when you "move to
another record and click the Save icon"? what version of Access are you
using? and is any other code running on the form's BeforeUpdate event?

hth
 
Pat, i tested rmcompute's code in an A2K db running in A2003 on OS Win2KPro.
i pasted it "as is" from his post, changing only the control name HRnum to
the name of a control on my form. the code runs fine on my setup. did you
have a different result? perhaps in another version of Access or Windows?

hth
 
As I already said -
The value of vbCancel is 2
The value of True is -1

That means that if the user chooses the cancel button, the message box will
return a 2 and place that value in the Cancel argument. Access is expecting
0 or -1 as a value for the Cancel argument. If Cancel = -1, Access will
cancel the update. If Cancel is anything else (I'm assuming that that is
the way the test is coded), the record is saved. Therefore, if the record
is saved even when vbCancel is chosen, the code does not work!

Just because a piece of code compiles or doesn't raise an error, doesn't
mean that it is correct. Check the table and you will see the invalid
records there.
 
Pat, Tina,

I re-tested the code. As mentioned before it is placed in Sub
Form_BeforeUpdate.
When I set the Cancel variable to anything except False, I could not save
the record.
It keeps looping through the Before Update Procedure.

RMConfused.
 
As I already said -
The value of vbCancel is 2
The value of True is -1
Click to expand...

yes, i did see that in your previous post, and i didn't understand what you
were referring to then, and i don't understand it now.

here's the code that rmcompute originally posted:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.HRnum) Then
Cancel = MsgBox("You must enter an HR number.", vbCritical, "Data
Validation Failure")
End

i don't see anything here that will result in the vbCancel value.

the generated message box has only an OK button, which is to be expected
since the Buttons argument of the function specifies only the icon to
display (vbCritical), so the default button value of 0 is assumed. with the
OK button clicked, the Msgbox function returns a value of 1 (vbOK).
Just because a piece of code compiles or doesn't raise an error, doesn't
mean that it is correct.
Click to expand...

you're right, of course. but when i posted that
<< i tested your code on a Continuous form, and it ran consistently no
matter what i did to "save" either a new record or an edit to an existing
record - pressing Shift+Enter, or clicking the toolbar icon, or choosing
menu Records | Save record, or moving to another record - either next or
previous, or closing the form.>>
i meant exactly that - i *tested* the code, not merely pasted it into a
module and compiled it. the code ran, and produced the desired result
without fail: it cancelled the Update event when the referenced control was
Null, displaying the message box in the process.

did you test the original posted code by using it in a form, Pat? if so,
under what conditions did it fail to produce the desired result for you?

btw, your comments about the value of Cancel made me curious.
if the user chooses the cancel button, the message box will
return a 2 and place that value in the Cancel argument. Access is expecting
0 or -1 as a value for the Cancel argument. If Cancel = -1, Access will
cancel the update. If Cancel is anything else (I'm assuming that that is
the way the test is coded), the record is saved.
Click to expand...

seems to me that i read somewhere (way back in the mists of time) that 0
(zero) = False, and anything other than zero = True, when evaluating a value
as True or False. just for yucks, i changed the code in my test form
slightly, adding vbOKCancel to the Button argument of the MsgBox() function.
so the only possible return values from the function are 1 or 2 - according
to the documentation in VBA Help. then i attempted to add a new record to
the form, and edit an existing record, with a Null value in my referenced
control. no matter which button i clicked in the message box, the Update
event was cancelled. then i went a step further, removed the msgbox code,
and hardcoded a Cancel value of 7, as

Cancel = 7

the Update cancelled as intended, when the referenced control was Null. so i
tried the number 259 in place of the 7. again, the Update event was
cancelled appropriately. how about 26549? you guessed it - Update event
cancelled. just to be thorough, i changed the hardcoded value of Cancel to
zero (0). again, the code responded as expected: the Update event was
**not** cancelled, and the record was updated, since the value of Cancel was
False.

i did manage to generate one error code, when i hardcoded the value of
Cancel to 86549. i got a

Run-time error '6': Overflow

i assume that's because 86549 is a Long Integer, while the BeforeUpdate
event procedure's Cancel variable is set as an Integer.

hth
 
what do you mean by looping? a continuous loop, so that you have to manually
"break" the code to stop it?

or do you mean that you're stopped and held in that record until you either
put a value in the control or press Esc to undo the record edits?

hth
 
I set up the code as listed below and entered a record with no PCSRName. I
moved off the record by clicking the next line and the procedure executed
issuing the error message. I clicked OK and the msgbox displayed the Cancel
value of 10. I moved off the record again and it repeated the above and
would not stop until I clicked the Exit button as listed below. After that
it repeated the above one more time ie display the message etc. and then it
exited. I checked the table but it did not save.

If IsNull(Me.PCSRName) Then
MsgBox "You must enter a PCSR number.", vbCritical, "Data Validation
Failure"
Cancel = 10
MsgBox "Cancel4: " & Cancel
End If

Private Sub cmdExit_Click()
DoCmd.Close
End Sub
 
i'm not sure why you changed your code from what you originally posted,
since you said it was working for you (and it also worked fine in my data
entry tests). at any rate, from your description of the system responses to
the code you posted below, it sounds like the code is executing properly.

maybe the problem lies in what your *expectations* are. when you set up
validation code in a form's BeforeUpdate event, so that if the validation
fails then the Update event is Cancelled, then that's exactly what happens
(when the code is working properly). when the form's Update event is
cancelled, the current record is not saved, and the focus stays on the
current record. the user's only options are to 1) satisfy the validation
requirement - in this case, by entering a value in the PCSRName control, or
2) to press the Esc key once or twice to clear all current edits to the
record (this action will *erase* a new record entirely), or 3) close the
form, which will also result in a failure to save all edits to the current
record (or a failure to save a new record, entirely).

so, when your user fails to enter a PCSRName value, and tries to save the
record, exactly what is it that you *want* to happen? if we understand your
goal, we can probably come up with a way to help you achieve it.

hth
 
I think that you will find that any non-zero value will be treated as
True by most (if not all) Functions that expect a Boolean value passed
as an Integer. It's dangerous practice to pass anything except 0 and
-1, but it will usually work. You will come unstuck if the code of the
called Function tries to invert the passed value. Not(0) is -1 and
Not(-1) is zero, but Not(7) (which will be treated as True in logical
tests) is -8, which will also be treated as True in logical tests,

As I already said -
The value of vbCancel is 2
The value of True is -1

That means that if the user chooses the cancel button, the message box will
return a 2 and place that value in the Cancel argument. Access is expecting
0 or -1 as a value for the Cancel argument. If Cancel = -1, Access will
cancel the update. If Cancel is anything else (I'm assuming that that is
the way the test is coded), the record is saved. Therefore, if the record
is saved even when vbCancel is chosen, the code does not work!

Just because a piece of code compiles or doesn't raise an error, doesn't
mean that it is correct. Check the table and you will see the invalid
records there.
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
I did not test the code. I looked at the value that was being passed to the
Cancel argument and knew it wasn't going to be -1. As it happens, vbOK
returns a 1. I didn't check to see that vbCritical has only an OK button.
I thought it had a cancel button.

In any event as Peter Fletcher has already pointed out, you take a large
risk when you pass an invalid value to a sub or function. Just because, it
seems to work today, does not mean that it will work with the next version
of Access or even with the next service pack.

I guess the real problem is the logic of the code. By its existence it
implies that something different might be passed to the Cancel argument when
in fact, it doesn't seem to matter what value is passed as long as it isn't
0. So, Access apparently tests for 0 and if Cancel is not 0, it assumes
that you want to cancel the update.

My final advice. Do what you want but be prepared for the code to fail if
the MS folks tighten up their code.
 
Back
Top