Data Validation

  • Thread starter Thread starter Dan McClelland
  • Start date Start date
D

Dan McClelland

SQL Server 2K back end, Access XP adp front end.

Desired effect: Bound form, user entering new record,
leaves a required field incomplete and clicks the
application close box. User is warned (USING MY CUSTOM
MESSAGE AND NOT THE GENERIC ACCESS MESSAGE) that a
required field is empty and given the opportunity to fill
it in before exiting the application.

Correct me if I'm wrong...the only way to suppress the
generic (and potentially confusing) Access message is to
use the form's OnError event and setting the intrinsic
constant Response to acDataErrContinue. But, by
definition, this setting will do TWO things: suppress the
Access message and ignore the error. Upon ignoring the
error, the partly-entered new record disappears and the
user is returned to a new blank record. (That's right, it
disappears. It is not saved in the table. It is NOWHERE.)

Let me be clear. If I don't use the form's OnError event,
I can find no way of avoiding Access' generic message. No
way. However, if I do use OnError, and set Response =
acDataErrContinue, the generic Access message is
supressed, but the error is ignored too. WHY, oh WHY
isn't there a way to suppress the message but NOT ignore
the error so I can handle it? There's no reason the
record should just vanish.
 
I know it does not answer your question directly, but
have you considered adding code to the form's Before
Update event that looks for emply fields. For example

Form_BeforeUpdate(Cancel As Integer)

If IsNull ([EmployeeNumber]) OR [EmployeeNumber] = ""
Then
MsgBox ("Your Message")
Else
'Whaterver else you want to do


You can write a series of these or, if there are a lot
you can write a Case Statement.

Does this help
 
Or if you choose you could handle it with a for next
statement rather than select case statement if you want to
ensure that all values have been added.

dim ctl as control

for each ctl in controls
if ctl.controltype = actextbox then
if trim(ctl.value) = "" or isnull(ctl.value) then
msgbox "Error danger will robinson, danger"
exit sub
else
end if
end if
next ctl

-----Original Message-----
I know it does not answer your question directly, but
have you considered adding code to the form's Before
Update event that looks for emply fields. For example

Form_BeforeUpdate(Cancel As Integer)

If IsNull ([EmployeeNumber]) OR [EmployeeNumber] = ""
Then
MsgBox ("Your Message")
Else
'Whaterver else you want to do


You can write a series of these or, if there are a lot
you can write a Case Statement.

Does this help



-----Original Message-----
SQL Server 2K back end, Access XP adp front end.

Desired effect: Bound form, user entering new record,
leaves a required field incomplete and clicks the
application close box. User is warned (USING MY CUSTOM
MESSAGE AND NOT THE GENERIC ACCESS MESSAGE) that a
required field is empty and given the opportunity to fill
it in before exiting the application.

Correct me if I'm wrong...the only way to suppress the
generic (and potentially confusing) Access message is to
use the form's OnError event and setting the intrinsic
constant Response to acDataErrContinue. But, by
definition, this setting will do TWO things: suppress the
Access message and ignore the error. Upon ignoring the
error, the partly-entered new record disappears and the
user is returned to a new blank record. (That's right, it
disappears. It is not saved in the table. It is NOWHERE.)

Let me be clear. If I don't use the form's OnError event,
I can find no way of avoiding Access' generic message. No
way. However, if I do use OnError, and set Response =
acDataErrContinue, the generic Access message is
supressed, but the error is ignored too. WHY, oh WHY
isn't there a way to suppress the message but NOT ignore
the error so I can handle it? There's no reason the
record should just vanish.
.
Click to expand...
.
Click to expand...
 
You could also use

If Len(Trim$(ctl.value & vbNullstring)) = 0 Then

instead of

if trim(ctl.value) = "" or isnull(ctl.value) then

It's slightly more efficient.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Matt Weyland said:
Or if you choose you could handle it with a for next
statement rather than select case statement if you want to
ensure that all values have been added.

dim ctl as control

for each ctl in controls
if ctl.controltype = actextbox then
if trim(ctl.value) = "" or isnull(ctl.value) then
msgbox "Error danger will robinson, danger"
exit sub
else
end if
end if
next ctl

-----Original Message-----
I know it does not answer your question directly, but
have you considered adding code to the form's Before
Update event that looks for emply fields. For example

Form_BeforeUpdate(Cancel As Integer)

If IsNull ([EmployeeNumber]) OR [EmployeeNumber] = ""
Then
MsgBox ("Your Message")
Else
'Whaterver else you want to do


You can write a series of these or, if there are a lot
you can write a Case Statement.

Does this help



-----Original Message-----
SQL Server 2K back end, Access XP adp front end.

Desired effect: Bound form, user entering new record,
leaves a required field incomplete and clicks the
application close box. User is warned (USING MY CUSTOM
MESSAGE AND NOT THE GENERIC ACCESS MESSAGE) that a
required field is empty and given the opportunity to fill
it in before exiting the application.

Correct me if I'm wrong...the only way to suppress the
generic (and potentially confusing) Access message is to
use the form's OnError event and setting the intrinsic
constant Response to acDataErrContinue. But, by
definition, this setting will do TWO things: suppress the
Access message and ignore the error. Upon ignoring the
error, the partly-entered new record disappears and the
user is returned to a new blank record. (That's right, it
disappears. It is not saved in the table. It is NOWHERE.)

Let me be clear. If I don't use the form's OnError event,
I can find no way of avoiding Access' generic message. No
way. However, if I do use OnError, and set Response =
acDataErrContinue, the generic Access message is
supressed, but the error is ignored too. WHY, oh WHY
isn't there a way to suppress the message but NOT ignore
the error so I can handle it? There's no reason the
record should just vanish.
.
Click to expand...
.
Click to expand...
Click to expand...
 
Personally I have never had Form_Error acdataerrcontinue ignore the error &
carry on regardless. For me, it has always displayed my custom error message
(in place of the standard one), but then stopped the process proceeding, as
it should when an error occurs. Are you sure that the form is not executing
some other statement (somewhere) that .Undo'es the changes (or whatever)?

HTH,
TC
 
I assume your "generic" message arises from a property requirement in your
database (ie. "allow zero length = no"; required = yes). So why don't you
turn these properties off; they serve no purpose?

I'm new to MS Access validation, but have resolved most of my problems
through a combination of validating on the form before update event, and
testing for me.dirty property on unrelated buttons to force saves (me.dirty
= false), and thus trigger the form before update event. I also restrict
keystrokes to numerics for controls for numbers.

John S
Aylmer, PQ
 
John,

In Access you would do what Dan asks by using the Validation Rule and
Validation Text properties of the field in table design. However, he
is using SQL Server, and I have no idea whether the equivalent
functionality is available there.

- Steve Schapel, Microsoft Access MVP
 
Back
Top