Reminder POPUPs

Introduction.

The Reminder Popup is a specially designed report that opens automatically, displays important content, and plays a background sound to draw the user's immediate attention. It is exported in Access Snapshot File Format (available in Microsoft Office 2000 and later), allowing it to open in a separate window independent of Microsoft Access.

Reports in Snapshot format can be viewed without Access, making them ideal for sharing via email or transporting as standalone files.

This reminder pop-up is useful for upcoming events—such as appointments, conferences, or birthdays—that fall within the next 7 days and require advance notice. The pop-up functionality is triggered through the Form_Timer() event procedure is coded in the Control Screen module.

The Demo Project.

Let’s try an example using the Employees table from the Northwind.mdb sample database. Begin by importing the Employees table into your current database.

If you're unsure about the location of the sample Northwind.mdb file, refer to the instructions provided on the page Saving Data on Forms Not in Table for guidance on locating it.

After importing the Employees table, create a new query using the SQL string provided below.
To do this, open a new query in Design View—when prompted, do not select any tables or queries from the list.

Instead, switch to the SQL View and paste the SQL string into the window.
Save the query with the name Birthday_Reminder.

SELECT Employees.EmployeeID,
 [TitleofCourtesy] & " " & [FirstName] & " " & [LastName] AS Name,
 Employees.BirthDate,
 DateValue(Format([birthdate],
 "dd-mm") & "-" & Year(Date())) AS BirthDay,
 DateDiff("yyyy",[birthdate],
 [BirthDay]) AS age
FROM Employees
WHERE (((DateValue(Format([birthdate],"dd-mm") & "-" & Year(Date()))) Between Date() And Date()+30));

Open the Birthday_Reminder query in Datasheet View to check if any records are returned based on the specified criteria.
If no records appear, open the Employees table and modify a few records by changing the Birth Date field so the month falls within the current month or within the next 30 days.

At the end of the query’s SQL string, you'll notice a condition like Date() + 30 used for testing.
You can adjust this value in Design View to suit your needs—for example, +7 or +15—depending on how many days in advance you want to be notified of upcoming birthdays or events.

Design a Report using Birthday_Reminder Query as Record Source, like the sample given below, and save the Report with the name Birthday_Reminder:

Click to Enlarge

Copy and paste the following Code into the VB Module of the Control Screen or the Main Screen of your Application.

'Global Declaration
Dim T As Long
'-------------------------------------------------------
Private Sub Form_Load()
Dim RCount

On Error GoTo Form_Load_Err
DoCmd.Restore

T = 0
RCount = DCount("*", "BirthDay_Reminder")
'if no records in BirthDay_Reminder Query then
'control is not passed into the Timer Event procedure

If RCount > 0 Then
    Me.TimerInterval = 250
End If

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Err.Description, , "Form_Load"
Resume Form_Load_Exit
End Sub


Private Sub Form_Timer()
On Error GoTo Form_Timer_Err

T = T + 1
Select Case T
    Case 20
        REMPOPUP
        'Me.Timerinterval = 0
    Case 260
        REMPOPUP
    Case 261
      T = 21
End Select

Form_Timer_Exit:
Exit Sub

Form_Timer_Err:
MsgBox Err.Description, , "Form_Timer"
Resume Form_Timer_Exit
End Sub

Private Function REMPOPUP()
Dim strPath As String, i As Integer, mplayerc As String
Dim mplayer As String, soundC As String
On Error GoTo REMPOPUP_Err

mplayerc = "C:\Program Files\Windows Media Player\mplayer2.exe " 'WindowsXP

soundC = "C:\Windows\Media\notify.wav" 'WindowsXP

'if media player2 not found then don't play sound
If Len(Dir(mplayerc)) > 0 Then
     mplayer = mplayerc & soundC
    Call Shell(mplayer, vbMinimizedNoFocus)
End If

strPath = "C:\Windows\Temp\BirthDay_Reminder.snp"

DoCmd.OutputTo acOutputReport, "BirthDay_Reminder", _"SnapshotFormat(*.snp)", strPath, True, ""

'if snapshot format is not available 
'disable the previous line and enable next line

'DoCmd.OpenReport "BirthDay_Reminder", acViewPreview

REMPOPUP_Exit:
Exit Function

REMPOPUP_Err:
MsgBox Err.Description, , "REMPOPUP"
Resume REMPOPUP_Exit
End Function

The Trial Run

When the main form containing the code is opened, the Form_Load() event procedure checks whether the Birthday_Reminder query returns any records.

If records are found, the Form_Timer() event is activated, running at an interval of 250 milliseconds ( quarter of a second), incrementing a globally declared variable T.

Once the value of T reaches 20 (after 5 seconds), the REMPOPUP() procedure is executed. This opens the Birthday_Reminder report in Access Snapshot Format. At the same time, Windows Media Player plays the notify.wav sound file to alert the user.

Click to Enlarge

After that, the Reminder Popup opens at hourly intervals. If this repetition is not required, then enable the statement (remove the ' character at the beginning).

Me.Timerinterval = 0

During the Form_Timer() event procedure, in that case, the Popup opens only once per Session.

Download Demo Database.

ReminderPopup.zip

• MS-Access & Graph Charts
• MS-Access & Graph Charts-2
• Highlighting Reports
• Reports Page Border