Validating Data Entry - Long explanation

[Guest]

I've tried this in Forms Before Update, in each Control both before/after
update, still doesn't work as it should. I am at a loss as to what is wrong
with this code. Here's a portion of it. In Access 2003
Here's what I am trying to do. This is an activity entry form, if the user
doesn't enter a date or enters a date outside of the acceptable range,
display a message, force correct entry before continuing on, same with travel
and hours. The first 3 fields are text fields.
Part is a combo field. This field can be blank or filled. The value of the
field will affect other fields further down on the form and their values. If
it is blank, both serial and sales order can be blank. If it isn't blank,
either or both serial and/or sales order must be filled in.
Here's the problem, whether I enter a valid or non valid date, the msgbox
messages display, but you can continue on. This should be so simple and
straight forward......although I think 2003 is a great program when it works,
the quirky things it used to do, it seems still occur. Maybe it's me, it
seems when things are saved, they are (sometimes, not always). Anyway, if
someone can help me out, I'd really appreciate it.

Private Sub txtDate_AfterUpdate()
If Len(Trim(Nz(txtDate, ""))) Then
MsgBox "Please Enter an Activity Date", vbCritical
Cancel = True
Else
If Me.txtDate <= #5/15/2006# Or Me.txtDate > Date Then
MsgBox "Please enter a Date between May 15, 2006 and Today's Date",
vbInformation
Cancel = True
Me.txtDate.Undo
End If
End Sub

Private Sub txtHours_AfterUpdate()
If Len(Trim(Nz(txtHours, ""))) Then
MsgBox "You must enter 0 - 24, use .25 for 15 minutes, .5 for 1/2 hour",
vbCritical
Cancel = True
Else
If Me.txtHours < 0 Or Me.txtHours > 24 Then
MsgBox "You must enter 0 - 24, use .25 for 15 minutes, .5 for 1/2 hour",
vbCritical
Cancel = True
Me.txtHours.Undo
End If
End Sub
Private Sub txtPart_AfterUpdate()
If Len(Trim(Nz(txtPart, ""))) Then
txtSerial = Null
txtOrder = Null
Else
PartID = "99"
Me.Category.SetFocus
End If
End Sub

 

[Nikos Yannacopoulos]

Hi Rita,

It looks like the message box will always appear because of your condition:
If Len(Trim(Nz(txtDate, "")))
which does not make sense! The result is always a number (0 if no date,
or >0 if a date is entered, whereas it should be evaluating to a boolean
value (true/false). Try:
If Len(Trim(Nz(Me.txtDate, ""))) = 0 Then
or, better yet,
If IsNull(Me.txtDate) Then

HTH,
Nikos

 

[Guest]

Nikos,
Thank you! Another question, if I don't want the user to be able to move on
without making the correct entry first, is that a matter of adding Cancel =
True and/or txt[control].undo?

 

[Nikos Yannacopoulos]

I think Me.txtDate = Null would also do it, if the field to which
txtDate is bound is set to Required > Yes in the table design... a null
control won't allow the user to move out of the record, except by
rejecting the record.

HTH,
Bikos

 

[Guest]

What Nikos said also applies to this code:

Private Sub txtHours_AfterUpdate()
If Len(Trim(Nz(txtHours, ""))) Then

should be

If Len(Trim(Nz(txtHours, ""))) = 0 Then

and

Private Sub txtPart_AfterUpdate()
If Len(Trim(Nz(txtPart, ""))) Then

would be

If Len(Trim(Nz(txtPart, ""))) = 0 Then


Note that, if you look at the code for the BeforeUpdate() and AfterUpdate()
code, you *cannot* cancel the AfterUpdate() event but you *can* the
BeforeUpdate() event.

Private Sub Text8_AfterUpdate()
'some code
End Sub

Private Sub Text8_BeforeUpdate(Cancel As Integer)
'some other code
End Sub

So, putting Cancel=True in the after update code doesn't do anything.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Nikos,
Thank you! Another question, if I don't want the user to be able to move on
without making the correct entry first, is that a matter of adding Cancel =
True and/or txt[control].undo?

--
Business Systems Analyst

Hi Rita,

It looks like the message box will always appear because of your condition:
If Len(Trim(Nz(txtDate, "")))
which does not make sense! The result is always a number (0 if no date,
or >0 if a date is entered, whereas it should be evaluating to a boolean
value (true/false). Try:
If Len(Trim(Nz(Me.txtDate, ""))) = 0 Then
or, better yet,
If IsNull(Me.txtDate) Then

HTH,
Nikos