Forum Discussion
Create a macro on a date field
If Date of visit < Date of birth -18
Then message box "patient must be at least 18."
Nothing happens when I test this and enter a Date of birth that would make the patient less than 18.
What else is needed to make this work?
Thank you.
5 Replies
First, that calculation is going to be inaccurate half the time. People age on their birthdate, not on the last day of a year. So on any given day PRIOR to that birthdate during the year they will be 17 and on any given day AFTER that birthedate they will be 18, and so on, year by year.
There are a number of ways to calculate current age accurately as of the current date. Here's one.
Function AgeOnLastBirthdate(ByVal Bdate As Date) As String
Dim intPreBirthdayYear As Integer
intPreBirthdayYear = Date < DateSerial(Year(Date), Month(Bdate), Day(Bdate))
AgeOnLastBirthdate = DateDiff("yyyy", Bdate, Date) + intPreBirthdayYear & " yrs"End Function
Second, this is going to be a lot easier in VBA than a macro. Macros are simple, yes, but kind of too simple.
Use the Before Update event of the control on the form.
Private Sub DateofVisit_BeforeUpdate(Cancel As Integer)
If CurrentAge(DateofBirth].Text) < 18 Then
Cancel = True
MsgBox Prompt:="Patient must be at least 18", Buttons:=vbOkOnly, Title:="Too Young"
Me.DateofVisit.Undo
End if
End Sub
You have to use the .Text property of the control instead of the .Value property because the control is not yet saved.
- Thank you, George. I'm going to give this a try! Best, Mary
- Continued success with the project.
- i think the control will Retain the Value even if the record is not yet saved.
you can test it on the BeforeUpdate event of the control.- "...the control will Retain the Value ..."
Yes, that's the case. Hence the line: Me.DateofVisit.Undo in the suggested response.
And, you may also have missed that I DID suggest the BeforeUpdate event.
