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:
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.