[AccessD] Ribbon context

[Bill Benson]

Writing an onActivate event into every form is a walk in the park. Just add
a reference to
    Microsoft Visual Basic for Applications Extensibility 5.3.


Below is a procedure that would add a Form_Activate event to every
form, check if there is already a line saying Call MyProcedure, if not add
that line of code before the End Sub. If no Form_Activate, it will add such
a procedure with that line of code.

Save your project before running it; and do a Debug.Compile afterwards to
make sure that nothing went wrong unexpectedly.

You will have to manually save after the code is checked.
Note, it is not easy to debug running code that is inserting code lines, so
test this on a small database or put in a loop controller to perform the
action on the first N forms, to make sure it works for you.

Sub PutInOnActivate()
Const Form_Activate         As String = "Private Sub Form_Activate()"
Const LINE_LENGTH           As Integer = 27

Dim vbComp                  As VBIDE.VBComponent
Dim vbMod                   As VBIDE.CodeModule
Dim Form                    As Object
Dim Container               As Object
Dim strAllForms             As String
Dim iLine                   As Long
Dim jLine                   As Long
Dim strLine                 As String
Dim strNextLine             As String
Dim bLineAdded              As Boolean

For Each Container In CurrentDb.Containers
    If Container.Name = "Forms" Then
        Exit For
    End If
Next

For Each Form In Container.Documents
    strAllForms = strAllForms & "|Form_" & Form.Name
Next
If strAllForms <> "" Then
    strAllForms = strAllForms & "|"
    For Each vbComp In Application.VBE.VBProjects(1).VBComponents
        If InStr(strAllForms, "|" & vbComp.Name & "|") > 0 Then
            With vbComp.CodeModule
                For iLine = 1 To .CountOfLines
                    strLine = Trim(.Lines(iLine, 1))
                    If Left(strLine, LINE_LENGTH) = Form_Activate Then
                        bLineAdded = False
                        For jLine = iLine + 1 To .CountOfLines
                            strNextLine = Trim(.Lines(jLine, 1))
                            If Left(strNextLine, 7) = "End Sub" Then
                                .InsertLines jLine, vbTab & "Call
MyProcedure"
                                GoTo NextForm
                            ElseIf Left(strNextLine, Len("Call
MyProcedure")) = "Call MyProcedure" Then
                                GoTo NextForm
                            End If
                        Next
                        .InsertLines iLine + 1, vbTab & "Call MyProcedure"
                        GoTo NextForm
                    End If
                Next
                .InsertLines iLine, "Private Sub Form_Activate()"
                .InsertLines iLine + 1, vbTab & "Call MyProcedure"
                .InsertLines iLine + 2, "End Sub"
            End With
        End If
NextForm:
    Next
End If
End Sub



On Mon, Jan 24, 2022 at 7:38 PM Ryan W <wrwehler at gmail.com> wrote:

> In Access I’m trying to disable some ribbon buttons based on the form in
> question and permissions to delete records on that form.
>
> I’ve got the getEnabled logic worked out and it disables the delete button
> on the ribbon when I invalidate the control via the OnActivate event for
> the form (so it runs again if the form tab is switched back).
>
> My issue is we have hundreds of forms that would need an OnActivate event
> added so the delete button can be contextually controlled.
>
>
> Is there a better way to do this that isn’t a really fast timer event or
> some sort of class for handling that event on all forms (can you even
> instantiate a class object on a closed form?)
>
>
>
> Right now the delete button always shows but the code behind it will not
> succeed if the back end tables don’t grant the permissions, it would just
> be nice to sort of give a visual cue that the action isn’t possible.
>
>
> Sent from my iPhone
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>