Message box when DOB meets certain criteria

[SamMexico via AccessMonster.com]

Hi all,

I am a relative novice to the ways of Access, I am currently setting up a
database in Access 2003.

I have a form field (DOB) that if the date of birth entered makes the person
between 2 and 16 years old I would like a message box to appear notifying the
user (and be able to turn it off). Similarly if an existing record is opened
and a child has it's second birthday I would also like the message to appear..
.I have posted a similar request and have got thus far:

Private Sub DOB_BeforeUpdate(Cancel As Integer)
If DateAdd("yyyy", 2, [DOB]) > Date Or DateAdd _
("yyyy", 16, [DOB]) < Date Then
MsgBox "Please contact the relevant department"
Cancel = False
End If
End Sub

Any help would be vert much appreciated...

Sam

 

[Tom van Stiphout]

On Mon, 19 Apr 2010 12:34:22 GMT, "SamMexico via AccessMonster.com"

For testing age between 2 and 16 you need an AND condition, not an OR:
If DateAdd("yyyy", 2, [DOB]) > Date AND ...

If you want to test for exactly 2nd birthday, that would be:
If DateAdd("yyyy", 2, [DOB]) = Date then msgbox "Happy 2nd b'day"

-Tom.
Microsoft Access MVP

 

[Steve Sanford]

.................................................Similarly if an existing
record is opened

and a child has it's second birthday I would also like the message to appear..

Where you have the code now, "DOB_BeforeUpdate", only fires when you
add/change the DOB.

You'll also need the code in the Form Current event to have the message
display for existing records.

user (and be able to turn it off).

Do you mean that you want to be able to not have the message box display for
any record at any time, or when the message box appears, you want to close
the message box but still have it available to display if the DOB is between
2 and 16??


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi all,

I am a relative novice to the ways of Access, I am currently setting up a
database in Access 2003.

I have a form field (DOB) that if the date of birth entered makes the person
between 2 and 16 years old I would like a message box to appear notifying the
user (and be able to turn it off). Similarly if an existing record is opened
and a child has it's second birthday I would also like the message to appear..
.I have posted a similar request and have got thus far:

Private Sub DOB_BeforeUpdate(Cancel As Integer)
If DateAdd("yyyy", 2, [DOB]) > Date Or DateAdd _
("yyyy", 16, [DOB]) < Date Then
MsgBox "Please contact the relevant department"
Cancel = False
End If
End Sub

Any help would be vert much appreciated...

Sam

--



.

 

[SamMexico via AccessMonster.com]

Hi Steve, thanks for the response. When I open the record in the form (by
search box) and the person has a birth date that means they have reached the
age of 2 I would like a message box to appear so that the user can notify the
relevant department. Similarly if I enter a new record and the person is
between the ages of 2 and 16 I would like the same message to appear.

This message should only appear once and not everytime I open a record where,
say, the person is 14...

I hope that makes sense....?

Sam

 

[Steve Sanford]

OK, the message box should appear only one per person if their age is between
2 and 16.

Just so we're using the same terms, Tables have fields, Forms have controls.
Controls are unbound or bound to fields.

One way would be to add a field to the table where the DOB is stored. Maybe
call it "Notified"; the datatype should be Boolean.

Add a control (a check box) bound to the field to the form.


Then I would modify to:

'----------------------------------
Private Sub DOB_BeforeUpdate(Cancel As Integer)

If Not Me.Notified Then
'age between 2 and 16?
If DateAdd("yyyy", 2, [DOB]) > Date Or DateAdd _
("yyyy", 16, [DOB]) < Date Then

MsgBox "Please contact the relevant department"
'You've been notified, so set the flag
Me.Notified = True

End If
End If
End Sub
'----------------------------------

If you add a person (baby), the message box won't open because it is too
young. But if you open the form in two years, the message box should appear.
If you don't also have the code in the form current event, you will never be
notified.

So, you need this code also (could be needed for more than one form):

'--------------------------
Private Sub Form_Current()

If Not Me.Notified Then
'age between 2 and 16?
If DateAdd("yyyy", 2, [DOB]) > Date Or DateAdd _
("yyyy", 16, [DOB]) < Date Then

MsgBox "Please contact the relevant department"
'You've been notified, so set the flag
Me.Notified = True

End If
End If

End Sub
'--------------------------

Like I said, there are several ways to do this. Maybe someone else will have
a better way.

HTH