Problems with input-validation

[Volker Neurath]

Hi all,

i\'m not sure whether i\'m right here with my problem or not, but i try.

Situation:

I have a table with just one field \"myfield\" date-type is \"LONG\"

I also have a form with just one textbox. The form ist bound to the
table an the text box is bound to that one table-field.
There are no validation-rules defined within the field-definiton in the
table or the definiton of the form-control.

Now i try to handle user -input-errors. Because the table-field
only can take numbers i want to check the user\'s input if it is numeric
-
if it\'s not, the user should get a message.

The user should also get a message if he tries to leave the field
without typing in anything, i.e. leaving the field empty.

Problem:
i cannot figure out, how to do this.

Using the Form_Error Event i can check for runtime-error 2113 of
course - but i\'m not able to chekt if this error was fired because
of leaving the field empty or because the user tried to type in
non-numeric values.

This is my procedure in Form_Error:

------------------
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim ctl As Control
Set ctl = Screen.ActiveControl

If DataErr = 2113 Then

If IsNull(Me!Text0) then
MsgBox \"field must have a value!\"
ELSE
If Not (IsNumeric(Me!Text0)) Then
MsgBox \"Only numerical input allowed in \" & ctl.Name
Response = acDataErrContinue
Else
MsgBox \"Incorrect input in field \" & ctl.name
Response = acDataErrContinue
End If
End If
end if
End Sub

but: it doesn\'t work.It will allways run into the last \"ELSE\".

I also tried changing the table-field into a text-field and placed the
above code into the control\'s \"BeforeUpdate\"-Event (without the Err
2113-check of course)
-- no change in behaviour.

What am i doing wrong or better:

how can i do a working validation of user-input?

The above is \"only\" testing vor the validation of my actual project;
there i have a small database with
8 fields that have to be tested on many conditions such as:

- left empty?
- numeric?
- numeric and > 0?
- numeric, >0 and integer? (It\'s simply not possible to sell 0.5
printers )
- date?
- is the value (date or quotationnumber) > (or <) than the date (or
quotationnumber)
in another field on the same form?

The Access-verion is Access 2003

Volker

 

[Douglas J. Steele]

As code in the BeforeUpdate event, I would expect something like the
following to work:

Private Sub Text0_BeforeUpdate(Cancel As Integer)

If IsNull(Me!Text0) = True Then
MsgBox "Text0 must have a value!"
Cancel = True
ElseIf IsNumeric(Me!Text0) = False Then
MsgBox "Only numerical input allowed in Text0"
Cancel = True
End If

End Sub

 

[Volker Neurath]

Hi Douglas,

sorry, for being late with my answer but programming (and therefore
checking newsgorups) is not the main part of my job

As code in the BeforeUpdate event, I would expect something like the
following to work:

Private Sub Text0_BeforeUpdate(Cancel As Integer)

If IsNull(Me!Text0) = True Then
MsgBox "Text0 must have a value!"
Cancel = True
ElseIf IsNumeric(Me!Text0) = False Then
MsgBox "Only numerical input allowed in Text0"
Cancel = True
End If

I've tried this before and found it doesn't work.
The Form_error will fire before the control's BeforeUpdate.

Tomorrow il try again replacing "Me!Text0" (what is: Me!Text0.Value) by
Me!Text0.Text and see what happens.

Volker

 

[Douglas J. Steele]

What does "doesn't work" mean? Did you get an error? If so, what was the
error? If you didn't get an error, what did you get, and what did you expect
to get instead?

Presumably you remembered to remove the code from the Form_Error event...

 

[Volker Neurath]

What does "doesn't work" mean? Did you get an error?

Difficult to explain.

Text0 is bound to a table-field - so JET will fire ERR2113 and, as a
result, Form_Error will be fired before "Text0"'s "BeforeUpdate" is
fired.

In the moment I have some further ideas how to get around - i will check
these and cry here again if the won't do.(Then i'll come with
detailed description)

Volker

 

[Douglas J. Steele]

That makes no sense. The BeforeUpdate event of the control fires before the
value is written to the table. Even the form's BeforeUpdate event fires
before the table knows anything about the value.

 

[Volker Neurath]

That makes no sense. The BeforeUpdate event of the control fires before the
value is written to the table. Even the form's BeforeUpdate event fires
before the table knows anything about the value.

I only can tell you, what I've learned during my tests. An a very simple
one was that simple one from my start-post.

And: i have a book here; the author says the same;

Quote from the book:

"With the technique above (rem:that is what you recommended) you cannot
catch all input errors. If limitations from the tabledesign are ignored
during input or when the Record is stored, the messages produced by this
are displayed /before the "BeforeUpdate" eventprocedures of controls and
forms are fired/.
*This is e.g. the case when you try to store a text in a date-field*

