I have a function that logs when users run a report. It is set to run "on open". Is there a way to change the on open for all reports and forms at once?
CodePudding user response:
This code loops through all forms:
Public Sub DoChangeModules()
Dim dstApp As Application
Dim dstDB As Database
Dim AO As Document
Set dstApp = Application
Set dstDB = dstApp.CurrentDb
' iterate forms's modules and insert code
Dim f As Form
Dim M As Module
Debug.Print "Modifying Modules:"
Debug.Print "Forms:"
For Each AO In dstDB.Containers("Forms").Documents
dstApp.DoCmd.OpenForm AO.Name, acViewDesign
Set f = dstApp.Forms(AO.Name)
Set M = f.Module
ProccessModule M
Set f = Nothing
dstApp.DoCmd.Close acForm, AO.Name, acSaveYes
Next AO
End Sub
Public Sub ProccessModule(M As Module)
Dim numLine As Long
Dim StartLine As Long, StartColumn As Long, EndLine As Long, EndColumn As Long
Debug.Print , , M.Name
' insert variable declaration
M.AddFromString "Private testVar As variant ' test addition of module-level variable"
' locate or create Load event procedure
StartLine = 0
StartColumn = 0
EndLine = 0
EndColumn = 0
If Not M.Find(" Form_Open(", StartLine, StartColumn, EndLine, EndColumn) Then
numLine = M.CreateEventProc("Open", "Form")
End If
numLine = M.ProcBodyLine("Form_Open", 0) ' 0 here is vbext_pk_Proc
M.InsertLines numLine 1, "" & _
" ' Call of global function that logs user" & vbNewLine & _
" doLogUser testVar " & vbNewLine & _
" ' that function called with local module variable "
End Sub