Computed Field in ACCESS

[rod_ampon]

Hi,

I'm trying to create a data entry form that will validate the data entered by user. The requirement is as follows:

columns:
Date of Birth, Age, Lastname

validation:
Age = can only accept >4 and <16

computed field:
Age = computed based on Date of Birth and current year.

error tracking:
User may only enter data with age >4 and <16, if not, message will be displayed and focus will go back to "Date of Birth" column.

code:
Option Compare Database
Dim AgeNumber As Integer

Private Sub DOB_AfterUpdate()

AgeNumber = Year(Now()) - Year(Me.DOB)
If AgeNumber > 4 And AgeNumber < 16 Then
Me.AGE = Year(Now()) - Year(Me.DOB)
Else
MsgBox "Can only accept age between 4 and 16 yrs old.", vbInformation, "Error..."
Me.DOB.SetFocus
End If

End Sub


Above script not working and focus will still go to the next column which is "Last Name"

Your help will be very much appreciated.

Thanks,
Rod

 

[bmacr]

You should use the BeforeUpdate event for this. You can then put Cancel = True after your MsgBox statement and focus should remain in your DOB field.

Another way would be to set the Validation Rule and Validation Text properties for the field in the underlying table's design view.

 

[rod_ampon]

This is now working. Thank you very much!

Cheers,
Rod