But - i now have a solution that runs.
I think that, in addition, i will fill the controls "keyDown" event with
a sub that prevents not allowed keys from writing into the field.
(i.e. suppress letter-keys in a field where only numbers are allowed)

Volker

 

[Volker Neurath]

Hello again,

now i did several test and the more i'm testing the more i think access
behaves weired.

Table: just one field, named "Eingabe"

Type: long
Required: yes

Form:
one field too an a button for crating a new record.

field-name: txtEingabe

Code in txtEingabe_BeforeUpdate:
Dim ctl As Control, erg As Boolean
Set ctl = Screen.ActiveControl

erg = Len(Trim$(Nz(Me!txtEingabe.Text, "")))


If IsNull(Me!txtEingabe.Text) Or _
Len(Trim$(Nz(Me!txtEingabe.Text, ""))) = 0 Then
MsgBox "Field " & ctl.Controls(0).Caption & "is mandatory!"
Cancel = True
Else
If Not (IsNumeric(Me!txtEingabe)) Then
MsgBox "Only numerical values!"
Cancel = True
End If
End If

Code in Form_Error:

Dim ctl As Control
Set ctl = Screen.ActiveControl

Debug.Print "Form_Error"
Debug.Print DataErr

Select Case DataErr
Case 2113
If Not IsNumeric(ctl.Text) Then
Response = acDataErrContinue
MsgBox "Numbers only!"

Else
Response = acDataErrContinue
MsgBox "input " & ctl.Controls(0).Caption & " incorrect!"

End If
Case 3314
Response = acDataErrContinue
MsgBox "Field " & ctl.Controls(0).Caption _
& " is mandatory."
Case 2279
Response = acDataErrContinue
MsgBox "Number must be 9-digit."
End Select

Problem:

That works perfectly.

But:

when i change the table-field settings to:

type TEXT
required YES
AllowZeroLength NO

