How to Add Birthday Reminders in Your Access Database

Access Database Recovery, Access Solutions June 20, 2017

Learn how you can add birthday reminders to your Access database. Be informed if your client or employee’s birthday is coming up so you can send them your warm greeting.

Sending birthday greetings can be a great way to connect and strengthen relationship with a client or employee. If you’re handling an Access database containing customer/employee records, it can be great to be notified from time to time if a client’s or employee’s birthday is coming up. In this tutorial, you will learn how you can create a function that will notify you of someone’s special day the moment you open the database.

Download Now

If you want to start to use the feature as soon as possible, then you can:

Download the Sample Database with VBA Codes Now

Otherwise, if you want to DIY, you can read the contents below.

Prerequisite

This tutorial assumes that you have a table in your database that contains these 3 fields: first name, last name and birthday.

Steps to Add Birthday Reminders in Access Database

  1. Create a new table and name it BirthdayCelebrants. This will serve as the repository for list of birthday celebrants. Add 3 fields as shown below. No need for a primary key.Table BirthdayCelebrants
  2. Create a form for BirthdayCelebrants. This form will pop up upon opening the database if there are upcoming birthdays.Create A Form For BirthdayCelebrants
  • In the navigation pane, select the BirthdayCelebrants table.
  • Go to Create > Form.
  • A Single Form will be automatically created for you.

Since we are going to display a list of birthday celebrants, I would suggest that you change the Default View of the form to either Datasheet or Continuous Forms. This way, you can have a quick overview of the list.Change The Default View Of The Form

  • While in design view, select the form.
  • Open the Property Sheet (ALT + F11)
  • Go to Format > Default View.
  • Select your desired view – either Datasheet or Continuous Forms.

If you have selected Continuous Forms, edit the design to match your preferences. Here’s how mine look like:Select Continuous Forms And Edit The Design

I have edited the format of the Birthday textbox so that values will be displayed in Long Date format. Also, I made the following updates on the properties of the form:

  • Allow Additions: No
  • Allow Deletions: No
  • Allow Edits: No

When done, close the form and save it as frm_BirthdayCelebrants.

  1. Add the code for the birthday reminder.
    • Add a new module.Add A New Module
    • This will open the VBA Editor.
    • Add the following code and modify it to fit your needs.
Public Function RunBirthdayReminders()
  Dim qry As String, fld_fName As String, fld_lName As String, fld_birthday As String, tbl As String
  Dim db As DAO.Database, rst As DAO.Recordset
 
  'TO DO: Update the ff. variables with the actual name of your table and fields
 
  tbl = "Members" 'table containing your client/employee reocrds
  fld_fName = "FirstName" 'first name field
  fld_lName = "LastName" 'last name field
  fld_birthday = "Birthday" 'birthday field
 
  Set db = CurrentDb
 
  'delete current record(s) from BirthdayCelebrants table
  DoCmd.SetWarnings False
  DoCmd.RunSQL ("DELETE FROM BirthdayCelebrants")
  DoCmd.SetWarnings True
 
  'insert new record(s) in BirthdayCelebrants table using a query that gets list of people celebrating their birthday today.
  qry = "INSERT INTO BirthdayCelebrants " & _
    "SELECT " & fld_fName & ", " & fld_lName & ", " & fld_birthday & " FROM " & tbl & " WHERE " & _
      "Month(" & fld_birthday & ")=Month(Date()) AND Day(" & fld_birthday & ")=Day(Date())"
 
  DoCmd.SetWarnings False
  DoCmd.RunSQL (qry)
  DoCmd.SetWarnings True
 
  Set rst = db.OpenRecordset("SELECT * FROM BirthdayCelebrants")
  If Not rst.EOF Then
    DoCmd.OpenForm ("frm_BirthdayCelebrants")
  End If
 
  Set db = Nothing
  Set rst = Nothing
End Function

Please note that the above code uses a query that gets a list of birthday celebrants for the current day. If you want to get a list of people whose birthday is coming up for the current month, use the following query instead.

'insert new record(s) in BirthdayCelebrants table using a query that gets list of people whose birthday is coming up for this month.
qry = "INSERT INTO BirthdayCelebrants " & _
  "SELECT " & fld_fName & ", " & fld_lName & ", " & fld_birthday & " FROM " & tbl & " WHERE " & _
    "Month(" & fld_birthday & ")=Month(Date()) AND Day(" & fld_birthday & ")>=Day(Date())" & _
      " ORDER BY Day(" & fld_birthday & ") ASC"
  1. Finally, we’re going to create an AutoExec macro. This will run the code upon opening the database.Create An AutoExec Macro
    • Go to Create > Macro.
    • Select RunCode from the dropdown list.
    • For the Function Name, write RunBirthdayReminders().
    • Save the macro as AutoExec and click OK.
  1. Now, test your Birthday Reminder.

Close the database and reopen it. If there are upcoming birthdays, the birthday reminder form should pop up. If none, the database will run as usual.

Quick Reminder

Now for a quick reminder. If one day, you can’t open your database, do not panic. Take the necessary steps to recover it. If you still can’t, try third party software that will fix Access. There’s still hope.

Author Introduction:

Jayme Stack is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.