Birthday Remainder VBA...

[Anbusds]

Hi All,
I would like to have a birthday remainder on access form load event VBA...
i have tblEmp with 2 fields,
Name DOB
A 22/04/1977
B 25/03/1965
C 17/08/1985

i would like to compare Date and month with my system date to show in the message box "Mr. A Birthday Today" on the Form load event...
Thanks in advance!!!

[Luuk]

Remainder is what is left over from a mathematical calculation
Reminder is an alarm for a date or a meeting

What id you try to accomplish this?

You do know the function(s): DAY() and MONTH() ?

[MonikaSR]

Hi,

Public Function fDOBNotices() As Boolean
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim strTo As String, strSubject As String, strBody As String
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("DOBNotification")
 
    strTo = "SOME_USER@SOME_SERVER.com"
    strSubject = "Birthday Notification"
    strBody = "This is to inform you that the following people will be celebrating their birthday tomorrow!" & vbNewLine
    If rs.RecordCount <> 0 Then
        Do While Not rs.EOF
            strBody = strBody & rs.Fields("PersonName") & vbNewLine
            rs.MoveNext
        Loop
        DoCmd.SendObject To:= strTo, Subject:= strSubject, MessageText:= strBody, EditMessage:=False
    End If
    Set rs = Nothing
    Set db = Nothing
End Function

May be this code will help you out and let me know the result whether it will work for you or not.

[zmbd]

Note in Monika's code line 6:
Set rs = db.OpenRecordset("DOBNotification")
Appears to require that query [DOBNotification] be available.
The SQL for this can be hardcoded into the VBA script as well.

Line 8 in Monika's code, I've removed the personal email address. We don't allow personal email addresses to be posted for the protection of our members. Too many spammers and criminals out there. This code could be modified to use a recordset that loops thru a set of email addresses to send the email to a group of people. Build the string in lines 11 thru 15 then use the email recordset looped around line 16.

This code may fail if not using Outlook as the primary email program if it doesn't use the MAPI interface.

Another thing to note, Anbusds is asking for a message to appear on screen during the form_load event, not an email. However, Anbusds does not indicate wither this should be a message box, a list box, show up on the form itself, a modal popup etc... all of the messages can be done by taking the basic loop concept in lines 11 thru 15 in Monika's code. Personally, I would setup a simple list box with the [record source] set to a query (either stored or at form level) that pulls against the DOB equal to today.

Finally, it is very important to note that internally, MSACCESS handles all dates in the #MM/DD/YYYY# format regardless of your local settings.
Allen Browne: International Dates in Access

[Anbusds]

Hi Monika,
Just to inform you previously i have tried this code not working & am using access 2003,
is it the reason?
Have you tried this code?
please let me know if any updates!!!
Thanks...

Anbu

[Anbusds]

Hi Zmbd,
Noted, i have found a solution for message box appears once you open the form, send via email also more useful than just a message box...
let me have your solution for send via email...
*Following code for appear as just message box after you open the form*
Form Code:

Option Compare Database
Option Explicit
 
Private Sub cmbFind_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[PersonID] = " & Str(Nz(Me![cmbFind], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 
 
Private Sub Form_Current()
  If DCount("PersonID", "qryBirthdayToday", "PersonID =" & Nz(Me.PersonID, 0)) > 0 Then
    Me.labBirthday.Visible = True
'    MsgBox "It's " & Me.PersonName & "'s Birthday Today"
  Else
    Me.labBirthday.Visible = False
  End If
End Sub
 
Private Sub Form_Open(Cancel As Integer)
  If DCount("PersonID", "qryBirthdayToday") > 0 Then
    DoCmd.OpenForm "frmBirthdaysToday", acNormal
  End If
End Sub
 
Query Design Code:
SELECT tblPersons.PersonID, tblPersons.PersonName, tblPersons.DOB, Day([DOB]) & Month([DOB]) AS DM
FROM tblPersons
WHERE (((Day([DOB]) & Month([DOB]))=Day(Date()) & Month(Date())));

Anbu...