My code stops working properly.
When i try to create a new record without typing anything into
txtEingabe - nothing happens (it shoul fire "Field.... ist mandatory!"

When i type in an alphanumeric value it fires "Numbers only" (as
expected, but in addition to that it fires the err 2116-message.


Why?

And: any ideas how to make it working properly?

Volker

 

[Douglas J. Steele]

"When i try to create a new record without typing anything into txtEingabe".
How are you trying to create a new record? If you've only got a single text
box on the form and you don't type anything into it, the BeforeUpdate event
won't fire, as the form isn't dirty.

I'll make a couple of comments on your code, if you don't mind.

First, you shouldn't be referring to the Text property. Text only works if
the control has focus. It may work in this situation, but when you've got
more than one text box, or when they click on the button, it won't.

For the same reason, relying on ctl.Controls(0).Caption for the error
message isn't a good idea.

 

[Volker Neurath]

"When i try to create a new record without typing anything into txtEingabe".
How are you trying to create a new record?

I created a button by using the wizard.

The code the wizard created is:

On Error GoTo Err_cmdNewRec_Click
Me!txtEingabe.SetFocus
'Me!txtEingabe.Value = Null

DoCmd.GoToRecord , , acNewRec

Exit_cmdNewRec_Click:
Exit Sub

Err_cmdNewRec_Click:
MsgBox Err.Description
Resume Exit_cmdNewRec_Click

If you've only got a single text
box on the form and you don't type anything into it, the BeforeUpdate event
won't fire, as the form isn't dirty.

OK. But there is the Form_error event and the Form_BeforeUpdate event
too.
And the related tablefield is set to .required=yes.
Why is .required=yes ignored in this case?

I'll make a couple of comments on your code, if you don't mind.

That would be great.

First, you shouldn't be referring to the Text property.

value won't work.

For the same reason, relying on ctl.Controls(0).Caption for the error
message isn't a good idea.

I want to give the user the Field-Name he sees on the form. With the
tech-name, I guess that the user can't do anything with and i didn't
want hardcoding the names.

Volker,
now working for 2 weeks on input-validation and getting frustrated

 

[Douglas J. Steele]

I created a button by using the wizard.

The code the wizard created is:

On Error GoTo Err_cmdNewRec_Click
Me!txtEingabe.SetFocus
'Me!txtEingabe.Value = Null

DoCmd.GoToRecord , , acNewRec

Exit_cmdNewRec_Click:
Exit Sub

Err_cmdNewRec_Click:
MsgBox Err.Description
Resume Exit_cmdNewRec_Click

That's fine, but all it does is create a new record.

OK. But there is the Form_error event and the Form_BeforeUpdate event too.
And the related tablefield is set to .required=yes.
Why is .required=yes ignored in this case?

Since the current record has not been modified in any way, there's nothing
to save. Why would you expect the validation to work? (Or are you saying
that a record with invalid data is being created?

That would be great.


value won't work.

What happens when you try? Do you get an error? If so, what's the error? If
you don't get an error, what do you get? (Incidentally, you don't need to
put .Value: it's the default property)

I want to give the user the Field-Name he sees on the form. With the
tech-name, I guess that the user can't do anything with and i didn't want
hardcoding the names.

But you don't know which control is the active one! The active control
doesn't change simply because you're examining the value in a particular
control. I believe you need something like:

If Len(Trim$(Me!txtEingabe & vbNullString)) = 0 Then
MsgBox "Field " & Me!txtEingabe.Caption & " is mandatory!"
Cancel = True
ElseIf Not (IsNumeric(Me!txtEingabe)) Then
MsgBox "Only numerical values!"
Cancel = True
End If

Having

If IsNull(Me!txtEingabe.Text) Or _
Len(Trim$(Nz(Me!txtEingabe.Text, ""))) = 0 Then

really is redundant, and using vbNullString is (marginally) more efficient
than using "".

 

[Volker Neurath]

Douglas J. Steele wrote:

That's fine, but all it does is create a new record.

That's what it is expected to. Ok, there's another button, created by
the wizard too. It's name is "Save record".

Here's it's code:

Private Sub cmdSaveData_Click()
On Error GoTo Err_Befehl5_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

Exit_Befehl5_Click:
Exit Sub

Err_Befehl5_Click:
MsgBox Err.Description
Resume Exit_Befehl5_Click

End Sub

Since the current record has not been modified in any way, there's nothing
to save. Why would you expect the validation to work? (Or are you saying
that a record with invalid data is being created?

As I said before, the field is not allowed to be empty.

What happens when you try? Do you get an error? If so, what's the error? If
you don't get an error, what do you get?

sorry, but I just don't understand why you're asking this.
When the _BeforeUpdate Event of a control ist fired, there is *nothing*
in .Value, the Value, the user typed in is in .Text.

Of course I could now change my code and test what happens.

But: i've leisure-time in less than 25 minutes

(Incidentally, you don't need to put .Value: it's the default property)

I know but nevertheless I do so - now and in future.
In my opinion, the code gets more readable.

But you don't know which control is the active one!

In Form_Error and Form_BeforeUpdate you're right

The active control

doesn't change simply because you're examining the value in a particular
control. I believe you need something like:

If Len(Trim$(Me!txtEingabe & vbNullString)) = 0 Then
MsgBox "Field " & Me!txtEingabe.Caption & " is mandatory!"
Cancel = True
ElseIf Not (IsNumeric(Me!txtEingabe)) Then
MsgBox "Only numerical values!"
Cancel = True
End If

As i said i don't want the user to see the tech-names becoause he can't
do anything with it.

What about something like:

Dim ctl as control

set ctl = Screen.ActiveControl

[..]
MsgBox "Field " & ctl.Controls(0).Caption & " is mandatory!"


Volker

 

[Douglas J. Steele]

That's what it is expected to. Ok, there's another button, created by the
wizard too. It's name is "Save record".

Here's it's code:

Private Sub cmdSaveData_Click()
On Error GoTo Err_Befehl5_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Befehl5_Click:
Exit Sub

Err_Befehl5_Click:
MsgBox Err.Description
Resume Exit_Befehl5_Click

End Sub

Much simpler is

Private Sub cmdSaveData_Click()
On Error GoTo Err_Befehl5_Click

Me.Dirty = False

Exit_Befehl5_Click:
Exit Sub

Err_Befehl5_Click:
MsgBox Err.Description
Resume Exit_Befehl5_Click

End Sub

sorry, but I just don't understand why you're asking this.
When the _BeforeUpdate Event of a control ist fired, there is *nothing*
in .Value, the Value, the user typed in is in .Text.

Hold on. The BeforeUpdate event of a control? I believe I said to use the
BeforeUpdate event of the form. (Using Text is fine in the BeforeUpdate
event of a control, since the control will have focus)

In Form_Error and Form_BeforeUpdate you're right

The active control

doesn't change simply because you're examining the value in a particular
control. I believe you need something like:

If Len(Trim$(Me!txtEingabe & vbNullString)) = 0 Then
MsgBox "Field " & Me!txtEingabe.Caption & " is mandatory!"
Cancel = True
ElseIf Not (IsNumeric(Me!txtEingabe)) Then
MsgBox "Only numerical values!"
Cancel = True
End If

As i said i don't want the user to see the tech-names becoause he can't do
anything with it.

What about something like:

Dim ctl as control

set ctl = Screen.ActiveControl

[..]
MsgBox "Field " & ctl.Controls(0).Caption & " is mandatory!"

Ah. I believe I see what you're trying to do: use the caption of the
associated label. Yes, what you have will work in the control's BeforeUpdate
event, but in the form's BeforeUpdate event, you can't be sure it will. Use

Me!txtEingabe.Controls(0).Caption

 

[Volker Neurath]

Hi Douglas,

Much simpler is

Private Sub cmdSaveData_Click()
On Error GoTo Err_Befehl5_Click

Me.Dirty = False

Exit_Befehl5_Click:
Exit Sub

Err_Befehl5_Click:
MsgBox Err.Description
Resume Exit_Befehl5_Click

End Sub

THX. the above code is what the wizard created
We're talking about my test-form.

Hold on. The BeforeUpdate event of a control? I believe I said to use the
BeforeUpdate event of the form.

My tests are confusing me. It seems that some events will be caught in
the control's .BeforeUpdate but not in From_BeforeUpdate.

And, please: keep in mind, that i'm working with bound form and bound
controls

(Using Text is fine in the BeforeUpdate
event of a control, since the control will have focus)

Just what I said

As i said i don't want the user to see the tech-names becoause he can't do
anything with it.

What about something like:

Dim ctl as control

set ctl = Screen.ActiveControl

[..]
MsgBox "Field " & ctl.Controls(0).Caption & " is mandatory!"

Ah. I believe I see what you're trying to do: use the caption of the
associated label.

You've got it

Yes, what you have will work in the control's BeforeUpdate

event, but in the form's BeforeUpdate event, you can't be sure it will. Use

Me!txtEingabe.Controls(0).Caption

Won't the above work?
Screen.ActiveControl should give me the at error-time active control i
suppose.

Volker

 

[Volker Neurath]

Hi again,

some thoughts i forgot...

There's one point which is driving me crazy; in txtEingabe_BeforeUpdate
i have the following piece of code:

If Not (IsNumeric(Me!txtEingabe)) Then
MsgBox "Only numerical values!"
Cancel = True
Exit Sub
End If

It will do what it is expected to: throwing a message when the user
types in anything but numbers.
But, and that's what is driving me crazy: when the box is confirmed by
klicking its OK button, Acces will immediatly throw the internal ERR2116
message.

so i have to handle this too - and that doesn't make any sense to me.

How can i get around this?

Besides - I think I have to think about the whole part "input
validation" again. The most important questions will be:

where is the best place for testing user-input (valid/invalid input)
where is the best place to catch Access Errors, including such resulting
from invalid user-input.

Volker

PS: thanks for your patience

 

[Douglas J. Steele]

If Access is raising unusual errors, you might consider decompiling your
application. Tony Toews has information about how to do that at
http://www.granite.ab.ca/access/decompile.htm

Incidentally, you might be surprised by some of what gets passed by the
IsNumeric function. From the Immediate window:

?IsNumeric("235a3")
False
?IsNumeric("235e3")
True
?IsNumeric("235d3")
True

I personally prefer putting all of my validations into the
Form_BeforeUpdate, but there's no reason why you can't use the
control_BeforeUpdate event for some checks. I've never used the Form_Error
event for anything.

 

[Volker Neurath]

?IsNumeric("235e3")
True

that is logical anyway...

?IsNumeric("235d3")
True

but this is not...

I personally prefer putting all of my validations into the
Form_BeforeUpdate,
ok.

I've never used the Form_Error
event for anything.

and how do you catch errors, risen by rules on table-design oder caused
by integrity-faults?

For instance, how do you catch an Unique-Index error(3022)?

Volker

 

[Douglas J. Steele]

that is logical anyway...


but this is not...

Actually, it is. In many languages, both e3 and d3 indicate 10**3, with e
being single precision and d being double precision.

and how do you catch errors, risen by rules on table-design oder caused by
integrity-faults?

For instance, how do you catch an Unique-Index error(3022)?

I don't rely on catching the Unique-Index error. While it may be redundant,
if I'm going to do validation, I'll check whether it's a duplicate in the
form's BeforeUpdate event.

 

[Volker Neurath]

Actually, it is. In many languages, both e3 and d3 indicate 10**3, with e
being single precision and d being double precision.

I see.

I don't rely on catching the Unique-Index error. While it may be
redundant, if I'm going to do validation, I'll check whether it's a
duplicate in the form's BeforeUpdate event.

Ok, but that is only one example.

Volker