[AccessD] Refreshing open forms when something changes

OK ... I was on vacation, and am only now catching up with the AccessD
posts.

So, I'm just going to ignore the philosophical discussion, and wade in where
I am almost surely not welcome ;)

Here is how I do this:

1) Define an interface named IMessage as a standard class:

Option Explicit
Public Function Message(ByVal MyMessage As String, _
    ByRef Parameters() As Variant) As Variant
End Function

2) Implement IMessage in a form:

Implements IMessage
Private Function IMessage_Message(ByVal MyMessage As String, _
    Parameters() As Variant) As Variant
    ' ... handle message using parameters
End Function

3) Send a message to a form, and get a response, using this function in a
standard module:

Public Function SendMessageToForm(ByVal sForm As String, _
    ByVal sMessage As String, _
    ParamArray vParameters() As Variant) As Variant

    Dim rForm As Object
    Dim vParmValues() As Variant
    Dim i As Long

    On Error GoTo HandleErr

    SendMessageToForm = Empty

    ' exit if form not open
    If SysCmd(acSysCmdGetObjectState, acForm, sForm) <> acObjStateOpen Then
        Exit Function
    End If

    Set rForm = Forms(sForm)

    ' exit if form doesn't implement IMessage
    If Not TypeOf rForm Is IMessage Then
        Exit Function
    End If

    ' pack parameter values array
    vParmValues = VBA.Array()

    If UBound(vParameters) > -1 Then
        ReDim vParmValues(0 To UBound(vParameters))
        For i = 0 To UBound(vParameters)
            If IsObject(vParameters(i)) Then
                Set vParmValues(i) = vParameters(i)
            Else
                vParmValues(i) = vParameters(i)
            End If
        Next
    End If

    ' send message, get response
    SendMessageToForm = rForm.Message(sMessage, vParmValues())

    Set rForm = Nothing
    Exit Function

HandleErr:
    Err.Raise Err.Number, "SendMessageToForm" & vbCrLf & Err.Source,
Err.Description
End Function


This is generic and flexible, and you can easily implement a change
notification scheme, passing whatever parameters the other forms need to
sync up.

This needn't clutter up your form code, because the message could be the
name of a public handler function, which you invoke using Application.Run

Heck, any class can implement IMessage, making it useful for more than just
forms.

-Ken