Class Module to trigger and handle field event & record event

Question

I am working on a database for our company. One of the big things they want this database to do is to create reminders and emails based on changed fields and newly created records. For example, when the user puts a date in the First_Meeting field, an event should be triggered that will create 3 reminders on an Outlook Calendar. As a second example, when a new record is created in the Contract table, an event should be triggered to create 2 reminders in an Outlook Calendar and 2 Outlook emails.

I have the logic to do all of this, but I am trying to figure out the best way to handle the events. It is important that the trigger happens on whatever form the First_Meeting field is updated. If I do a form field event, I have to make sure I add the code to all forms that include that field. I am wondering if there is a way to do this with Class modules so that I could fire an event on a table field or record. I have not done any OO, but looked into it a little bit years ago, so I have a very vague understanding of how it works. I apologize that my question is somewhat non-specific, but I don’t want to spend a lot of time on the learning curve of OO & Class Modules only to find out that what I am trying to do cannot be done. On the other hand, if I could do all of this in one place and not have to worry about it going forward that would be well worth any time spent!

My question is: Can I create a class on a table field that would fire an event anytime that field is edited? And can I create a class on a table (or table record) that would fire any time there is a record inserted into the table? What is the logic to accomplish this?

I am using a table to hold all of the items that will be created based on the field that is updated, or record that is created.

I am using Access 2016. Thanks in advance for any help you can give me!!! Kim

This is the event code I am currently using for the First_Meeting Event:

'This code calls a form to select the reminders to create

Private Sub First_Meeting_AfterUpdate()
Dim strSql As String
Dim strWhere As String
Dim strOrderBy As String
Dim intRecordCount As Integer

'Save any changes to data before selecting appointments to set
If Me.Dirty Then
    Me.Dirty = False
End If

'The "Where" keyword is not included here so it can be used for the DCount function
strWhere = " [Appt Defaults].[Field Name]='First Meeting Date'"
strOrderBy = " ORDER BY [Appt Defaults].[Order for List], [Appt Defaults Child].[Date Offset]"

strSql = "SELECT Count([Appt Defaults Child].ID) AS CountOfID " & _
    "FROM [Appt Defaults] INNER JOIN [Appt Defaults Child] ON [Appt Defaults].ID = [Appt Defaults Child].ReminderID"

intRecordCount = DCount("ReminderID", "qDefaultAppts", strWhere)

If intRecordCount > 0 Then

    DoCmd.SetWarnings False
    'Delete records from the Temp table
    DoCmd.RunSQL "Delete * From TempApptToSelect"

    'Add the "Where" keyword to be used in the query
    strWhere = "Where " & strWhere
    strSql = CurrentDb.QueryDefs("[qAddApptsToTemp-MinusCriteria]").SQL
    'The ";" symbol is added to the end of the query so it needs to be stripped off
    strSql = Replace(strSql, ";", "")
    strSql = strSql & strWhere & strOrderBy
    DoCmd.RunSQL strSql
    'Flag all of the events in the Temp Table as Selected
    DoCmd.RunSQL "UPDATE TempApptToSelect SET TempApptToSelect.IsSelected = -1"
    DoCmd.SetWarnings True

    DoCmd.OpenForm "Reminders - Select Main", , , , , , OpenArgs:=Me.Name

End If
End Sub

'This code is from the form where the reminders are selected

Private Sub cmdCreateReminders_Click()
' This Routine copies all of the selected default records from the Appt Defaults tables and copies them to the Reminder Tables
'
Dim rstReminderDefaults As Recordset
Dim rstReminders As Recordset
Dim nID As Integer
Dim dtStartDate As Date
Dim dtStartTime As Date
Dim dtEndTime As Date
Dim strProjectName As String
Dim strProjectAddress As String
Dim strApptArea As String
Dim iCount As Integer

' The calling form has the info needed to set the values for the reminders
' The form "frmCalendarReminders" is generic and will be on all forms that need to set reminders

txtCallingForm = Me.OpenArgs()

'The form recordset is a temp query created from the calling routine which determines the record filter
Set rstReminders = Forms(txtCallingForm)!frmCalendarReminders.Form.RecordsetClone
Set rstReminderDefaults = CurrentDb.OpenRecordset("qApptsToSet")

nID = Forms(txtCallingForm)!ID

strApptArea = Left(rstReminderDefaults![Appt Area], 8)

