Me.Undo and validation of fields

[True.Kilted.Scot]

My form has several data entry controls on it, the first of which is a
date control. There is validation in the txtDateWorked.Exit procedure
which validates the entered date. This is to ensure that the date is
not in the previous week, nor is it more than two weeks in advance of
the current system date.

If an invalid date is entered, a custom message window appears advising
the user that an invalid date has been entered. This give sthem the
option of either entering a valid date, or cancelling the entire record
by using the Undo button, or closing the form. The txtDateWorked.Exit
procedure then performs a txtDateWorked.Undo call, followed by a call
to txtDateWorked.SetFocus.

My problem is, that when I select either the Undo button, or the Close
button, the txtDateWorked.Exit procedure is called again, with the
date--which I know is invalid--being checked again. This results in
the message window appearing, and me being left in a continous loop,
which I cannot get out of.

What am I doing wrong?

Regards

Duncs

 

[Marshall Barton]

My form has several data entry controls on it, the first of which is a
date control. There is validation in the txtDateWorked.Exit procedure
which validates the entered date. This is to ensure that the date is
not in the previous week, nor is it more than two weeks in advance of
the current system date.

If an invalid date is entered, a custom message window appears advising
the user that an invalid date has been entered. This give sthem the
option of either entering a valid date, or cancelling the entire record
by using the Undo button, or closing the form. The txtDateWorked.Exit
procedure then performs a txtDateWorked.Undo call, followed by a call
to txtDateWorked.SetFocus.

My problem is, that when I select either the Undo button, or the Close
button, the txtDateWorked.Exit procedure is called again, with the
date--which I know is invalid--being checked again. This results in
the message window appearing, and me being left in a continous loop,
which I cannot get out of.

Use the text box's BeforeUpdate event instead of the Exit
event. This way you can cancel the event if the value
doesn't pass muster and there is no need to fiddle with the
focus.

 

[True.Kilted.Scot]

Marshall,

I've changed the code to the BeforeUpdate event. It now looks like
this:

Private Sub txtDateWorked_BeforeUpdate(Cancel As Integer)

Dim datStartOfValidPeriod As Date
Dim datEndOfValidPeriod As Date


' Ensure date is within current week, and no more than two
weeks in advance
datStartOfValidPeriod = DateAdd("d", 1 - DatePart("w", Date,
vbMonday), Date)
datEndOfValidPeriod = DateAdd("ww", 2, datStartOfValidPeriod)

If (Me.fldDateWorked < datStartOfValidPeriod) Or _
(Me.fldDateWorked >= datEndOfValidPeriod) Then
If ((Time >= #12:00:00 PM#) And (DatePart("w", Date, vbMonday)
= 1)) Then
Call CustomMsgBox("!!! Invalid Date Entry !!!", _
"You are attempting to add an entry", 0,
vbBlue, 2, _
"for a date that is either:", 0, vbBlue, 2, _
"Before the start of the current week", 0,
vbBlue, 2, _
"OR", -1, vbRed, 2, _
"More than two weeks in advance of the", 0,
vbBlue, 2, _
"start of the current week.", 0, vbBlue, 2, _
"Either re-enter a valid date or cancel the",
0, vbBlue, 2, _
"record entry by selecting 'Undo'", 0, vbBlue,
2, _
"", _
"", 0)

Cancel = True
booValidRecord = False
End If
End If

End Sub

When I enter an invalid date, the message box appears, as I would
expect. If however, I then click on the "Undo" button, the above code,
txtDateWorked_BeforeUpdate, is re-executed. As the date field is still
invalid, it displays the message box again. I click OK, it takes me
back to the form, I click "Undo", and the same thing happens again,
over and over and over.

What am I missing?

Duncs

 

[Marshall Barton]

I've changed the code to the BeforeUpdate event. It now looks like
this:

Private Sub txtDateWorked_BeforeUpdate(Cancel As Integer)

Dim datStartOfValidPeriod As Date
Dim datEndOfValidPeriod As Date


' Ensure date is within current week, and no more than two
weeks in advance
datStartOfValidPeriod = DateAdd("d", 1 - DatePart("w", Date,
vbMonday), Date)
datEndOfValidPeriod = DateAdd("ww", 2, datStartOfValidPeriod)

