VBA to Open Reports and Add an On Open Event 1

[dmon000]

Does anyone know of a VBA routine that will loop through all the reports in a mdb and add an on open event?

Thanks!!

 

[Remou]

Here are some notes:

   Sub AddDAO(ProcName As String)
       Dim oVBE As Object
       Dim mdl As Object
       Dim blnFound As Boolean
       Dim lngLine As Long
       Dim strMdlName As String
       Dim strDAO As String

       'To make life easier and lines shorter.
       Set oVBE = VBE.ActiveVBProject.VBComponents

       'The code to be inserted.
       strDAO = "'Requires Microsoft DAO 3.x Object Library" & vbCrLf _
       & vbTab & "Dim db As DAO.Database" & vbCrLf _
       & vbTab & "Dim rs as DAO.Recordset" & vbCrLf & vbCrLf _
       & vbTab & "Set db = CurrentDB" & vbCrLf _
       & vbTab & "Set rs = db.Openrecordset("""")" & vbCrLf

       'Check each module ...
       For Each mdl In oVBE
           'for the required procedure ...
           blnFound = oVBE(mdl.Name).CodeModule.Find("AddDAOToThisSub", 1, 1, 60, 1)
           'if it is found. ...
           If blnFound = True Then
               'get the line number ...
               lngLine = oVBE(mdl.Name).CodeModule.ProcStartLine(ProcName, vbext_pk_Proc)
               'and insert the code.
               oVBE(mdl.Name).CodeModule.InsertLines (lngLine + 2), strDAO
           End If
       Next
   End Sub

You can use the reports collection to loop through the reports.

 

[dmon000]

Remou,

Thanks for the help. I'm going to try to figure out how to get this to work. I'm a bit in over my head, but if I work at it I'll probably get there. Thanks Again!!

Dave M.