Select Case strApptArea
    Case "Projects"

        strProjectName = Forms(txtCallingForm)!txtProjectName
        strProjectAddress = Forms(txtCallingForm)!txtProjectAddressLine & vbCrLf & Forms(txtCallingForm)!txtProjectCityLine

        With rstReminderDefaults
            Do While Not .EOF
                'If this reminder has not already been created
                If DCount("ID", "PR_Child-Reminders", "[Project ID] =" & Forms(txtCallingForm)![ID] & " And [ReminderChildID]= " & ![ReminderChildID]) = 0 Then
                    rstReminders.AddNew
                    'Initialize fields with values from defaults
                    rstReminders![ReminderChildID] = ![ReminderChildID]
                    rstReminders![Project ID] = nID
                    rstReminders![Reminder Type] = ![Outlook Item Type]
                    rstReminders![Reminder Subject] = ![Subject]
                    rstReminders![Reminder Text] = ![Body]
                    rstReminders![Invited] = ![Invite]
                    rstReminders![Email CC] = ![Email CC]
                    rstReminders!Calendar = !CalendarID
                    rstReminders!Color = !ColorID
                    Select Case ![Appt Type]
        .
        .
                        Case "First Meeting"
                            If Not IsNull(Forms(txtCallingForm)!dtFirstMeeting) Then
                                'dtStartDate will be used later to fill in Placeholder field in Subject and Body of Calendar and Email Items
                                 dtStartDate = Forms(txtCallingForm)!dtFirstMeeting
                                 rstReminders![Reminder Date] = dtStartDate + ![Date Offset]
                            Else
                                'Quit working on this reminder since it has invalid conditions
                                MsgBox "No date has been set for the " & ![Appt Type] & " so reminders cannot be created"
                                rstReminders.CancelUpdate
                                GoTo NextLoop
                            End If
                    End Select
                        .
          rstReminders.Update
                    CreateOrSend (txtCallingForm)
           .            
NextLoop:
                .MoveNext
            Loop
        End With
End Select
DoCmd.Close

End Sub

‘This code is used to create the reminder or email

Sub CreateOrSend(CallingForm)
Dim bError As Boolean
Dim strName As String
Dim strSubject As String
Dim strBody As String
Dim strType As String
Dim strAttendees As String
Dim strCC As String
Dim strColorCategory As String
Dim dtStartDate As Date
Dim dtEndDate As Date
Dim strReminderText As String
Dim strLocation As String
Dim decDuration As Single

With Forms(CallingForm)!frmCalendarReminders.Form
    'bError will be used to determine if the calendar item is created without error
    bError = False
    If !cmbReminderType = "Calendar" Then
        strName = !cmbCalendar.Column(2)
        strSubject = !txtReminderSubject
        If Not IsNull(!txtReminderNote) Then
            strBody = !txtReminderNote
        Else
            strBody = ""
        End If
        If Not IsNull(!txtInvite) Then
            strAttendees = !txtInvite
        Else
            strAttendees = ""
        End If
        strColorCategory = !cmbColor.Column(1)
        dtStartDate = !dtStartDate & " " & !dtStartTime
        dtEndDate = !dtEndDate & " " & !dtEndTime
        If Not IsNull(!txtReminderNote) Then
            strReminderText = !txtReminderNote
        Else
            strReminderText = ""
        End If
        strLocation = IIf(IsNull(.Parent!txtProjectAddressLine), ".", .Parent!txtProjectAddressLine & ", " & .Parent![Project City])
        ' Parameter Order: strName, strSubject, strBody, strAttendees, strColorCategory, dtStartDate, dtEndDate, strReminderText Optional:  strLocation, decDuration
        Call CreateCalendarAppt(bError, strName, strSubject, strBody, strAttendees, strColorCategory, dtStartDate, dtEndDate, strReminderText, strLocation)

        If bError = False Then
            !dtCreatedItem = Date
        Else
            MsgBox "***** YOUR APPOINTMENT FAILED ******"
        End If
    Else
        If Not IsNull(!txtReminderNote) Then
            strBody = !txtReminderNote
        Else
            strBody = ""
        End If
        strSubject = !txtReminderSubject
        If Not IsNull(!txtInvite) Then
            strAttendees = !txtInvite
            strCC = !txtEmailCC
            SendCustomHTMLMessages strAttendees, strCC, strSubject, strBody
            !dtCreatedItem = Date
        Else
            MsgBox "There were no email addresses to send this message to"
        End If

    End If
End With
End Sub

Answer 1

I don't agree with Wolfgang.

Of course I would suggest using MSSQL Server as backend, but with Access and the Data-Macros you can update a timestamp field in the underlying tables that updates on every change.

In addition run a script on a server (I don't knpw what intervall would be sufficent for you) every x minutes and check if row was updated since last run of script (compare timestamp)..

If true run your tasks.

If this is not an option we can talk about intercepting form-events with a class and WithEvents but this will need more effort to implement.

Answer 2

Unfortunately, there is no way to accomplish what you want. Although Access has something like "Data Macros", there is no way to to run a VBA procedure from there.

But don't be afraid of using event procedures in your forms. You don't have to copy all your existing code to each and every event procedure. You can place the existing code in a standard module, and in the forms, use very short event procedures that call these procedures in the standard modules. This still makes the main routines easy to maintain.