How do I change the year 89 to 1989 in txt box.

[Dennis]

Hi,

I'm runnining Access 2003.

I allow the user to enter a two digit number for the year and then want to
expand it to a 4 digit year.

I have some edits and the code to add either 1900 or 2000 to the 2 digit
date. My question is where do I put the code - in the Before Update or After
Update event for the text box.


The following code is currently in the text box's Before Update event:

Dim intCurrYr As Integer
Dim strErrMsg As String
Dim intErrButton As Integer
Dim strMsgResp As String

intCurrYr = Format(Date, "yyyy")
If IsNull(Me.txtMilEntryYr) = False Then
If IsNumeric(Me.txtMilEntryYr) Then
If Me.txtMilEntryYr >= 0 And Me.txtMilEntryYr <= 29 Then
Me.txtMilEntryYr = 2000 + Me.txtMilEntryYr '
Default 0 to 29 to 2000 thru 2029
End If
If Me.txtMilEntryYr >= 30 And Me.txtMilEntryYr <= 99 Then
Me.txtMilEntryYr = 1900 + Me.txtMilEntryYr
End If
' If the date is before 1930 or after the Current Year, then
display an error message and cancel the field update.
If Me.txtMilEntryYr < 1930 Or Me.txtMilEntryYr > intCurrYr Then
Cancel = True
intErrButton = vbOKOnly + vbCritical
strErrMsg = "You entered of " & Me.txtMilEntryYr & "for the
year is invalid." & vbCrLf
strErrMsg = strErrMsg & "Please enter a year that is between
1930 and " & intCurrYr & "."
strMsgResp = MsgBox(strErrMsg, intErrButton, "Military Entry
Year is incorrect")
End If
End If
End If


Everything works ok, but when it executes the line

Me.txtMilEntryYr = 1900 + Me.txtMilEntryYr

I recevie a error message:

"The macro or function set to the BeforeUpdate or ValidationRule property
for this field is preventing Access from saving the data in the field."

I'm guessing that I can not change the value of txtMilEntryYr within the
Before Update event.

What is the best way to accomplish adding 1900 or 2000 to the 2 digit year?

 

[PieterLinden via AccessMonster.com]

Hi,

I'm runnining Access 2003.

I allow the user to enter a two digit number for the year and then want to
expand it to a 4 digit year.

I have some edits and the code to add either 1900 or 2000 to the 2 digit
date. My question is where do I put the code - in the Before Update or After
Update event for the text box.

The following code is currently in the text box's Before Update event:

Dim intCurrYr As Integer
Dim strErrMsg As String
Dim intErrButton As Integer
Dim strMsgResp As String

' THIS IS THE WRONG WAY TO DO IT...
' intCurrYr = Format(Date, "yyyy")

If IsNull(Me.txtMilEntryYr) = False Then
If IsNumeric(Me.txtMilEntryYr) Then
If Me.txtMilEntryYr >= 0 And Me.txtMilEntryYr <= 29 Then
Me.txtMilEntryYr = 2000 + Me.txtMilEntryYr '
Default 0 to 29 to 2000 thru 2029
End If
If Me.txtMilEntryYr >= 30 And Me.txtMilEntryYr <= 99 Then
Me.txtMilEntryYr = 1900 + Me.txtMilEntryYr
End If
' If the date is before 1930 or after the Current Year, then
display an error message and cancel the field update.
If Me.txtMilEntryYr < 1930 Or Me.txtMilEntryYr > intCurrYr Then
Cancel = True
intErrButton = vbOKOnly + vbCritical
strErrMsg = "You entered of " & Me.txtMilEntryYr & "for the
year is invalid." & vbCrLf
strErrMsg = strErrMsg & "Please enter a year that is between
1930 and " & intCurrYr & "."
strMsgResp = MsgBox(strErrMsg, intErrButton, "Military Entry
Year is incorrect")
End If
End If
End If

Everything works ok, but when it executes the line

Me.txtMilEntryYr = 1900 + Me.txtMilEntryYr

I recevie a error message:

"The macro or function set to the BeforeUpdate or ValidationRule property
for this field is preventing Access from saving the data in the field."

I'm guessing that I can not change the value of txtMilEntryYr within the
Before Update event.

What is the best way to accomplish adding 1900 or 2000 to the 2 digit year?

the Format() function returns a STRING. You need to use CINT() to convert a
text value to an integer. Then your code should work.

 

[Dennis]

Pieter

Thank you for you suggestion. I have done that and I still have the same
problem.

I have used debug and according to the debugger, the error is occuring on
the two lines within the IF statement:

Me.txtMilEntryYr = 2000 + Me.txtMilEntryYr

Me.txtMilEntryYr = 1900 + Me.txtMilEntryYr

My guess is I'm in the txtMilEnryYr's Before Update event and I am trying to
change the values, which would cause the Before Update event to fire again.
(Just a guess)

But the lines on which it is failing have nothing to do with intCurrYr.

Thanks

 

[anlu]

I'm guessing that I can not change the value of txtMilEntryYr within the
Before Update event.

What is the best way to accomplish adding 1900 or 2000 to the 2 digit year?

Hi Dennis,

Why not execute the code in the LostFocus event? I think that could work for
you.

Regards,
anlu

 

[Dirk Goldgar]

Pieter

Thank you for you suggestion. I have done that and I still have the same
problem.

I have used debug and according to the debugger, the error is occuring on
the two lines within the IF statement:

Me.txtMilEntryYr = 2000 + Me.txtMilEntryYr

Me.txtMilEntryYr = 1900 + Me.txtMilEntryYr

My guess is I'm in the txtMilEnryYr's Before Update event and I am trying
to
change the values, which would cause the Before Update event to fire
again.
(Just a guess)

But the lines on which it is failing have nothing to do with intCurrYr.

You're right -- you can't change the value of a control in that control's
BeforeUpdate event. Why not do it in the control's AfterUpdate event?

I haven't looked at your code to see if it has anything wrong with it aside
from that.

 

[Dennis]

All,

I am trying to edit the value of the control item after the user entered the
value. I moved the portion of code which changes the value from the Before
Update to After Update event and everything works fine.

Where is the correct place to edit the user entered data and then change it
if needed?
- In the control's Before Update event?
- In the control's After Update event?
- In the control's Lost Focus event?

 

[KenSheridan via AccessMonster.com]

Actually you can quite happily assign a string expression to a variable of
integer data type if the expression is capable of evaluation as an integer
number, though sometimes with results which might not be foreseen:

Try this:

Dim intYear As Integer
Dim intNumber As Integer

intYear = Format(VBA.Date, "yyyy")
Debug.Print intYear
' as you'd expect

intYear = Year(VBA.Date)
Debug.Print intYear
' again as you'd expect

intNumber = "4E2"
Debug.Print intNumber
' maybe not what's expected

' now try entering
intNumber = 4E2
Debug.Print intNumber

I've no doubt you understand why the last two examples do what they do but an
inexperienced developer might not. The last gives the clue as to why the
penultimate one does what it does of course.

Ken Sheridan
Stafford, England

Hi,

[quoted text clipped - 14 lines]

Dim strMsgResp As String

' THIS IS THE WRONG WAY TO DO IT...
' intCurrYr = Format(Date, "yyyy")

If IsNull(Me.txtMilEntryYr) = False Then
If IsNumeric(Me.txtMilEntryYr) Then

[quoted text clipped - 33 lines]

What is the best way to accomplish adding 1900 or 2000 to the 2 digit year?

the Format() function returns a STRING. You need to use CINT() to convert a
text value to an integer. Then your code should work.