Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Reminder POPUPs

Introduction

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-Office2000 or later) and opens 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 it with others.

The reminder popup can be of any future event that falls between the current date and within the next 7 days or so that we need advanced 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.

The Demo Project

We will try an example with the Employees Table from the Northwind.mdb database. Import the Employees Table from the 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 the Employees table create a Query with the SQL String given below. Open a new Query in the 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 set that we have specified for testing. If there are no output records, then open the Employees Table and change a few records' Months in the Birth Date field to the current month or the month that falls within the 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 the 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

The Trial Run

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 a 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 an accompaniment to the event.

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

Share:

3 comments:

  1. you solutiopn to database are very ok but may i ask is it possiblr to
    use ms access to develope a complete software to run an organization? if yes please send me some samples.

    ReplyDelete
  2. To Run an Organization you may need several Applications like Accounting Package, Inventory Control, Payroll and so on. All of them cannot be fit into one Database. Microsoft Access is good for developing small Applications that can be shared by few Users on a Network. 

    You may engage Software Experts to study your Organizaton's specific requirements and seek their advise. 

    Regards, 

    ReplyDelete
  3. [...] updating of records. Take a look at a simple example that is used for a Birthday reminder: Reminder PopUps. __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced [...]

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code