Current Year Data Validation

[William Wisnieski]

Hello Everyone,

Access 2000

I'm trying to set up data validation code for a date field on a form. I've
put the code below in the AfterUpdate event of the [TADate] field. If the
user enters a date with a year other than the current year, I'd like the
user to be prompted with a question that asks, "do you want to use that
year"? If they answer yes, then the value is accepted and the cursor moves
to the next field [EmployeeID]. If they answer no, the value is not
accepted and the user must enter the current year. I've tried the following
code, but it doesn't work for two reasons: 1) it doesn't recognize when the
year entered is greater than or less than the current year and 2) if the
user selects no, it still accepts the value and sends them to the next field
[EmployeeID] anyway. I also tried putting it on the BeforeUpdate event of
the field but that wouldn't work because I kept getting an error message
saying I couldn't use SetFocus without saving the field. Here's the code:

Private Sub TADate_AfterUpdate()
If Year(Me.TADate) <> Now Then
Dim Msg, Style, Title, Response
Msg = "The year entered is not current. Do you want to use that year?" '
Define message.
Style = vbYesNo + vbCritical ' Define buttons.
Title = "Year Not Current" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes. Move to Next Field.
Me.EmployeeID.SetFocus
Else ' User chose No. Cancel Event and remain on Current Field.
DoCmd.CancelEvent
End If
End If
End Sub

Any help is much appreciated.

William

 

[Cheryl Fischer]

William,

Try changing this line:

If Year(Me.TADate) <> Now Then
to
If Year(Me.TADate) <> Year(Now) Then


hth,

 

[Dirk Goldgar]

Hello Everyone,

Access 2000

I'm trying to set up data validation code for a date field on a form.
I've put the code below in the AfterUpdate event of the [TADate]
field. If the user enters a date with a year other than the current
year, I'd like the user to be prompted with a question that asks, "do
you want to use that year"? If they answer yes, then the value is
accepted and the cursor moves to the next field [EmployeeID]. If
they answer no, the value is not accepted and the user must enter the
current year. I've tried the following code, but it doesn't work for
two reasons: 1) it doesn't recognize when the year entered is
greater than or less than the current year and 2) if the user selects
no, it still accepts the value and sends them to the next field
[EmployeeID] anyway. I also tried putting it on the BeforeUpdate
event of the field but that wouldn't work because I kept getting an
error message saying I couldn't use SetFocus without saving the
field. Here's the code:

Private Sub TADate_AfterUpdate()
If Year(Me.TADate) <> Now Then
Dim Msg, Style, Title, Response
Msg = "The year entered is not current. Do you want to use that
year?" ' Define message.
Style = vbYesNo + vbCritical ' Define buttons.
Title = "Year Not Current" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes. Move to Next Field.
Me.EmployeeID.SetFocus
Else ' User chose No. Cancel Event and remain on Current Field.
DoCmd.CancelEvent
End If
End If
End Sub

Any help is much appreciated.

William

The AfterUpdate event can't be cancelled. Since this is a matter of
control validation, you should put your validation code in the control's
BeforeUpdate event and set the event procedure's Cancel argument if it's
necessary to reject the input. Try it like this:

'----- start of revised code -----
Private Sub TADate_BeforeUpdate(Cancel As Integer)

If IsNull(TADate) Then
' ignore or reject -- your choice
Else

If Year(Me.TADate) <> Year(Date) Then
If MsgBox( _
"The year entered is not current. " & _
"Do you want to use that year?", _
vbYesNo + vbCritical, _
"Year Not Current") _
= vbYes _
Then
' allow focus to move to next control
Else
Cancel = True
End If
End If

End If

End Sub
'----- end of revised code -----

 

[William Wisnieski]

Thanks Dirk.....that did the trick!

Hello Everyone,

Access 2000

I'm trying to set up data validation code for a date field on a form.
I've put the code below in the AfterUpdate event of the [TADate]
field. If the user enters a date with a year other than the current
year, I'd like the user to be prompted with a question that asks, "do
you want to use that year"? If they answer yes, then the value is
accepted and the cursor moves to the next field [EmployeeID]. If
they answer no, the value is not accepted and the user must enter the
current year. I've tried the following code, but it doesn't work for
two reasons: 1) it doesn't recognize when the year entered is
greater than or less than the current year and 2) if the user selects
no, it still accepts the value and sends them to the next field
[EmployeeID] anyway. I also tried putting it on the BeforeUpdate
event of the field but that wouldn't work because I kept getting an
error message saying I couldn't use SetFocus without saving the
field. Here's the code:

Private Sub TADate_AfterUpdate()
If Year(Me.TADate) <> Now Then
Dim Msg, Style, Title, Response
Msg = "The year entered is not current. Do you want to use that
year?" ' Define message.
Style = vbYesNo + vbCritical ' Define buttons.
Title = "Year Not Current" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes. Move to Next Field.
Me.EmployeeID.SetFocus
Else ' User chose No. Cancel Event and remain on Current Field.
DoCmd.CancelEvent
End If
End If
End Sub

Any help is much appreciated.

William

The AfterUpdate event can't be cancelled. Since this is a matter of
control validation, you should put your validation code in the control's
BeforeUpdate event and set the event procedure's Cancel argument if it's
necessary to reject the input. Try it like this:

'----- start of revised code -----
Private Sub TADate_BeforeUpdate(Cancel As Integer)

If IsNull(TADate) Then
' ignore or reject -- your choice
Else

If Year(Me.TADate) <> Year(Date) Then
If MsgBox( _
"The year entered is not current. " & _
"Do you want to use that year?", _
vbYesNo + vbCritical, _
"Year Not Current") _
= vbYes _
Then
' allow focus to move to next control
Else
Cancel = True
End If
End If

End If

End Sub
'----- end of revised code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)