If (Me.fldDateWorked < datStartOfValidPeriod) Or _
(Me.fldDateWorked >= datEndOfValidPeriod) Then
If ((Time >= #12:00:00 PM#) And (DatePart("w", Date, vbMonday)
= 1)) Then
Call CustomMsgBox("!!! Invalid Date Entry !!!", _
"You are attempting to add an entry", 0,
vbBlue, 2, _
"for a date that is either:", 0, vbBlue, 2, _
"Before the start of the current week", 0,
vbBlue, 2, _
"OR", -1, vbRed, 2, _
"More than two weeks in advance of the", 0,
vbBlue, 2, _
"start of the current week.", 0, vbBlue, 2, _
"Either re-enter a valid date or cancel the",
0, vbBlue, 2, _
"record entry by selecting 'Undo'", 0, vbBlue,
2, _
"", _
"", 0)

Cancel = True
booValidRecord = False
End If
End If

End Sub

When I enter an invalid date, the message box appears, as I would
expect. If however, I then click on the "Undo" button, the above code,
txtDateWorked_BeforeUpdate, is re-executed. As the date field is still
invalid, it displays the message box again. I click OK, it takes me
back to the form, I click "Undo", and the same thing happens again,
over and over and over.

Well, I don't know what your "undo" button is doing, but if
the DateWorked text box is bound and if you always want to
undo the invalid entry, then just add the line:
Me.txtDateWorked.Undo
after the Cancel line.

I don't understand how an "undo" can cause the BeforeUpdate
event to fire unless you are doing something strange (e.g.
setting the text box's .Text property) so I think we need to
investigate what's going on with this button. You may not
even need such a button.

I just had another thought. With the Cancel = True the
focus will not leave the text box until it has a valid
value. If your "Undo" operation is setting the text box to
Null (i.e. clearing it) then you should add code to the
BeforeUpdate event to accept a Null value.

 

[True.Kilted.Scot]

Marshall,

Thanks for the reply.

My "Undo" button has the following code attached to it:

Private Sub cmdUndo_Click()

If Me.Dirty Then
If MsgBox("Are you sure you want to cancel the changes to this
record?", _
vbYesNo + vbQuestion, "Cancel changes?") = vbYes Then
Me.Undo
End If
End If

End Sub

However, if I set a breakpoint on the first line of the above event and
run the code, the "undo" event is not executed.

The process followed seems to be as follows:

1. I enter an invalid date into the DateWorked field and press
TAB
2. The txtDateWorked_BeforeUpdate event is triggered and a
message box appears
advising me of the invalid date I have entered
3. I close the message box and I press the Undo button, as I am
unsure as to what
date should be entered
4. I then get the message box again, advising me of the problem
with the date. The breakpoint
that was set in the Undo event, HAS NOT been triggered.

I've no idea why the Undo event is not being triggered.

Do you?

Rgds

Duncs

 

[Marshall Barton]

My "Undo" button has the following code attached to it:

Private Sub cmdUndo_Click()

If Me.Dirty Then
If MsgBox("Are you sure you want to cancel the changes to this
record?", _
vbYesNo + vbQuestion, "Cancel changes?") = vbYes Then
Me.Undo
End If
End If

End Sub

However, if I set a breakpoint on the first line of the above event and
run the code, the "undo" event is not executed.

The process followed seems to be as follows:

1. I enter an invalid date into the DateWorked field and press
TAB
2. The txtDateWorked_BeforeUpdate event is triggered and a
message box appears
advising me of the invalid date I have entered
3. I close the message box and I press the Undo button, as I am
unsure as to what
date should be entered
4. I then get the message box again, advising me of the problem
with the date. The breakpoint
that was set in the Undo event, HAS NOT been triggered.

I've no idea why the Undo event is not being triggered.

I have not used the Undo event, but I seem to remember
something about it not operating under some(?) conditions.

Have you tried to undo the date text box?

I am stumped. The Undo method should not be triggering
control events. The only straw grasping, last gasp thing I
can come up with is either you have some code somewhere that
is causing it or the form is corrupted.