The Reminder Popup is a Report that opens and displays its contents automatically and plays a background sound to remind about something that needs the User’s immediate attention. The Report is exported into an external independent file format (Access Snapshot File Format, need MS-Office 2000 or later) and opens it in an external Window.
We can open and view Access Reports exported into Access Snapshot File Format independently without the use of Microsoft Access and easily transport the Report alone through e-mails and share with others.
The reminder can be of any future event that falls between current date and within next 7 days or so that we need advance information about, like appointments, conferences, Birthdays etc.
This action is programmed into the Control Screen module with the help of the Form_Timer() Event Procedure.
We will try an example with the Employees Table from the Northwind.mdb database. Import the Employees Table from Northwind.mdb sample database. If you are not sure about the exact location of the sample database, go to the page Saving Data on Forms not in Table for the location references.
After importing Employees table create a Query with the SQL String given below. Open a new Query in design view (don’t select any table or Query from the displayed list) and display the SQL Window. Copy and paste the SQL String given below into the SQL Window of the Query and save it 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 Query manually in normal view and check whether there is any record present in the Query output based on the criteria setting that we have specified for testing. If there are no output records then open the Employees Table and change few record’s Month in the BirthDate field to current month or the month that falls within next 30 days. At the end of the Query String you can see an expression Date()+30 set in the Criteria part for testing purposes. After opening the Query in design view you can change this value according to your needs, like +7 or +15 etc. depending on how many days in advance you would like to know about the event.
Design a Report using Birthday_Reminder Query as Record Source, like the sample given below and save the Report with the name Birthday_Reminder:
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
When the Main Form with the Code is open, the Form_Load() Event Procedure checks the presence of any record in the Birthday_Reminder Query. If there are records in the Source Query then the Form_Timer() Event Procedure is invoked with quarter of a second interval incrementing the globally declared variable T. When the value in variable T reaches 20 (5 seconds) the REMPOPUP() Procedure runs and the Birthday_Reminder Report will open in Access Snapshot Format. Windows Media Player will play the notify.wav file as accompaniment to the event.
After that the Reminder Popup opens in hourly intervals. If this repetition is not required then enable the statement (remove the ‘ character at the beginning)
Me.timerinterval = 0
in the Form_Timer() event procedure, in that case the Popup opens only once per Session.