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.
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.
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
- 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.
- Create a form for BirthdayCelebrants. This form will pop up upon opening the database if there are upcoming birthdays.
- 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.
- 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:
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.
- Add the code for the birthday reminder.
- 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"
- Finally, we’re going to create an AutoExec macro. This will run the code upon opening the database.
- Go to Create > Macro.
- Select RunCode from the dropdown list.
- For the Function Name, write RunBirthdayReminders().
- Save the macro as AutoExec and click OK.
- 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.
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.
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
Great code. Quick question, if the birthday date doesn’t match the code criteria, can you a code for a msg box like ” no data found” or something similar?