databasedev.co.uk - database solutions and downloads for microsoft access

Popping Up A Reminder Form in Microsoft Access:

As with anything in Microsoft Access there are several ways to perform an action. It may be necessary to provide reminders of when a job, order or appointment is overdue and this can be acheived using the following process.

In this example, we show when a Job's Expected Completion date has been passed. Suppose we store the following data:

• JobNumber
• JobName
• ExpectedCompletionDate
• Complete

And this data is entered as:

Reminder form, showing uncomplete jobs that are past their Completion Date

Now we somehow need to display this information at a specified event. This can be done using various events, but the example shows this happening when a user opens the database application.

Usually, the first object that is visible when a user opens a database would be a splash screen or a switchboard. With this in mind, we will display a reminder pop-up on opening the database (using the On Load event of the switchboard, which is set to open at StartUp), but prior to displaying the switchboard (if any reminders need to be shown). We do this by adding the following code to the On Load event:

Private Sub Form_Load()

'On Load of the switchboard check Jobs table for any uncompleted jobs

Dim intStore As Integer

'Count of uncomplete jobs that are past the Expected Completion Date
intStore = DCount("[JobNumber]", "[tblJobs]",
"[ExpectedCompletionDate] <=Now() AND [Complete] =0")

'If count of uncomplete jobs is zero display switchboard
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
    If intStore = 0 Then
            Exit Sub
                Else
                    If MsgBox("There are " & intStore & " uncompleted jobs" & _
                    vbCrLf & vbCrLf & "Would you like to see these now?", _
                    vbYesNo, "You Have Uncomplete Jobs...") = vbYes Then
                    DoCmd.Minimize
                    DoCmd.OpenForm "frmReminders", acNormal
                Else
            Exit Sub
        End If
    End If
End Sub

So when the database switchboard loads, and there are uncomplete jobs as shown in the form above we will now recieve the following message box:

Pop-Up reminder message detailing uncomplete jobs.

Clicking Yes will display the frmReminders form, clicking No will open up the switchboard form.

To see an example of this process you can download a Microsoft Access 2000 database from the Microsoft Access Forms menu or the Microsoft Access Downloads menu.