By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,759 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

Birthday Remainder VBA...

P: 17
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!!!
Apr 27 '15 #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 1,043
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() ?
Apr 27 '15 #2

P: 3

Expand|Select|Wrap|Line Numbers
  1. Public Function fDOBNotices() As Boolean
  2.     Dim db As DAO.Database, rs As DAO.Recordset
  3.     Dim strTo As String, strSubject As String, strBody As String
  5.     Set db = CurrentDb()
  6.     Set rs = db.OpenRecordset("DOBNotification")
  8.     strTo = ""
  9.     strSubject = "Birthday Notification"
  10.     strBody = "This is to inform you that the following people will be celebrating their birthday tomorrow!" & vbNewLine
  11.     If rs.RecordCount <> 0 Then
  12.         Do While Not rs.EOF
  13.             strBody = strBody & rs.Fields("PersonName") & vbNewLine
  14.             rs.MoveNext
  15.         Loop
  16.         DoCmd.SendObject To:= strTo, Subject:= strSubject, MessageText:= strBody, EditMessage:=False
  17.     End If
  18.     Set rs = Nothing
  19.     Set db = Nothing
  20. End Function
May be this code will help you out and let me know the result whether it will work for you or not.
Apr 28 '15 #3

Expert Mod 5K+
P: 5,397
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
Apr 28 '15 #4

P: 17
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!!!

Apr 29 '15 #5

P: 17
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Sub cmbFind_AfterUpdate()
  5.     ' Find the record that matches the control.
  6.     Dim rs As Object
  8.     Set rs = Me.Recordset.Clone
  9.     rs.FindFirst "[PersonID] = " & Str(Nz(Me![cmbFind], 0))
  10.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  11. End Sub
  14. Private Sub Form_Current()
  15.   If DCount("PersonID", "qryBirthdayToday", "PersonID =" & Nz(Me.PersonID, 0)) > 0 Then
  16.     Me.labBirthday.Visible = True
  17. '    MsgBox "It's " & Me.PersonName & "'s Birthday Today"
  18.   Else
  19.     Me.labBirthday.Visible = False
  20.   End If
  21. End Sub
  23. Private Sub Form_Open(Cancel As Integer)
  24.   If DCount("PersonID", "qryBirthdayToday") > 0 Then
  25.     DoCmd.OpenForm "frmBirthdaysToday", acNormal
  26.   End If
  27. End Sub
  29. Query Design Code:
  30. SELECT tblPersons.PersonID, tblPersons.PersonName, tblPersons.DOB, Day([DOB]) & Month([DOB]) AS DM
  31. FROM tblPersons
  32. WHERE (((Day([DOB]) & Month([DOB]))=Day(Date()) & Month(Date())));
Apr 29 '15 #6

Post your reply

Sign in to post your reply or Sign up for a free account.