Tags: 2000im, access, afterupdate, below, code, current, database, date, field, form, iveput, microsoft, mysql, oracle, sql, validation

Current Year Data Validation

On Database » Microsoft Access

8,587 words with 3 Comments; publish: Mon, 26 May 2008 11:04:00 GMT; (250218.75, « »)

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

All Comments

Leave a comment...

  • 3 Comments
    • William,

      Try changing this line:

      If Year(Me.TADate) <> Now Then

      to

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

      hth,

      Cheryl Fischer, MVP Microsoft Access

      Law/Sys Associates, Houston, TX

      "William Wisnieski" <william.wisnieski.ms-access.todaysummary.com.verizon.net> wrote in message

      news:99gbc.28$_K3.14.ms-access.todaysummary.com.nwrdny01.gnilink.net...

      > 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

      >

      #1; Mon, 26 May 2008 11:06:00 GMT
    • "William Wisnieski" <william.wisnieski.ms-access.todaysummary.com.verizon.net> wrote in message

      news:99gbc.28$_K3.14.ms-access.todaysummary.com.nwrdny01.gnilink.net

      > 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)

      #2; Mon, 26 May 2008 11:06:00 GMT
    • Thanks Dirk....that did the trick!

      "Dirk Goldgar" <dg.ms-access.todaysummary.com.NOdataSPAMgnostics.com> wrote in message

      news:e8vMi6MGEHA.1128.ms-access.todaysummary.com.TK2MSFTNGP11.phx.gbl...

      > "William Wisnieski" <william.wisnieski.ms-access.todaysummary.com.verizon.net> wrote in message

      > news:99gbc.28$_K3.14.ms-access.todaysummary.com.nwrdny01.gnilink.net

      > 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)

      >

      #3; Mon, 26 May 2008 11:07:00 GMT