Unpredicatable BeforeUpdate event

[chanu]

Hi,
I am totally new to programming. I have tried to design a form with some
code in the beforeupdate. The behaviour of the form has baffled me so much i
can’t understand where it’s going wrong. It’s totally unpredictable. So,
friends please don’t get impatient as i am reproducing the entire code here
but please help me solve this problem.

I have a form with a few unbound controls .
The fields are:
FromDate
ToDate
Applicable period(this is an option Group)
OldRate (if applicable in earlier time)
NewRate ( if applicable at present).
And cmdADD button and cmdCANCEL button. When the ADD button is clicked,
it passes the values to the either or both of the subforms depending on the
option group value.
I have given validation for FromDate and ToDate field in the BEFORE UPDATE
events of the respective controls:
1.if it is a valid date (a test for both fields)
2.if ToDate is greater than FromDate.
Validation for FromDate is working very well. if I correct a wrong entry,
it is taking the new value . But for ToDate, it pops the message box if
validation is wrong in the beginning . for example if i give
FromDate=01-01-2009 and ToDate= 01-01-2008, it displays the message box
“ToDate must be greater than FromDateâ€.but if i correct the ToDate the same
message is displayed after i try to tab away from the field(it is not
letting me tab away sometimes) as though i have not corrected but it is
working very well when ToDate is an invalid date.(Some other time it is
accepting a value lesser than FromDate without popping any message quite
unpredicatbly).And even cmdCancel button is not working and the cursor is
staying only in the ToDate field and the cursor can't be moved. Can any one
tell me why it is behaving so only for the ToDate field BeforeUpdate event.
The code for both the fields is:

Private Sub txtToDate_BeforeUpdate(Cancel As Integer)
If Not IsNothing(Me.txtToDate) Then

If Not IsDate(Me.txtToDate) Then
MsgBox "The value in TO is not a valid date.", vbCritical,
"Chanaky's tip:"
Cancel = True
Undo


ElseIf Me.txtFromDate > Me.txtToDate Then

MsgBox "FROM date must be lesser than / or equal to To date",
vbInformation, "Chanakya's tip:"
Cancel = True
Undo


End If

End If
End Sub


Private Sub txtFromDate_BeforeUpdate(Cancel As Integer)
If Not IsDate(Me.txtFromDate) Then
MsgBox "The value in From is not a valid date.", vbCritical,
"Chanaky's tip:"
Cancel = True
End If

End Sub

Private Sub cmdCancel_Click()
Dim ctl As Control
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If ctl.ControlSource = "" Then
ctl.Value = Null
End If
Case Else
End Select
Next ctl
Me.txtFromDate.SetFocus

End Sub


Private Sub CmdAdd_Click()
Dim db As DAO.Database
Dim rstNewAllowance, rstOldAllowance As DAO.Recordset
Dim strSQL1, strSQL2 As String
Set db = CurrentDb
strSQL1 = "SELECT tblNewAllowances.EmpSerialID,
tblNewAllowances.AllowanceName, tblNewAllowances.FromDate, " & _
" tblNewAllowances.ToDate , tblNewAllowances.AllowanceRate " & _
" FROM tblNewAllowances " & _
" WHERE (((tblNewAllowances.EmpSerialID) = 1) And
((tblNewAllowances.AllowanceName) = 'hra'))" & _
" ORDER BY tblNewAllowances.EmpSerialID,
tblNewAllowances.AllowanceName, tblNewAllowances.FromDate; "
strSQL2 = "SELECT tblOldAllowances.EmpSerialID,
tblOldAllowances.AllowanceName, tblOldAllowances.FromDate, " & _
" tblOldAllowances.ToDate , tblOldAllowances.AllowanceRate " & _
" FROM tblOldAllowances " & _
" WHERE (((tblOldAllowances.EmpSerialID) = 1) And
((tblOldAllowances.AllowanceName) = 'hra'))" & _
" ORDER BY tblOldAllowances.EmpSerialID,
tblOldAllowances.AllowanceName, tblOldAllowances.FromDate; "
Set rstNewAllowance = db.OpenRecordset(strSQL1)
Set rstOldAllowance = db.OpenRecordset(strSQL2)
If Me.Frame6 <> 2 Then
rstNewAllowance.AddNew
rstNewAllowance!FromDate = Me.txtFromDate
rstNewAllowance!ToDate = Me.txtToDate
rstNewAllowance!allowancerate = Me!cmbNewRate.Column(1)
rstNewAllowance.Update
Me.sfrm21.Requery
End If

If Me.Frame6 <> 3 Then
rstOldAllowance.AddNew
rstOldAllowance!FromDate = Me.txtFromDate
rstOldAllowance!ToDate = Me.txtToDate
rst!allowancerate = Me!cmbOldRate.Column(1)
rstOldAllowance.Update
Me.sfrm22.Requery
End If
ClearCriteria Me
Me.txtFromDate.SetFocus
End Sub



Public Sub ClearCriteria(frm As Form)
Dim ctl As Control
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If ctl.ControlSource = "" Then
ctl.Value = Null
End If
Case Else
End Select
Next ctl
End Sub

 

[chanu]

Thanks Douglas for your help. I have found the answer to the question by
consulting the msdn library BEFORE UPDATE event. when i added
ME.CONTROL.UNDO in the event it worked well without showing any problems.
But i have found another trouble when the user directly clicks the ADD
command button when the value entered (a wrong value in the control for ex:
15/15/2009) in txtToDate control without tabbing away, it is directly
throwing the error without firing the BEFORE update event of the control.
please help me how to guard against this